This question keeps repeating so many times that i thought this in itself deserves a seperate blog entry. There are 2 parts to this question. They are
1. Updating Session Variables from Answers
2. Updating Session Variables using Presentation Variables from a Dashboard Prompt
2. Updating Session Variables using Presentation Variables from a Dashboard Prompt
The first one is pretty straightforward. Lets start with a simple example. Lets create a simple session variable called Test_Var and make that variable to be initialized from an init block. Enable that variable to be set by any user. If you are using row-wise initialization then by default the variable will allow any user to set the variable.
Now, go to Answers and create a report which will show the Session Variable.
Now, as you see the session variable has a value of Test. Now, in order to update this variable, go to the Advanced Tab->Advanced SQL Clauses in Answers. And enter the below formula in the Prefix section (Do not click on Set SQL. This is executed when you move away from this tab)
SET VARIABLE Test_Var=’Test1′;
Now, also ensure that you are bypassing Presentation Services Cache.
If you go to the Results tab, you should notice that your Session Variable Value would have changed.
This has a lot of use cases. For example, you can set LOGLEVEL variable dynamically for every report. I shall also be blogging about other possible use cases later this week. This was pretty straightforward. But what if we want to set these variables from a dashboard prompt. In order to do this, lets create a simple dashboard prompt on any column and lets make it to set a presentation variable named Test
Now go back to the report that we created above and navigate to Advanced Tab. Now in the prefix type in the below formula
SET VARIABLE Test_Var=’@{Test}’;
Now, save this report and go to the dashboard and include the prompt and the report that we created. Enter any value in the Edit Prompt and click on go. You would notice that the Session Variable Value changes based on the value that was entered in the dashboard prompt.
This is again very useful for making update calls from the presentation layer to the BI Server.
Posts
