Overview & Features
SSRS Toolbox provides the following controls: Textbox, Line, Table, Matrix, Rectangle, List, Image, Subreport, Chart, Gauge. The most frequently used controls are the Textbox and Table.
Textbox -- can be used as both, a textbox and a label (if you make it readonly).
Table -- used for displaying data in a tabular format. It has Header Row and Detail Row, which is nice, because I can use just a Table in SSRS where I would have to use a subreport in Crystal Reports.
SSRS provides a powerful expressions engine based on Visual Basic syntax, a capabilty to add some vb.net code, and it also allows to add a reference to a dll. Also, there are quite a few built-in functions, similar to those in Crystal Reports. Although, if you have a Crystal Reports background and are switching to SSRS reports, some things will require getting used to. For example, to display current page number you would need to place a textbox control in the report's footer, go into expressions for that textbox and code it there instead of just dragging and dropping as it is done in Crystal Reports. This is what the expression for something like "Page 2 of 5" would look like:
="Page " & Globals!PageNumber & " of " & Globals!TotalPages
SSRS Development
To develop SSRS, you need to have BIDS (Business Intelligence Development Studio) installed. It can be part of SQL Server 2008 install.
Once you have it, you can create a couple of different types of projects: Report Server Project and Report Server Project Wizard. My preference is the Report Server Project, because it allows for more customization.
Before you begin designing and developing a new SSRS report, you have to create a DataSource. If you are planning to use this datasource in more than one report, make it shared.
Once you are done creating a datasource, you will have to create a dataset that you will use with the report. Here is the confusing part: you can't create a dataset without having a report, so you have to add a report to your project first and then create a dataset based on the datasource you created earlier. When creating a dataset, SSRS allows you to either type your SQL into a text field or you can choose the option to enter a stored procedure name. I personally prefer using stored procedures, since they allow to do much more than a plain SQL statement.
Let's create a simple report consisting of a table and a header. The header will contain the report name and will be placed in the header of the report; the data will be displayed in a table and will be placed in the body of the report. In order to do that, we'll drag a textbox control from the toolbox and position it within the header section and type a name for the report in it; then, we'll drag a table control from the toolbox and put it in the body section of the report. Once that's done, let's specify what data will be displayed in each table column. Assume that we are displaying customer info from Northwind database, we'll have 3 columns: First Name, Last Name, and Phone Number.
The table control will display little buttons in each table column for you to choose what data will be feeding this column, such as First Name, Last Name, or Phone Number in our case. Let's display First Name in the first column, Last Name in the second column, and Phone Number in the third column, and give those columns appropriate headings. Now the report is ready and you can run it by clicking the Preview tab. Optionally, you may add a page number in the footer and a date of last execution of the report in the header.
To display the execution date and time, simply add a textbox to the report that will display this information, right-click on the textbox and choose Expressions; in the expression text area enter the following code:
= Globals!ExecutionTime
This will display the date and time when the report was last executed in a General Date format (something like 1/26/2011 11:15:05 AM).
No comments:
Post a Comment