If you had gone through my blog entry here, you would have known how to go about dynamically varying the columns using presentation variables and dashboard prompts. But say you have a scenario wherein you are integrating BI EE into an external application using GO URL, then the above procedure would not be valid since presentation variables would work only from dashboards. Now, lets look at an approach wherein we can dynamically vary the columns in a report using GO URL. This approach is based on yesterday’s blog entry here. Our idea is to create a simple report wherein there would be 2 columns. One column will be dynamic based on what we pass in the GO URL and the other would be static. In our example, we shall be making use of a session variable called DSN while shall be initialized to some value through an init block. Now, lets create a simple report with 2 columns. One column would have the below formula
CASE WHEN VALUEOF(NQ_SESSION.DSN) = ’0′ THEN Products.Type WHEN VALUEOF(NQ_SESSION.DSN) = ’1′ THEN Products.Brand ELSE Products.Color END
So, basically what this does is, it chooses the columns based on the value of the session variable. The value of the session variable will be varied from the GO URL.
The other column will be a static column (Sales).
Now, save this report. Ensure that you have added the Session variable to the instanceconfig.xml using the procedure here. Now, if you issue the below GO URLs the report columns will change automatically.
http://localhost:9704/analytics/saw.dll?Go&nquser=Administrator&nqpassword=Administrator&Path=/shared/Paint%20Demo/GO%20URL%20-%20Vary%20Session%20Variable/Report1&Options=rmf&DSN=0
http://localhost:9704/analytics/saw.dll?Go&nquser=Administrator&nqpassword=Administrator&Path=/shared/Paint%20Demo/GO%20URL%20-%20Vary%20Session%20Variable/Report1&Options=rmf&DSN=1
The disadvantage of this approach is that you will be losing the default hierarchy drills. You can still use Navigate to achieve the drills though.
Posts
