[OBIEE 10g] Dynamic Repository Variable

Dynamic repository variable

To create a dynamic repository variable, perform the following steps:

1.	Return to the SH repository open in online mode.
2.	Click Manage > Variables to open the Variable Manager.
3.	Click Repository > Initialization Blocks.
4.	Right-click the white space and select New Initialization Block to open the Repository Variable Init Block dialog box.
5.	Name the initialization block getMaxSalesDate.
6.	Click Edit Data Source to open the Repository Variable Init Block Data Source dialog box.
7.	Click the Browse button to open the Select Connection Pool dialog box.
8.	Double-click the SH > Connection Pool object to add it to the Connection Pool field in the Repository Variable Init Block Data Source dialog box.
9.	In the Default Initialization String field, type the following SQL: select TIME_ID, CALENDAR_YEAR, CALENDAR_MONTH_DESC, CALENDAR_MONTH_ID from TIMES WHERE TIME_ID = (select max(TIME_ID) from SALES)
10.	Click OK to close the Repository Variable Init Block Data Source dialog box. The connection pool and initialization string are added to the Repository Variable Init Block dialog box.
11.	Click Edit Data Target to open the Repository Variable Init Block Variable Target dialog box.
12.	Use the New button to create four variables: maxSalesDate, maxYear, maxMonthDesc, and maxMonthID. The order is important. The order of the variables must match the column order in the initialization string.
13.	Click OK to close the Repository Variable Init Block Variable Target dialog box. The variables appear in the Variable Target field in the Repository Variable Init Block dialog box.
14.	Click Edit Data Source to open the Repository Variable Init Block Data Source dialog box.
15.	Click Test and verify you get the results in the picture.
16.	Close Results.
17.	Click OK to close the Repository Variable Init Block Data Source dialog box.
18.	Click OK to close the Repository Variable Init Block dialog box. The getMaxSalesDate initialization block is displayed in the Variable Manager.
19.	Select Repository > Initialization Blocks > Variables > Dynamic to see the variables displayed in the Variable Manager.
20.	Click Action > Close to close the Variable Manager.
21.	Check in changes.
22.	Select File > Check Global Consistency. If the Consistency Check Manager displays any errors, edit the repository to correct the errors before continuing. If there are no error messages, close the Consistency Check Manager.
23.	Save the repository.
24.	Return to Answers.
25.	Build the following query: Calendar.Calendar Year, Sales Facts.Amount Sold.
26.	Click the Add Filter button for the Calendar Year column.
27.	In the Create/Edit Filter dialog box, click Add > Variable > Repository.
28.	In the Server Variable field, type maxYear.
29.	Click OK to close the Create/Edit Filter dialog box. The filter is added to the request.
30.	Click Results and verify that Calendar Year returns the expected result.