|Figure 1. Report Projects: To create a new report project from Visual Studio, select "New Project," and select "Report Project" or "Report Project Wizard" from the "Business Intelligence Projects" project type.|
Data Sources and Datasets
To create a new report project from Visual Studio, select "New Project," and select "Report Project" or "Report Project Wizard" from the "Business Intelligence Projects" project type.
The first thing you do to create a new report project is to create a data source (if you use the wizard you will be led though this process). Reports projects can have as many data sources as you needthey are simply XML (.rds) files containing ADO connection strings. To create a data source, right-click the "Shared Data Sources" project item and select "Add New Data Source."
|Figure 2. Data Sources: To create a data source, right-click the "Shared Data Sources" project item and select "Add New Data Source."|
You will be prompted to select a database server and database. Your data source can be any ADO data sourceit does not have to be SQL server.
Once you have created a data source you can create your first report. Right-click the Reports item in the solution explorer, then select "Add New Report" to run through the New Report Wizard, or select "Add … Add New Item…" and choose "Report" from the "Add New Item" dialog.
The report wizard will prompt you for the data source to use, then prompts for a query string. You can click "Edit" to use the query builder to create an ad-hoc query (which automatically creates a dataset), then follow the wizard to automatically generate a report. I prefer to design my reports from scratch, particularly because the wizard does not allow you to use a stored procedure for your data source.
To add a dataset to your report, select the "Data" tab, then choose the "New Dataset" item in the "Dataset" drop-down list. You will be prompted for a dataset name, data source, command type and query string.
|Figure 3. Adding a Dataset: To add a dataset to your report, select the "Data" tab, then choose the "New Dataset" item in the "Dataset" drop-down list.|
The dataset name is whatever you want to call your dataset, and the data source will be the name of the dataset you created earlier. The command type will be one of the following options:
- Text: An ad-hoc SELECT statement, which you can type directly into the query string text box.
- StoredProcedure: The name of a stored procedure. You have to enter the stored procedure name in the query string textbox.
- TableDirect: For reading from a single table only. Enter the name of the table in the query string textbox.
In my opinion, the dataset property page dialog is missing some key productivity enhancements. For the Text
query type, the query designer is not available, and for Stored Procedures
, the query string textbox should really be turned into a drop-down list to save you the trouble of entering a stored procedure or table name.
As you create datasets, the IDE automatically adds report parameters to your report. These provide data for the parameters of your stored procedure or select statement. Report parameters can be manipulated in the "Layout" view. You can specify available ranges from a pre-defined list or from another dataset, and the selection pages that are automatically generated by Reporting Services will display a drop-down list with appropriate values.