How to evaluate an expression for last 12 weeks in SSRS

Question

I have the following sql query which will save some value to a table by date:

/* ADD TODAY DATE (e.g. 04-11-2014) WITH THE ABANDON RATE % */
--TRUNCATE TABLE [db].[dbo].[table]
IF NOT EXISTS(SELECT * FROM [db].[DBO].[table] WHERE CONVERT(VARCHAR(10),GETDATE(),110) = [Date])

INSERT INTO [db].[dbo].[table] --uncomment on second and consequent run

SELECT CONVERT(VARCHAR(10),GETDATE(),110) AS [Date], [F5] AS [Abandon_Rate]
    --INTO [db].[DBO].[table] --on first run and then comment
    FROM [db].[DBO].[table2]

So every Sunday when it runs it will have the following data:

Date          Abandon_Rate
4-13-2014     12.3

And every week it will keep inserting to the existing data for every week.

How can I write a expression in SSRS where it will only show the last 12 weeks data only in a bar chart.

So for example, if there was data for the last 18 weeks:

Date             Abandon_Rate
4-13-2014        12.3
4-6-2014         9.6
3-30-2014        8.9
3-23-2014        11.3
...
1-15-2014        7.6

I will only see data from 1-26-2014 to 4-13-2014 and consequently, every week it will increase a week from the previous week.

Is there only one row in the table per week? If so, then this should work: SELECT TOP 12 CONVERT(VARCHAR(10),GETDATE(),110) AS [Date], [F5] AS [Abandon_Rate] FROM [db].[DBO].[table2] ORDER BY [Date] DESC — BateTech
Yes just one row per week :) How about if I want to add a column with the week number as well? — Si8

KrazzyNefarious KrazzyNefarious · Accepted Answer · 2014-04-16T21:57:10

Try the following as your data source query

SELECT TOP 12 [Date], [Abandon_Rate]
FROM
[db].[dbo].[table]
ORDER BY CONVERT(DATE,[Date]) DESC

Every week when you run this query, you get the latest 12 weeks data.

How to evaluate an expression for last 12 weeks in SSRS

1 Answers