March 7, 2007 at 11:08 am
I have a SSAS 2005 Cube that collects order data by Transaction Type / Branch / Date.
Built on this cube, I have a report which summarizes Order counts, filtered by Date and Branch, with Transaction Type as the row detail. Included in the report is a calculated value for Month to Date counts. I have found an error in my report definition where any given day in the month that doesn't have all the Transaction Types of all the previous days, the totals don't show on the report.
E.G. for 01/01
Transaction Type Count MTD
AA 1 1
BB 2 2
TOTALS 3 3
for 01/02
Transaction Type Count MTD
AA 1 2
TOTALS 1 2
As you can see, the report from 01/02 is missing data. It should look like this:
Transaction Type Count MTD
AA 1 2
BB 0 2
TOTALS 1 4
I did get around this problem by creating dummy records for every combination of Transaction Type / Branch / Date, but that adds 86 million rows to the cube, and increases processing time from 15 minutes to 2 hours (at current data volumes, which will of course grow over time).
Is there a better way to force the report to include the zero Order Count rows that anyone knows of?
Thanks in advance for your help ...
March 7, 2007 at 1:55 pm
Can you post the MDX that you're using to generate the dataset?
Steve.
March 7, 2007 at 2:25 pm
Before you read this, I have to add the disclaimer that I just copied this from BIDS - I dont actually understand it (I'm working on learning MDX, but it is just an incantation in a strange language to me right now).
SELECT NON EMPTY { [Measures].[Num Orders], [Measures].[Order Count MTD] } ON COLUMNS, NON EMPTY { ([ORDER TRANSACTION TYPES].[Order Transaction Types].[TRANSACTION TYPES].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@ORDERTYPEOrderTypeHierarchy, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@CompanyParentId, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DATESTime, CONSTRAINED) ) ON COLUMNS FROM [UDM ATC Datamart]))) WHERE ( IIF( STRTOSET(@DATESTime, CONSTRAINED).Count = 1, STRTOSET(@DATESTime, CONSTRAINED), [DATES].[Time].currentmember ), IIF( STRTOSET(@CompanyParentId, CONSTRAINED).Count = 1, STRTOSET(@CompanyParentId, CONSTRAINED), [Company].[Parent Id].currentmember ), IIF( STRTOSET(@ORDERTYPEOrderTypeHierarchy, CONSTRAINED).Count = 1, STRTOSET(@ORDERTYPEOrderTypeHierarchy, CONSTRAINED), [ORDER TYPE].[Order Type Hierarchy].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
[Order Count MTD] is calculated by (in the Cube, which is the limity of my expertise, to date):
CREATE
MEMBER CURRENTCUBE.[MEASURES].[Order Count MTD]
AS Aggregate
(
PeriodsToDate([Dates].[Time].[Month Desc], [Dates].[Time].CurrentMember),
[Measures].[Num Orders]
),
FORMAT_STRING
= "#,#",
NON_EMPTY_BEHAVIOR
= { [Num Orders] },
VISIBLE
= 1 ;
March 7, 2007 at 3:04 pm
Worked it out ... I got rid of the NON EMPTY clauses in the MDX query, and it works as required.
Steve - thanks for pointing me in the right direction.
January 22, 2014 at 6:54 am
Hi!
Did you remove both NONEMPTY or just one of them? In that case which one?
Regard,
David
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply