Search code examples
ssasmdx

Sum Specific Columns for Select Statement

I'm very new to SSAS and MDX and trying to get the concept of it. I need help please.

I have a booking fact table and I want to get the number of passengers by market for specific booking year and departure year and for each departure year I want a total columns. But can't figure out how to aggregate these columns to one.

Here is my code right now:

SELECT  {
            (CROSSJOIN([Dep Date].[Calendar Year].&[2012],[Book Date].[Calendar Year].&[2011] )),
            (CROSSJOIN([Dep Date].[Calendar Year].&[2012],[Book Date].[Calendar Year].&[2012] )),
            (CROSSJOIN([Dep Date].[Calendar Year].&[2013],[Book Date].[Calendar Year].&[2012] )),
            (CROSSJOIN([Dep Date].[Calendar Year].&[2013],[Book Date].[Calendar Year].&[2013] ))
        }
        ON 0,  
NON EMPTY [Fact Lead Pax Report].[Mc Major].MEMBERS
ON 1
FROM [Lead Pax Report]
WHERE { [Fact Lead Pax Report].[Res Status].&[A] }

And here s my result table, I want to add the total columns where the yellow marker is:

https://i.sstatic.net/5SNAk.png

Solution

  • What about adding another column to count all departures for specific year?

    For example:

    WITH SET [ESC TOURS BY MARKET] AS 
Filter( 
  [Fact Lead Pax Report].[Mc Major].[Mc Major], 
  ([Fact Lead Pax Report].[Mc Major].currentMember.name <> 'AIR') AND ([Fact Lead Pax Report].[Mc Major].currentMember.name <> 'DEFAULT')
)
SELECT  {
            (CROSSJOIN([Dep Date].[Calendar Year].&[2012],[Book Date].[Calendar Year].&[2011] )),
            (CROSSJOIN([Dep Date].[Calendar Year].&[2012],[Book Date].[Calendar Year].&[2012] )),
            (CROSSJOIN([Dep Date].[Calendar Year].&[2012],[Book Date].[Calendar Year] )),
            (CROSSJOIN([Dep Date].[Calendar Year].&[2013],[Book Date].[Calendar Year].&[2012] )),
            (CROSSJOIN([Dep Date].[Calendar Year].&[2013],[Book Date].[Calendar Year].&[2013] )),
            (CROSSJOIN([Dep Date].[Calendar Year].&[2013],[Book Date].[Calendar Year] ))
        }
        ON 0,  
[ESC TOURS BY MARKET]
ON 1
FROM [Lead Pax Report]
WHERE { [Fact Lead Pax Report].[Res Status].&[A] }