Tuesday, April 13, 2010

T-SQL Tuesday #005 SQL Server Report Services Basic Parameter Tips

Today is my first attempt to participate in T-SQL Tuesday. T-SQL Tuesday is an idea by Adam Machanic, inviting new and existing SQL Server bloggers to post about the same topic on the same day. I decided to take a look at this Tuesday’s topic, Reporting, and give it a go.
I come from developing Visual Studio applications with SQL Server databases, and Crystal Reports have been my reporting tool of choice. Until now of course, I utilize SQL Server Reporting Services. This has been a recent change, and the hardest thing for me was to find basic help or online resources. Aside from sources writings on how to install and configure SSRS, therefore I share with you basic parameter tricks. The first thing one need to know is they must have SQL Server Business Intelligence Development Studio or Visual Studio installed.
SSRS allows you to set parameters on your report on the SQL syntax. For instance, you want to show a report pertaining to specific date range, your SQL would be something like this:
SELECT ProductID, Description, ModelNo, WarrantyExp FROM Products WHERE WarrantyExp  BETWEEN (@DateFrom and @DateTo)
When you browse to this report, you will see on the toolbar two input boxes, DateFrom and DateTo. Open the “Report Data” Tab from the View menu. You will see a directory with Parameters, these being the ones in your query. You can right click and select their dataype, to where a DateTime datatype will enable a date picker in the input boxes at run time. Keep in mind that the input boxes will be names accordingly to the parameter variable without the “@” sign.

No comments:

Post a Comment