Download SSRS_1.zip - 1,784.1 KB
How To Create Sample Reports Using SSRS:
Introduction
Microsoft has come up with its own reporting service, in conjuction with SQL server database to introduce the Microsoft SQL Server Reporting services[SSRS]. It provides projects of type Business Intelligence Project thus enabling not only large companies but also medium-sized and small companies also to earn from its benefits. This aids in better business decisions too.
SSRS provides several extensions towards the data rendering, delivery and security of reports thereby allowing it to have a higher programmable ability. This innovative approach enables reports to be created with lesser development effort[compared to other reporting services], along with customized security options.
SSRS is a comprehensive reporting platform whereby reports are stored on a centralized web server (or set of servers). Because reports are centralized, users run reports from one place. Having centralized reports also means that report deployment is quite simplified.
Architecture
After using SSRS, the architecture is just like a small operating system. The Report Manager is the central person who acts as a manager to decide when the reports will be scheduled to run along with maintaining the user profiles on the report server. Also, you can ask the report manager to view or search certain reports. He also helps in site property configuration and folder management in the report server.
There is something known as a Report Server, at which all the reports reside. All other activities pertaining to SSRS is done at report server. I believe it acts like a workstation for the reporting tool.
Report Designer is basically a graphical tool that are hosted within the Microsoft Visual Studio IDE. Report Designer provides a tabbed windows for Data, Layout, and Preview that allow you to design a report interactively. You can add datasets to accommodate a new report design idea, or adjust report layout based on preview results. Also, he provides query builders, an Expression editor, and wizards to step you through the process of creating a simple report.
There is also a Scheduling and Delivery processor who pushes the reports to email inboxes or ftp locations. Like an operating system the adds processes to the queue, the Scheduling and Delivery processor adds processed[executed by the report processor] reports to the queue.
The Report Processor, as the name suggests, executes the reports present in the server. Now that we know the basics of SSRS, lets get starting with our first report.
Getting Started
After installing SQL server reporting services on your system, start the Visual studio IDE.
Go to File -> New Project, and you will be shown a prompt with 'New project'.
Select Business Intelligence Projects from the Project Types. As this is our first project, use Report Project Wizard in the templates pane.
Specify the name of the project as well as the location where the project will be placed.
On click of OK, you will be prompted with a report wizard screen. Click on Next to follow up to the next screen.
On the next screen, you will need to create a datasource for the report. This screen is similar to the Connection string creation wizard on creation of a udl file. However, here you just need to click on Edit to specify the server name and the database the will be used from that server. The connection string is automatically created.
This datasource can be made as a shared datasource for multiple reports to access. This can be done by checking in the checkbox present below.
On click of edit, you will be prompted with the Connection Properties Screen. Here you can add Servername and database name.
Here:
1:select Servername
2:Use SqlServer Authentication
3:select or Enter a Database
4:Test Connection
Then press ok
On click of Next, you will be prompted with the Query Builder screen. Here you can add tables, select columns as well as execute the SQL statements therby created.
Based on what query suits your report, create the SQL statement and proceed forward. On next click, you will be prompted with the report type screen. You can choose as Tabular or matrix. To make things simpler, use the Tabular format.
On next click, you will come to the table designing screen, wherein you will be prompted to display the fields as Page, Group or Details. The relevant locations where the fields will be placed is shown at the side.
On next click, you will be prompted with the Table Style prompt, which contains a list to choose. Select any from them .
n next, you will be prompted with the deployment details screen. Specify the report server name ; normally it is http://localhost/ReportServer. if you are using another server then you can specify the location as http://servername/ReportServer . Also provide in the deployment folder.
Finally, the Report name needs to be entered and voila, you got your first report in place.
nou can preview the report to change the data specs using the 3 tabs from the report designer - as mentioned previously.
Here:
1:select preview
2:enter credentials here
3;select view report
Sorting:
Right click on this field Productid and select properties .
Select the interactivesort tab.
Select the checkbox(add an interactive sort action to this text box) .
Then press ok.go to preview.
Use Reports to pass parameters:
Suppose you need to pass a value from Report 1 to Report 2, then in normal asp.net applications, we either use context or query strings. Lets see how we can do this too.
To demonstrate this, I've created 2 reports Parent.rdl and Child.rdl. The child report has in input parameter named ChildProdID. Select any column from the Parent report as shown below.
Right click on this field and you will see the following parameters
Select the textbox properties and select the navigation tab.
Select the 'jump to report' tab, and set the report to which you want to jump i.e Child report.
This means that this column will be treated as a hyperlink and on click of this hyperlink, we will jump to Child report. However, we have a parameter that needs to be provided to Child report in order to be executed. Hence, we click on the Parameter button situated at the Navigation tab. On click of this, we get another tab - Parameters.
In the dropdown we can select the parameter name for that report. For the parameter value, select the field that needs to be evaluated to the parameter name for the report to be executed.
Run the Parent report and click on any of the product ID to navigate to the Child report.
Manual report creation :
The Above section, while perhaps overly simplistic, does show you how to get a report up and running with zero code and zero property setting. Obviously, such reports will not meet most development requirements, but do not discount this option as a starting point for more complicated reports. Once you've created a report with the Report Wizard, you are free to make any modifications to it.
In this section, we are going to create a more realistic report from scratch, including how to create and use a shared data source object, stored procedures, and how to format your report, set report properties and use report parameters.
Creating a shared data source
Before we start the report, we are going to build a shared data source i.e. a data source that is common to, and can be used by, all of the reports on a reporting server. While each report can contain its own connection information, it is good practice to use shared data sources, as it will save you a lot time and headaches. This way, you only have to set the connection information once. For example, let's say your company has Development, Test and Production environments. If you put the connection information into each report, you will have to change it each time when you publish to Development, Test and Production. If you use a shared data source, you still have to set up a data source object for each of three environments, but you can simply publish the report to each environment, and they will automatically use the connection information associated with that environment.
From the menu select Project > Add New Item > Select Data Source. On the General tab, call the data source "ReportsDB". Leave the Type as Microsoft SQL Server. Click the Edit button on the right, and enter the connection information for Reporting Demo, as before. Click OK on the Shared Data Source screen and the data source is done.
Add a new report
From the menu select Project > Add New Item. Select Report and name it "FirstReportMan.rdl". A new report will be added to the project, and the Report Designer will open at the Data tab. At this point, let's take a closer look at the Report Designer tool. At the top of the Report Designer window are three tabs: Data, Layout and Preview. The Data tab is used to build data sources for your report. The Layout tab is the physical report designer where you set up the header, the footer and the data presentation of the report. The Preview tab allows you to actually run the report from Visual Studio 2005, without having to publish it to a report server first. If your report takes parameters, the Preview tab will ask you to fill them out before it runs the report.
The first thing we need to do is get data into our report. At the top of the Data tab choose <New Dataset…> from the dropdown list:
The Dataset dialog opens:
Name the dataset "ReportData", and select the ReportsDB data source from the dropdown. Select StoredProcedure as the command type, and enter "spr_CustomerSelectAll" in the query string box. Click OK to close the dialog.
Test the dataset by clicking on the big red exclamation point at the top of the report designer. This executes the query and displays the results. You can repeat these steps if you wish to add multiple datasets to your report.
Setting up the report display
Next, switch to the Layout tab. This is where you actually build the report display. Start by dragging a table from the Toolbox, onto the Body section of the report. By default, the table shows a header row at the top, a detail row in the middle and a footer row at the bottom. You can add additional columns to the table by right-clicking on one of the columns and selecting one of the two Insert Column options.
Click on the Datasets tab under Toolbox on the left-hand side of the Visual Studio environment. You should see the ReportData dataset. Expand it, select FirstName and drag it to the first cell of the body row of the table. This will display the First Name field in the first column. Now put the LastName in the second column, and the CustomerStatus in the third:
Note that, when you drag the column FirstName into the body row of the table, SSRS make a guess as to what to call the row. It calls it "First Name". You can click on the Preview tab to view the report.
Adding formatting
Let's add the DateOfBirth column to the table. You can expand and shrink the size of the columns by highlighting and dragging:
If you wish to get rid of the footer row of the table and then click on any field in the table. You will see a grey box surround the table. Right-click on the icon at the beginning of the body row and then click the Table Footer option to deselect it.
The table header is next. To format all the cells in the header in the same way, click and drag over all the cells to select them. Open the Properties window and set background color, font, and so on.
Note, however, that if you wish to change the justification of the text or the size, then you do so using the main menu at the top, not via the properties window. Again, you can click on the Preview tab to view the report.
Add a report header
The table already has a header, but the report can also have a header and footer. Report headers and footers appear on every page of the report. Put the Report Designer in Layout mode and select Report > Page Header from the menu. A new band labeled Page Header appears on the report above the body. Drag a text box onto the header and click directly on it. Type "My First Report", not in the Properties window, but right on the control.
To adjust the text style and size of your heading, highlight the text box and use the option from the top menu.
Formatting
Each field can have formatting. Let's deal with that ugly Date of Birth field. The time information is superfluous, so let's format the date properly. Right-click on the cell that contains the date of birth, and choose Properties. Next, select the Format tab.
Click on the second button (labeled "…") to the right of the Format code field. When the dialog opens, use the standard options. Select Date in the list on the left, then choose
the sample date format you want on the right. Click OK to close the dialog.
Click OK to close the Properties window and then click on the Preview tab to view the report.
Setting report properties
To access the report properties, you need to be on the Layout tab of the Report Designer. When you click on this tab, a new menu item, Report, activates. This menu gives you options for turning on and off the report header and footer sections (as we have seen), adding embedded images, setting report properties, and setting report parameters.
Setting up the report print settings is not intuitive, compared to other products. In the report Layout tab you see your report with a ruler across the top. That ruler is set up in inches. A standard sheet of paper is 8.5 x 11 inches. The default setting in SSRS is to have a 1-inch margin all the way around the content area. So, at 8.5 inches wide with a 1-inch margin on the left and another on the right, you have 6.5 inches for content. Any more, and the content beyond the 6.5 inches will spill over onto a second sheet of paper when printed. It will not automatically shift to Landscape.
To get Landscape reports, you have to make the proper report settings. Navigate to the Layout tab of the Report Designer. From the Report menu select Report Properties. Switch to the Layout tab of the dialog box. To switch to Landscape printing, you need to change Page width to 11in and the Page height to 8.5in.
On the same Layout tab, you can also get up your report to display the data in multiple columns, and you can control the margins. By default, all the margins are set at 1 inch. Remember, if your report width, plus the right and left margins, is greater than the width of the printer, you will get spill-over when you print out your report.
Using parameters
There are two ways to add parameters to your reports. The first is to use parameters in the queries in your data sources. The second is to set up parameters through the Report Parameters dialog box. We will do both.
Query parameters
First, we will set up parameters using a data source query. Navigate to the Data tab, and click the "…" button right next the Dataset dropdown box. The Dataset dialog box opens and allows you to edit the query for the dataset. Change the Query string from "spr_CustomerSelectAll" to "spr_CustomerSelectByState".
Click OK and then run the query by clicking the red exclamation point (!) on the Data tab toolbar. You will be prompted to enter a parameter value for StateCD (the State code). Enter "CA" and click OK. The query should return all the customers that live in California.
Switch to the Preview tab. Instead of the report just running, there should be a place for you to enter a value for the "StateCD" and run the report. Enter "CA" and click the View Report button. You should see your report filtered by state.
To edit the parameters, or to add new ones, switch to the Layout tab of the Report Designer and open the Report Parameters dialog from the Report menu. First, we are going to make the "State code" prompt a little more friendly. Select StateCD in the Parameters list box then, in the Parameters Properties box, change the prompt to read "State". In the Available values section enter "California" and "CA" on the first line. Enter "Louisiana" and "LA" on the second line.
This will make the prompt for "State value" into a dropdown. You can also build additional datasets into your report, and use the values in those datasets as criteria for dropdowns.
Report parameters
Now let's add a parameter that is not used in a query. Click the Add button in the Report Parameters dialog from the Report menu. Give the new parameter the name "ReportTitle". Its Data type should be String. Set the Prompt to "Report Title". Click OK and close the dialog box.
Drag a new text box onto the report header area. Right-click on it and choose the Expression option. In the Edit Expression dialog select Parameters. Double-click on the ReportTitle parameter. This text box will now display the value you pass to the Report Title parameter.
Switch to the Preview tab and try it out.
Publishing your reports
Up to now, you have been running your reports in the Visual Studio 2005 environment but, to make them useful, you must publish them to a report server. The easiest way to do this is to have Visual Studio publish your reports.
Start by right-clicking on Project in the Project Explorer and choose Properties.
Set TargetServerURL to the URL of your report server. Use the TargetReportFolder property to set up a folder for your reports. If the folder does not exist, the Publisher will create it for you. The other key thing to pay attention to here is the OverwriteDataSources property. When this property is set to "True", it will automatically copy over all your data source objects when you publish. When set to "False", it will copy any new data source objects, but it will not overwrite existing ones. This is important when dealing with development, test and production servers. You can publish a set of data source objects to each server, pointed at the correct database, and never have to worry about what database your reports are hitting in each environment.
From the Build menu select Deploy Solution. This will publish the project to the selected folder on your report server. You can also deploy individual reports by right-clicking on the file in the Solution explorer and selecting Deploy.
Once your report has been published, you can access and run it on your server through the browser at http://<servername(STPPC1742)>/<reportservername(myreport)>. From the Home page, you should be able to find the folder you published to, with the reports in it. Select a report to run. At the top of the page you can enter any values for report parameters, and then run the report. From here, you can also print or export the report.
Subreports :
A subreport is a report that is embedded into another report. Subreports can take parameters and execute their own datasets. A key aspect to note is that a subreport in SSRS is actually just another report (unlike some reporting tools, where a subreport is a special construct). In fact, in SSRS you can execute a subreport on its own.
To add a subreport to a report, you simply drag a subreport control onto the report and tell it which report to display. If the subreport requires parameters, you have to tell the main report which value to pass to the subreport. It's actually very simple.
Let's add a new report to the project and call it MainReport.rdl. Create a new dataset using the shared datasource and the query:
SELECT CustomerID, FirstName, LastName FROM Customer
Switch to the Layout tab. Drag a table on the report detail area. Set up the first column to display the customer's first name (by dragging that column from the Datasets tab into the Detail row) and set up the second column to display the customer's last name. Label the third column 'Address'. Preview the report, just to be sure it works.
Create another report, and call this one MySubReport.rdl. This time, create a dataset that uses the shared data source, and use the following query text:
SELECT Address, City, State, ZipCode
FROM Customer
WHERE (CustomerID = @CustomerID)
In the Layout tab, use text boxes to create the address layout, as shown in figure 5. You can simply drag the text boxes onto the screen by clicking on the field in datasets tab and dragging it onto design surface. You will also note that when you do this, the expression used to set the value property for the textbox uses the First() function. This function will use the value from the first row returned by the dataset. All other rows returned are ignored.
Now preview the report and use '100' for the CustomerID parameter.
Let's jump back to the MainReport.rdl. To embed the subreport, drag a SubReport control into the detail cell for the column you labeled 'Address'. Right-click on the SubReport control and select Properties. In the Properties dialog choose MySubReport from the subreport dropdown.
Next, switch to the Parameters tab. This is where you connect your subreport to the main report. You do this by indicating which value from the main report is to be passed to the subreport to fulfill its parameter requirements.
In the Parameter Name column choose CustomerID and in the Parameter Value column choose =Fields!CustomerID.Value. This will wire up the subreport to whichever customer is being displayed in the row of the table.
<img src="/KB/reporting-services/ReportingServices/image031.gif" width="600" height="497" alt="Screenshot - image031.gif" />
Click OK to close the dialog, and then preview the main report.When the report runs, it looks like this:
The Matrix :
Create the sample report and choose the report type as matrix see below..
And click next button..
From above window select page,coloumns, rows and details from available fields.ad enable drill down also(Optional).
The drill down functionality in SSRS allows you to have areas of your report that can expand and collapse, much like a tree view.
Click on next button fallowing window will be opened.
Click finish to close the dialog, and then preview the main report.When the report runs, it looks like this:
you can see the customer Company name by using the tree-style +/- controls.
Creating Charts:
SQL Server 2005 Reporting Services includes a basic chart control that handles most situations quite nicely, and third-party chart controls are available as well. The chart controls let you set categories for the x axis and data points for the y axis, and you can add one or more series to the chart.
To create a chart, you can drag a chart control onto the report designer in the Layout tab. The chart control automatically goes into "set me up" mode, letting you drag the DataSet's fields into the category, data, and series areas. The fields you drag into the category area are used for the x axis while those dragged to the data area are used as the data points that define the bars, pie chart, or lines .You need at least one field in each of these areas to create a useful chart, though you can add more if you want.
Rightclick on chart control chart properties window will get the chartproperties window.
By using this we can edit the chat properties.
and then preview the report.When the report runs, it looks like this:
