如何利用查询计算创建动态显示超12个月数据的Cognos图表报表

Got it, let’s walk through exactly how to build this dynamic Cognos report— I’ve worked on similar rolling date and KPI formatting requirements before, so here’s a practical, step-by-step approach:

Your example shows the report shifting from 2018.12–2019.12 to 2019.12–2020.12 once the calendar rolls into a new year. Here’s how to automate that:

1.1 Create Calculated Date Fields in Your Query #

First, add two calculated fields to your main query to dynamically calculate the start and end dates of your report range:

• Rolling Start Date: This grabs December 1st of the previous year. Use this expression:

  _add_months(_first_of_year(current_date), -1)
  

  For example, if today is in 2019, this returns 2018-12-01; if it’s 2020, it returns 2019-12-01.
• Rolling End Date: This grabs December 31st of the current year. Use:

  _last_of_month(_add_months(_first_of_year(current_date), 11))
  

  For 2019, this gives 2019-12-31; for 2020, it’s 2020-12-31.

If you actually need a true rolling 12 months (e.g., current month minus 12 months) instead of the calendar year-to-December range, swap the expressions to:

• Start Date: _add_months(current_date, -12)
• End Date: current_date

1.2 Apply the Date Filter #

Add a filter to your query using these calculated fields to restrict data to your dynamic range:

[Your Date Column] between [Rolling Start Date] and [Rolling End Date]

Now your report will automatically update its date range without manual tweaks.

To display KPI values divided by 1000 (like turning 98765 into 98.765), follow these steps:

2.1 Create a Calculated KPI Field #

In your query, add a new calculated field for the adjusted KPI:

[Original KPI Value] / 1000

If you need to handle null values (to avoid errors), add a quick check:

if ([Original KPI Value] is not null) then ([Original KPI Value] / 1000) else (0)

2.2 Format and Add to the Summary Table #

• Go to the properties of your new calculated field, find Data Format, and set it to Number with your preferred decimal places (e.g., 2 for 98.77).
• Drag this adjusted field into your summary table’s corresponding cell. If you want to show both original and adjusted values, include both fields and label them clearly (e.g., Original KPI vs KPI (in Thousands)).

• Test the Date Logic: To verify the range switches correctly, temporarily replace current_date with a fixed date like date('2020-01-01') in your calculated fields and check if the range updates to 2019.12–2020.12.
• Dynamic Chart Titles: Make your chart’s title update with the date range by using a calculated title expression:

  'KPI Trend: ' || cast([Rolling Start Date], varchar(10)) || ' to ' || cast([Rolling End Date], varchar(10))
  

• Handle Edge Cases: If your data has partial months at the start/end of the range, add a filter to exclude incomplete periods if needed.

内容的提问来源于stack exchange，提问作者Richa Srivastava