Skip to main content

SQL Server Tips: Create and Edit a Report in SQL Server ReportingServices

SSRS (SQL Server Reporting Services) is an enterprise level reporting solution available as a package in SQL Server 2005, 2008 and 2008 R2. Using this SSRS, it is very easy to create, schedule, publish and manage a multitude of reports using a variety of the data sources. In this article, let’s discuss about creating, editing and exporting a SSRS report manually.

First, let’s discuss adding an SSRS Report to a Project in BIDS.
Follow the following steps to create a report manually in Business Intelligence Development Studio (BIDS):
1.    Open BIDS, select File, Open and then select Project/Solution.
2.    Now, locate and open the previously created Report Server Project.
3.    Then, in the Solution Explorer, it is required to right click the Reports folder and select Add> New Item.
4.    Now from the Add New Item window, select “Report” and name the report (e.g., "MyReport.rdl").
5.    Then, click OK.

Yes, you’ve an empty SSRS report open in BIDS now.

Either side of the screen, you can view tabs for Toolbox and Report Data. If you’re able to view any of the tabs, you can select the appropriate tab from the “View” menu. The Toolbox tab can display the items available for your report like textboxes and tables. The other tab, Report Data is able to display the available data elements that include the result sets from your data sources, images and built-in report fields like page numbers.

How to add a data source and dataset to the project?
Normally, a data source is able to identify the database from which we wish to pull the data out and the connection used to connect to it like server name and login credentials. The example uses the previously created SQL Server data source. If you wish to add a data source to your report, follow the instructions.
1.    Select New, then Data Source from the Report Data tab.
2.    Now, name the data source.
3.    It is required to select the "Use shared data source reference" radio button.
4.    After that, select the previously created SQL Server data source from the drop-down menu.
5.    Now, click OK.

A dataset is the specific set of data requested - via a query or stored procedure execution - from the data source.  The dataset defines what data is available to display in the report.  To add a dataset to your report:
Usually, a dataset is the specific set of data requested via a query or stored procedure execution from the data source. This data defines that what data should be available for display in the report. Follow the instructions to add a dataset to your report.
1.    As usual, select New and Dataset on the Report Data tab.
2.    Now, name the dataset.
3.    Once you finish naming the dataset, it is required to select the data source you’ve added in the steps above.
4.    This screen lets you type or paste your query or stored procedure call.
5.    Moreover, you can click “Query Designer” to build the query in the graphical design.
6.    Now, you can paste the following query in the Query text area:
ProductNumber ,
Color ,
ListPrice ,
SC.Name [Category]
FROM Production.Product P
LEFT OUTER JOIN Production.ProductSubCategory SC
ON P.ProductSubCategoryID = SC.ProductSubCategoryID
WHERE ListPrice BETWEEN 0 AND 1000
ORDER BY Category, ListPrice ASC

7.    Then, click OK.
8.    Now, you can see the data set and available data fields in the Report Data tab.
Adding Data Elements to the Report
Now that you have defined your dataset, you can begin to build the report itself, starting with a table of products, title, and page number:
As you’ve successfully defined the dataset, you can begin to build the report itself, starting with a table of products, title and page number.
1.    Now, it is required to drag a “Table” item from the Toolbox tab onto the reports surface.
2.    Now from the Report Data tab, you can drag and drop the data fields Category, Color, Product, Name and list price into columns in the empty table.

3.    After that, drag a Textbox item from the Toolbox tab onto the surface of the report above the table.
4.    Then, type “AdventureWorks Products” in the textbox.
5.    Now, it is required to right click on the blank area of the report, select Insert and then Page Footer.
6.    Drag a textbox item into the footer.
7.    From the “Built-In-Fields” section on the Report Data tab, drag the “Page Number” in to the textbox.
8.    Now, click the Preview tab above the report area to see the report populated with the data.

This section is about formatting the Report and Export
As said, the design tab lets many options improve the report presentation comprising the following:
•    Add and delete columns: Right click the table and select Insert Column> Left or right.
•    Change the Column Width: Drag and drop column header borders.
•    Change Text font and style: Here, you can select the fields and apply changes from the format toolbar.
•    Format numeric fields: Here, you can right click the numeric field, select the “Text Box Properties” option and make changes using the Number option like List Price might be displayed as currency.
•    Change the report area: In this case, you can drag the edges of the report to make it larger or smaller. Moreover, you can drag the border of the footer to move it closer to the end of the table.

The size of the report area may impact the way the report is displayed when you export it. Consider that you’re exporting to PDF at standard size (8.5 x 11”) portrait layout and your report is too wide, the report will be containing the extra pages to accommodate the extra width. Moreover, you can use the ruler (right click and select View>Ruler) to monitor the report width and modify the report “Page layout and borders” as needed in Report> Report Properties.
Now, let’s discuss about reporting to PDF, Excel, or other file format:
1.    First, click the Preview tab.
2.    Now, click the “Export” icon above the report.
3.    Then, you can select your preferred file format.

4.    After that, it is required to select the location and name for your file.
5.    Then, click Save.
That’s it; we have covered the information about creating, formatting and exporting a SSRS report.


Popular posts from this blog

New Micro battery, power packed for high performance

For a size of a few millimetres, the new micro-batteries are powerful enough to jump start a car battery and then quickly charge the cell phone. Researchers from the University of Illinois have developed the micro batteries which can be used to drive compact electronic devices and new applications for radio communications. The micro batteries are so good that they can even out power the super capacitors. The results were published in ‘Nature Communications’ in the April 16 issue. The micro batteries have energy and power and with the researchers tweaking the structure of the micro batteries a little bit, its use can be for wide range applications. The high performance of the micro battery is based on its 3-dimensional micro-structure. Based on the design of fast charging cathode by Professor Paul Braun’s group, researchers developed the matching anode and integrated it with the fast charging cathode at a micro-scale and made a complete battery with high performance. These batteries cou

HTC Touch Diamond SmartPhone!

The phones design is an innovative in which the user has the geometric form language to make sure a clutter-free environment for screening content. The San Francisco based studio One & Co collaborated with HTC Taiwan Design team to create Diamond HTC smartphone. The HTC phone is quite wonderful with design and the rapid development in the smartphone market the HTC has taken innovative steps to capture the SmartPhone market HTC develops these kind of new inventive designs. Inspired

Google Health Login Page is Ready!

Google's Marissa Mayer announced that the Google Health will be launched on 2008. The service of the Google Health Login Page is ready. It's only the first intro page is displayed and I didn't get passed to the next page. In the same page y0u can see the information displaying on the Google Health. If you point your mouse on the below link you can visit the Google Health Page With Google Health, you can: * Build online health profiles that belong to you * Download medical records from doctors and pharmacies * Get personalized health guidance and relevant news * Find qualified doctors and connect to time-saving services * Share selected information with family or caregivers This will be a nice development about caring health and this will helps in having some cautious about health. Inspired