February 23, 2007 at 7:35 am
Dear Forum
I've created a virtual cube which combines a set of measures with another cube with a single 'count distinct' measure. I am now attempting to drillthrough to details which works fine on the atomic level but when I go up the dimension to higher levels the resultset returned contains all the records, not just the distinct ones.
To keep things simple the virtual cube contains the dimensions WARDNAME and DATE and the measures OCCUPIED BEDDAYS and NO OF PATIENTS (distinct). If I select a particular ward and day (the lowest level of the date dimension) I get all the patients on that ward on that day. If I select a particular ward and month (the next level up the date dimension) I get a record for each patient on each day, whereas I want to get the distinct list of patients on the ward.
To be clear, the aggregated values work fine, it's just when it comes to drillthrough that this problem occurs.
Can anyone help with this?
February 23, 2007 at 10:24 am
From memory I thought you could modify the drill through query for a partition.
But anyway, before doing this, it sounds to me like you're getting what you should be getting. The 'distinct'ness or 'distinct'ing of the patients is being done for you by the AS server. Drilling through (in AS2K) executes a query on the SQL/source server and will pass through the dimensional values as 'filters' to the query/source thereby limiting the rows returned. The drill through is going to a fact table that has multiple rows for patients and I've not seen the drill through query send a 'distinct' clause which is what it sounds like you're wanting. Like i mentioned earlier, you may be able to specify a specific drill through query bt this query would then be used for all drill throughs.
Just as a left field suggestion, mybe you could use an action/actions and pass your parameters to a RS report that runs exactly th query you want run?
Steve.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply