Steps to Use Dynamic Server name in the OpenDoc URL when moving reports from one environment to other
This is a document to show how we can maintain the server name in the OpenDoc Hyperlink across different environments like DEV, SIT, UAT, and PROD. It’s often seen that when we create a webi report with hyperlink in DEV environment and then move the report to UAT environment then we have to go and change the hyperlink and change the server name to UAT so that the target report opens from UAT instead of DEV. We can overcome this by setting few parameters in the database and use them in the reports.
1. In the DEV database we can create a parameters table with two columns. One is Parameter name and other is key value. Enter parameter name like “Finance reports” and then enter the key value of the parameter as the basic OPENDOC URL as below against the “Finance reports”
Here the server name is highlighted in Bold and its pointing to DEV.
Now similarly in the UAT database the same table should exists but the key value of the parameter name (Finance reports) should be
Below is the example of the parameter table which has the name and the key value for different parameters as per requirement.
In this way if we have multiple environments/databases then the URL will have different server names and its prefix will change as
dev for development environment,
sit for system integration testing environment
uat for user acceptance testing environment
prd for production environment.
2. Now in the universe we have to use this table as a standalone table with no join to any other tables as shown below Image below has the table selected (hence shown in blue color) and it can be observed that it’s not joined to any other table present in the universe.
This table will be used to create an object in universe based on the parameter name and its key value.
3. Now we create a detail object based on this table in the universe, the screenshot below will show the detail object created
Its observed that the select clause will have the key value in the select and the where will have the parameter name which we want to use as per our requirement.
This object will give us the Key value (i.e. the basic OpenDoc URL as mentioned in point 1). So it will give the output based on the connection of the universe to which database this is pointing to. So for example if the universe is in DEV then it should be pointing to DEV database and we will get the URL which has the server name prefix as DEV or
If the universe is in UAT then it should be pointing to UAT database and we will get the URL which has the server name prefix as UAT as shown in the point 1. (it is assumed that when the universe is migrated from DEV to UAT then the universe connection in UAT is working fine)
The purpose of the where clause is that in real life situations there can be different universes for different verticals. Like for example the Finance people will have different universe and the HR people will have different universe though the database remains same. Hence we can have different parameter names
4. Now we can export the universe after creating the detail object as shown above and then we can move to the Webi report created on this universe. In the Webi report we have to use this detail object and create a variable based on this object as shown below.
Our purpose is to fetch only the server name from the complete key value, so we fetch it from the first position till the server name ends.
For example if the below key value is the output (present in the database)
And after applying the formula as shown in the variable then the output will be
so after using the variable the OpenDoc URL can be designed like below
Now, If the report is refreshed by developers in the DEV environment then the URL will be as
If the report is refreshed in PRD (Production) then the URL will be
Ultimately whatever report this URL points to(as per the iDocID), it will open the report of/in the same environment. We don’t have to manually go and change the server name every time we move the reports from one environment to other. This will help save lot of time when testing reports in UAT testing or anyother purpose