Applying Filter to Report using string operators

[ssp]

Here is the example.

I have a first prompt such as; "Please select month: (jan, feb, mar, apr, may, jun, jul...)

This prompt filters the first report which is at a month level.

However, my second report is by YTD and has (jan YTD, feb YTD, mar YTD, apr YTD, may YTD, jun YTD, jul YTD...) as column headers.


I want the second report to filter the column header by using the "month" YTD combination based on the first prompt filter.

for example, if the user selects month ; "Oct" in the first prompt, the filter in the second report should automatically display "Oct YTD" as the column header.


1. Where should this secondary filter take place?
2. How to concatenate the "YTD" string with the first month prompt such that the second report is filtered by Oct YTD in the example above. By the way, this Oct YTD is a member in the Month dimension off a Dim Modelled source and OCT YTD is a consolidation of (Jul, Aug, Sep, Oct)

Suggestions much appreciated.

Thanks.

[CognosPaul]

You can do this using prompt macros.

Specifically, your first query should be something along the lines of #prompt('Month','string')#

The YTD query should have this prompt:

Code Select Expand

#prompt('Month','string','','','',' + '' YTD'' ')#

This will automatically concatenate YTD to the end of the string submitted to the prompt.

This being said, it's not a very efficient way of doing things. It would probably be better to use a month key. If possible, set up the db so 10 = Oct in the time dimension, and 10 = Oct YTD in your YTD source.

[billylodz]

you can also create calculation for YTD  based on Month selection.

[ssp]

Hi billylodz, thanks for your suggestion. I like that idea as well but I have the YTD aggregations already in my month dimension, which are defined in my TM1 cube.