We learned custom report generation in our previous post i.e Creating report without using Report wizard. So, now in this article we will go further in report generation using MS SQL Server Reporting Services (SSRS) 2008. This article will take you through the steps needed for creating a parameterized report using SSRS.
What do we mean by a Parameterized reports?
- Parameters are used in a report to provide a dynamic feature by providing user input to the report execution process i.e. the report is generated as per the inputs supplied by the user.
- Parameters are usually used to filter large set of records so as to narrow down the end report for better analysis.
- Parameters can be data-driven, or they can have a static list of valid values.
- Parameterized reports are frequently used for drill-through and sub-reports, connecting reports with related data.
Let’s validate our theoretical concepts with the practical implementation of a Parameterized reports.
STEPS TO FOLLOW: –
Step 1. Go to start, then All Programs and click on Business Intelligence Development Studio (BIDS). Click on create a New project. This will show New project screen. Choose Report Server Project from the template under Business Intelligence Projects tab. Specify desired Name, Location, Solution name for the new report server project.
Step 2. Go to Reports under Solution Explorer, then go to Add and choose New Item option from the menu. Choose Report and provide the name for the report. This will show you the working environment where we will design and preview our report.
Step 3. Drag and drop the table option from the toolbox into the design window. This will pop up Data set properties window. Now, we need to configure this window with correct credentials. If everything is supplied correctly then clicking on Test connection button will pop a successful message box.
After testing the connection, click OK button. This will create a new data source connection. Now, we need to have data from the connected database which we made recently. For this click on Query Designer button.
Step 4. Click on Query designer button to generate the query automatically. Now, click Add table button and choose your tables from the database and execute the query. This will show you the query and columns from the database.
Adding Parameter to the report: – Now, we will add Gamer_Id as a Parameter field for this report. So our query will look like –
Select Gamer_Id, Name, Gender, Location, Game, Age
NOTE: – We can also write the query manually in the text area without going into the query designer part but it is a good way to write in a query designer as we can debug the query easily.
Step 5. Once you click OK button, go to the parameters folder in “Report Data” – you will find that @param_id automatically appears in the “Parameters” Fields. Double click on this parameter (@param_id). This will show Report parameter properties screen.
This is the place to configure, how parameter should behave – should it allow blank values or Null values or Multiple value. You can write any text under the prompt field to get the input from the user. Also, we can configure the Visibility, Data-types, Available values, Default values etc. Let’s leave it as it is for now.
Step 6. It’s the time to hit the preview button and see the report. Clicking Preview will ask for the input field ( As in here, it is asking “Enter Gamer’s Id”). Lets enter 1 as Gamer’s Id and view the Report.Now, Enter Gamer’s Id = 3 and view the Report.
NOTE:- The parameter appears as a Text box in the preview pane. If we need to have pre-configured values in a drop down menu then go to Report parameters properties by double-clicking parameter (@param_id) and click “Available Values”/”Default value” and click “Get values from a query”. Choose the datasets created and specify the other fields.
With this we validate our theoretical knowledge on creating a Parameterized reports using MS SQL Server Reporting Services (SSRS) 2008. Using parameters in a report provides a dynamic feature to the report and makes it interactive and attractive in many ways. Your rating and comments are welcome.