Dimensional Filter with Parameter

[jeff_rob]

Hi,
After a number of years working with Report Studio I'm now trying to learn how to use dimensional models and am having a dilly of a time getting my head wrapped around the stuff.  I think I've downloaded everything about it that can be found on IBM's site and worked all the examples, but I still can't do something as simple as a filter with a parameter.

Using a sample that I found in one of the documents against the Sales and Marketing (cube), it seems like it should look something like this:

FILTER([Product line] , CAPTION( [Product line] in ?SelectLine?) )

But every variation of it that I've tried bombs.  This one gives a parsing error at position 51, which is the first question mark.

What am I doing wrong?

JR

[Lynn]

Hi,
After a number of years working with Report Studio I'm now trying to learn how to use dimensional models and am having a dilly of a time getting my head wrapped around the stuff. 

Oh I feel your pain and have been on that same journey!  :o

Your parenthesis are out of place -- the "in" clause shouldn't be part of the caption function.

Try this:
FILTER([Product line] , CAPTION( [Product line] ) in ?SelectLine? )

[jeff_rob]

Ah!  Thank you, thank you, thank you!

Looks like you've made it to high ground on the dimensional path.  I sure appreciate your help.

JR

[bdbits]

It is very confusing when you first start using dimensional models, especially building more complicated expressions instead of simple filters. But after you have it figured out, you'll have to go back to a relational package for something and realize how cool dimensional can be. Hang in there!  :-)

[pumccg]

Filtering on captions is very inefficient, even if it works. If all you want to do is filter the members in a dimension then the best dimensional expression to use is:

[level or hierarchy]->?p?
or
set([level or hierarchy]->?p?)

These expressions can be used as edge or slicer expressions. The first gives you single select, the latter multiple selections. Using [level] filters only on the chosen level. Using [hierarchy] gives you a tree prompt.

You must be very carefull when you filter, and not mix relational filters with dimensional filters.

The Report Studio User Guide now has better explanations of the two authoring styles - relational versus dimensional. However the best source dimensional authoring techniques is the Cognos one day course called Report Studio: Author Reports with Multidimensional Data. This topic was a source of much frustration to me until I did that course (now I teach it).

The filter() expression is meant to filter members against a measure, not really to select members. For example:

filter(set_expression,[Revenue]>100)

[jeff_rob]

To bdbits:  Thanks for the encouragement.  It's good to hear that crossing the finish line is worth the pain, dimensional does seem to have much more flexibility.

To pumcgg:  Thanks for the advice about the course.  I'd love to take it and will ask if the budget might handle it.  They might be willing to do that for something that comes recommended.

I see what you're saying about using CAPTION.  I changed it to:

Code Select Expand

set([sales_and_marketing].[Products].[Products].[Product line]-> ?SelectLine? )

and that works.  Thanks for the pointer.

I was initially just trying to get a NOT IN filter to work by supplying

Code Select Expand

FILTER([sales_and_marketing].[Products].[Products].[Product line] ,  CAPTION([sales_and_marketing].[Products].[Products].[Product line])  NOT IN ('Camping Equipment','Outdoor Products'))

and didn't know enough to remove the CAPTION when I tried to convert it to a parameter!

[MFGF]

Hi,

One of the biggest mindset changes when authoring dimensional reports is that you are working with members and sets of members rather than data items. In a relational paradigm you would need to filter on the Product Line item to include or exclude product line rows you didn't want. In a dimensional paradigm you can simply select the desired Product Line members from the member tree and drag them into your reporting object. If for your exclusion example you wanted all product lines including any which might appear in the data in future (except the two you indicated), you could perhaps bring in a query calculation that uses an except() function to exclude the relevant members from your product line level set of members - eg except([Your Product Line level], set([Camping equipment member], [Outdoor products member]))

The thing that gets you tied up in knots at first is realising that filters are mostly not required - they can be replaced with query calculations that address the desired members directly from the cube. Once you get used to thinking about things dimensionally, you will realise how cumbersome relational reporting has been for all these years! :)

Cheers!

MF.

[pumccg]

Have you seen this Cognos article?

http://www.ibm.com/developerworks/data/library/cognos/page364.html

[jeff_rob]

Yes, I had downloaded it a week or so ago.  However, I appreciate you pointing it out (and it may help some other sojourner).

It was very helpful in getting a broad overview of dimensional reporting in general and a better idea of how filters should be employed.  I learned a couple of neat things that you can do with crosstabs that I'd never considered too.

I've also downloaded and worked through the Cognos BI Dimensional Functions document at: http://www.ibm.com/developerworks/data/library/cognos/page82.html

I still don't understand a lot of what's in there, but I trust it will fall into place as I practice with it.

There are also some documents pertaining to specific uses of dimensional functions on the Cognos Proven Practices site at:

http://www.ibm.com/developerworks/analytics/practices.html when you click on the Dimensional queries link under the Reporting heading.

Hope this helps someone else who's trying to learn this.

Jeff