Hi
I am moving from being an 'intermediate' user of Query Studio to a 'novice' user of Reports Studio...!
I have a report I created in Query Studio and am trying to automate it for the users - to do so I need to remove any date prompts (to allow it to be scheduled), and have it collate data for previous 7 days (to be scheduled to run each Monday).
So, I have a data item TRANSACTION_DATE in the report (normal format dd/mm/yyyy).
The report currently runs with a filter "[TRANSACTION_DATE] = current_date" - it runs but I need it to run for 'previous 7 days'.
I've played around with [TRANSACTION_DATE] in_range (current_date -7 :current_date), needless to say it won't validate.
I hope that is clear. This must be a common requirement with a simple solution - please can someone point me in the right direction?
Thanks
I seem to have found a solution, just got to verify the data.
Two filters:
[TRANSACTION_DATE] >= _add_days(current_date,-7)
[TRANSACTION_DATE] < current_date
Still be useful to see if anyone has any comments...is there a better way.
No, that last attempt did not work either. Returned the data just for 1 day, yesterday, rather than the previous 7 days. Tried 'in_range' but it does not like that.
You solution does not seem bad at all. Did you check what could be the cause of the data not being returned? Are there any other filters at work? could it be that the source does not yield any 'older' transaction dates? What happens when you temporarily disable the filter?
Quote from: andy.lee on 07 Feb 2012 08:33:36 AM
I seem to have found a solution, just got to verify the data.
Two filters:
[TRANSACTION_DATE] >= _add_days(current_date,-7)
[TRANSACTION_DATE] < current_date
Still be useful to see if anyone has any comments...is there a better way.
Andy,
You don't need two filters. All our reports have date range filters like this:
[TRANSACTION_DATE] >= _add_days(current_date,-7) and current_date
If the TRANSACTION_DATE column contains time as well, then use this:
[TRANSACTION_DATE] >= trunc(_add_days(current_date,-7)) and trunc(current_date)
Sriram.
http://www.cognosonsteroids.com
Hi - some good feedback, thanks. I did get it working (before I read your replies!) with
[TRANSACTION_DATE] between _add_days(current_date,-7) and current_date.
Part 2:
In the header area of the report I want to show to the user a summary of the dates for which the report is returning data. Yes, the dates are contained in the 'List' body, but I just want a simple message to show the user the values of (current_date,-7) to (current_date)
I've been playing around with a 'Text Item', source type 'Report Expression'.
In there I have tried inserting an expression that would give me "(current_date,-7) to current_date" but just can't get it to work.
Tried combinations of AsOfTime() and _add_days but cannot find the syntax/command. Been browsing for examples, looking at the IBM documentation, really strugglign with somethign that I guess is simple!
You can use layout calculations.
Create one with this expression:
_add_days (ReportDate (), -7)
And other with this expression:
ReportDate ()
You can arrange them as needed with other text items. You can also set the data format for the expressions so they display as desired.
If you want to use a query calculation you need to use some sort of query container (or associate the query to the page) and have a query item that contains _add_days(current_date,-7) and another than contains just current_date.
These little things can be annoying to figure out but you'll be zipping along in no time.
Superb!!! Thanks :)