January 26, 2010 at 8:24 pm
I'm attempting to create an MDX query that will return data that essentially treats each "day" (Monday, Tuesday, Wednesday) categorically the same as the Week To Date, Month To Date, and Year To Date values, looking something like this:
[font="Courier New"]
Sales Center Mon Tue Wed Thu Fri Sat Sun Week MTD YTD
Oak Ridge 615 123 665 799 802 1058 491 4553 12657.34 77209.774
Falling Crest 728 335 784 368 459 1870 953 5497 15281.66 93218.126
Whispering Pine 326 105 188 409 287 609 149 2073 5990.97 25761.171[/font]
I know how I would do it in SQL, creating a "category" and filling that with the "Time interval", along with unions and other such good stuff. How to do it in MDX has me stumped at the moment, so any guidance would be appreciated.
My apologies that the spacing doesn't translate from Excel.
Respectfully,
John
January 28, 2010 at 6:51 am
If you can get any one column in MDX, can you then output that to a temp table, and then use T-SQL to gather from the temp tables created?
Steve
(aka sgmunson)
:-):-):-)
bikerdadHLV (1/26/2010)
I'm attempting to create an MDX query that will return data that essentially treats each "day" (Monday, Tuesday, Wednesday) categorically the same as the Week To Date, Month To Date, and Year To Date values, looking something like this:[font="Courier New"]
Sales Center Mon Tue Wed Thu Fri Sat Sun Week MTD YTD
Oak Ridge 615 123 665 799 802 1058 491 4553 12657.34 77209.774
Falling Crest 728 335 784 368 459 1870 953 5497 15281.66 93218.126
Whispering Pine 326 105 188 409 287 609 149 2073 5990.97 25761.171[/font]
I know how I would do it in SQL, creating a "category" and filling that with the "Time interval", along with unions and other such good stuff. How to do it in MDX has me stumped at the moment, so any guidance would be appreciated.
My apologies that the spacing doesn't translate from Excel.
Respectfully,
John
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
January 28, 2010 at 10:14 am
I suppose that I could, except that I haven't done anything with MDX aside from feeding it straight into the reports. So how I'd go about doing what you have in mind is something I'll have to sort out. That's actually similar to what I'd like to do, what with joins and unions and subqueries and temporary tables, I can fold, spindle and mutilate the data to my hearts content with T-SQL. Getting the MDX to a temporary table though... :unsure:
sgmunson (1/28/2010)
If you can get any one column in MDX, can you then output that to a temp table, and then use T-SQL to gather from the temp tables created?Steve
(aka sgmunson)
:-):-):-)
January 28, 2010 at 12:47 pm
How about using reporting services (SSRS)? I would like to think that each of the individual MDX queries could be a data source, but check it out for yourself and see... I'd also like to think that would eliminate temp tables.
Steve
(aka sgmunson)
:-):-):-)
bikerdadHLV (1/28/2010)
I suppose that I could, except that I haven't done anything with MDX aside from feeding it straight into the reports. So how I'd go about doing what you have in mind is something I'll have to sort out. That's actually similar to what I'd like to do, what with joins and unions and subqueries and temporary tables, I can fold, spindle and mutilate the data to my hearts content with T-SQL. Getting the MDX to a temporary table though... :unsure:sgmunson (1/28/2010)
If you can get any one column in MDX, can you then output that to a temp table, and then use T-SQL to gather from the temp tables created?Steve
(aka sgmunson)
:-):-):-)
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
January 10, 2012 at 1:27 pm
The simple answer is that there is no way to do it in MDX.
In other words, the XTD aggregations are already part of the time dimension and cannot be presented side by side.
Instead of attempting to do it with MDX, what we did is create a fact table in SSAS with a snapshot of the WTD, MTD, QTD, and YTD measures for everyday. The fact table does have a time dimension, but you only use it to calculate year over year and month over month variances and to present time periods side by side.
I hope that this helps.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply