11 MDX for Reporting Services

1
11 MDX for Reporting Services
2
MDX Queries in Reporting Services

• Reporting Services includes a query builder for
  MDX, but also allows you to enter your own MDX
  queries
• However
• There are some queries that cant be constructed
  using the query builder
• You may also need to alter the MDX generated by
  the query builder to improve performance
• Reporting Services automatically hides some data
  in queries that you may want to display

3
Reporting Services Limitations

• Reporting Services also imposes a major
  limitation on MDX when you use a data source of
  type Microsoft SQL Server Analysis Services
• It is that you can only enter queries which have
  the Measures dimension on the columns axis
• This is not the case when you connect through an
  OLE DB source
• But doing this means you cant use the query
  builder or parameters

4
Parameterising Measures

• To be able to parameterise a query by Measures,
  you first need a query which shows measures on
  rows to populate the parameter.
• To do this you need to choose an attribute with
  more members on than there are measures, and
  write a query as follows

5
Measures on Rows Example

• WITH
• SET MYSET AS
• HEAD(
• Date.Date.Date.MEMBERS,
• COUNT(MEASURES.ALLMEMBERS) - 1
• )
• MEMBER MEASURES.MeasureUniqueName AS
• MEASURES.ALLMEMBERS.ITEM (
• RANK(Date.Date.CURRENTMEMBER, MYSET) -
  1
• ).UNIQUENAME
• MEMBER MEASURES.MeasureDisplayName AS
• MEASURES.ALLMEMBERS.ITEM (
• RANK( Date.Date.CURRENTMEMBER, MYSET) -
  1
• ).NAME
• SELECT
• MEASURES.MeasureUniqueName,
• MEASURES.MeasureDisplayName
• ON 0, MYSET ON 1 FROM Adventure Works

6
Parameterising Measures

• You then need to create a calculated measure to
  display on the columns axis whose value is
  parameterised
• Reporting Services needs to have static columns
  for its queries
• You also need to create a query parameter using
  the Query Parameters button in the MDX toolbar
• The resulting query might look as follows

7
Query Parameterised by Measures

• WITH
• MEMBER MEASURES.SELECTEDMEASURE AS
• STRTOMEMBER(_at_MyMeasure, CONSTRAINED)
• SELECT
• NON EMPTY
• MEASURES.SELECTEDMEASURE
• ON COLUMNS,
• NON EMPTY
• Date.Calendar Year.Calendar
  Year.ALLMEMBERS
• DIMENSION PROPERTIES MEMBER_CAPTION,
  MEMBER_UNIQUE_NAME
• ON ROWS
• FROM Adventure Works
• CELL PROPERTIES VALUE

8
Demonstration 11A

• Parameterising by Measures
• Creating a report parameterised by Measures in
  Report Designer

9
Displaying Aggregated Values

• Pre-SP2, when you use an MDX query that displays
  data at more than one level of granularity in
  Reporting Services, you only see the lowest level
  of granularity by default
• You can see higher levels of granularity by
• Adding groups to your table or matrix
• Making sure that the group expression uses the
  Reporting Services Aggregate() function and not
  Sum(), otherwise non-additive values will be
  summed

10
Displaying Aggregated Values

• Post SP2, if you reference any field from an
  Analysis Services sourced dataset in the
  Aggregate function, nothing changes
• Otherwise all the rows in the query become
  visible in the report
• The danger is that reports designed pre-SP2 which
  dont use the Aggregate function may display
  unwanted rows post-SP2

11
blogroll

• Displaying Aggregated Values
• http//www.artisconsulting.com/Blogs/tabid/94/Entr
  yID/1/Default.aspx

12
Extended Properties

• By default, Report Designer adds a number of cell
  properties to all MDX queries
• Note that on very large queries, removing
  unnecessary properties can lead to improved query
  performance
• These can be accessed in your reports as
  Extended Properties of each field
• Eg Fields!Sales("FORMAT_STRING")
• Note that this is only works at the lowest level
  of granularity in the resultset

13
Demonstration 11C

• Using Extended Properties
• Creating a report that uses Extended Properties
  in Report Designer

14
Breaking free of RS/AS limitations

• Lets read blog and watch screencast to get some
  SQL2008 insight
• But lets also think outside the box
• RS supports calling SOAP with parameters
• In a SOAP method, we can instantiate an arbitrary
  MDX template and tabularize the results