Can a single prompt be used to filter a Cube based query and a relational query?

[djarratt]

Hi All,

I have a need to create a single report including one query from a relational source and one query from an Analysis Services Cube. This is no problem as I can happily create a Framework Manager package including both and then reference them in a report.

My problem comes in trying to set up a single prompt to filter both queries. My instinct is to create the prompt from one of the cubes dimensions, and then try to convert the MUN into something that the relational query can then use in a filter. However, I have tried this in a multitude of different ways, and generated a wide variety of errors whilst never achieving the objective.

Have any bright sparks out there managed to filter both relational and dimensional queries using a single prompt?

Thanks,

Dan

[CognosPaul]

It is certainly possible, you can also use the same method to filter multiple cubes.

The prompt use should be the code of the value you need to filter. For example

Products
Use   | Display
------+----------
   1  | Bikes
   2  | Motor boats
   3  | Moon buggies
   4  | Harrier Jets
   5  | Tardises 

The relational filter would be a very simple

Code Select Expand

[Product_key] = #prompt('Product','integer')#

The dimensional is obviously more complex since this is a dimensional query you aren't a detail filter. Since the end result of this needs to be a member, we'll start by building the member directly.

First drag in a member from the product line into a data item. You need to do this to understand the structure of the MUN. Depending on the structure of the MUN the following method may not be possible.

The MUN may look like the following:

Code Select Expand

[Cube].[ProductsDimension].[ProductHierarchy].[ProductName]->:[PC].[@MEMBER].[4]

From the table above we know that's a Harrier Jet.

We can use this to build a full MUN with the prompt macro:

Code Select Expand

[Cube].[ProductsDimension].[ProductHierarchy].[ProductName]->:[PC].[@MEMBER].#sb(prompt('Product','integer'))#

The sb function will wrap whatever the prompt returns in square brackets.

That method will only work if you're selecting a single value, or for cubes that have MUNs that look like that. But what if you have a MUN that looks like the following:

Code Select Expand

[Cube].[Dim].[Hier].[Level]->:[2010].[2].[3].[7].[27]

That is MUN shows me that it's in a time hierarchy Year 2010, Semester 2, Third Quarter, July, 27. The previous method will obviously not work. For this case, and for multiple values, we can use the filter function.

The filter function returns a set of members that match a specific criteria. The criteria we need to filter on is one of the member attributes. You remembered to set the member attributes in whatever application you used to build the cube, right?

Since I'm being especially lazy today, I'll just give you an example from a powercube.

Code Select Expand

filter
(
   [Cube].[ProductsDimension].[ProductHierarchy].[ProductName]
   , [Cube].[ProductsDimension].[ProductHierarchy].[ProductName].[CategoryCode]
      in (#promptmany('Product','integer')#)
)

And, as I said before, you can use this method to slice multiple cubes in different queries.

[djarratt]

Thank you very much for that.

I had to do a bit of fiddling as I couldn't get the first method to work - not sure what the [PC] stands for, but I suspect I need to replace with [YK], I also wasn't sure what the [@MEMBER] phrase was doing. (I'm working with SQL if that's of any relevance)

I tried the filter concept however and got that to work.

Cheers,

Dan

[CognosPaul]

It's possible that you need to replace the [PC] with [YK].  I was just giving an example from one of my cubes, which may have differently formatted MUNs than your cubes. (Also, I'm not sure what the PC or YK stand for).

One thing I should have added - when working with cubes I find it's best to turn off aliasing MUNs. Go to Tools-->Options-->Report and uncheck "Alias member unique names". This will allow you to drag a member into the data item to see its structure.

I'm very happy that you got it to work.

[MFGF]

(Also, I'm not sure what the PC or YK stand for).

Another jaw-droppingly clever post from Paul there - when I read these I realise how little I know!  However, I can add a little to the quote above - PC stands for PowerCube.  I always thought MSAS cubes used MS at the beginning of their MUNs though - YK is a new one on me! :)

MF.