none
ON ROWS versus ON COLUMNS

    Question

  • I have a report I created in SSRS that goes against an SSAS cube. The report is fairly straight forward. What I need to do is introduce some custom MDX to get the report to display as the user requested, and what I need to do is to implement ON COLUMNS I think.

    Below is a screen shot of the current report:

    What I would like to do is to take the two function categories and display them on the columns, not in the rows. In this example, there are two fucntion categories: FEE and OOP. For job number 7197 the FEE portion of the estimate is 54.5 hours, with an extended amount of $8640, etc. The OOP component has estimated hours of 1, etended amount of $1500, etc.

    I would like to have both of these appear on ONE line, the FEE cmponent appearing under the appropriate heading, and the OOP compoenent appearing under its proper heading. The data I would like to move is highlighted in yellow. There would then be no need for the "Function Category" column in the report.

    Below is the MDX that the Query Builer in SSRS generated automatically:

    SELECT
    NON EMPTY
    {
    [Measures].[Estimate Rate]
    ,[Measures].[Estimated Hours]
    ,[Measures].[Extended Amount]
    ,[Measures].[Line Total]
    ,[Measures].[Markup Ampunt]
    } ON COLUMNS
    ,NON EMPTY
    {
    [DIM PROJECT].[Client - Division - Product - Component].[Job Component Number].ALLMEMBERS*
    [DIM ESTIMATE].[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 ESTIMATE].[Function Category].[Function Category].ALLMEMBERS*
    [DIM ESTIMATE].[Estimate Number].[Estimate Number].ALLMEMBERS*
    [DIM ESTIMATE].[Estimate Status].[Estimate Status].ALLMEMBERS
    }
    DIMENSION PROPERTIES
    MEMBER_CAPTION
    ,MEMBER_UNIQUE_NAME
    ON ROWS
    FROM
    (
    SELECT
    {[DIM ESTIMATE].[Estimate Status].&[Approved]} ON COLUMNS
    FROM
    (
    SELECT
    StrToSet
    (@DIMPROJECTProduct
    ,CONSTRAINED
    ) ON COLUMNS
    FROM
    (
    SELECT
    StrToSet
    (@DIMPROJECTDivision
    ,CONSTRAINED
    ) ON COLUMNS
    FROM
    (
    SELECT
    StrToSet
    (@DIMPROJECTClient
    ,CONSTRAINED
    ) ON COLUMNS
    FROM [ESTIMATES]
    )
    )
    )
    )
    WHERE
    (
    IIF
    (
    StrToSet
    (@DIMPROJECTClient
    ,CONSTRAINED
    ).Count
    = 1
    ,StrToSet
    (@DIMPROJECTClient
    ,CONSTRAINED
    )
    ,[DIM PROJECT].[Client].CurrentMember
    )
    ,IIF
    (
    StrToSet
    (@DIMPROJECTDivision
    ,CONSTRAINED
    ).Count
    = 1
    ,StrToSet
    (@DIMPROJECTDivision
    ,CONSTRAINED
    )
    ,[DIM PROJECT].[Division].CurrentMember
    )
    ,IIF
    (
    StrToSet
    (@DIMPROJECTProduct
    ,CONSTRAINED
    ).Count
    = 1
    ,StrToSet
    (@DIMPROJECTProduct
    ,CONSTRAINED
    )
    ,[DIM PROJECT].[Product].CurrentMember
    )
    )
    CELL PROPERTIES
    VALUE
    ,BACK_COLOR
    ,FORE_COLOR
    ,FORMATTED_VALUE
    ,FORMAT_STRING
    ,FONT_NAME
    ,FONT_SIZE
    ,FONT_FLAGS;

    MDX is NOT my strong point, so if anyone can provide some ssistance, it would be greatly appreciated!!

    Thanks!!

     

     

     

     

     

     

     


    A. M. Robinson
    Monday, January 23, 2012 8:26 PM

Answers

  • Hi ansonee,

    Thanks for feedback.

    It seems that your tablix have other columns which is not belonging to Function Category column group, but you still put them right adjacent to the function category column group, that’s why the data show on different rows even though they had already be grouped by Function Category. You can try to specify these columns locate after Function category column group( like  ‘Total OOP + Fee’,’Actual Hours Fee’) as the details column of the Function Category column to have a try.

    Thanks,
    Bill Lu


    Bill Lu

    TechNet Community Support

    • Proposed as answer by SOW Noo Tuesday, January 24, 2012 2:22 PM
    • Marked as answer by Zilong LuModerator Monday, January 30, 2012 2:24 AM
    Tuesday, January 24, 2012 10:08 AM
    Moderator

All replies

  • It seems like you could just use column groups inside of the report and keep the MDX fairly similar to what you have; however, SSRS writes notoriously bad MDX and it can easily be re-written to make it easier to follow and more efficient:

    WITH DYNAMIC SET ColumnSet AS {[Measures].[Estimate Rate],[Measures].[Estimated Hours],[Measures].[Extended Amount],[Measures].[Line Total],[Measures].[Markup Ampunt]}
    SELECT 
    ColumnSet
    ON COLUMNS,
    NONEMPTY 
    ({
    	[DIM PROJECT].[Client - Division - Product - Component].CHILDREN*
    	[DIM ESTIMATE].[Component Description].CHILDREN*
    	[DIM PROJECT].[Client PO Number].CHILDREN*
    	[DIM PROJECT].[Job Status].CHILDREN*
    	[DIM PROJECT].[Project Start Date].CHILDREN*
    	[DIM PROJECT].[Project End Date].CHILDREN*
    	[DIM ESTIMATE].[Function Category].CHILDREN*
    	[DIM ESTIMATE].[Estimate Number].CHILDREN*
    	[DIM ESTIMATE].[Estimate Status].&[Approved]
    }, ColumnSet)
    DIMENSION PROPERTIES 
    MEMBER_CAPTION,
    MEMBER_UNIQUE_NAME
    ON ROWS
    FROM 
    (
    SELECT 
    StrToSet
    (
    	@DIMPROJECTProduct,
    	CONSTRAINED
    ) ON COLUMNS
    FROM 
    (
    SELECT 
    StrToSet
    (
    	@DIMPROJECTDivision,
    	CONSTRAINED
    ) ON COLUMNS
    FROM 
    (
    SELECT 
    StrToSet
    (
    	@DIMPROJECTClient,
    	CONSTRAINED
    ) ON COLUMNS
    FROM [ESTIMATES])))
    CELL PROPERTIES 
    VALUE,
    BACK_COLOR,
    FORE_COLOR,
    FORMATTED_VALUE,
    FORMAT_STRING,
    FONT_NAME,
    FONT_SIZE,
    FONT_FLAGS;
    

    Hope that helps!

     

    Cheers!


    Ryan - Please mark all answers and useful posts!
    • Proposed as answer by Naomi N Monday, January 23, 2012 10:07 PM
    Monday, January 23, 2012 8:49 PM
  • Waco:

    Thanks for the help! I did go ahead and create that column grouping. I went ahead and deleted the column and groups for the "old" row way, but for some reason, I'm still getting the rows being broken down - in addition to the category being on the columns...?!?!

    Not sure what the scoop is here!! That Function Category does not appear in the row groups at all...

    As youo can see from the screen shot below, estimates 8051 and 8143 have both a FEE and OOP component, but they are still showing up on separate lines, even though the "facts" are appearing under their respective columns.

    Here are the row and column groups as defined in the report:

     

     

     

     

     

     

     

     

     


    A. M. Robinson
    • Edited by ansonee Monday, January 23, 2012 10:20 PM added pictures
    • Proposed as answer by SOW Noo Tuesday, January 24, 2012 2:22 PM
    Monday, January 23, 2012 9:53 PM
  • Hi ansonee,

    Thanks for feedback.

    It seems that your tablix have other columns which is not belonging to Function Category column group, but you still put them right adjacent to the function category column group, that’s why the data show on different rows even though they had already be grouped by Function Category. You can try to specify these columns locate after Function category column group( like  ‘Total OOP + Fee’,’Actual Hours Fee’) as the details column of the Function Category column to have a try.

    Thanks,
    Bill Lu


    Bill Lu

    TechNet Community Support

    • Proposed as answer by SOW Noo Tuesday, January 24, 2012 2:22 PM
    • Marked as answer by Zilong LuModerator Monday, January 30, 2012 2:24 AM
    Tuesday, January 24, 2012 10:08 AM
    Moderator
  • Do you still have a row group for the Function Category?

     

    Cheers!


    Ryan - Please mark all answers and useful posts!
    Tuesday, January 24, 2012 2:04 PM
  • No Row Group for the Function Category...
    A. M. Robinson
    Tuesday, January 24, 2012 4:18 PM
  • I don't just want to group those amounts after the Function Category column, I want to group all facts based on Function Category: Quantity, Extended, etc.

     

    ...and sorry, but not sure what you mean by specifying the columns as detail columns of the Function Category group...have been trying to find that option and can't.


    A. M. Robinson
    Tuesday, January 24, 2012 5:08 PM
  • After thinking for a bit, just realized what you meant!  ;-)

     

    I'll check....


    A. M. Robinson
    Tuesday, January 24, 2012 7:59 PM