Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Feb 26, 2008

Support Parameterization from Analysis Services - Page 6

By William Pearson

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM