Cognos Report Studio Monthly date

1
votes

I need to calculate a logic on the basis of Weekending Date in a report for the Monthly report

We have two pages of reports in the report studio

  1. Weekly Report with the weekending data (Example: 26/02/2021) Day 1 of the week starts from Saturday to Friday
  2. Monthly Report with the month data. I know the monthly calculation to get the data current month data using the below TRANSACTION_DATE between _first_of_month (_add_months (current_date,0) and _last_of_month (_add_months (current_date, 0)

But here the requirement is how can we calculate based on the Weekending Date. The requirement is to show the previous month data even in the 1st week of next month (New Month) that is from (01-Mar-2021 to 05-Mar-2021) since the weekending of the weekly report is showing the data for 26/02/2021 (Friday).

This should work dynamically since this is a scheduled report and we are not having any prompt page to select the date ranges since the report take to long to execute.

Please share your ideas and thoughts on this.

Your help is much appreciated.

Yours Sincerely Cognos Man

1
cognos
Let me know if this is correct. Show the previous month data even in the 1st week of next month if it is before Saturday. 1) We will need to calculate the week for the month. 2) Then if it is the first week of the month, show the prior month, else current month - VAI Jason
Hey Jason, You are 100% correct friend. Yes the requirement is to show previous month data in the 1st week of next month before Saturday. Yes your logic is right. Do you have the code with you dear? If so can you please post. Thanks in advance Thanks M Rao - MRAO
Yep. Let me know when you get a chance to try it and how it went - VAI Jason

1 Answers

0
votes

We need to know

  • day of the week
  • first week of the month
  • current week
  • prior month range
  • current month range

For the example, we will refer to the transaction data item as [Date]

To get the week, we are going to create a data item [Week of Year] like this:

_week_of_year ([Date])

To compare, we are going to create a data item [First of Month]

_first_of_month (Current_date)

That will allow us to create another data item [First of Month Week]

_week_of_year ([First of month])

Almost there, we need the day of the week [DOW]

_day_of_week (Current_Date,1)

We can create a data item just to make the filter a little easier, let's call it [Test] with an expression like this:

IF ([Week of Year] = [First of Month Week] and [DOW] < 6)Then('Prior')Else('Current')

Now we can create a filter

([Test] = 'Prior' AND 
[Date] between [Prior Month Start] and [Prior Month End])
OR 
 (([Test] <> 'Prior' AND 
  [Date] between [Current Month Start] and [Current Month End])

To get the starting points:

  • [Current Month Start] definition would be

    _first_of_month (Current_date)

  • [Current Month End] definition would be

    _add_days (_first_of_month (_add_months(Current_date,1)),-1)

Prior month would be similar, just reduce the month, to get PrevDate

_add_months (Current_date, -1)

For example, [Prior Month Start]:

_first_of_month ([PrevDate])

For example, [Prior Month End]:

_add_days (_first_of_month (_add_months([PrevDate],1)),-1)