December 1, 2014 at 1:19 pm
Hi All
I am new to MDX language and need a MDX functions/query on the cube to get the required output, Given below is the scenario with the data.
I am maintaining the data in a table in dataMart with given structure. We have the data at day and weekly in a single table with granularity indicator and count is the measure group column. While loading the data in to mart table we are populaiting the week Key from week table and Month key from month table and joining in the cube.
we need to calculate the inventory for a particular month. If a user selects a particular month the output would be count = 30 as a measure called Closed and count = 16 as a measure value called Open.
Need a MDX query to get output.
GranularityCount WeekKeyMonthKey
Daily 10w1M1
Daily 11w2M1
Daily 12w3M1
Daily 13w1M1
Daily 14w2M1
Daily 15w1M1
Weekly16W1M1
Weekly17W1M1
Weekly18w1M1
Weekly19W1M1
Weekly20W1M1
Weekly21W1M1
Weekly22W1M1
Weekly23w2M1
Weekly24w2M1
Weekly25w2M1
Weekly26w2M1
Weekly27w2M1
Weekly28w2M1
Weekly29w2M1
Weekly30w2M1
Weekly16w3M1
Weekly17w3M1
Weekly18w3M1
Weekly19w3M1
Weekly20w3M1
Weekly21w3M1
Weekly22w3M1
Weekly23w4M1
Weekly24w4M1
Weekly25w4M1
Weekly26w4M1
Weekly27w4M1
Weekly28w4M1
Weekly29w4M1
Weekly30w4M1
Thanks in advance
December 2, 2014 at 7:40 am
Without knowing any more detail it's impossible to provide you with any sort of solution. Firstly as a guideline, just imagine creating the code in T-SQL, think of all of the joins you would need. Do those joins exist in your cube? If they do then the actual MDX should be relatively simple. An example using a similar scenario on AdventureWorks is as follows:
SELECT
[Measures].[Reseller Sales Amount] ON 0,
NON EMPTY
{[Scenario].[Scenario].[Scenario]
*
[Date].[Month of Year].[Month of Year]
*
[Date].[Calendar Week of Year].[Calendar Week of Year]} ON 1
FROM
[Adventure Works]
Please read the following article for help with MDX http://www.sqlservercentral.com/articles/MDX/91228/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply