Author Topic: cross hierarchy slicer to simulate an 'or' statement  (Read 1394 times)

Offline tlambert

  • Full Member
  • ***
  • Join Date: Jun 2010
  • Posts: 14
  • Forum Citizenship: +0/-0
cross hierarchy slicer to simulate an 'or' statement
« on: 10 Apr 2012 02:24:02 pm »
Hi all,
I have a dimensional data source (a cube I built) for hospital discharge data.

This is what I need to do: Count up the number of discharges where the principal diagnosis code or any of the 9 other secondary diagnosis codes where the code equals 493.

I have each of these dimensions in separate hierarchies.
1) Principal diagnosis
2) SD1 - the first secondary diagnosis code
3) SD2 - the second secondary diagnosis code
4) SD3
9) SD9

If I create one slicer for principal diagnosis = 493, and one slicer for each of the secondary diagnosis codes=493, the results are wrong (far too small, basically it's always 0) because it's like putting an 'and' statement between the slicers. Like saying: Count all the discharges where the principal diagnosis code = 493 and all of the secondary diagnosis codes are also equal 493.

What Cognos won't allow is a slicer which crosses hierarchies.

Perhaps my model of the diagnosis codes should be different (I'm also up against cube size issues). Perhaps there's a magic method to overcome this [can I somehow use the filter() function which isn't supposed to be used on dimensional sources?]

Another simplified example of what I'm trying to do might resemble: tell me how many products we sold to any woman or person over 50.

Thanks in advance for any help you can provide.
Cognos version I use: 8.4.1
State of NH Department of Health and Human Services