none
Fields in MDX Dataset Not Appearing in Dataset Properties

    Question

  • I have an SSRS dataset that is driven off of an MDX query. Here is the query:

    WITH MEMBER [Measures].[Total Fee % Complete] AS
    	IIf (
    		( 
    			[DIM FUNCTION].[Function Category].&[Fee],
    			[DIM PROJECT].[Job Number].[All],
    			[DIM PROJECT].[Job Component Number].[All],
    			[DIM PROJECT].[Job Status].[All],
    			[Measures].[Estimate Line Total]
    		) = 0,
    		NULL,	
    		( 
    			[DIM FUNCTION].[Function Category].&[Fee],
    			[DIM PROJECT].[Job Number].[All],
    			[DIM PROJECT].[Job Component Number].[All],
    			[DIM PROJECT].[Job Status].[All],
    			[Measures].[Actual Line Total]
    		) / ( 
    			[DIM FUNCTION].[Function Category].&[Fee],
    			[DIM PROJECT].[Job Number].[All],
    			[DIM PROJECT].[Job Component Number].[All],
    			[DIM PROJECT].[Job Status].[All]]
    			[Measures].[Estimate Line Total]
    		)
    	)
    
    
    MEMBER [Measures].[Total % Complete] AS	
    		( 
    			[DIM FUNCTION].[Function Category].&[Fee],
    			[DIM PROJECT].[Job Number].[All],
    			[DIM PROJECT].[Job Component Number].[All],
    			[DIM PROJECT].[Job Status].[All],
    			[Measures].[Recognized Amount]
    		) / ( 
    			[DIM FUNCTION].[Function Category].&[Fee],
    			[DIM PROJECT].[Job Number].[All],
    			[DIM PROJECT].[Job Component Number].[All],
    			[DIM PROJECT].[Job Status].[All]]
    			[Measures].[Estimate Line Total]
    		)
    	
    SELECT
      NON EMPTY
        {
          [Measures].[% Complete]
         ,[Measures].[Forecasted % Complete]
         ,[Measures].[Overage \ Under]
         ,[Measures].[Estimate Line Total]
         ,[Measures].[Actual Line Total]
         ,[Measures].[Actual Extended Amount]
         ,[Measures].[Variance]
         ,[Measures].[Recognized Amount]
         ,[Measures].[$ Variance]
         ,[Measures].[Calculated Member]
         ,[Measures].[Invoice OOP + Markup]
         ,[Measures].[Total Labor Amount]
         ,[Measures].[Total Invoice Amount]
         ,[Measures].[Total Fee % Complete] 
         ,[Measures].[Total % Complete]
         ,[Measures].[NEW % Variance]
        } ON COLUMNS
     ,NON EMPTY
        {
            [DIM PROJECT].[Client].[Client].ALLMEMBERS*
            [DIM PROJECT].[Division].[Division].ALLMEMBERS*
            [DIM PROJECT].[Product].[Product].ALLMEMBERS*
            [DIM PROJECT].[Job Number].[Job Number].ALLMEMBERS*
            [DIM PROJECT].[Job Component Number].[Job Component Number].ALLMEMBERS*
            [DIM PROJECT].[Component ID].[Component ID].ALLMEMBERS*
            [DIM PROJECT].[Component Description].[Component Description].ALLMEMBERS*
            [DIM PROJECT].[Client PO Number].[Client PO Number].ALLMEMBERS*
            [DIM PROJECT].[Job Status].[Job Status].ALLMEMBERS*
            [DIM PROJECT].[Project Start Date].[Project Start Date].ALLMEMBERS*
            [DIM PROJECT].[Project End Date].[Project End Date].ALLMEMBERS*
            [DIM FUNCTION].[Function Category].[Function Category].ALLMEMBERS*
            [DIM ESTIMATE].[Estimate Status].[Estimate Status].ALLMEMBERS
        }
      DIMENSION PROPERTIES
        MEMBER_CAPTION
       ,MEMBER_UNIQUE_NAME
       ON ROWS
    FROM
    (
      SELECT
        StrToSet
        (@DIMPROJECTComponentID
        
        ) ON COLUMNS
      FROM
      (
        SELECT
          StrToSet
          (@DIMPROJECTProduct
          
          ) ON COLUMNS
        FROM
        (
          SELECT
            StrToSet
            (@DIMPROJECTDivision
           
            ) ON COLUMNS
          FROM
          (
            SELECT
              StrToSet
              (@DIMPROJECTClient
              
              ) ON COLUMNS
            FROM
            (
              SELECT
                {[DIM ESTIMATE].[Estimate Status].&[Approved]} ON COLUMNS
              FROM
              (
                SELECT
                  StrToSet
                  (@DIMPROJECTJobStatus
                 
                  ) ON COLUMNS
                FROM
                (
                  SELECT
                    {
                      [DIM FUNCTION].[Function Category].&[FEE]
                     ,[DIM FUNCTION].[Function Category].&[OOP]
                    } ON COLUMNS
                  FROM [ESTIMATES]
                )
              )
            )
          )
        )
      )
    )
    CELL PROPERTIES
      VALUE
     ,BACK_COLOR
     ,FORE_COLOR
     ,FORMATTED_VALUE
     ,FORMAT_STRING
     ,FONT_NAME
     ,FONT_SIZE
     ,FONT_FLAGS;

    When I view the properties of the dataset the fields all appear. In designing the report, all the fields appear as well. The problem is when I add a new field to the query called JOB TYPE:

    WITH MEMBER [Measures].[Total Fee % Complete] AS
    	IIf (
    		( 
    			[DIM FUNCTION].[Function Category].&[Fee],
    			[DIM PROJECT].[Job Number].[All],
    			[DIM PROJECT].[Job Component Number].[All],
    			[DIM PROJECT].[Job Status].[All],
    			[Measures].[Estimate Line Total]
    		) = 0,
    		NULL,	
    		( 
    			[DIM FUNCTION].[Function Category].&[Fee],
    			[DIM PROJECT].[Job Number].[All],
    			[DIM PROJECT].[Job Component Number].[All],
    			[DIM PROJECT].[Job Status].[All],
    			[Measures].[Actual Line Total]
    		) / ( 
    			[DIM FUNCTION].[Function Category].&[Fee],
    			[DIM PROJECT].[Job Number].[All],
    			[DIM PROJECT].[Job Component Number].[All],
    			[DIM PROJECT].[Job Status].[All]]
    			[Measures].[Estimate Line Total]
    		)
    	)
    
    
    MEMBER [Measures].[Total % Complete] AS	
    		( 
    			[DIM FUNCTION].[Function Category].&[Fee],
    			[DIM PROJECT].[Job Number].[All],
    			[DIM PROJECT].[Job Component Number].[All],
    			[DIM PROJECT].[Job Status].[All],
    			[Measures].[Recognized Amount]
    		) / ( 
    			[DIM FUNCTION].[Function Category].&[Fee],
    			[DIM PROJECT].[Job Number].[All],
    			[DIM PROJECT].[Job Component Number].[All],
    			[DIM PROJECT].[Job Status].[All]]
    			[Measures].[Estimate Line Total]
    		)
    	
    SELECT
      NON EMPTY
        {
          [Measures].[% Complete]
         ,[Measures].[Forecasted % Complete]
         ,[Measures].[Overage \ Under]
         ,[Measures].[Estimate Line Total]
         ,[Measures].[Actual Line Total]
         ,[Measures].[Actual Extended Amount]
         ,[Measures].[Variance]
         ,[Measures].[Recognized Amount]
         ,[Measures].[$ Variance]
         ,[Measures].[Calculated Member]
         ,[Measures].[Invoice OOP + Markup]
         ,[Measures].[Total Labor Amount]
         ,[Measures].[Total Invoice Amount]
         ,[Measures].[Total Fee % Complete] 
         ,[Measures].[Total % Complete]
         ,[Measures].[NEW % Variance]
        } ON COLUMNS
     ,NON EMPTY
        {
            [DIM PROJECT].[Client].[Client].ALLMEMBERS*
            [DIM PROJECT].[Division].[Division].ALLMEMBERS*
            [DIM PROJECT].[Product].[Product].ALLMEMBERS*
            [DIM PROJECT].[Job Number].[Job Number].ALLMEMBERS*
            [DIM PROJECT].[Job Component Number].[Job Component Number].ALLMEMBERS*
            [DIM PROJECT].[Component ID].[Component ID].ALLMEMBERS*
            [DIM PROJECT].[Component Description].[Component Description].ALLMEMBERS*
            [DIM PROJECT].[Client PO Number].[Client PO Number].ALLMEMBERS*
            [DIM PROJECT].[Job Status].[Job Status].ALLMEMBERS*
            [DIM PROJECT].[Job Type].[Job Type].ALLMEMBERS*
            [DIM PROJECT].[Project Start Date].[Project Start Date].ALLMEMBERS*
            [DIM PROJECT].[Project End Date].[Project End Date].ALLMEMBERS*
            [DIM FUNCTION].[Function Category].[Function Category].ALLMEMBERS*
            [DIM ESTIMATE].[Estimate Status].[Estimate Status].ALLMEMBERS
        }
      DIMENSION PROPERTIES
        MEMBER_CAPTION
       ,MEMBER_UNIQUE_NAME
       ON ROWS
    FROM
    (
      SELECT
        StrToSet
        (@DIMPROJECTComponentID
        
        ) ON COLUMNS
      FROM
      (
        SELECT
          StrToSet
          (@DIMPROJECTProduct
          
          ) ON COLUMNS
        FROM
        (
          SELECT
            StrToSet
            (@DIMPROJECTDivision
           
            ) ON COLUMNS
          FROM
          (
            SELECT
              StrToSet
              (@DIMPROJECTClient
              
              ) ON COLUMNS
            FROM
            (
              SELECT
                {[DIM ESTIMATE].[Estimate Status].&[Approved]} ON COLUMNS
              FROM
              (
                SELECT
                  StrToSet
                  (@DIMPROJECTJobStatus
                 
                  ) ON COLUMNS
                FROM
                (
                  SELECT
                    {
                      [DIM FUNCTION].[Function Category].&[FEE]
                     ,[DIM FUNCTION].[Function Category].&[OOP]
                    } ON COLUMNS
                  FROM [ESTIMATES]
                )
              )
            )
          )
        )
      )
    )
    CELL PROPERTIES
      VALUE
     ,BACK_COLOR
     ,FORE_COLOR
     ,FORMATTED_VALUE
     ,FORMAT_STRING
     ,FONT_NAME
     ,FONT_SIZE
     ,FONT_FLAGS;

    Now suddenly, the fields have all disappeared from the properties dialog - even though I click "Refresh Fields" repeatedly. The fields also do not appear under the dataset node in the Report Data pane nor are they available when designing the report.

    What happened?!?!?

    That field does exist and no errors are thrown when I click OK when editing the query.

    No idea what the heck is going on!!

    Thanks!!

    

    A. M. Robinson

    Wednesday, August 22, 2012 8:17 PM

Answers

  • Yes...I do need ALL.

    And the issue is not with the data source - it is the report data set that is having the issue. The data set is an MDX query, not XML.

    I have on countless occasions added one, two, any number of columns to a dataset and haven't had to recreate the datasets whenever I add columns. Just seems like a real hassle and sporadic behavior.


    A. M. Robinson

    • Marked as answer by ansonee Friday, March 22, 2013 3:15 PM
    Friday, August 24, 2012 8:48 PM

All replies

  • Hi 

    Can you try the query in SSMS and see if you get proper output? If so, simply delete and recreate the Dataset with the new query and see if it works.

    Let me know if does not solve your issue.

    Regards,

    Vinaya

    Wednesday, August 22, 2012 10:24 PM
  • Yes...it works fine in SSMS.

    I just don't know why the fields do not refresh when Refresh Fields is clicked.

    I've actually found several posts from many users who experience something similar - although each situation differs slightly from mine. I'm inclined to believe this is a bug with SSRS. One really shouldn't have to create a completely new dataset just because they add one additional field to the dataset.


    A. M. Robinson

    Thursday, August 23, 2012 2:57 AM
  • Agreed, but if it is a bug is SSRS to refresh the metadata of the dataset, we could just try deleting and re-creating and see if this solves your issue.

    Regards,

    Vinaya

    Thursday, August 23, 2012 11:26 PM
  • [DIM PROJECT].[Job Number].[All],
    [DIM PROJECT].[Job Component Number].[All],
    [DIM PROJECT].[Job Status].[All],

    Not that it helps your issue but do you really need to specify these, is the default member All here?

    I'd try Vinaya's suggestion - delete and recreate the data source.

    Push come to shove, if you really need to you could try to edit the data source XML manually in order to get the new field in there.


    Josh Ash


    Friday, August 24, 2012 12:43 AM
  • Yes...I do need ALL.

    And the issue is not with the data source - it is the report data set that is having the issue. The data set is an MDX query, not XML.

    I have on countless occasions added one, two, any number of columns to a dataset and haven't had to recreate the datasets whenever I add columns. Just seems like a real hassle and sporadic behavior.


    A. M. Robinson

    • Marked as answer by ansonee Friday, March 22, 2013 3:15 PM
    Friday, August 24, 2012 8:48 PM
  • Ho Robinson,

    What's the specific version of SQL Server? I suggest you that install the latest the Service Pack for SQL Server in your test environment to see if this help.
    SQL Server version: http://sqlserverbuilds.blogspot.com/

    If this issue still persists, I would suggest opening a case with Microsoft Customer Support Services (CSS) (http://support.microsoft.com), so that a dedicated Support Professional can assist you in a more efficient manner.

    Regards,
    Bin Long

    TechNet Subscriber Support
    If you are
    TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Bin Long

    TechNet Community Support

    Sunday, August 26, 2012 4:00 AM
  • You need to remove the Non Empty keywords, then hit the refresh button. You'll notice that the fields then refresh correctly.

    The way around this is to quote every line like this:

    ="Select [Measures].[Sales Amount] "&

    "Non Empty [Sales Region].[Region].[Region] "&

    "From [MyCube]"

    So you need to keep two copies of your query, one with the quotes and one without, used for refreshing fields.

    Monday, March 18, 2013 5:19 PM
  • I'm sorry, but your suggestion doesn't make any sense to me...

    Removing the NON EMPTY keywords completely changes the underlying intent of my query. And quoting every line does nothing. Trying to pass that into a reporting services dataset would just completely fail.

    And keeping two copies of the same query...? How does that work?


    A. M. Robinson

    Monday, March 18, 2013 5:48 PM
  • Hi Ansonee,

    I agree that removing the Non Empty keywords changes the underlying query, but you only need that query to refresh the field list in RS.

    By quoting every line RS interprets the entire thing as one giant dynamic query, so you can add in all sorts of MDX parameters etc. Although you need to change your data source connection to be OleDB, this also means that you can't use the query designed as you're telling RS the MDX it should use.

    I'm at work at the minute so don't have long to reply. I'll have a search around for more info when I get home tonight and post back.

    Jim.


    Wednesday, March 20, 2013 2:56 PM
  • Ok,

    Here's a bit more of the information I'm talking about. If you are using the Analysis Services connection in SSRS it can be a bit painful, so we've opted to use the OleDB connection. What you can do then is create your query using a combination of parameters and strings (As per Con 1 in the below this blog post). The problem I've found with this is that the designer doesn't like to refresh all the field names if you use NON EMPTY in your MDX. I find that I have to remove the NON EMPTY keywords to get the fields to refresh, add my query in using " (quotes) as per my above post and then preview the report.

    To test in your report above, just remove the NON EMPTY keywords and see if the new field appears...

    Friday, March 22, 2013 2:43 PM