MULTIPLE DISTINCT COUNT Problem
Hello experts,
My inventoryfact table consists three dimensions such as Stores,Dates and Products. In SSRS environment I have used parallelperiod to calculate the stock levels of each stylecodes.
Here is the code:
WITH
MEMBER Measures.[Store Stock Level] AS SUM([Dim Date].[WeeklyCalendar].[Week Of Year].members(0):ClosingPeriod([Dim Date].[WeeklyCalendar].[Week Of Year]), Measures.[Quantity] )
When I used this measure in my reports , I retrieve the correct results.
I have create two measures from DimProduct dimension attributes and set their aggregation type as "DISTINCT COUNT". When I try to open this combined resultset then I get wrong results in addition my previous stock levels.
Here is my complete code SSRS code:
WITH
MEMBER Measures.[Store Stock Level] AS SUM([Dim Date].[WeeklyCalendar].[Week Of Year].members(0):ClosingPeriod([Dim Date].[WeeklyCalendar].[Week Of Year]), Measures.[StockLevel] )
SELECT NON EMPTY { [Measures].[Store Stock Level] } ON COLUMNS, NON EMPTY { ([Products].[ProductHierachy].[Sub Class ID].ALLMEMBERS * [Products].[Style Code].[Style Code].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@DimDateWeeklyCalendar, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DimStoresSegmentation, CONSTRAINED) ) ON COLUMNS FROM [MNG_MASTER])) WHERE ( IIF( STRTOSET(@DimStoresSegmentation, CONSTRAINED).Count = 1, STRTOSET(@DimStoresSegmentation, CONSTRAINED), [Dim Stores].[Segmentation].currentmember ), IIF( STRTOSET(@DimDateWeeklyCalendar, CONSTRAINED).Count = 1, STRTOSET(@DimDateWeeklyCalendar, CONSTRAINED), [Dim Date].[WeeklyCalendar].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Any idea?
King Regards
June 22nd, 2011 5:12pm
Hello Again,
I am now able to get correct inventory stock levels of each product. But DISTINCT COUNT aggregation type does not work properly for these two measures. This aggregation considers just DISTINCT COUNT values of the specifed date range. For example:
DateID
StoreID
StyleCode
Quantity
20110101
1001
A
-5
20110102
1001
A
15
20110103
1001
A
5
20110101
1001
B
3
20110102
1001
B
-1
20110101
1001
C
12
And in addition, I have two Stylecodes
D (stock level =10),
E (Stock Level=5)
Finally I get the following results:
DistinctStyleCode count = 3 ----> This must be "5"
Inventory Level = 44 -----> This is correct.
Please help!!
Free Windows Admin Tool Kit Click here and download it now
June 23rd, 2011 10:12am
Hi innocent1973,
Please understanding that here we generally focus on the troubleshooting of reporting services issues and the problem we
are encountering has beyond our specialty. I suggest you post your question on the
Analysis
Services forum, because it seems you are now facing a MDX issue rather than a Reporting
Services issue.
Hope you can get the issue resolved soon with helps from appropriate team.
Thanks,
Eileen
June 28th, 2011 11:16am