Learning SQL Server 2008 Reporting Services — Save 50%
A step-by-step book and eBook to getting the most of Microsoft SQL Server Reporting Services 2008
In the previous part of the article, we had a look at the Report Builder overview and described the Report Builder 2.0 interface. In this part by Jayaram Krishnaswamy, we will discuss about Enabling and reviewing My Reports, and will see how to Modify a basic report.
Enabling and reviewing My Reports
As described in Part 1 the My Reports folder needs to be enabled in order to use the folder or display it in the Open Report dialogue. The RC0 version had a documentation bug which has been rectified (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=366413)
In order to enable the My Reports folder you need to carry out a few tasks. This will require authentication and working with the SQL Server Management Studio. These tasks are listed here:
- Make sure the Report Server has started.
- Make sure you have adequate permissions to access the Servers.
- Open the Microsoft SQL Server Management Studio as described previously.
- Connect to the Reporting Services after making sure you have started the Reporting Services.
- Right-click the Report Server node.
- In the General page enable the My Reports feature by placing a check mark.
- Click on the Advanced list item in the left.
- Now expand the Security node of Reporting Services and you will see that the My Reports role is present in the list of roles as shown. This is also added to the ReportServer database.
- Now bring up Report Builder 2.0 by clicking Start | All Programs | Microsoft SQL Server 2008 Report Builder | Report Builder 2.0.
- Click on Office Button | Open.
- Choose the Recent sites and Servers.
- Highlight the Server URL and click Open.
- Open the Report Manager by providing its URL address.
The Server Properties window is displayed with a navigation list on the left consisting of the following:
In the General page the name, version, edition, authentication mode, and URL of Reporting Service is displayed. Download of an ActiveX Client Print control is enabled by default. In order to work with Report Builder effectively and provide a My Reports folder for each user, you need to place a check mark for the check box Enable a My Reports folder for each user. The My Reports feature has been turned on as shown in the next screenshot.
In the Execution page there is choice for report timeout execution, with the default set such that the report execution expires after 1800 seconds.
In the History page there is choice between keeping an unlimited number of snapshots in the report history (default) or to limit the copies allowing you to specify how many to be kept.
In the Logging page, report execution logging is enabled and the log entries older than 60 days are removed by default. This can be changed if desired.
In the Security page, both Windows integrated security for report data sources and ad hoc report executions are enabled by default.
The Advanced page shows several more items including the ones described thus far as shown in the next figure.
The Advanced page is displayed as shown:
The description of everything that a user with the assignment My Reports role can do is as follows:
May publish reports and linked reports, manage folders, reports, and resources in a users My Reports folder.
Report Builder 2.0 is displayed.
The Open Report dialogue appears as shown. When the report Server is offline, the default location is My Documents, like Microsoft products Excel and MS Access.
The Report server that is active should get displayed here as shown:
All the folders and files on the server become accessible as shown:
Verify that a My Reports folder is created for the user (current user).
There could be slight differences in the look of the interface depending on whether you are using the RTM or the final version of SQL Server 2008 Enterprise edition.
eBook Price: $29.99
Book Price: $49.99
Modifying a basic report
In this exercise, the report created will be modified to illustrate the formatting, layout, and other capabilities built into the Report Builder.
This hands-on will be using a MS Access report that was imported using Visual Studio and hosted on the Report Server. The MS Access report will be modified to use the new Report Items in Report Builder 2.0.
Follow the steps
You will be carrying out the following steps:
- Open Report Builder and open the ByOrders.rdl report.
- Review the imported MS Access report.
- Modify the properties.
Open Report Builder and open the ByOrders.rdl report
You will be accessing the ByOrders.rdl file from the Report Builder in order to modify it in the Report Builder. The steps are listed here:
- Start Report Builder from its shortcut.
- Click the Office button and in the drop-down window click on Open. The Open dialogue is displayed.
- Click on Recent Sites and Servers in the left navigation area. The Report Servers' URL is displayed.
- Highlight the Report Server URL and click on the Open button.
- Click on the MS Access folder and click on the Open button.
- Highlight the report ByOrders and click on the Open button.
The ByOrders report gets displayed in the Report Builder as shown:
Review the imported MS Access report
The dataset for the report is DataSet1 on the left and the report body is in the design area. The various report items and their data binding will be examined for one such control, the CompanyName. It will be instructive to study the others as well. In order to review the report we will follow these steps:
- Highlight CompanyName and right–click on it.
- Click on Placeholder Properties….
- Change the Markup type to HTML – Interpret HTML tags as styles.
- Click on the fx symbol (which opens an Expression window) along the Value.
- In the Expression window that is displayed, modify the expression as shown in the following screenshot:
- Click on the OK button on the Expression window as well as the Placeholder Properties' window.
- Click on the Run button in the Home menu.
- Click on the View Report button.
- Click on the Address field in the report design.
- Change Report title ByOrders to Orders by editing the textbox.
- Extend the length of the <<Expr>> directly below the report title to accommodate a longer string.
- Make the EmployeeID feld left aligned as well as formatted. Set the font weight Bold.
- Click on the table that has the Address, City, and so on. Extend it to the right by dragging the table handles so that the Required Date can be fully displayed.
- Rearrange the positions of objects and the size of textboxes to fully display the data.
The properties of CompanyName are displayed as shown. CompanyName is inside a container textbox inside the Tablix and it is a place holder. You should also notice the large square bracket on the left ranging three rows. This is the grouping symbol.
This opens the Placeholder's Properties page as shown:
You have added the HTML tags <u> and </u> on either side of the original CompanyName data that came from the dataset. Now it is slightly more than the data and in the design view (CompanyName) it is replaced by this expression. A placeholder is the holding place of an expression. You can make a textbox into a placeholder by designating the textbox to hold an expression. For example there are two place holders for the time Now () and the expression ="Page" & Globals.PageNumber & " of "& Globals.TotalPages.
The report gets processed and you may need to provide the login for this report. The username is Admin and there is no password.
The report is displayed in the Report Builder as shown:
The Address field is highlighted in the report as shown:
You can see that this represents the detail (the data that comes in each of the rows). It is also a placeholder and its value is that of "Address". You can apply Rich Text formatting by using HTML tags for textboxes as well as place-holder values.
Note that only certain HTML tags and CSS attributes are supported. For the supported HTML tags refer to http://msdn.microsoft.com/en-us/library/dd207048.aspx. For an example of CSS rendering, refer to http://hodentek.blogspot.com/2009/01/can-you-use-css-style-attributes-in.html.
Both vertical and horizontal movements of objects can be very smooth and can be changed in Points as shown:
Highlighted objects can be expanded and moved using the Ctrl or Shift keys together with the arrow keys.
The modified report design is shown in the following screenshot:
The Report Builder 2.0 tool is described in full, starting from the top to the bottom of this interface. Creating reports with this tool is described using an existing report and modifying it as well as creating a new report from data. Embedding charts and gauges are also described. Some of the interactive and rich text features are also discussed.
Report Builder 1.0 is briefly mentioned but its utility for reports models created using VS 2008 or BIDS did not warrant an example due to current limitations. It may be possible to use this tool with Report Models created with an earlier version of SQL Server. One of the main features of Report Builder 2.0 is its ability to create Ad Hoc reports. The reader may notice slight differences in the look of the interfaces slightly at variance depending on the version of the SQL Server 2008 and Report Builder 2.0 used.
eBook Price: $29.99
Book Price: $49.99
About the Author :
Jayaram Krishnaswamy studied at the Indian Institute of Science in Bangalore India and Madras University in India and taught at the Indian Institute of Technology in Madras. He went to Japan on a Japanese Ministry of Education Research scholarship to complete his PhD in Electrical Engineering from Nagoya University. He was a Post-Doctoral Fellow at Sydney University in Australia; a Government of India Senior Scientific Officer at the Indian Institute of Science in Bangalore and Indian Institute of Technology at Kanpur; a Visiting Scientist at the Eindhoven Institute of Technology in the Netherlands; a visiting Professor of Physics at the Federal University in Brazil; an Associate Research Scientist at a government laboratory in São Jose dos Campos in São Paulo, Brazil; a visiting scientist at the National Research Council in Ottawa, Canada before coming to USA in 1985. He has also taught and worked at the Colorado State University in Fort Collins and North Carolina State University in Raleigh, North Carolina. He worked with Northrop Grumman Corporation on a number of projects related to high energy electron accelerators and Free Electron Lasers. These projects were undertaken at the Brookhaven National Laboratory in Long Island and in the Physics Department at Princeton University. He has over 80 publications in refereed and non-refereed publications and 8 issued patents. He is fluent in Japanese and Portuguese and lives in Honolulu, Hawaii, USA.
He has been working in IT-related fields since 1997. He was once a Microsoft Certified Trainer in Networking and a Siebel Certified developer. He has worked with several IT related companies, such as the Butler International in their Siebel practice, with several IBM sub-contractors and smaller companies. Presently he is active in writing technical articles in the IT field to many online sites such as http://CodeProject.com, http://APSFree.com, http://DevShed.com, http://DevArticles.com, http://OfficeUsers.org, http://ASPAlliance.com, Egghead Café, http://SSWUG.org, Packt Article Network, http://databasedev.co.uk, http://cimaware.com, and many others. Between 2006 and 2010 he wrote more than 400 articles mostly related to database and web related technologies covering Microsoft, Oracle, Sybase, ColdFusion, Sun, and other vendor products.
He has written four books all published by Packt related to Microsoft Database and Application Development: SQL Server Integration Services Using Visual Studio 2005, Learning SQL Server Reporting Services 2008, Microsoft SQL Azure; Enterprise Application Development, and Microsoft Visual Studio Lightswitch Business Application Development. He regularly writes for his four blogs on Blogger; http://Hodentek.blogspot.com, http://HodentekHelp.blogspot.com, http://HodentekMobile.blogspot.com, and http://HodentekMSSS.blogspot.com. He received the 2011 Microsoft Community Contributor award.
Books From Packt