Jayaram Krishnaswamy

 

Work with Part 1 on this site before you start .

Hands-on exercise 6.1: Enabling and

reviewing My Reports

As described previously 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.

The Server Properties window is displayed with a navigation list on the left

consisting of the following:

• General

• Execution

For More Information:

www.packtpub.com/learning-sql-server-2008-reporting-services/book

Working with the Report Builder

[ 316 ]

• History

• Logging

• Security

• Advanced

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 fi gure.

6. In the General page enable the My Reports feature by placing a check mark.

7. Click on the Advanced list item in the left.

The Advanced page is displayed as shown:

8. 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.

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." As discussed in Chapter 5, this role

may be assigned to a user in Report Manager.

9. Now bring up Report Builder 2.0 by clicking Start | All Programs |

Microsoft SQL Server 2008 Report Builder | Report Builder 2.0.

Report Builder 2.0 is displayed.

10 Click on Offi ce Button | Open.

The Open Report dialogue appears as shown. When the report Server is

offl ine, the default location is My Documents, like Microsoft products Excel

and MS Access.

11. Choose the Recent sites and Servers.

The Report server that is active should get displayed here as shown:

12. Highlight the Server URL and click Open.

All the folders and fi les on the server become accessible as shown:

13. Open the Report Manager by providing its URL address.

Verify that a My Reports folder is created for the user (current user). For

other users you need to go into Report Manager and assign the role as discussed

in Chapter 5.

There could be slight differences in the look of the interface depending on

whether you are using the RTM or the fi nal version of SQL Server 2008

Enterprise edition.

Hands-on exercise 6.2: Modifying

a basic report

In this exercise, the report created in Chapter 4 will be modifi ed to illustrate the

formatting, layout, and other capabilities built into the Report Builder. A number

of other features of Report Builder will be taken up in the next chapter.

 

Getting ready

This hands-on will be using a MS Access report that was imported using Visual

Studio in Chapter 4 and hosted on the Report Server. The MS Access report will be

modifi ed 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 imported

in Chapter 4.

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 fi le 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 Offi ce 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.

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:

1. Highlight CompanyName and right–click on it.

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.

 

 

2. Click on Placeholder Properties….

This opens the Placeholder's Properties page as shown:

 

3. Change the Markup type to HTML – Interpret HTML tags as styles.

4. Click on the fx symbol (which opens an Expression window) along

the Value.

5. In the Expression window that is displayed, modify the expression as shown

in the following screenshot:

 

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.

6. Click on the OK button on the Expression window as well as the Placeholder

Properties' window.

7. Click on the Run button in the Home menu.

The report gets processed and you may need to provide the login for this

report. The username is Admin and there is no password.

 

 

 

 

8. Click on the View Report button.

The report is displayed in the Report Builder as shown:

 

 

 

9. Click on the Address field in the report design.

 

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 placeholder

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.

10. Change Report title ByOrders to Orders by editing the textbox.

11. Extend the length of the <<Expr>> directly below the report title to

accommodate a longer string.

12. Make the EmployeeID fi eld left aligned as well as formatted. Set the font

weight Bold.

13. 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.

14. Rearrange the positions of objects and the size of textboxes to fully display

the data.

 

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:

 

Hands-on exercise 6.3: Creating reports

with charts and gauges

In this exercise you will be connecting to an Excel spreadsheet with some data and

will be creating a simple report. You will also add a chart and gauge data regions to

the report to display the data. In authoring the report you will be creating a report

using the New Table or Matrix wizard which is supposed to lead to the creation of

a table or a matrix according to the documentation. What has been noted is that this

wizard can only create a matrix report and if you need a table report you need to

start from scratch. However, a workaround has been adopted to use it as is. Again

this was another reported bug that was fi xed in the fi nal version.

Getting ready

In order to carry out the tasks make sure you have Microsoft Excel installed on the

machine and that the Report Server is running.

Follow on

In this exercise you will carry out the following steps:

1. Create a Microsoft Excel Spreadsheet with some data.

2. Create an ODBC DSN to access the data.

3. Create a datasource using this DSN in Report Builder 2.0.

4. Create a dataset based on the data in the Excel fi le.

5. Design a report to display the data.

6. Create a chart to display the data.

7. Add gauges to the report.

Creating a Microsoft Excel spreadsheet with

some data

In order to work with this exercise we will create a simple spreadsheet with data

using the MS Excel program. The chart we will be creating will use this spreadsheet.

Open an empty MS Excel fi le and type in some numbers as shown in the following

screenshot. Delete Sheet 2 and Sheet 3 from the fi le. Save this fi le after providing a

name (herein RptChart.xls).

The fi le is quite simple and you can just type in the numbers shown or makeup

your own data. All data are numbers. The file gets saved to the default folder

MyDocuments on the C:\drive.

 

 

Create an ODBC DSN to access the data

The MS Excel data will be accessed by creating an ODBC DSN. The details of how to

do it are listed here:

1. Click on Start | All Programs | Control Panel | Administrative Tools |

Data Sources (ODBC) to open the ODBC Data Source Administrator.

2. If the tabbed page is not in User DSN, change the tab to User DSN and click

on the Add button.

3. Scroll down and highlight Microsoft Excel Driver (*.xls) [Version 12.00]

and click on the Finish button.

This opens the ODBC Microsoft Excel Setup window as shown.

 

4. Provide a name (Gauges) and a Description. Click on the Select

Workbook… button.

5. Use the controls on the Select Workbook window to locate the fi le you

saved: the RptChart.xls file. Highlight the fi le. This will get the fi le into the

Database Name window as shown.

 

6. Click on the OK button.

 

This creates the ODBC DSN as shown in the following screenshot. The DSN

you created enters the USER DSN folder.

 

7. Click on the OK button to close the window.

Create a datasource using a DSN in

Report Builder 2.0

After creating the ODBC DSN it will be possible to access the data using Report

Builder. The following steps show how you may carry out this task.

1. Open Report Builder 2.0 just as you have done in other exercises.

2. Click on the Office button and choose New in the drop-down.

The design surface displaying the body of the report with the two wizards

will be displayed.

3. Click on the Table or Matrix wizard to open the New Table or

Matrix window.

4. Click on the New… button to open the Data Source Properties window.

5. Provide a name for the datasource and replace the default DataSource1

(Herein GaugesSource).

6. Click on the handle for Select connection type and choose ODBC as shown:

 

 

 

7. After choosing ODBC in the drop-down click on the Build… button.

The Connection Properties window is displayed as shown:

 

 

 

 

8. Click on the Build… button in the Connection Properties window.

9. The Select Data Source window with the File Data Source tabbed

page will be displayed.

10. Change over to the Machine Data Source window. Scroll up/down if

necessary, highlight Gauges and click on the OK button.

The Select Work Book window will come up.

11. Locate the RptChart.xls by browsing the folders and click on the OK button.

12. The DSN connection string will be entered into the Connection

Properties window.

13. Click on the Test Connection window to verify that the connection is good.

14. Click on the OK button on the Connection Properties window.

15. The Connection string fi eld in the Data Source Properties page will be

updated with the connection information.

 

 

The Connection string is as follows:

Dsn=Gauges;

dbq=C:\DOCUMENTS AND SETTINGS\John Doe\MY DOCUMENTS\RptChart.

xls; defaultdir=C:\DOCUMENTS AND SETTINGS\John Doe\MY DOCUMENTS;

driverid=790;fil=excel 8.0;

maxbuffersize=2048; pagetimeout=5

16. Click on the OK button in the Data Source Properties window.

The GaugesSource datasource is added to Data Source Connections list in

the New Table or Matrix window and appears at the top of the list.

Create a Dataset based on the data in the Excel fi le

Data that gets displayed in a report is obtained from a dataset. In this section you

will see how you get a dataset by using a query.

1. Click on the Next button in the New Table or Matrix window.

2. The Design a query page of the New Table or Matrix wizard is displayed.

3. Type in the following statement and hit on the (!)[Query run] button.

Select * from [Sheet1$]

The query is executed and the result is displayed as shown:

 

 

 

 

 

Design a report to display the data

Once the dataset is available then the report can be laid out to display this data.

Again you will follow the wizard to fashion the report to display the data.

1. Click on the Next button.

The Arrange fi elds page of the New Table or Matrix wizard gets displayed.

2. Click on Time and drop it on the Values fi eld and drop each of First,

Second_, (an underscore is appended to Second as it would represent a

system variable otherwise) and the Third on the Row groups as shown:

 

 

3. Click on the Next button to display the Choose the layout page of

the wizard.

4. Click on the Next button on the Choose the layout page.

5. Choose a style (herein Mahogany) on the Choose a style page and click on

the Finish button.

 

The report design gets displayed in the design surface of Report Builder

 

as shown:

 

6. Run the report from Home | Run (F5).

The report is displayed as shown in the following fi gure:

 

 

 

 

Format a column based on some criterion

In the above report in the third column, there is a wide variation in the value of the

rows. The purpose of this is to demonstrate using a condition in the expression. This

section shows how you may use the Expression to set the color of that textbox based

on the value it contains when you run the report.

1. In the design of the report, select the textbox for the Third

(third row second Column).

2. Right-click to access the textbox's properties.

3. In the Text Box Properties window, click on the navigation item Fill.

4. In the Fill page, click on the symbol fx to open the Expression window for the

Fill Color.

5. In the Expression window, type in the expression as shown in the following

screenshot.

This screenshot has two windows superposed. By using this expression you

are changing the background color of the textbox. The expression contains

the value that will appear in the textbox. Now you are using the Program

Flow function in the Expression window to coin the expression. The syntax

of this function is also displayed in the Expression window. The expression

contains a nested IIF function. If the value is greater than or equal to 10, the

background will be Blue otherwise the color will depend on another condition.

If the value is greater than or equal to 1, (must be less than 10 because of

nesting) it will be Yellow. If less than 1 it will be Red.

 

6. Click on the OK button in the Expression window and also on the OK button

in the Text Box Properties window.

7. Run the report from the "ribbon".

The report gets displayed as shown in the following screenshot:

 

 

 

 

 

 

Create a chart to display the data

The chart you will be creating will be based on the data. You will be using the chart

template to begin creating the chart. You will be using the placeholders on the chart

to associate data with the details you will be displaying in the chart.

1. Return to the design view of the report by clicking on the Design button of

the Run tab.

2. Enlarge the length of the report body by dragging down the bottom side of

the report to create space for the chart.

3. Click on Insert | Chart | Insert Chart and then click in the space

you created.

4. The Select Chart Type window gets displayed as described earlier. Accept

the default choice and click on the OK button.

This adds a chart template to the report as shown. All objects shown on the

chart can be configured.

5. On double-clicking inside the chart, the drop data fields appear on the three

sides of the chart as shown:

 

 

 

 

 

 

 

 

 

 

 

 

 

6. Click on Time in the Report Data and drag it over and drop it on the Drop

category fi elds here.

7. Click on First in Report Data and drag it over and drop it on the Drop data

fi elds here.

8. Repeat the previous step for the Report Data fields Second_ and Third.

9. The design of the chart now appears as shown in the following screenshot

showing the drop fi elds. The chart in the design view is not the data from the

source but shows how the chart would look when rendered.

10. Run the report from the "ribbon".

The chart in the report is displayed as shown:

 

 

 

 

 

The chart data region has a large number of properties which allows you

to fi ne tune the formatting of the chart. All properties are accessible in the

design. The best place to obtain information is the online documentation

(Help menu). In addition to the regular properties, the properties that you

can assign using expressions provide even more options to format.

Add gauges to display the data

Gauges add visual features to enhance the aesthetic appearance as discussed earlier.

However, whether gauges are more appealing than just data is a matter open for

discussion. We will see how to add gauges here:

1. Click on the report to display the report handles. Right-click the last

column and from the drop-down menu that gets displayed click on

Insert Column | Right.

This inserts an extra column on the right.

2. Click on Insert | Gauge and click on the Data cell of the new column

you created.

The Select Gauge Type gets displayed.

3. Accept the default that shows up and click on the OK button.

A gauge gets placed in the cell as shown:

 

 

 

The vertical side was extended to improve the display.

4. Double-click the gauge to display its configurable features as shown:

Let us say that this Gauge should represent the data in column of

our report table.

5. Drag Second_ from Report Data and drop it on the location Drop Data

fi elds here.

Alternatively you may also choose the drop-down from the data list icon that

appears in that textbox when you hover over it.

6. Click on Home | Run to process and display the report.

The report gets displayed as shown (only part of it shown).

 

 

 

 

 

 

 

Observe that the gauge needle is pointing at the values in the Second_column.

 

Add a report item to display the average value of a column

 

Gauges can show not only item data but they can also be used to show aggregates

such as SUM, Average and so on. In this section, you will be creating another display

type of a gauge and associating the average value of a column.

1. Right-click the data cell of second column (second column, second row).

2. Choose from the drop-down menu Insert Row | Outside Group Below.

3. Right-click the inserted textbox to access the properties of the Tablix as

shown and click on Outside Group Below.

 

This adds a textbox outside the group for the second column as shown in the

following screenshot:

 

 

 

 

 

 

4. Right-click the inserted textbox and click on Expression….

5. At the top, in the Set expression pane type in ="Avg= " &.

6. Expand the category Common Functions. Click on Aggregate to expand. In

the Item fi eld double-click Avg.

7. The Set expression for: Value gets fi led with "="Avg= "& Avg (".

8. Now click on Fields (DataSet1) and in the Values: fi eld double-click Second_.

9. The expression now becomes "="Avg= " & Avg(Fields!Second_.Value".

10 Close the parentheses to make the expression complete as shown:

="Avg= " & Avg (Fields!Second_.Value)

11. The textbox you created now gets this expression.

12. Click Home | Run to run the report. It is displayed as shown in the

following screenshot, on the second page of the report.

Add a gauge to display this average

In this section you will add a single gauge in the same column as the others

but outside the group, and set its value to represent the group average of the

second column:

1. Click Insert | Gauge and drop it on the intersection of the last row. This row

is outside the group and the column containing the gauge from the previous

section.

The Select Gauge Type gets displayed.

2. Select the default Linear type (the fi rst one).

The linear type gauge gets added to the report.

3. From the dataset, click on Second_ to add it to the gauge as shown:

 

 

 

4. Now the Linear Pointer gets the value [Sum (Second_)].

5. Right-click the Linear Pointer and choose to review the Pointer Properties….

The Linear Pointer Properties window gets displayed as shown:

 

 

 

 

 

 

6. Click fx to open the Expression window and alter the value displayed to

the following.

=Avg(Fields!Second_.Value)

7. Close the Expression window and close the Linear Pointer

Properties window.

8. Run the report from Home | Run.

The report gets displayed as shown (only the relevant part is shown):

 

 

 

 

The linear gauge now displays the same average value as seen in the above

screenshot. There is a row and a textbox that do not have any data shown in the

above (row above and the textbox to the right of the average) and these may be

prevented from showing up by using the hide property.

Create a bookmark and jump to it

Bookmarks are also like hyperlinks in web pages but they jump to another part in

the same document. Microsoft Word supports adding bookmarks to several locations

in the document and makes a provision to jump to them. In web pages, the same

is achieved by having links and anchors (http://www.w3.org/TR/REC-html40/

struct/links.html). In this section you will create a bookmark for the title of

Second Column. This way when you click on the Second series on the chart, the

display changes to the second column title.

Create a bookmark using the Bookmark property

You need to create a bookmark fi rst:

1. Click View and place a check mark in the Properties checkbox.

This allows you to access the Properties window for all objects in the

report. right-clicking on an object shows only a limited number of

available properties.

 

2. In the design view of the report click on the Second column title.

 

3. In its Properties window type in the text Go to data for its Bookmark

property. This is shown in the following screenshot:

Jump to bookmark using the Action property

There are basically two steps in order to have the ability to jump to a known location.

The fi rst step is to create a bookmark and the next step is jump to it. Different parts of

the report builder are used and this section shows how you may do it.

1. In the design view of the chart, click on the series Second_ as shown and set

its Action property to Go to Data as shown:

2. Run the report from Home | Run.

3. Verify that only the series Second_ in the chart takes you to the top of

the report.

 

 

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 briefl y 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 and this will be described in detail in the next chapter. 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.

For More Information:

www.packtpub.com/learning-sql-server-2008-reporting-services/book

Where to buy this book

You can buy Learning SQL Server 2008 Reporting Services from the Packt Publishing

website: http://www.packtpub.com/learning-sql-server-2008-

reporting-services/book

Free shipping to the US, UK, Europe and selected Asian countries. For more information, please

read our shipping policy.

Alternatively, you can buy the book from Amazon, BN.com, Computer Manuals and

most internet book retailers.

 

 

 

 

 

 

 

 

 

 

Related Stories

• SQL Server Report based on an ad hoc query
• Generating reports from an OLAP server
• Generating a report using data on an EnterpriseDB Server