Create an RDL File

Note: These steps describe how to create the file and add the needed parameters, embedded data source, and embedded data set for the OLTP version of the report. The steps are similar for the data warehouse versions. But the stored procedures executed by the datasets in those versions are located in the data warehouse database. However, the data warehouse versions still access the OLTP database to populate the available values for the prospect plan type parameter. This could also be done from the data warehouse. But as of writing, that would require an additional extension to the data warehouse.

Open Visual Studio 2008 with Business Intelligence Development Studio functionality installed.

For more information, see Microsoft's MSDN article at Introducing Business Intelligence Development Studio.
To create a new Report Server project, click File > New > Project. The New Project screen appears.

Otherwise skip this and the next step and open your existing Report Server project.
Select the Report Server Project template from the Business Intelligence Projects type templates, enter a name, and click OK. The new project appears.
From Solution Explorer, right-click Reports and select Add > New Item.

The Add New Item screen appears.
From Categories, select Report Project and from Templates select Report.
Enter a name and click Add.

The report appears in the designer.
Connect the RDL to your Blackbaud Infinity OLTP database.

From the Report Data window, right-click Data Sources and select Add Data Source.
The Data Source Properties screen appears. Enter a name such as BBInfinity.
Select Embedded connection and from Type, select Microsoft SQL Server.
Click the Edit button next to Connection string. The Connection Properties screen appears.
From Server Name, select the name of the server which hosts your OLTP database.
From Connect to a database > Select or enter a database name, select the name of your OLTP database such as BBInfinity.
Click OK. You return to the Data Source Properties screen.
Click OK. The Data Source is added to the RDL file.
From Report Data, create a data set from your Blackbaud Infinity OLTP database. Right-click Datasets and select Add Dataset. The Dataset Properties screen appears.
The goal is to create a dataset for each stored procedure in the report.

Enter a name such as USR_USP_REPORT_PLANSTAGEDURATIONSCONSECUTIVE and select Use a dataset embedded in my report.
From Data source, select the data source you created for the OLTP database.
From Query type, select Stored Procedure.
From Select or enter stored procedure name, select the name of the stored procedure in your Report Spec.

Note: If you create the RDL file first, you can temporarily add the stored procedure to your development database with SQL Server Management Studio. If you create the Report Spec first, you load the spec with LoadSpec. This way when you select the stored procedure, the fields and parameters will be recognized by the dataset.
Click Refresh Fields. If the stored procedure exists in the database in your connection, the other tabs of the Dataset Properties screen will be updated. This is easier than filling those out manually.
Open the Fields tab of the Dataset Properties screen and confirm the fields were found.
Click the Parameters tab of the Dataset Properties screen.
There is no report parameter to map the dataset parameter to yet. Click OK. Visual Studio will probably recognize the discrepancy and add the parameter. If not, right-click Parameters and add the PROSPECTPLANTYPECODEID parameter.
We need to populate the available values for the PROSPECTPLANTYPECODEID parameter. Add a new dataset for that. Right-click Datasets and select Add Dataset.
For Name, enter ProspectPlanTypeCode.
Select User a dataset embedded in my report.
Select the OLTP data source you created.
Select Query type > Text.

In the Query field, enter:

select [ID], [DESCRIPTION] from [PROSPECTPLANTYPECODE]

Click OK.
Return to the Parameter Properties screen for the PROSPECTPLANTYPECODEID parameter.
From Available Values, select Get values from a query.
From Dataset, select ProspectPlanTypeCode.
From Value field, select ID.
From Label field, select DESCRIPTION.
Click OK.
Return to the Parameters tab on the Dataset Properties screen for the stored procedure dataset.
You can now select a parameter value for the parameter. But click the function button next to the Parameter Value field. The Expression screen appears.

Enter this expression.

=Parameters!PROSPECTPLANTYPECODEID.Value

Click OK twice to exit those screens.
Right-click the PROSPECTPLANTYPECODEID parameter and select Parameter Properties.

Create datasets for the other two stored procedures:

USR_USP_REPORT_PLANSTAGEDURATIONSNONCONSECUTIVE

USR_USP_REPORT_PLANSTAGEDURATIONSOVERLAPPING

Create a parameter for user ID. Right-click Parameters and select Add Parameter.
From Name, enter ALTREPORTUSERID and from Prompt enter Alt Report UserID.
From the Default Values tab, select Specify values.
Click the function button next to the Value field. The Expression screen appears.
From the Set expression for: Value field, enter:
```
=User!UserID
```
Click OK twice.
Save the RDL file.