Full disclosure, I'm new to Cognos, Report Studio, Query Studio, etc,,,
I have a report that I created in Report Studio. The report has detail filters in it to extract data from the previous quarter, and they are working fine. I want to display the dates of the quarter at the top of the report. Can anyone tell me how to do this or at least point me in the right direction?
TIA
Can you get at the calculations that the filter uses to get the dates for the start and end dates? You should be able to build a layout calculation with those calculations - possible looking something along the lines of:
'Dates for Quarter: ' + [Calc for Start Date] + ' to ' + [Calc for End Date]
Thanks BigChris for the suggestion. I did try it but I keep getting errors, so I'm obviously doing something wrong. I tried two different approaches. First, I inserted a table with a report expression. Next I tried the layout calculation. In both cases I tried to insert the detail filters but again, I kept getting the syntax errors.
Here are the detail filters. These work fine for the report, but not for displaying the dates.
[TF-M Level2].[Tier-Month].[Year]>= extract (year, _first_of_month (_add_months (current_date, -3)))
[TF-M Level2].[Tier-Month].[Year]<= extract (year, _first_of_month (_add_months (current_date, -1)))
[TF-M Level2].[Tier-Month].[Month Num]>= extract (month, _first_of_month (_add_months (current_date, -3)))
[TF-M Level2].[Tier-Month].[Month Num]<= extract (month, _first_of_month (_add_months (current_date, -1)))
If you were trying to use the entire filter expression, that might be the problem. Just use the right-hand part of the expression, like what is underlined here.
[TF-M Level2].[Tier-Month].[Year]>= extract (year, _first_of_month (_add_months (current_date, -3)))
When I paste in the expression as you suggested and validate, I get an "Invalid expression" followed by "A parsing error was found at or near position 9.
extract (year, _first_of_month (_add_months (current_date, -3)))
Hi,
A Layout calculation is (as the name suggests) for layout objects rather than for calculating things using query items. Because you are using functions that relate to query items, you will need to add your expression in a query calculation. This can sit within a Singleton at the top of your report layout. You can add text items around it, too to present a more user-friendly message.
Cheers!
MF.
The query calculation seemed to do the trick :)
Thank you all for your help, I really appreciate it.