Dear Gurus
We have a requirement where we need to show the users the data for Selected Quarters.
We have two date fields, Start date and End date.
In database, Start date field has values like 2008-01-01, 2008-04-01, 2008-07-01, 2009-01-01 etc
and End date field 2008-03-31, 2008-06-30, 2008-09-30, 2009-03-31. The dates are not continuous, it means if there is no data for a quarter then it does not appear in the field.
How to achieve the following ?
1. In Prompt page, user has to select a start date and the end date should be filtered to show the next FOUR end dates in the End date prompt values. That means if the user selects 2008-01-01 as start date, the end date prompt value should show 2008-03-31, 2008-06-30, 2008-09-30, 2009-03-31 in the End date prompt.
2. If the user selects 2008-01-01 as the start date and 2009-03-31 as end date, all the quarters in between should be considered for reporting data. This date range can be across two years.
2. The report should show data for the quarters that lie between the start date and end date, do some percentage calculations/pick the maximum value from all the quarters and show all these in the report.
We are using a relational Database and Cognos 10.1.
Please help !
Thanks
Pravin