February 14, 2012 at 7:30 am
Hi,
I've picked up some MDX (which I know very little about) that needs amending and I'm stuck!
We have a query that should return the number of times someone leaves with a particular reason each month and have a total, so for example it should return:
Total Oct Nov Dec
Reason1 1 0 1 0
Reason2 1 0 1 0
Reason3 2 1 1 0
Unfortunately the Total appears to be summing all of the data in the cube and not the data within the month time frame.
If I run the SELECT part of the MDX it returns the 3 months (Oct, Nov, Dec) with the correct totals, but the SUM doesn't seem to restrict the date range.
Can anyone point out where I've gone wrong?
MDX:
WITH MEMBER [Date].[Calendar Hierarchy].[Total] AS
SUM
(
{[Date].[Calendar Hierarchy].[Calendar Month Name].ALLMEMBERS} ,
[Measures].[Fact Patient Leaving Reg Fac Count]
)
SELECT
{[Date].[Calendar Hierarchy].[Total], [Date].[Calendar Hierarchy].[Calendar Month Name].ALLMEMBERS} *
{[Measures].[Fact Patient Leaving Reg Fac Count]} ON COLUMNS,
NON EMPTY
{ ORDER([Leave Reason].[Leave Reason Code].[Leave Reason Code].ALLMEMBERS,
[Measures].[Fact Patient Leaving Reg Fac Count], DESC)}
ON ROWS FROM
(
SELECT
([Date].[Calendar Hierarchy].[Calendar Date].&[2011-10-01T00:00:00]
: [Date].[Calendar Hierarchy].[Calendar Date].&[2011-12-01T00:00:00] )
ON COLUMNS FROM
[Denplan] )
WHERE
( [Date].[Calendar Date].&[2011-08-01T00:00:00]
: [Date].[Calendar Date].&[2011-12-01T00:00:00] ) *
([Reg Fac].[Reg Fac Id].[Reg Fac Id].&[1000001])
Thanks,
Graham
February 16, 2012 at 9:14 am
Hi,
With a little (ok a lot) of help from Google I've been able to get the MDX working.
It does take a bit longer to run (from 1 seconds to ~4) but here it is in case it helps someone else:
WITH SET LeavingReasonsSet AS [Date].[Calendar Hierarchy].[Calendar Month Name].MEMBERS
MEMBER [Date].[Calendar Hierarchy].[Total]
AS
SUM
(
EXISTING (LeavingReasonsSet),
[Measures].[Fact Patient Leaving Reg Fac Count]
)
SELECT
{[Date].[Calendar Hierarchy].[Total], [Date].[Calendar Hierarchy].[Calendar Month Name].ALLMEMBERS} *
{[Measures].[Fact Patient Leaving Reg Fac Count]} ON COLUMNS,
NON EMPTY
{ ORDER([Leave Reason].[Leave Reason Code].[Leave Reason Code].ALLMEMBERS,
[Measures].[Fact Patient Leaving Reg Fac Count], DESC)}
ON ROWS FROM
[Denplan]
WHERE
( [Date].[Calendar Date].&[2011-10-01T00:00:00]
: [Date].[Calendar Date].&[2011-12-01T00:00:00] ) *
([Reg Fac].[Reg Fac Id].[Reg Fac Id].&[1100001])
February 29, 2012 at 5:39 pm
Seems like you are filtering the date in the where clause where you could simply limit the set in the rows.
e.g.
SELECT
{[Measures].[Fact Patient Leaving Reg Fac Count]} ON COLUMNS,
NON EMPTY
{[Leave Reason].[Leave Reason Code].[Leave Reason Code].ALLMEMBERS *
{[Date].[Calendar Hierarchy].[Calendar Month Name].&[2011 Oct]:
[Date].[Calendar Hierarchy].[Calendar Month Name].&[2011 Dec]}} ON ROWS
FROM [Denplan]
WHERE ([Reg Fac].[Reg Fac Id].[Reg Fac Id].&[1000001])
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply