Monday, July 28, 2014

More Measure Columns

Adding more measure columns is simple, but you have to be aware of the extra Curly Brackets “{ }” which enclose your row or columns definition. Let’s try:
SELECT
    {
     [Measures].[Sales Amount]
    ,[Measures].[Tax Amount]
    ,[Measures].[Total Product Cost]    
    } ON COLUMNS
    ,   
    NON EMPTY
    {
    [Date].[Calendar Year].Children
    }                    
    ON ROWS
FROM
    [Adventure Works]

This will get us:
image
Although it’s common practice to start you query with the column definition you are allowed to switch it around:
SELECT
    {
     [Measures].[Sales Amount]
    ,[Measures].[Tax Amount]
    ,[Measures].[Total Product Cost]    
    } ON ROWS    ,   
    NON EMPTY
    {
    [Date].[Calendar Year].Children
    }                    
    ON COLUMNSFROM
    [Adventure Works]
This will get us:
image
Have Fun And Till Next Time

No comments:

Post a Comment