You can create a report using a stored procedure as a dataset. A stored procedure is a group of SQL statements that are used to encapsulate a set of operations or queries to execute on a database server.
This walkthrough illustrates how to create a report that uses a stored procedure as a data set.
The walkthrough is split up into the following activities:
- Creating a Data Dynamics report
- Connecting the report to a data source
- Adding a dataset (stored procedure) with a parameter
- Adding controls to the report to contain data
- Viewing the report
To complete the walkthrough, you must have access to the Reels sample database included with this installation.
When you are finished you will have a report that looks similar to the following:
Connecting the report to a data source
To connect the report to a data source
- In the Data Dynamics Reports Data Explorer, click the Add icon and select the Data Source... option.
- In the Report Data Source smart panel that appears, select the General page.
- Change the Name to Reels.
- Check the Shared Reference checkbox.
- Click the Browse button and select ReelsDataSource.rdsx, which is located in C:\My Documents\Data Dynamics\Reports\build number\Samples\Reels.
Note: In Windows Vista, the path is C:\User\your name\Documents\Data Dynamics\Reports\build number\Samples\Reels\.
Click the Accept button in the lower right corner to close the smart panel and see Reels appear in the Data Explorer.
Adding a dataset with a parameter
Dataset parameters are passed to the query to help it collect the data needed for the report. These parameters could simply be the values of a report parameter or hard coded. For this walkthrough we will hard code the values.
To add a dataset with a parameter
- Click the Add icon and select the Data Set... option.
- In the DataSet smart panel that appears, select the General page.
- Change the Name to any text that you want to appear in the Data Explorer as the name of the dataset (e.g. SalesDataForStore).
- On the Query page, set the Command Type to Stored Procedure.
- Enter the stored procedure name in the Query text box (e.g. SalesDataForStore).
- Click the Validate icon to validate the query and to populate the Fields list.
NOTE: You may receive an error at this point, which is okay because required parameters have not yet been added.
- Go to the Parameters page:
- Enter StoreID under Parameter Name.
- Enter =1002 under Value.
- Click the Accept button in the lower right corner to close the smart panel and see your dataset and selected fields appear in the Data Explorer.
Adding controls to contain data
To add controls to the report
- From the toolbox, drag a Table data region onto the body of the report.
- Hover your mouse over the Textbox located in the first column of the Detail row to make the Field Selection Adorner appear.
- Click on this adorner to display a list of available fields from the SalesDataForStore dataset, select StoreID from this context menu.
Note: This automatically places an expression in the detail row and simultaneously places a static label in the header row of the same column.
- Using the Field Selection Adorner, select the UnitsSold field for the second column of the Detail row of the table.
- Use this same method to select the NetSales field for the third column of the Detail row.
- To optionally display the static labels in bold at the top of every page of the report:
Select the Header row by clicking the table handle to the left of the row.
In the Report Formatting tool bar, click the Bold button.
In the Properties grid, set the RepeatOnNewPage property to True.
- To improve the appearance of the report:
Select the third column and change its Width property to 1in.
Select the second column and change its Width property to 2in.
Select the first column and change its Width property to 3.5in.
Tip: Making some columns narrower before making other columns wider prevents your report width from changing.
- To add a page header to the report:
- From the Report menu select Page Header.
- From the toolbox, select Textbox and draw it onto the page header section to span the entire width of the report.
- Set the TextAlign property to Center and the FontSize property to 14pt.
- Click inside the textbox and enter the text Net Sales by Store.
- From the Report menu select Page Header.
- From the File menu, select Save.
- Click the Preview tab to view the report.