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”

       http://dev.boserver.net/OpenDocument/opendoc/openDocument.jsp?

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

       http://uat.boserver.net/OpenDocument/opendoc/openDocument.jsp?

Below is the example of the parameter table which has the name and the key value for different parameters as per requirement.

    /wp-content/uploads/2014/07/1_488811.png

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.

/wp-content/uploads/2014/07/2_488890.png

     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

/wp-content/uploads/2014/07/3_488858.png

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.

/wp-content/uploads/2014/07/4_488924.png

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)

http://dev.boserver.net/OpenDocument/opendoc/openDocument.jsp

And after applying the formula as shown in the variable then the output will be

http://dev.boserver.net/

so after using the variable the OpenDoc URL can be designed like below

/wp-content/uploads/2014/07/5_488904.png

Now, If the report is refreshed by developers in the DEV environment then the URL will be as

=a href=http://dev.boserver.net/OpenDocument/opendoc/openDocument.jsp?sIDType=CUID&iDocID=AZFZ

If the report is refreshed in PRD (Production) then the URL will be

=a href=http://prd.boserver.net/OpenDocument/opendoc/openDocument.jsp?sIDType=CUID&iDocID=AZFZ

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

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

Leave a Reply