Graph - just show last 12 months
-
I have a line graph in my report.
My report has no parameters on it.
What I ideally want it to do is if I run the report today (May) it will only show me the last 12 months so May to May.
At the moment it is including April from last year as well, so would want that excluded. If I run it in two months time in July I would want it to only show July to July.
The chart has the categories of Financial Year (in the 2014-2015 format) and the actual month name.
-
Do you need information from April somewhere else in your report?
If you don't you should probably change the query to obtain your resultset. We could help you with that if you share it along with some DDL and sample data
-
Hi,
The key here is the DatePaid field. This is the date that drives the graph.
The graph gets the Financial Year and Month from a date table.
The plots on the graph use an expression -
=SUM(IIF(Fields!InvoicePaid_.Value = "Yes" And Fields!PaidTarget.Value = "InTarget",1, 0))/Count(IIF(Fields!InvoicePaid_.Value = "Yes", 1, Nothing))This allows a % to be calculated on the graph series you see above, to just show invoices that have been paid and are in target.
The report uses TSQL and I'm wondering if there is a flag I can place against any invoice that was paid 12 months ago. And then via a filter remove them from the graph?
So today date is 02/06/2015. I would want to mark all invoices with a flag upto 01/06/14 so they all appear on the graph - but May and April 2014 would be excluded.
Then when I run the report next month say - 7th of July 2015 - June 2014 will no longer appear on the graph but the 01/07/2014 and onwards would?
Hope that makes sense.
-
What I ideally want it to do is if I run the report today (May) it will only show me the last 12 months so May to May.
Why not add the filter in your stored procedure?
SELECT ...
FROM ...
WHERE DatePaid BETWEEN @StartDate AND DATEADD(d,-1,DATEADD(yyyy,1,@StartDate))
(my syntax may be slightly off, but basically it says "show data for all dates between the start date and a year ago plus one day".) Then you don't have to worry about it, because your "time window" is a year long.
-
I believe that the formula to calculate the date is a little bit wrong.
Here's an example according to what I understood:
DECLARE @Date datetime= GETDATE()
SELECT DATEADD(MM, DATEDIFF(MM, '19010101', @Date), '19000101'), --Using one year difference
DATEADD(MM, DATEDIFF(MM, '19000101', @Date) - 12, '19000101'), --Subtracting one year with same dates
DATEADD(MM, DATEDIFF(MM, 0, @Date) - 12, 0) --Substracting one year using integers instead of dates
Obviously, you'll have to use one of these formulas in your WHERE clause. If you have any more questions, feel free to ask.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply