Working with the Report Builder in Microsoft SQL Server 2008: Part 2

Exclusive offer: get 50% off this eBook here
Learning SQL Server 2008 Reporting Services

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

$29.99    $15.00
by Jayaram Krishnaswamy | April 2009 | .NET Microsoft

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)

Getting ready

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:

  1. Make sure the Report Server has started.
  2. Make sure you have adequate permissions to access the Servers.
  3. Open the Microsoft SQL Server Management Studio as described previously.
  4. Connect to the Reporting Services after making sure you have started the Reporting Services.
  5. Right-click the Report Server node.
    • General
    • Execution
    • History
    • Logging
    • Security
    • Advanced
  6. 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.

  7. In the General page enable the My Reports feature by placing a check mark.
  8. Click on the Advanced list item in the left.
  9. The Advanced page is displayed as shown:

    Learning SQL Server 2008 Reporting Services

  10. 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.
  11. Learning SQL Server 2008 Reporting Services

    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.

  12. Now bring up Report Builder 2.0 by clicking Start | All Programs | Microsoft SQL Server 2008 Report Builder | Report Builder 2.0.
  13. Report Builder 2.0 is displayed.

  14. Click on Office Button | Open.
  15. 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.

    Learning SQL Server 2008 Reporting Services

  16. Choose the Recent sites and Servers.
  17. The Report server that is active should get displayed here as shown:

    Learning SQL Server 2008 Reporting Services

  18. Highlight the Server URL and click Open.
  19. All the folders and files on the server become accessible as shown:

    Learning SQL Server 2008 Reporting Services

  20. Open the Report Manager by providing its URL address.
  21. 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.

Learning SQL Server 2008 Reporting Services A step-by-step book and eBook to getting the most of Microsoft SQL Server Reporting Services 2008
Published: March 2009
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

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.

Getting ready

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:

  1. Open Report Builder and open the ByOrders.rdl report.
  2. Review the imported MS Access report.
  3. 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:

  1. Start Report Builder from its shortcut.
  2. Click the Office button and in the drop-down window click on Open. The Open dialogue is displayed.
  3. Click on Recent Sites and Servers in the left navigation area. The Report Servers' URL is displayed.
  4. Highlight the Report Server URL and click on the Open button.
  5. Click on the MS Access folder and click on the Open button.
  6. Highlight the report ByOrders and click on the Open button.
  7. The ByOrders report gets displayed in the Report Builder as shown:

Learning SQL Server 2008 Reporting Services

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:

  1. Highlight CompanyName and right–click on it.
  2. 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.

    Learning SQL Server 2008 Reporting Services

  3. Click on Placeholder Properties….
  4. This opens the Placeholder's Properties page as shown:

    Learning SQL Server 2008 Reporting Services

  5. Change the Markup type to HTML – Interpret HTML tags as styles.
  6. Click on the fx symbol (which opens an Expression window) along  the Value.
  7. In the Expression window that is displayed, modify the expression as shown in the following screenshot:
  8. Learning SQL Server 2008 Reporting Services

    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.

  9. Click on the OK button on the Expression window as well as the Placeholder Properties' window.
  10. Click on the Run button in the Home menu.
  11. The report gets processed and you may need to provide the login for this report. The username is Admin and there is no password.

    Learning SQL Server 2008 Reporting Services

  12. Click on the View Report button.
  13. The report is displayed in the Report Builder as shown:

    Learning SQL Server 2008 Reporting Services

  14. Click on the Address field in the report design.
  15. The Address field is highlighted in the report as shown:

    Learning SQL Server 2008 Reporting Services

    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.

  16. Change Report title ByOrders to Orders by editing the textbox.
  17. Extend the length of the <<Expr>> directly below the report title to accommodate a longer string.
  18. Make the EmployeeID feld left aligned as well as formatted. Set the font weight Bold.
  19. 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.
  20. Rearrange the positions of objects and the size of textboxes to fully display the data.
  21. Both vertical and horizontal movements of objects can be very smooth and can be changed in Points as shown:

    Learning SQL Server 2008 Reporting Services

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:

Learning SQL Server 2008 Reporting Services

Summary

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.

Learning SQL Server 2008 Reporting Services A step-by-step book and eBook to getting the most of Microsoft SQL Server Reporting Services 2008
Published: March 2009
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

About the Author :


Jayaram Krishnaswamy

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.

Contact Jayaram Krishnaswamy

Books From Packt

 

  C# 2008 and 2005 Threaded Programming: Beginner's Guide
C# 2008 and 2005 Threaded Programming: Beginner's Guide

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005
Beginners Guide to SQL Server Integration Services Using Visual Studio 2005

Implementing Microsoft Dynamics NAV 2009
Implementing Microsoft Dynamics NAV 2009

ASP.NET Data Presentation Controls Essentials
ASP.NET Data Presentation Controls Essentials

Software Testing with Visual Studio Team System 2008
Software Testing with Visual Studio Team System 2008

VSTO 3.0 for Office 2007 Programming
VSTO 3.0 for Office 2007 Programming

LINQ Quickly
LINQ Quickly

SOA Patterns with BizTalk Server 2009
SOA Patterns with BizTalk Server 2009

 

No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
5
K
j
F
Z
w
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software