Now that we've covered how to create a stored procedure and then create a shared data source that uses a stored procedure, its time to display the data in our report using a table in Part 3 of my Building a Report from the Ground Up series. In Part 3 I'm going to provide a quick demonstration on how to display the results column returned by your stored procedure in a table and then how to add a little bit of formatting to your table to give it some pop.
Now before we go any further, I just want to clarify that there are some significant differences between SSRS 2005 and SSRS 2008. There are some architectual differences that I won't get into at this point. There are also some big differences in report authoring in SSRS 2008 as opposed to SSRS 2005:
(1) New Chart types that include pyramid, range column, range bar, just to name a few.
(2) New Gauge Data Region that can display a single value within a range.
(3) Tablix Data Region combines both features of the matrix and table. The Tablix also allows the creation of multiple row and column groups at the same time. Very useful.
(4) Report Data Pane shows the data sources and data sets in a tree view, instead of in the Data tab in SSRS 2005.
There's a few other differences as well. To read about all the new features in SSRS 2008 compared to SSRS 2005, check it out at Microsoft TechNet. I am using SSRS 2008, so if you are using SSRS 2005, you may notice these differences. I'll try to mention the differences as we come to them, but if I forget or you have a question, please send me a message so that I can clarify for you.
1. Open Business Intelligence Development Studio and create a new report project and add a new data source as well as a dataset. After creating your new report project, you should be staring at the Design pane with a blank design surface, called the body.
2. If your Toolbox pane is not already open, you can open it by clicking View at the top in the menu bar and selecting Toolbox, or by pressing Ctrl + W once, then X. You can see a pic of the Toolbox below. I'm using Visual Studio 2008, so if you are using BIDS 2005 or Visual Studio 2005, your Toolbox may look different.
3. In the Toolbox pane, click the Table tool and drag it into the design surface. You could also right-click in the design surface, select insert, and then select table to add a table data region. You should have a blank table featuring three columns and two rows: A Header row and a Data Row.
4. Now its time to add some data to this table. In the Report Data pane, select a field you wish to display in the report and drag it into the table, dropping it into one of the Data columns. The Header column in the top row is populated with the name of the Field. The Data column in the bottom row will show the actual data of the field. I'm using a field called Name from a Products table, so the Header displays "Name" while the Data shows "[Name]". If I want to remove an existing column from the table, click anywhere inside the table so that the gray frame appears around the table. Right-click on the gray frame above the column you wish to delete and select Delete. Alternatively, to add or insert a column to the table, right-click in the gray frame, select insert, and then select Left or Right to create a new column to the left or right of the column you have selected. Now that I have added the Name field to the table, I can change the column Header to anything I want. Since Name is a bit ambiguous, I'm going to change the header to "Product Name."
5. Add other fields to your table by dragging them from the Report Data pane into your table. To preview what your report will look, click the Preview tab directly above the design surface. This is what my simple report looks like for now:
6. Sure, we're displaying the data, but that just isn't good enough. We've got to add some bling to it so that it's not so boring to look at. To add some color to the header, click inside the table and select the Header row by click in the gray frame around the table next to the Header row. The entire Header row should now be selected. In the Properties pane on the right, change the BackgroundColor property to something exciting. You can also change the font color and weight to make it stand out.
7. Let's also format the List Price column so that it doesn't show four decimal places. Right-click the List Price field in the Data Row and select Text Box Properties. Click Number in the Text Box Properties window and then select Currency under Category. Change Decimal Place to 2 and check the checkbox next to Use 1000 seperator (,). Click OK. Now when I preview my report, it should look a bit more refined and professional.
It's starting to come together, but we've still got a lot of work to do before I can let the company executives look at this and maintain a clear conscience.
I know I said I was going to cover parameters in Part 3, but I decided that the subject of parameters would be better suited being covered in a later section, so keep an eye out for that. In Part 4 we'll cover creating a Matrix to display data and adding a Header and Footer to report. Later on we'll cover formatting a bit more so your data is as easily readable as possible, so stay tuned!