To add Datetime parameter select the Report in the top menu - > report parameters. Add a new parameter by clicking the add button. The parameter name is given in the Name section which is used in the query or stored procedures as parameter. the Datatype should be DateTime.



The prompt is the display name for the parameter in the report. Therefore it should given in the user understandable format irrespective to name.



Select Report parameter can be done by two methods.

1. By Creating the Dataset and use the From Query to populate the values.
2. By populating the values directly to the parameter thro Non-queried.

1. Create a Dataset and if the parameter value should be from Database means write a select query with the column name. If it is hard-coded text means write a select statement with that text and combine all the select statement with Union statement. Once the Dataset set is defined create a new parameter. Enter the name, Data type and Prompt. If the parameter output is multiple value means check the Multi-value checkbox.



If it is "From query" means select the Dataset for that input parameter and then fill the Value field and label field. the label field is the display field in the input parameter and the value field is used for internal process.

2. If it is Non-Query means the Value field and Label field are entered directly and those values are displayed in the input parameter.

If there is default value for the input parameter enter those thro two methods. By default Null is selected means there is no default value. If the value is hard-coded means select Non-queried and enter the default text. If the value has to be retrieved from the Database means use Queried statement and select the Dataset and the value field. the top column of the result set is the default value for that column.

Once the parameter values are defined clearly check the report.



If the parameter value is not mandatory or it should allow null values means select the appropriate checkboxes in the input parameters.

To Start the Reporting service -> Start - > All programs - > Microsoft Sql server 2005 - > Sql server Business Intelligence Development Studio.



Create a New Project



As Report server project



Once the project is created, Reports and Shared Data sources folder are created by default. The Shared Data sources will act as a Data source for the report and the report contains the rdl files.



In order to connect the database and the reporting service the data source is used. To create the data source right-click on the Shared Data Sources and click “Add New Data Source”.



Shared Data source Window will pop-up asking the user to fill the Data source information. To add that Press the Edit button.



Enter the Server name if it is from other PC, otherwise enter ‘.’ To represent the same PC Database and provided the Sql server authentication and based on the Server Name it displays the Database information. Select the Database that is going to be used for the reports. After that press the “Test Connection” button to make sure the connection is established properly.



Once the connection is created the Data source Name is displayed under the “Shared Data Sources”.



To add Report – Right-click on the Reports -> Add - > New Item



A pop-up will appear and asking the user for the Report Name.



Once the “Add” Button is pressed the created Report is displayed under the “Reports” in the Solution Explorer.



Create the Dataset for the Data source to display the report



On clicking the New Dataset link a pop-up will appear asking the user whether the dataset is going to be a inline-query or stored procedure. If it is stored procedure give the Execute statement of the stored procedure with the parameter name. If it is Inline query write the query inside the Query string.



In this example I am writing an Inline Query as “select * from temp2 where empno > @Empno”. To add the Report Parameter , Menu – Report – Report parameters





By Default the Empno is added in the report parameter, if more needs to added it can be added thro this method or adding the condition with parameter in the inline query. Thro Inline query again I add a parameter thro the design mode as “Sal”. Now the query is modified as “select * from temp2 where empno > @Empno and sal > @Sal” The selected columns are displayed in the Dataset. Now to display the report drag a table from the tool box and drop it in the Layout, since it retrieves more than one value. By default the table is created with the Header, Detailed and Footer row. If need any row can be added on right clicking the row and press the “Insert above” or “insert below” button. Then Drag and drop the Dataset values in the Table in the detailed row. To view the report press the preview button next to the layout tab.





Enter some values in the empno and sal textboxes and press the “View Report” button. It displays the report based on the applied filter. We can able to export the report in the required format.