One of the important requirements for creating dynamic reports are variables. Variables can store values in the memory and can be accessed by name. We're going to create these variables by using the Variable Manager tool that is accessed from the BI Administration Tool window. There are two different types of variables:
Repository variables: They persist in the memory when the BI server service is started and until it's shutdown. They have two forms, static and dynamic. Static variables are like constants. You cannot change their value. Dynamic repository variable values are set when the BI service is started. Then you can easily change their value. Only one instance is created for repository variables and their values can be set by creating an initialization block. These initialization blocks can also be scheduled in order to reset the values.
Session variables: They persist during a user's session period. There are two types. One of them is called a system variable and the other is called a non-system variable. Unlike repository variables, session variables have many instances based on the number of the sessions. We can use initialization blocks in order to set their value in the beginning. But unlike repository initialization blocks, these blocks can be rescheduled.
Open the Variable Manager block from the Manage menu.
When the Variable Manager is opened, you'll see that variables are grouped into two main categories, Repository and Session.
Create a repository variable as an example. We're going to click on the Action menu and go to New | Repository variable, and it's going to open a new variable window. As you'll see there are two options for the type of variable, static and dynamic. Let's assume that we need dynamic variables. So in this window we're going to click on the New... button.
This time a new Initialization Block window is going to open. First we will schedule this block regarding when it's going to be executed in order to set the variable's value. We're going to use
1hour as the period.
The second important configuration is editing the data source. When you click on the Edit Data Source... button, you'll have to specify the Connection Pool name and the query that you want to execute.
You can also test your query by clicking on the Test... button. The output will be similar to the following screenshot:
At the end, you'll see that both the dynamic repository variable and the initialization block are created. It's also a good practice to create different connection pools for the initialization blocks.
We covered the repository variables in this recipe. Variable object definitions are very important from both the end user's and the BI developer's perspectives. When it comes to dynamic reports, we'll need to use them. Business users can access these variables from the Presentation Services. BI developers should share these variables with the business users because we do not have a list of variables on the Presentation Service.
These variables are accessible either from the repository or from the Presentation Services. In order to access the variables from the repository, we'll have to use the VALUEOF function. Let's assume that there's a variable named Current_Year. If you want it to have a dynamic design in the repository, you'll call the variable as follows:
VALUEOF("Current_Year")This statement is going to return the value of the Current_Year variable. The VALUEOF function is expecting one argument, that's the name of the variable. Let me remind you again that variable names are case sensitive. When it comes to accessing them in the Presentation Services, we'll just write the name of the variable and we won't use the VALUEOF function.