Hi Guys,
there are some requirements for the Cognos report,
Need to display data as quarterly 
Example:  user enters date as 03-31-2023
then expecting the below output:                     
Dimension                      Q2-2022  Q3-2022  Q4-2022  Q1-2023
Great Out Door sale              50            100          30           25
Equipments                           20            30            10          30

basically, rolling 12 months data should be displayed as quarterly.
it is a Relation List report, no cubes or DQM or OLAP are involved.

if it's cube-based I would have handled it easily, it's a relational report so I think need to use some date functions like _add_months or _add_years functions or something to handle previous dates, need help on this guys.

Thanks!!

Based on the gosales database, modeled in a Data module.
The sorting of the date-related information will be easier when the format would be yyyy-Qn, which is what the following expression gives:
      cast(_year([C].[C_gosales_8_2_src].[ORDER_HEADER].[ORDER_DATE]),varchar(4))
      + '-Q' 
      + cast(floor((_month([C].[C_gosales_8_2_src].[ORDER_HEADER].[ORDER_DATE]) + 2)/3) ,varchar(1))
The filter for the last 12 months could be:
     [C].[C_gosales_8_2_src].[ORDER_HEADER].[ORDER_DATE] <= ?last_order_date?
     and _add_years([C].[C_gosales_8_2_src].[ORDER_HEADER].[ORDER_DATE],1) > ?last_order_date?

Hopefully this will give you a starting point.

Hi Guys,
there are some requirements for the Cognos report,
Need to display data as quarterly 
Example:  user enters date as 03-31-2023
then expecting the below output:                     
Dimension                      Q2-2022  Q3-2022  Q4-2022  Q1-2023
Great Out Door sale              50            100          30           25
Equipments                           20            30            10          30

basically, rolling 12 months data should be displayed as quarterly.
it is a Relation List report, no cubes or DQM or OLAP are involved.

if it's cube-based I would have handled it easily, it's a relational report so I think need to use some date functions like _add_months or _add_years functions or something to handle previous dates, need help on this guys.

Thanks!!

Hi Henk,
Thanks for the reply, it's a good start.
I wrote some code similar to this but the Quarter data are stored in data items, right? And yes the above code which you have given is working but need those quarters as columns to get count for the report.
basically need like this:
Dimension                      Q2-2022  Q3-2022  Q4-2022  Q1-2023
row1
row2


Thanks!!

Based on the gosales database, modeled in a Data module.
The sorting of the date-related information will be easier when the format would be yyyy-Qn, which is what the following expression gives:
      cast(_year([C].[C_gosales_8_2_src].[ORDER_HEADER].[ORDER_DATE]),varchar(4))
      + '-Q' 
      + cast(floor((_month([C].[C_gosales_8_2_src].[ORDER_HEADER].[ORDER_DATE]) + 2)/3) ,varchar(1))
The filter for the last 12 months could be:
     [C].[C_gosales_8_2_src].[ORDER_HEADER].[ORDER_DATE] <= ?last_order_date?
     and _add_years([C].[C_gosales_8_2_src].[ORDER_HEADER].[ORDER_DATE],1) > ?last_order_date?

Hopefully this will give you a starting point.

Hi Guys,
there are some requirements for the Cognos report,
Need to display data as quarterly 
Example:  user enters date as 03-31-2023
then expecting the below output:                     
Dimension                      Q2-2022  Q3-2022  Q4-2022  Q1-2023
Great Out Door sale              50            100          30           25
Equipments                           20            30            10          30

basically, rolling 12 months data should be displayed as quarterly.
it is a Relation List report, no cubes or DQM or OLAP are involved.

if it's cube-based I would have handled it easily, it's a relational report so I think need to use some date functions like _add_months or _add_years functions or something to handle previous dates, need help on this guys.

Thanks!!

It can be done, but it's complex. I've attached the report spec that defines the above output.

E.g. the expression for qty-PPPQ is:

case when [ORDER_DATE] 
between
          _add_days(_add_months([cLastDayOfQ], -12),1)
and  _add_months([cLastDayOfQ], -9)
then [Quantity] end

Hi Henk,
Thanks for the reply, it's a good start.
I wrote some code similar to this but the Quarter data are stored in data items, right? And yes the above code which you have given is working but need those quarters as columns to get count for the report.
basically need like this:
Dimension                      Q2-2022  Q3-2022  Q4-2022  Q1-2023
row1
row2


Thanks!!

Based on the gosales database, modeled in a Data module.
The sorting of the date-related information will be easier when the format would be yyyy-Qn, which is what the following expression gives:
      cast(_year([C].[C_gosales_8_2_src].[ORDER_HEADER].[ORDER_DATE]),varchar(4))
      + '-Q' 
      + cast(floor((_month([C].[C_gosales_8_2_src].[ORDER_HEADER].[ORDER_DATE]) + 2)/3) ,varchar(1))
The filter for the last 12 months could be:
     [C].[C_gosales_8_2_src].[ORDER_HEADER].[ORDER_DATE] <= ?last_order_date?
     and _add_years([C].[C_gosales_8_2_src].[ORDER_HEADER].[ORDER_DATE],1) > ?last_order_date?

Hopefully this will give you a starting point.

Hi Guys,
there are some requirements for the Cognos report,
Need to display data as quarterly 
Example:  user enters date as 03-31-2023
then expecting the below output:                     
Dimension                      Q2-2022  Q3-2022  Q4-2022  Q1-2023
Great Out Door sale              50            100          30           25
Equipments                           20            30            10          30

basically, rolling 12 months data should be displayed as quarterly.
it is a Relation List report, no cubes or DQM or OLAP are involved.

if it's cube-based I would have handled it easily, it's a relational report so I think need to use some date functions like _add_months or _add_years functions or something to handle previous dates, need help on this guys.

Thanks!!