Creating Filters in Framework Manager

Filters in Framework Manager - ease the frustration

Creating a filter in IBM Cognos is normally straight forward. Most people are familiar with creating them in Report Studio and Query Studio. For more advanced folks, you might even know how to create a slicer in Analysis Studio that works just like a filter but for dimensionally modeled relational (DMR) data. However, if you have ever tried to create filters in Framework Manager you might be ready to pull your hair out and jump off a tall building. Okay, maybe a short building, but you are definitely losing your hair over it.

Filters in Framework Manager
Sounds simple … you just create a filter on the query subject and include the query subject in the package and click ‘publish’. So far, that is pretty simple - but what about getting fancy and making the filter a prompt and then formatting that prompt so that it is a drop down or a date? Now you are thinking, well … they covered prompting or parameters in IBM Cognos Training Class….what is that class called? Uh … that would be Report Studio Training and you would be correct so, you reach for your class material and thumb through the book to find parameters or prompts. You find it and go step by step and create a filter using the question mark enclosure to create a parameterized filter. Now you are cooking with gas and you do a little happy dance until you notice that your parameter is not right. You are prompting on a date and you want the prompt to show a calendar so that your end users will only see a calendar. So you think “How do I format a prompt in FM?”. You think “Oh, I know I will use that thing they showed us in Framework Manager Training Class…what’s that called? Right! A Parameter Map!” So with confidence you build a parameter map and realize that didn’t work like you planned and now you are stuck.

Well, you are not really stuck because you Google “How to make filters in Framework Manager” and you arrived here … ok, happy dance - someone is going to give me the steps and end my madness.

1. What you filter on must be in the query subject – this will ensure that the parameterized filter will show up no matter what your end user picks from the query subject.

2. You must use question mark enclosures – the syntax looks like this [query subject item] = ?Parameter1?

3. You must use the prompt info – on the item that you are filtering by, you will set the prompt info to be a drop down, date, search type … etc. You will also input the display values and the use values.

4. Publish – publish the package

This little post has been a service announcement sponsored by a frustrated framework manager modeler. You can simply pay thanks by sharing this post with someone else that might be standing on the ledge of a short building.

If you need help navigating framework manager or training on more advanced techniques please contact us at services@lodestarsolutions.com

If you like this blog on filters in framework manager, please check out this blog on IBM Cognos Report Studio: Bursting Cognos Reports by clicking here.

Crosstabs Dimensional Data in Report Studio

​Crosstabs Dimensional Data in Report Studio

Let me first start by saying this is not a how to or even necessarily the right way to do things with Dimensional Data in Report Studio, rather some options to explore learned from hours spent cursing at the computer trying to get the seemingly ‘simple’ things to work in crosstabs. You know, things like adding two columns together, calculate a variance, or filter out crosstab rows with no data. I run into these issues most when I’m building financial reports where we’re comparing multiple forecast/budget versions across specific spans of time.

Dimensional Data in Report Studio

----The Tuple

Love me some Tuple! Whenever I need to pick some data point seemingly out of thin air… the answer usually lies in the form of a tuple. Basically, the tuple function allows you to pinpoint cross sections of dimensions. Note: it only works with data MEMBERS!
Sample: tuple([2009],[Budget])

----Calculating Totals/Variance
For those occasions when clicking the nice little ‘Total’ icon isn’t working for you… don’t be afraid to explore the currentMeasure function and build the total yourself. It will really help you start to understand what the crosstab is trying to do when you click the ‘total’ icon and help you create more robust crosstabs. Some sample syntax:
Sample: total(currentMeasure within set[Forecast]).
If your Forecast is nested in the crosstab you might want to try using within detail.
Sample: total(currentMeasure within detail[Forecast]).
This same logic can be extended to help compute variance.
Sample: total(currentMeasure within detail [Budget]) – total(currentMeasure within detail [Actual])

----Filter crosstab rows.
I recently created a report where I had to show Hours worked by employee by project for the entire company. Many projects had employees that didn’t have any worked hours allocated to a project for a particular time period… yet these employees were still showing up in the report. Naturally, the report ballooned to well over 100 pages. To fix this, I embeded the filter function in a new data item to help filter out the unwanted rows.
Sample: filter([Employee],tuple([2009],[Actual Hours]) >0 or tuple([2009],[Actual/Fcst]) >0)

As a side note to this, in Cognos 8.4 there are options to filter out rows with nulls and zeros, however, I know most of you are still 8.3 or earlier versions. So until you’re on board with 8.4, hopefully this will help you stay afloat!

If you liked this blog on Crosstabs Dimensional Data in Report Studio, then check out this blog on what is Cognos TM1 performance modeler by clicking here.

X