When would I use this?
Say you have an SSRS report which provides users with the parameter options month and year and the report returns data for that month and year. You will likely need that month name (varchar) and year (int) to be converted into a date fields representing the first and last day of that month/year. Once you have those two dates they can then be used in the WHERE clause of your SQL query to return data within that range. The logic below will allow the conversion of month name and year into start and end dates described above. You can now take this logic and input it into a stored procedure or user defined function.
There will only ever be 12 months going forward but to create an ever updating parameter option for years please see this tutorial.
DECLARE @year AS INT
DECLARE @month AS VARCHAR(9)
DECLARE @monthNumber AS CHAR(2)
DECLARE @startDate AS DATE
DECLARE @endDate AS DATE;
SET @year = 2016
SET @month = 'February'
IF @year IS NOT NULL
WHEN @month = 'January'
WHEN @month = 'February'
WHEN @month = 'March'
WHEN @month = 'April'
WHEN @month = 'May'
WHEN @month = 'June'
WHEN @month = 'July'
WHEN @month = 'August'
WHEN @month = 'September'
WHEN @month = 'October'
WHEN @month = 'November'
WHEN @month = 'December'
END AS monthPicked
SELECT @monthNumber = (
SET @startDate = (
SELECT CAST(CAST(@year AS VARCHAR(4)) + @monthNumber + '01' AS DATETIME)
SET @endDate = (
SELECT DATEADD(s, - 1, DATEADD(MM, DATEDIFF(M, 0, @startDate) + 1, 0))
SET @startDate = (
SELECT DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)
SET @endDate = (DATEADD(MONTH, 1 + DATEDIFF(MONTH, 0, GETDATE()), - 1))
Using visual studio left click just under design to focus on the report.
Now the report properties window should be visible in the bottom right of the screen.
Set the interactive height to 0 as shown below.
You will get the following warning sign below. Limiting the report to one page, by setting the interactive height to zero, means everything needs to be loaded all at once. This will create lag when a user loads the report and interacts with it.
If you are basically using SSRS as a means for end users to pull a data dump via excel, heed this warning. If you just have a few aggregated tables and you want them all on the same page you should be fine.
When setting up your data sets in an SSRS report if you are using a complicated stored procedure, i.e. a SP which relies on dynamic SQL, temp tables or finishes with an IF statement, chances are the SSRS report will not be able to figure out what the SP returns. When this happens you will not be able to populate the data set with data.
This happens because the execution plan of the SSRS software isn’t smart enough and won’t be able to determine what fields the SP creates and therefore will not be able to create a means of storing the data on the report end.
Subsequently you’ll see the, often misleading, table below popup.
The solution to stop this from happening is quite simple, but considering how expensive this software is it’s a solution that shouldn’t have to be employed.
The solution is to trick the SSRS software by simplifying the SP. Basically perform a select on the specific fields you need with no additional logic or create a table with the exact fields you need with corresponding data types and select from that.
Use this dumb SP to populate the dataset in the SSRS report.
In the “Choose a data source and create a query” window as below, click refresh fields.
The software should now pick up the fields.
Change the SP back to the way it was before and do not refresh the fields again in the SSRS software and the fields should continue to populate as you need them.
If your job is to create reports using SQL chances are you have or will encounter this situation:
You’ve been asked to prepare a report, but the person who has asked for the report simply has a list of fields they want and they have no idea where those fields come from. They may have received previous reports in the past, so they know the fields exist, but they cannot provide any of the SQL queries used to create these reports as an example.
You, the developer, may not be familiar with that particular area of the business or associated data sources. Possibly because you typically prepared financial reports and this request has come from the operations or marketing departments.
So the first step is to locate these columns within the database.
The following query will return the Table Name, Schema Name and Column Name from the database.
In the example below all instances where the column name equals CustomerID, OrderID, OrderDate will be returned. Also Column names that contain the word Status or Promotion will also be return. Simply change or add additional columns names as needed.
SELECT T.NAME AS TableName
,SCHEMA_NAME(SCHEMA_ID) AS SchemaName
,C.NAME AS ColumnName
FROM SYS.TABLES AS t
INNER JOIN SYS.COLUMNS C ON T.OBJECT_ID = C.OBJECT_ID
WHERE C.NAME = 'CustomerID'
OR C.NAME = 'OrderID'
OR C.NAME = 'OrderDate'
OR C.NAME LIKE '%Status%'
OR C.NAME LIKE '%Promotion%'
ORDER BY SchemaName
That should help get you started in preparing the report.