Support Parameterization from Analysis Services - Page 6

February 26, 2008

Reference Analysis Services Support within Report Parameters

We will return to each of the three Report Parameters at this juncture, where we will reference each of the supporting datasets we created in the previous subsection within the respective Dataset, Value field, and Label field settings. We will also make a few additional changes to the pre-existing settings to make the Report Parameters a bit more user-friendly at runtime.

1.  From the main menu, select Report ->Report Parameters, as depicted in Illustration 44.


Illustration 44: Select Report ->Report Parameters

The Report Parameters dialog opens.

2.  Within the Parameters list box, on the left side of the dialog, click DateCalendarYear.

The Properties, Available values, and Default values settings for the selected Report Parameter appear.

3.  Within the Properties section on the right side of the dialog, change the Prompt setting from Date.Calendar Year to the following:

Year

4.  Ensure that all five checkboxes underneath the Prompt setting are unchecked.

5.  Within the Available values section, underneath the Properties section, ensure that the From query radio button is selected.

6.  Select AS_DateYrParams_Support in the Dataset selector, as shown in Illustration 45.


Illustration 45: Select AS_DateYrParams_Support as the Dataset ...

7.  Underneath the Dataset selector, select the following in the Value field selector:

YearParamValue

8.  Underneath the Value field selector, select the following in the Label field selector:

YearParamCaption

9.  Within the Default values section, underneath the Available values section, ensure that the radio button to the immediate left of Null is selected.

The Properties, Available values, and Default values settings for the DateCalendarYear Report Parameter appear, with our modifications, as depicted in Illustration 46.


Illustration 46: DateCalendarYear Report Parameter Settings, with Modifications

10.  Within the Parameters list box, as before, click DateCalendarQuarterofYear.

The settings for the selected Report Parameter appear.

11.  Within the Properties section on the right side of the dialog, change the Prompt setting from Date.Calendar Quarter of Year to the following:

Quarter

12.  Ensure that all five checkboxes underneath the Prompt setting are unchecked.

13.  Within the Available values section, underneath the Properties section, ensure that the From query radio button is selected.

14.   In the Dataset selector, select the following:

AS_DateQtrParams_Support

15.  Underneath the Dataset selector, select the following in the Value field selector:

QtrParamValue

16.  Underneath the Value field selector, select the following in the Label field selector:

QtrParamCaption

17.  Within the Default values section, underneath the Available values section, ensure that the radio button to the immediate left of Null is selected.

The Properties, Available values, and Default values settings for the DateCalendarQuarterofYear Report Parameter appear, with our modifications, as shown in Illustration 47.


Illustration 47: DateCalendarQuarterofYear Report Parameter Settings, with Modifications

18.  Within the Parameters list box, once more, click DateMonthofYear.

The settings for the selected Report Parameter appear.

19.  Within the Properties section on the right side of the dialog, change the Prompt setting from Date.Month of Year to the following:

Month

20.  Ensure that all five checkboxes underneath the Prompt setting are unchecked.

21.  Within the Available values section, underneath the Properties section, ensure that the From query radio button is selected.

22.  In the Dataset selector, select the following:

AS_DateMoParams_Support

23.  Underneath the Dataset selector, select the following in the Value field selector:

MoParamValue

24.  Underneath the Value field selector, select the following in the Label field selector:

MoParamCaption

25.  Within the Default values section, underneath the Available values section, ensure that the radio button to the immediate left of Null is selected.

The Properties, Available values, and Default values settings for the DateMonthofYear Report Parameter appear, with our modifications, as depicted in Illustration 48.


Illustration 48: DateMonthofYear Report Parameter Settings, with Modifications

26.  Click OK to accept all our Report Parameter modifications, and to dismiss the Report Parameters dialog.

As a final cleanup item, we will remove the datasets created automatically by Reporting Services to support the parameters we created as a part of preparation for our practice session (when we added the parameterized filters to the report). We can accomplish this by taking the following steps within the Data tab.

27.  Click the downward selector arrow on the right side of the Dataset selector.

We note that the three automatically created datasets appear, including DateCalendarYear, DateCalendarQuarterofYear, and DateMonthofYear.

28.  Select the DateCalendarYear dataset within the selector.

29.  Once the dataset is selected, click the Delete Selected Dataset button to the right of the selector, as shown in Illustration 49.


Illustration 49: Deleting an Unwanted Dataset ...

30.  Click Yes on the message box that appears, asking if we are sure we want to perform the deletion, as depicted in Illustration 50.


Illustration 50: Confirm Wishes to Delete with a “Yes” ...

The automatically created dataset DateCalendarYear disappears.

31.  Delete the DateCalendarQuarterofYear and DateMonthofYear datasets in the same manner.

Having created the parameter support structures within Analysis Services, we added supporting datasets, based upon those structures, within our sample report. We then referenced those datasets to the respective Report Parameters, and, as a cleanup step, removed the datasets that Reporting Services had automatically created for each parameterized filter we set up as a part of our preparation for the practice session. We are now ready to verify the effectiveness of our solution. We will verify that the date-related Report Parameters – now supported by objects within the Analysis Services layer of our integrated BI solution – perform as expected at runtime.








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers