Master Data Services Tutorial

I’m on vacation this week and next so I’ll just let you know that if you want a quick overview of SQL Server 2008 R2 Master Data Services there’s a series of 15 minute tutorials on msdev.com.  Here’s the link
http://www.msdev.com/Directory/SeriesDescription.aspx?CourseId=155
The Master Data Services team also has a blog with links to even more info.
 http://blogs.msdn.com/b/mds/
My next posting should be Sept. 28th.

Adding Parameter for Analysis Services Data Source in SSRS

I recently need to add a parameter to a report using the Analysis Services Data Source.  I didn’t find the process to be straight forward so I’m documenting the steps I took.

1. Go into the query designer’s MDX query  mode. 
2. Click the Query Parameters button on the menu bar.
3. Enter parameter, dimension, hierarchy, and default values.  Click OK.
4. Add the parameter to the query.  Click OK.   
5. I don’t want to give the user the choice of every member in the hierarchy so I have to prevent the parameter from auto updating.  To do this switch to the report's code view.
6. Find the entry for the parameter’s dataset and add SuppressAutoUpdate
   

   </DataSet>
    <DataSet Name="EnrolledFromYear">
      <Fields>
        <Field Name="ParameterValue">
          <DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="Measure" UniqueName="[Measures].[ParameterValue]" /&gt;</DataField>
        </Field>
        <Field Name="ParameterCaptionIndented">
          <Value>=Space(3*Fields!ParameterLevel.Value) + Fields!ParameterCaption.Value</Value>
        </Field>
        <Field Name="ParameterCaption">
          <DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="Measure" UniqueName="[Measures].[ParameterCaption]" /&gt;</DataField>
        </Field>
        <Field Name="ParameterLevel">
          <DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="Measure" UniqueName="[Measures].[ParameterLevel]" /&gt;</DataField>
        </Field>
      </Fields>
      <Query>
        <DataSourceName>EdDWDataMart</DataSourceName>
        <CommandText>WITH MEMBER [Measures].[ParameterCaption] AS [Enrolled From Date].[Academic Calendar].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] AS [Enrolled From Date].[Academic Calendar].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [Enrolled From Date].[Academic Calendar].CURRENTMEMBER.LEVEL.ORDINAL SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Enrolled From Date].[Academic Calendar].ALLMEMBERS ON ROWS FROM [EdDW]</CommandText>
        <rd:DesignerState><QueryDefinition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification xsi:type="MDXQuerySpecification"><Select><Items /></Select><From>EdDW</From></QuerySpecification><Query><Statement>WITH MEMBER [Measures].[ParameterCaption] AS [Enrolled From Date].[Academic Calendar].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] AS [Enrolled From Date].[Academic Calendar].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [Enrolled From Date].[Academic Calendar].CURRENTMEMBER.LEVEL.ORDINAL SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Enrolled From Date].[Academic Calendar].ALLMEMBERS ON ROWS FROM [EdDW]</Statement></Query></QueryDefinition></rd:DesignerState>
        <rd:AutoGenerated>true</rd:AutoGenerated>
        <rd:Hidden>true</rd:Hidden>
        <rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>
      </Query>
    </DataSet>

   
   
   
   

7. Go back to the report's design view. Right click on the datasource in the Report Data view and choose show hidden datasets. The EnrolledFromYear dataset will appear and can be edited