Dynamic Date Scale for Chart X-Axis / Query Summary

[werkkrew]

Hello -

New to the forum, and to Cognos.

I am currently working on a report which has an input parameter where the user can specify a number and a unit, for the historical range of the report.

For example:

30 Days
6 Months
4 Years

Where the number and the days/months/years are separate parameters.

There is a table in the report that shows a snapshot of the data from the newest date available in the data set, and there are charts based on the historical data.  Right now the query has a date field in it with a resolution of 1 summary data point per day.

This all works fine, except the report consumer wants the charts to show 1 bar per day on a daily report, 1 bar per month on a monthly report, and 1 bar per year on a yearly report.

Right now the bar chart seems to make one bar per day of data (due to the date field in the query) up to about 35 bars, then it will smooth out the data so it can fit within 35 chart bars.

What would be the most correct way to have the query or chart resolution vary based on the parameters?

Currently my primary query has a field [Start Date] defined:

Code Select Expand

CASE
WHEN ?interval_unit? = 'days' THEN  _add_days(current_date, -?interval_value?)
WHEN ?interval_unit? = 'months' THEN  _add_months(current_date, -?interval_value?)
WHEN ?interval_unit? = 'years' THEN  _add_years(current_date, -?interval_value?)
ELSE _add_days(current_date, -?interval_value?)
END

and a detail filter defined:

Code Select Expand

[Full Date] between [Start Date] and current_date

[BigChris]

Hi,

It sounds to me like you want three charts in your report. Experiment with that and let me know if you need any help

[Lynn]

Yup, you could conditionally render the appropriate chart based on the unit parameter. You will need additional query items that hold the month and the year associated with each of the dates. The extract function might be handy to produce these.