February 18, 2017 at 12:54 pm
Let's say I have a simple cube, my fact table has a Total Sales measure and I have a date dimension with a full date attribute in there. I'd like to write an MDX query where I can group dates into buckets and output the results. This is ultimately going to end up in a report and these buckets will have to be dynamic, so I don't want to have to define them in a dimension somewhere.
For example, I may run the report and want to group 1/1/2015-3/20/2015 into bucket 1, 3/21/2015-5/31/2015 into bucket 2. The output of he MDX query would look like this:
Date Bucket......................Total Sales
01/01/2015-3/20/2015......$10,000
03/21/2015-5/31/2015......$7,500
At first I thought this should be pretty simple, but I'm having a little bit of trouble putting it together and would appreciate any advice!
Thanks!
February 18, 2017 at 1:34 pm
After struggling with this for a couple of days I found an example on StackOverflow that looks like it does what I need (figures it was right after I asked the question).
with member [Date].[Calendar Year].[All].[2010 - 2012] as Aggregate( {[Date].[Calendar Year].[2010] : [Date].[Calendar Year].[2012]})
member [Date].[Calendar Year].[All].[2013 - 2014] as Aggregate( {[Date].[Calendar Year].[2013] : [Date].[Calendar Year].[2014]})
select {
[Measures].[Internet Total Sales]
} on columns,
non empty
{
[Date].[Calendar Year].[All].[2010 - 2012],
[Date].[Calendar Year].[All].[2013 - 2014]
} on rows
from [Model]
If anyone has any feedback on if this is a good or bad approach I'd still appreciate it.
Thanks.
February 19, 2017 at 11:09 am
Cannot necessarily comment on the good/bad aspect of the above, but I would be inclined to use named sets. See reference here: https://technet.microsoft.com/en-us/library/ms145487(v=sql.110).aspx
February 19, 2017 at 12:21 pm
Thanks for the reply. I originally went down the named sets path but wasn't getting the results I was looking for. A named set wasn't giving me the ability to group all the values in the set together. Here's an example:
This query
with set [Bucket 1] as {[Date].[Calendar Year].[2010] : [Date].[Calendar Year].[2012]}
select {
[Measures].[Internet Total Sales]
} on columns,
non empty
{
[Bucket 1]
} on rows
from [Model]
Was giving me the following:
Calendar Year......Internet Total Sales
2010.....................$43,421.04
2011.....................$7,075,525.93
2012.....................$5,842,485.20
But the output I would be looking for is
....................Internet Total Sales
Bucket 1......$12,961,432.17
Unless there is something I am not doing right when creating the named set using this method.
February 20, 2017 at 8:48 am
In that case your first example is the only way to achieve that. I was not able to find any other plausible way.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply