none
SSRS 2008: MDX Query and Dimension Properties

    Question

  • Hello all,

    I'm building some MDX reports where I have to show some dimension properties in the report. An example for one of my MDX queries is:

    select 
    [Measures].[Total Costs] 
    on columns,
    [Projects].[Project].members
    DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, 
    [Projects].[Project].[Coordinator],
    [Projects].[Project].[Head]
    on rows
    from ProPlan
    

    If I preview this query in Visual Studio's query designer the resultset will contain columns for the Coordinator and Head attributes. But when previewing the report always the following warning messages are shown for both attributes:

    [rsMissingFieldInDataSet] The dataset ‘Projects’ contains a definition for the Field ‘Head’. This field is missing from the returned result set from the data source.
    [rsErrorReadingDataSetField] The dataset ‘Projects’ contains a definition for the Field ‘Head’. The data extension returned an error during reading the field. There is no data for the field at position 5.

    Do you have any idea what could be the problem?
    Visual Studio 2008 9.0.30729.1 SP, Microsoft SQL Server Reporting Services Designers Version 10.0.2799.0

    One important thing to care about is that Projects is a parent/child dimension. With normal dimensions everything works fine. If the query designer didn't show the attribute columns I would accept it as a disadvantage of parent/child dimensions. But I don't understand why it is working in the query designer (and in Management Studio), but not at report execution.

    Thanks in advance and best regards,
    Gerald 

     

    Wednesday, December 15, 2010 8:06 PM

Answers

All replies

  • Hello Gerald

    I believe that the dimension properties shoud be the last directive on an axis expression.  Your query includes attribute references before and after the dimension properties list.  Try placing the dimsnsion properties directive just before ON ROWS, something like this:

    select 
    [Measures].[Total Costs] 
    on columns,
    ([Projects].[Project].members, {[Projects].[Project].[Coordinator],
    [Projects].[Project].[Head]}) 
    DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME 
    on rows
    from ProPlan
    

    Seems like this query will return the Head and Coordinator members multiple times because they will also be in the Projects.Project.Members set.  I trust that this is intentional.
    Hope this is helpful.

    Paul Turley, MVP [Hitachi Consulting] SQLServerBIBlog.com
    Thursday, December 16, 2010 8:51 AM
  • Hello Paul,

    thank you for your reply. One thing is that Coordinator and Head are non aggregatable attributes, so I cannot use the query you have suggested.

    The other thing is that Projects is a parent/child-dimension. My original MDX returns only two columns in SQL Server Management Studio (SSMS): one column containing the project names and one column containing the measure value. If I double click a project name SSMS will show this project's attributes. Beside some default attributes which are always shown there are also the attributes I have specified using the DIMENSION PROPERTIES clause (this is what this clause is for). 

    If I execute my original query in Visual Studio's query designer for MDX the result will contain four columns: Project, Coordinator, Head and Total Cost. But once I run the report reporting services will complain that Coordinator and Head are missing from the dataset.

    If I use this syntax on a non-parent/child dimension everything will work ok.

    I have tested this also with SSRS 2008 w/ Service Pack 2 and SSRS 2008 R2 and could reproduce this behaviour, too.

    So I will have to create some calculated members which return these properties' values. But this requires some tricks regarding zero supression (because these members will always have a value).

    Best regards,
    Gerald

    Friday, December 17, 2010 8:53 AM
  • Through the related topics I have been able to find a work-around in the thread SSRS using Dimension Property acting strange . Using Fields!Project("Coordinator") instead of Field!Coordinator.Value delivers the expected result.

    I have also filed feedback at Microsoft Connect (in case anyone is interested):
    SSRS 2008(R2): Parent/Child-Dimension's Properties only available in Query Designer

    Best regards,
    Gerald

    • Marked as answer by actinium.ga Friday, December 17, 2010 10:04 AM
    Friday, December 17, 2010 10:04 AM