Hi All,
I am trying to build a crosstab which compares the measures as at qrtA with those as at qrtB.
My datasource is [YOA]X[AsAtQrt]X[Measures]
____|_____________________________________________________________________
YOA |MyMes1 |MyMes2 |
|MyMes1(qrtA) | MyMes1(qrtB) | Change |MyMes2(qrtA) | MyMes2(qrtB) | Change |
I thought a way to get these results would be to set up 2 queries with a prompted slicer on the quarter dimension (not a time one, but a hierarchy in this case).
So I have Query1 retrieving MyMes1 and MyMes2, in the context of a prompted slicer, and Query2 the same but with it's own slicer.
If I try to drag items from each query into the same crosstab I get an error about different query contexts, which is understandable.
I've tried joining on the unfiltered dimensions and unioning the 2 filtered queries, neither of these seemed to help as the resulting query has no items in it - are these only for relational models?
Should I perhaps be using tuple to pull out the data items I'm after?
Your advice, castigation, or comiseration are all invited!
Regards,
Alexis
Ah, and I'm expecting to need to set the Changes up as a pair of calculated members in CombinedQuery once I have the filtering going on at the base level.
Users could want to compare data 'as at' any 2 quarters, but are happy to use a prompt page (doesn't have to be all snazzy-dynamic).
Hi All,
Please let me know if I can clarify what I'm looking to achieve here - I can't find anything on combining queries with 2 different slicers around the 'net.
Cheers,
Alexis
Hi,
If I'm understanding your requirements, it should be fairly straightforward to achieve this with a single query - a DMR package behaves like an OLAP cube in terms of functionality.
Firstly, you can find one of the required quarters (quarter B?) via a query calculation
[Quarter level from the hierarchy] -> ?Qtr?
If you need the prior quarter to compare against (quarter A), you could pinpoint this with a second query calculation
lag([your quarter item above],1)
or you could simply prompt for the second quarter in the same way as the first, if that's what you prefer.
You can then add query calculations for your measure values, using the tuple() function
eg
tuple([Your Quarter A calc],[Your Measure 1])
Hope that helps!
MF.
Thanks very much for that MFGF - I'd thought I was being clever using slicers to reduce the dataset rather than filtering after retrieval, but then putting the Join in kills a lot of the point of going Dimensional in the first place. Baby steps, as overconvoluted as I can make 'em...
Reading suggested that tuple() calls can be expensive (and this is DMR not real OLAP), in the end I got it all working by defining some new query items that sliced on different dimensions even though in the crosstab they were all on the columns. I like this thinking in sets lark :)
Quote from: Rutulian on 21 Jan 2011 08:34:19 AMI like this thinking in sets lark :)
Makes my brain hurt most of the time! :) I like the Personal Text in your profile, by the way! :D
MF.