Impromptu Cross Tab Report past 13 months

[jjb373]

Hello All!

I am currently using Cognos Impromptu.

I am creating a cross tab report which sets up like this:
ROWS = Product Name (field from table)
Columns = Month (field from Time Dimension table showing just month number, example 6 for June basically takes a date and strips off everything leaving just month number)
Cells = Total Issue Count (Formula: total(if(upper(Issue Inquiry Id) = 'ISSUE') then (1) else (0)

My filter has the following:
year (Open Date) = ?Year? and Product Name in (?Product?)

For the ?Year? prompt the user selects a year
The ?Product? prompt points to a .txt file with a list of products. This is static so the user does not need to select anything.


Here is my problem. The report is to show how many Total Issues each product has for a given month. I can get this to work just perfectly for my current year because of the ?Year? prompt. The result is the Total # Issues per product each month for 12 months.

What we want is a running time frame showing the Total # Issues for each product for the past 13 months.

Like this:
Dec05 Jan06 Feb06 Mar06 .... Nov06 Dec06
Product1 15 25 13 19 11 19
Product2 30 35 30 31 40 29


I would like to set up the report so the user is not prompted to enter a year. Thus the report looks at the current month and also displays the previous 12 months. Basically December 05 to December 06

Does anyone have any suggestions.



-- JJB373 --

 

[SaskMike]

I have a couple of thoughts for you on this....

1. change your filter to have a start and end date (ie open date between ?start? and ?end?). This will allow the users to as large of a report as they want.

2. Use the add-months function. I haven't tried this, but it should work. It would look something like this open_date between add-months(?Date_Prompt?,-13) and ?Date_Prompt?.

Hope one of these work for you...

Michael