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 4

By William Pearson

Practice

Practice: Add Parameter Support Objects to the Analysis Services Layer

We will next add parameter support within the Analysis Services database upon which our sample report is based. To do so, we will access the Cube Designer within the Business Intelligence Development Studio, taking the following steps:

1.  Right-click the Adventure Works cube within the Solution Explorer.

2.  Select Open from the context menu that appears, as depicted in Illustration 14.


Illustration 14: Opening the Cube Designer ...

Add Calculated Members in Analysis Services to Support Parameters within Reporting Services

As we learned in Reporting Services: Customize Automatically Created Parameter Support Objects and Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets, as well as in other recent articles within our series, when we create parameterized filters within our dataset(s), Reporting Services automatically creates support objects for self-contained parameterization at the reporting layer. These support objects include Report Parameters, such as those we have examined in our preparation steps above, as well as datasets to support each of those Report Parameters. The datasets use the WITH MEMBER keywords to create calculated members at runtime, when the datasets are instantiated in Reporting Services, which are then referenced, by default, in the Value and Label fields within the associated Report Parameter settings page.

Because we took the shortcut approach of having Reporting Services generate the support objects automatically in our creation of the date-related parameters earlier, we not only have the Report Parameters in place, ready to be pointed to the calculated members we will create in Analysis Services, but we also have a useful set of calculated member definitions already in place upon which we can easily model our Analysis Services calculated members. Let’s take a look at the DateCalendarYear dataset for starters, as we create the calculated members we will need in Analysis Services. In our practice session, we will only create those we actually intend to use in the associated parameters of our report, ignoring the additional calculated members that are provided in the automatically created datasets. We could, of course, include all, or create custom calculated members that are entirely different, should the need arise in our local business environments.

We review the requirements with the client representatives, reiterating that, while parameterization can certainly be managed using the automatically created objects provided within Reporting Services, several benefits accrue to us in creating the support objects within the Analysis Services layer. Positing the intelligence within the cube will, we assert, mean that the same logic can be carried forward to multiple reports by simply referencing the calculations in their respective data sources, versus performing the calculations for every affected measure in the report (and adding to processing time, etc.). Moreover, we emphasize that maintaining the logic in a single location in the Analysis Services layer means a single point of maintenance: we can modify the logic in one place and rely upon the changes to “ripple through” to all reports that reference that logic, versus having to mechanically modify each individual report. This also ensures consistency of application of the logic to all affected reports: None will be overlooked for prospective modifications in the logic, for instance, when modifications become necessary, nor will different logic be accidentally applied to different reports.

From within the now open Cube Designer, we will begin creating calculated members to support parameterization in our practice report.

1.  Select the Calculations tab within the Cube Designer.

The Calculations tab opens.

2.  Click the bottom entry in the Script Organizer pane, to anchor the cursor, as shown (the name of the bottom item will likely differ in your own environment) in Illustration 15.


Illustration 15: Click the Bottom Entry in the Script Organizer to Position the New Calculation

3.  Click the New Calculated Member button atop the Calculations tab, as depicted in Illustration 16.


Illustration 16: Click the New Calculated Member Button

Clicking the New Calculated Member button adds a new calculation to the Script Organizer pane (by default named “[Calculated Member]”) and displays fields for its definition in the calculations form in the Calculations Expressions pane.

4.  Leaving the Cube Designer open at the Calculations tab, return to the Data tab of the report with which we are working in Reporting Services.

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

6.  Select the DateCalendarYear dataset within the selector.

The MDX for the dataset, which was automatically generated as one of a chain of events triggered by our creation of the parameterized filter for Year in the report’s primary dataset (ProductData), appears (rearranged, so as to make examination of the various sections easier), as shown in Illustration 17.


Illustration 17: MDX for the DateCalendarYear Dataset

Many will recognize the WITH MEMBER clauses as representing the definition of various calculated members, which we also specify within our SELECT clause for the purposes of supporting our parameter defaults. These work the same as the calculated members that appeared within the automatically generated dataset queries that we examined in Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I and Pt. II.

NOTE: For and introduction to Calculated Members, particularly from an MDX syntax perspective, please see the following articles:

Each of these articles is a member of my MDX in Analysis Services series at Database Journal.

In this dataset, the following calculated members are created:

  • ParameterCaption
  • ParameterValue
  • ParameterLevel

We will, as we have already noted, not be referencing these fields in the DateCalendarYear Report Parameter definitions, but the underlying MDX for the parameter caption and value can be used as a template to create a calculated member in Analysis Services, as we shall see. The following portion of the MDX is of immediate use to us in our next steps.

 WITH 
 MEMBER 
    [Measures].[ParameterCaption] 
 AS 
    '[Date].[Calendar Year].  CURRENTMEMBER.MEMBER_CAPTION' 
 
 MEMBER 
    [Measures].[ParameterValue] 
 AS 
    '[Date].[Calendar Year].CURRENTMEMBER.UNIQUENAME'

7.  Return to the Calculations tab of the Cube Designer.

8.  Type the following into the Name box of the currently open calculations form in the Calculations Expressions pane (replacing the default name of “[Calculated Member]”).

YearParamCaption

9.  Type (or cut and paste) the following into the Expression box of the expanded Expression section below:

[Date].[Calendar Year]. CURRENTMEMBER.MEMBER_CAPTION

The new YearParamCaption calculation appears in the Calculations Expressions pane, with our input, as depicted in Illustration 18.


Illustration 18: The YearParamCaption Calculation in the Calculations Expressions Pane

10.  Click the Check Syntax button atop the Calculations tab, as shown in Illustration 19.


Illustration 19: Click the Check Syntax Button

The Check Syntax message box appears, indicating that the syntax check was successful, as depicted in Illustration 20.


Illustration 20: Our Syntax Appears to Have Passed Checking ...

11.  Click OK to dismiss the message box.

We have successfully added support for the caption display of the Year Report Parameter. We will now add a calculated member to house the logic behind the value (the “qualified” MDX name) we wish to pass for the same Report Parameter, based upon our caption selection at runtime.

12.  From our current position within the Calculations tab, once again, click the bottom entry in the Script Organizer pane, to anchor the cursor.

NOTE: Once a calculation is created, we can always move it up or down within the Script Organizer pane using the Move Up or Move Down buttons, respectively. These buttons appear together atop the Calculations tab, as shown in Illustration 21.


Illustration 21: Move Up and Move Down Buttons, atop the Calculations Tab

13.  Click the New Calculated Member button, once again.

14.  Type the following into the Name box of the currently open calculations form in the Calculations Expressions pane (replacing the default name of “[Calculated Member]”).

YearParamValue

15.  Type (or cut and paste) the following into the Expression box of the expanded Expression section below:

 [Date].[Calendar Year].CURRENTMEMBER.UNIQUENAME

The new YearParamValue calculation appears in the Calculations Expressions pane, with our input, as depicted in Illustration 22.


Illustration 22: The YearParamValueCalculation in the Calculations Expressions Pane

16.  Click the Check Syntax button atop the Calculations tab to ascertain syntactically correct input, as we did for the first calculated member we added.

17.  Click OK to dismiss the message box indicating the successful syntax check has occurred.

We have successfully added support for the value display of the Year Report Parameter. This means we have now added support for both the caption and value fields of the Report Parameter within our report for Year. We will next add, while we are “under the hood” within Analysis Services, a similar pair of calculated members for each of the two remaining, date-related Report Parameters we have selected for our practice exercise, DateCalendarQuarterofYear and DateMonthofYear.

18.  Click the New Calculated Member button, once again.

19.  Type the following into the Name box of the currently open calculations form in the Calculations Expressions pane (replacing the default name of “[Calculated Member]”).

QtrParamCaption

20.  Type (or cut and paste) the following into the Expression box of the expanded Expression section below:

 [Date].[Calendar Quarter of Year].CURRENTMEMBER.MEMBER_CAPTION

The new QtrParamCaption calculation appears in the Calculations Expressions pane, with our input, as shown in Illustration 23.


Illustration 23: The QtrParamCaption Calculation in the Calculations Expressions Pane

21.  Click the Check Syntax button atop the Calculations tab to ascertain syntactically correct input, as we did for the first pair of calculated members we added.

22.  Click OK to dismiss the message box indicating the successful syntax check has occurred.

23.  Click the New Calculated Member button, once again.

24.  Type the following into the Name box of the currently open calculations form in the Calculations Expressions pane (replacing the default name of “[Calculated Member]”).

QtrParamValue

25.  Type (or cut and paste) the following into the Expression box of the expanded Expression section below:

[Date].[Calendar Quarter of Year].CURRENTMEMBER.UNIQUENAME

The new QtrParamValue calculation appears in the Calculations Expressions pane, with our input, as depicted in Illustration 24.


Illustration 24: The QtrParamValue Calculation in the Calculations Expressions Pane

26.  Click the Check Syntax button atop the Calculations tab to ascertain syntactically correct input, as we did for the calculated member we added earlier.

27.  Click OK to dismiss the message box indicating the successful syntax check has occurred.

28.  Click the New Calculated Member button, once again.

29.  Type the following into the Name box of the currently open calculations form in the Calculations Expressions pane (replacing the default name of “[Calculated Member]”).

MoParamCaption

30.  Type (or cut and paste) the following into the Expression box of the expanded Expression section below:

[Date].[Month of Year].CURRENTMEMBER.MEMBER_CAPTION

The new MoParamCaption calculation appears in the Calculations Expressions pane, with our input, as shown in Illustration 25.


Illustration 25: The MoParamCaption Calculation in the Calculations Expressions Pane

31.  Click the Check Syntax button atop the Calculations tab to ascertain syntactically correct input, as we did for the other calculated members we added.

32.  Click OK to dismiss the message box indicating the successful syntax check has occurred.

33.  Click the New Calculated Member button, once again.

34.  Type the following into the Name box of the currently open calculations form in the Calculations Expressions pane (replacing the default name of “[Calculated Member]”).

MoParamValue

35.  Type (or cut and paste) the following into the Expression box of the expanded Expression section below:

 [Date].[Month of Year].CURRENTMEMBER.UNIQUENAME

The new MoParamValue calculation appears in the Calculations Expressions pane, with our input, as depicted in Illustration 26.


Illustration 26: The MoParamValue Calculation in the Calculations Expressions Pane

36.  Click the Check Syntax button atop the Calculations tab to ascertain syntactically correct input, as we did for the first calculated member we added.

37.  Click OK to dismiss the message box indicating the successful syntax check has occurred.

We have completed the addition of the Analysis Services objects required to support the targeted date-related Report Parameters in the Reporting layer.



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