In Part 1 of my Building a Report from the Ground Up series, we covered how to create a stored procedure, store it in the database, as well as some of the reasons why you should use a stored procedure in your reports. In Part 2, we're going to cover how to get started creating your first report, adding a shared data source to access your database, and adding a dataset that will make use of the stored procedure we created last time.
But before we get started, I wanted to share a little tip that will help your copy of Business Intelligence Develoment Studio (BIDS) open up a bit faster. Go into your start menu, navigate to BIDS and right-click on it and select properties. In the target text box, add "-NOSPLASH" at the end. Mine looks like this:
"C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe" -NOSPLASH
For some reason, BIDS and Visual Studio open much faster without the splash screen. Now on to the reports.
1. Open your copy of BIDS. In the Menu bar, click File, New, and select Project.
2. In the Project types window, select Business Intelligence Projects. In the Templates window, select Report Server Project. Name your Report project and your solution and click OK.
3. In the Solution Explorer on the right in your BIDS environment, you should see your newly create solution and report project. Your solution can contain different kinds of projects including Integration Services projects, Analysis Services projects, and Web applications, just to name a few. Any shared data sources you create will be in the Shared Data Sources folder and any Reports you create will be stored in the Reports folder.
4. To create a shared data source, right-click the Shared Data Sources folder in the Solution Explorer and select Add New Data Source. Name the Data Source and select the type of Data Source in the drop down box. For this example, I'm selecting Microsoft SQL Server. Next, click edit to specify the Connection String properties. The reason we want to use a Shared Data Source is so that it's very easy to change our Data Source from a development environment to production. Simply edit the Shared Data Source to point to the desired server & database and any reports that referenced that Shared Data Source will now be using the new connection information.
5. In the Connection Properties window, type in your Server name. Try to avoid click the drop down arrow, especially if you have a lot of servers to choose from because then you'll have to wait for the drop down list to populate. I'm working on my local machine so I typed "localhost". Specify which kind of authentication necessary to connect to the server. Lastly, select your database. Finally, click Test Connection to make sure you have a connection to the desired database. Click OK and click OK again.
6. Now that we have access to the database from our report, lets create a new report. Right-click on the Reports folder, select Add, and select New Item. In the Add New Item window, select Report (not Report Wizard), and name your report, leaving the .rdl extension. Click OK.
*If you are using SSRS 2005, this is where it gets different. Since I'm using SSRS 2008, I created my data sources and data sets in the Report Data Window. If I was using SSRS 2005, I would click the Data tab and add them there. There are some serious differences between SSRS 2005 and SSRS 2008, but I'll cover those more in my next article.
7. In the Report Data window on the left, we can now add a reference to our Shared Data Source and a new dataset. In the Report Data Window, click new and select Data Source. Click the radio button next to Use shared data source reference and select the Shared Data Source that you previously created. Click OK.
8. Right-click on the Shared Data Source reference in the Report Data window and select Add Dataset. Name the Dataset and under Query type, click the radio button next to Stored Procedure. In the drop down menu, select the stored procedure you created. Click OK.
9. Now you've created your first dataset using a stored procedure and are ready to start creating your first report that uses datasets.
Your newly created dataset should look like this, with the result columns displayed in the tree view.
In Part 3 of Building a Report from the Ground Up, we'll cover creating a table that displays our dataset fields as well as using parameters in our reports, so stay tuned.