Urgent Help needed - Filter MDX result by date range

Question

Post reply

Urgent Help needed - Filter MDX result by date range

Del Piero

SSCrazy

Points: 2265

July 19, 2005 at 1:53 pm

#110148

Dear all,

I really need help on this. We know it's difficult to filter a date range from an MDX query ... it's easy to specify an exact date, like "where [time].[year].[2005]", but what if I want to specify a range? Like a date between '20050301' and '20050331'.

The following example does partially what I want to do:

WITH   MEMBER [Measures].[YTD Salaries] AS   'Sum(PeriodsToDate([Time].[Year]),[Org Salary])'SELECT  {[Measures].[Org Salary],[Measures].[YTD Salaries]} ON COLUMNS,     [Time].[Quarter].Members ON ROWSFROM HR

 Result will be like:

            Org Salary      Ytd Salaries

Q1           $9,858.38       $9,858.38

Q2           $9,860.21       $19,718.59

...............

In other words, "Org Salary" is the value from Ytd Salaries filtered

out by a particular date range.

Here, we have the time dimension showing on the rows, and 2 members

showing on the columns. What if I want to add one more dimension

on the columns? e.g., what I need to do if I want to add a "Store"

dimension to the columns, thus showing one store in each column,

and the values in the columns are being filtered out by the "PeriodsToDate"

function as well?

Many thanks,

delpiero

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

Hey Delpiero,

It seems like you've got a couple of questions in there

To answer the 'how to put store into this query', you have a couple of options. If you want the

two measures within/underneath each store on columns (ie 3 stores = a report with 6 columns = 2 measures X 3 stores),

then just crossjoin the stores dim with your measures, so...

WITH MEMBER [Measures].[YTD Salaries] AS 'Sum(PeriodsToDate([Time].[Year]),[Org Salary])'

SELECT

{[Store].[USA].CHILDREN} * {[Measures].[Org Salary],[Measures].[YTD Salaries]} ON 0,

{[Time].[Quarter].Members} ON 1

FROM HR

If you want the stores on rows, then they'll have to either nest in or around the quarters (ie it either goes

Store1|Q1

Store1|Q2

Store2|Q1

Store2|Q2

Or

Q1|Store1

Q1|Store2

Q2|Store1

Q2|Store2

To achieve either one, use a crossjoin again as per the column example (NB crossjoin can be done via

the Crossjoin(<set1>,<set2&gt function of by using the shortcut * (asterisk, ie SHIFT+8).

If you want to use a different time filter like a range then I think you have to create a calculated member

and use this within the WHERE clause. e.g

WITH MEMBER [Time].[bob] AS '[Time].[1998]:[Time].[2005]'

and thenuse this in your where clause. NB This methods works well/OK when you're dealing only with SUM

type aggregations, you may get mixed results when using aggregations like distinct count.

Steve.

Del Piero SSCrazy Points: 2265 More actions · Answer 2

Hi Steve,

The cross join is exactly what I need and it solved my problem. It saves me hours of work. Many many thanks!

Best regards,

delpiero

Del Piero SSCrazy Points: 2265 More actions · Answer 3

Just one minor issue is that when I used the asterisk it prompted me error, but it worked fine when I used the "crossjoin" function.

delpiero

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 4

The asterisk needs to be between two sets, and typically I 'set' the result also to be sure; so the following should work. I think the * is also covered off in BOL under the Crossjoin function entry.

{{<some_set>} * {<some_other_set>}}

NB that the * is a MSft specific implemtation of MDX to submit this to say a SAP BW or Hyperion server may not work.

Steve.

WilburSmith Hall of Fame Points: 3225 More actions · Answer 5

Hi, (I'm AS newbie)

I viewed this thread and based on the solution I fear that my case could be hopeless?

I've been asked to implement cubes with several dimensions and several metrics like:

Item - prodgr

customer

disccount_circle

Item - prodname

principal

etc..

Sales gross Prev month

Sales gross prev YTD

Sales gross YTD

Sales net prev month

Sales net YTD

Sales quantity prev month

Sales quantity YTD

etc.

(prev - previous year, YTD - Year To date)

the biggest cubes/reports have 25 posssible dimensions and 60 possible metrics...

Well, if the formulas got quite a lot bigger when adding one dimension (delpiero's case here), will they continue to grow as rapidly (or even exponentially) when more and more dimensions are added?

Only instructions I got was to create calculated members of the metrics but I do not have any idea of MDX and I just told them OK. I thought this must be done in the report software (BO Olap Intelligence) but there seems to be the same MDX functions availabale. Their previous reporting tool, Micro Strategy, implements these as TSQL in the reports, which makes sence to me.

What I need is someone to tell me some quidelines i.e. no way or yes it is possible but with solid mdx knowledge or...

Regards, Ville

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 6

Some (most?) front end tools will let/help you do these type of time calcs on existing measures. You can also pre-create them as calculated measures. Some do this with them as measures, some do them as members within the time dimension. Whichever approach you take, there are mdx functions that can and will help (e.g. the YTD function).

Steve.