Create Parameter Filter with values filtering on multiple selections

[fkli222]

I have my data set to a Cognos Planning model that we currently use. The months dimension is a little unorthodox. We have the months with Hours and Dollars combined into one dimension (this made the model significantly faster).

Now in Report Studio, I would like to create a report parameter with the months Jan-Dec. If I were to create a automatic parameter based off the source value, it would display Jan Hours, Jan Dollars, Feb Hours, Feb Dollars and so on. How would I create the values Jan-Dec and when selecting Jan for example would filter Jan Hours and Jan Dollars.

This would be a quick fix for now in the report until we make these changes in Framework Manager. Please let me know if you would like me to clarify further.

Thank you in advance!

[MFGF]

I have my data set to a Cognos Planning model that we currently use. The months dimension is a little unorthodox. We have the months with Hours and Dollars combined into one dimension (this made the model significantly faster).

Now in Report Studio, I would like to create a report parameter with the months Jan-Dec. If I were to create a automatic parameter based off the source value, it would display Jan Hours, Jan Dollars, Feb Hours, Feb Dollars and so on. How would I create the values Jan-Dec and when selecting Jan for example would filter Jan Hours and Jan Dollars.

This would be a quick fix for now in the report until we make these changes in Framework Manager. Please let me know if you would like me to clarify further.

Thank you in advance!

Is it a relational or dimensional package?

I modified the thread title for you too :)

MF.

[fkli222]

Thank you for fixing the title!

I believe the data source is setup as relational.

[MFGF]

Thank you for fixing the title!

I believe the data source is setup as relational.

Ok, so what query items exist in the query subject? If you have a Month query item and you drag just this into a new list report, what do you see?

MF.

[fkli222]

Ok, so what query items exist in the query subject? If you have a Month query item and you drag just this into a new list report, what do you see?

MF.

We don't have a Month query item currently. The query item we have merges the dimensions month and hours/dollars/etc. If I drag in the Month query item, you would see Jan Hours, Jan Dollars, Feb Hours, Feb Dollars and so on.

I need to have a filter where if you select Jan or Feb it will pull both Hours and Dollars into the report. Also it would be nice if we can also filter on whether you want hours or dollars.

Thanks!

[MFGF]

We don't have a Month query item currently. The query item we have merges the dimensions month and hours/dollars/etc. If I drag in the Month query item, you would see Jan Hours, Jan Dollars, Feb Hours, Feb Dollars and so on.

I need to have a filter where if you select Jan or Feb it will pull both Hours and Dollars into the report. Also it would be nice if we can also filter on whether you want hours or dollars.

Thanks!

Hi,

So one single item of data contains multiple values? Or do you mean the query subject contains multiple query items? I'm struggling to picture how your data is structured. Could you mock up an example of what exactly you have so we can understand?

MF.

[fkli222]

Hi,

So one single item of data contains multiple values? Or do you mean the query subject contains multiple query items? I'm struggling to picture how your data is structured. Could you mock up an example of what exactly you have so we can understand?

MF.

Not really sure how else to explain it. Let me mock up an example.

You can see in the current is how our model from planning currently pulls data. We had to change this because our model was so big and running slow and IBM suggested we merge our Month and Calc dimensions into one. This now makes reporting more difficult.

So in the proposed you will see how it should be displayed. I want to be able to report in this format without changing the structure of the model. I know this can be done in framework but for now I would like to know how to manipulate it to display in report studio.



Hope this is more clear.

Thanks!

[CognosPaul]

While I'm not familiar with your model, it's odd that merging the two dimensions should be the solution. Personally I think this situation should have two tables,

Time:
Year
Month
MonthKey

Agents:
AgentName
AgentKey

Facts:
AgentKey
MonthKey
Hours
Dollars

That would create the type of data warehouse Cognos is optimized for. It sounds like you're working essentially against a single table:

Agent (Bob, Tracey, Frank)
MonthCalc (Jan Hours, Jan Dollars)
Value (5, 4, 3, 10)

Is this correct? If it is, you could probably do something like creating a data item for Month: substring([Month Calc],1,3) and another for Calc: substring([Month Calc],4,100)

You'd filter those two data items with value prompts with static values.