Graph - just show last 12 months

Question

Graph - just show last 12 months

TSQL Tryer

SSCrazy Eights

Points: 8869
More actions
June 1, 2015 at 2:41 am

#317881

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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 2

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.

pietlinden SSC Guru Points: 63503 More actions · Answer 3

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.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

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.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2