Using Excel As A Reporting Services Datasource

It’s been said that Excel is the poor man’s database. I don’t know about that, but I can tell you that an awful lot of data lives in Excel spreadsheets in corporate America and around the world. Whether it’s project timelines or employee lists, many business depend on Excel.

This is evident in my consulting practice. It’s also commonly mentioned when I’m teaching Microsoft SQL Server Reporting Services courses for clients. Students want to see an example of a report that uses a Microsoft Excel Spreadsheet as its datasource.

Dude, Where’s My Data?

The first step in using an Excel spreadsheet as a datasource is to create a System Data Source Name (DSN) for the spreadsheet. Open the Data Sources (ODBC) applet from Control Panel | Administrative Tools. Of course the name and location of the applet can change depending on the version of Windows you’re using.

Add a new System DSN, providing a name in the Data Source Name box, and selecting the workbook that you wish to use for your report.

Next, from within Business Intelligence Developers Studio (BIDS), create a new data source. Change the Type to ODBC. Click the Edit… button and select the DSN that you just created in the prior steps.

Creating the Report

To use the data from the spreadsheed in a report, create a new Dataset. For the query, type

SELECT * FROM [Sheet1$] WHERE Is_Enabled = 1

Note that you can replace Sheet1 with the actual name of the worksheet tab in the Excel Workbook. The syntax requires that the name of the worksheet be followed by a dollar sign ($). As such, you must enclose the worksheet name in square brackets [] as shown below since a dollar sign is not considered a valid character in a select statement.

Once we’ve created the Dataset, we can display it in a report like any other Dataset.

And there you have it, a Reporting Services report that uses data stored in a Microsoft Excel spreadsheet as the source of its data.

Data-Driven Subscriptions at OPASS

ledger_notes.jpg

Six months ago, I spoke at a SQLSaturday in Orlando, Florida. I had a great time. Andy Warren (Blog | Twitter), Jack Corbett (blog | Twitter), and the rest of the team from the Orlando SQL Server User Group, OPASS, did a great job putting together a fantastic event. My hat’s off to them for the effort and results. And I’m glad to see that they’re already working on this year’s event.

And speaking of Orlando, next Tuesday, the 13th of April 2010, I’ll speak at the OPASS User Group meeting. I’m delivering a session on Reporting Services. Here’s the abstract.

Creating Data-Driven Subscriptions in SQL Server Reporting Services

Providing information to those that need it when they need it is key for many businesses. But, being able to automatically disseminate critical information in a timely fashion can be a challenge. In this session, we will discover how data-driven subscriptions in SQL Server Reporting Services can be used to effectively deliver reports to a varying list of subscribers.

You can join me from the comfort of your own living room via LiveMeeting. We’ll start at 6:45 EDT.

Licensing SQL Server Reporting Services

license_please-2010-03-19.jpg

I have my license here somewhere, officer.

Licensing models can sometimes make database modeling seem trivial. Per processor or per seat? Single core, dual-core, multi-core processors? Multiple instances on one server? Virtualizing servers on a single server? Active / Passive clustering? It can get very confusing, very quickly.

Recently, I was asked by a former student about licensing for a SQL Server Reporting Services instance. In the proposed scenario, the Report Server would be installed on one server and the back end database would be installed on a separate, remote server. How many licenses of SQL Server are required?

In short, each server where a Business Intelligence component is installed requires a valid SQL Server license. So, a remote database deployment scenario where the Report Server is installed on one server and the ReportServer database is installed on a separate server requires two licenses of SQL Server.

But don’t take my word for it. Here are a couple of links that will help clarify licensing requirements.

Got any licensing stories you’d like to share? I’d love to hear about them.

Interactive Sorting Using a Tablix in Reporting Services

Many organizations collect data, lots and lots of data. We have the opportunity and ability today to collect more data than ever before. The challenge is turning the massive amounts of data into usable and actionable information and putting that information into the hands of those who need it when they need it. To do that effectively requires some forethought and planning. Unfortunately, many organizations stop at the data collection phase. Transforming the data into information and reporting that to users is almost an afterthought. The result is static and hard-to-interpret reports similar to the financial ledger books of the days of old.

Business Intelligence Developers Studio for Microsoft SQL Server 2008 Reporting Services, BIDS for short, makes creating engaging and dynamic reports nearly trivial in many respects. You can easily create drill-down reports that allow users to initially see a high-level overview of the information they need and then drill down into specific areas of interest with just a few clicks of the mouse. You can also create drill-through reports that allows users to seamlessly navigate from one report to another.

One dynamic feature that many users find very useful is known as Interactive Sorting. With Interactive Sorting, users can click on column headings in the Tablix data region and have the report automatically sort by that column. Let’s walk through a graphical example to demonstrate.

Sample Customer Report

Here’s an example of a contact phone list report created in BIDS. It lists contacts from the sample Adventureworks database using a Tablix data region.

Right now, the report shows the list of customers in no particular order. Let’s provide a way for our users to sort the list as they wish. In the Layout view, click the Tablix header cell for the Last Name column to select it, right click, and then choose Text Box Properties from the context menu.


A new window will open. Click on the Interactive Sort link on the left hand side of the window.


To allow users to dynamically sort the report, check the Enable interactive sort on this text box check box. For this simple example, we’ll sort on the Detail Row by the LastName column.

Click the Ok button to save the changes and close the window. Repeat the process for each column you would like to have sortable. In this example, we’ll enable it for every column in the report.

When we preview the report again, you’ll notice that each column now has a couple of arrows in the header.


By clicking on one of the column header arrows, the sort order of the rows will dynamically change.


And there you have it. With only a few mouse clicks, we’ve enriched the experience our users have while viewing this report. Of course, this technique only works when the report is rendered in formats that support interactivity. When being viewed as a TIFF, for example, the user will not be able alter its presentation.


Reporting Services Licensing

Licensing models can sometimes make database modeling seem trivial. Per processor or per seat? Single core, dual-core, multi-core processors? Multiple instances on one server? Virtualizing servers on a single server? It can get very confusing, very quickly.

I’m regularly asked by a former students, clients, and people in the Forums about licensing for a SQL Server Reporting Services instances. A typical question is: How many SQL Server licenses are required when the Report Server is installed on one server and the back end database is installed on a separate remote server?

Here are a couple of links that help describe the SQL Server licensing models.

And by the way, the answer to the sample question? Each server where a Business Intelligence component is installed requires a valid SQL Server license. So, a remote database deployment scenario where the Report Server is installed on one server and the ReportServer database is installed on a separate server requires two licenses of SQL Server.

Reporting Services Licensing

Licensing models can sometimes make database modeling seem trivial. Per processor or per seat? Single core, dual-core, multi-core processors? Multiple instances on one server? Virtualizing servers on a single server? It can get very confusing, very quickly.

Recently, I was asked by a former student about licensing for a SQL Server Reporting Services instance. In the proposed scenario, the Report Server would be installed on one server and the back end database would be installed on a separate, remote server. How many licenses of SQL Server are required?

In short, each server where a Business Intelligence component is installed requires a valid SQL Server license. So, a remote database deployment scenario where the Report Server is installed on one server and the ReportServer database is installed on a separate server requires two licenses of SQL Server.

But don’t take my word for it. Here are a couple of links that will help clarify licensing requirements.

Special Licensing Considerations for SQL Server 2005

SQL Server 2008 Pricing and Licensing

Cheers and I hope this helps!

Joe
kick it on DotNetKicks.com

Print | posted on Tuesday, February 17, 2009 8:01 AM

<!– –>

Feedback

# re: Reporting Services Licensing

really, as far as I know every server that runs any service of sql server needs separate licensing… And it is very expensive to have at leat 2 physical processor server for your SSRS…

2/17/2009 8:16 AM | Remote DBA

# re: Reporting Services Licensing

I could use a clarification here. Having any SQL Service run on a separate server makes sense that it would require an additional license. However, I’m under the impression that having the web components run on a different server does NOT constitute a SQL Service and therefore would NOT require a separate license.

Am I mistaken there?

Nick

11/10/2009 1:03 PM | Nick L Duckstein

# re: Reporting Services Licensing

Check this document:

http://download.microsoft.com/download/1/e/6/1e68f92c-f334-4517-b610-e4dee946ef91/2008%20SQL%20Licensing%20Overview%20final.docx

It seems like if you want to install reporting services web components (the site and the web service) on a different server where SQL Server 2008 is installed then you will need an other license.

12/17/2009 7:58 AM | Jairo Portela

Post Comment

Title
Name
Email
Url
Comment 
Remember Me?

Enter the code shown above:

SQL Saturday #21 Orlando Presentation Materials

I’m back in the office now after having a great time in Orlando this past weekend for SQLSaturday #21 in sunny Orlando, Florida! Andy Warren (twitter), Jack Corbett (twitter) and everyone else who had a hand in putting it together did a great job. Their hard work definitely paid off.

The technical content at these kind of events is great! An excellent job was done by all of the speakers that I saw.

But for me the best part of the event is getting to catch up with friends that I only get to see a couple of times a year, meeting Twitter-friends for the first time, and walking around talking with other SQL Server enthusiasts.

With this event, I enjoyed meeting Twitter-friends Jorge Segarra (twitter), Kendal Van Dyke (twitter), and Pam Shaw (twitter), among others for the first time. It was also good to catch up again with Andy Leonard (twitter), Buck Woody (twitter), Kevin Kline (twitter), Jonathan Kehayias (twitter), and so many others. I know I’m leaving many people out, but not intentionally. It’s just that there is so many people to talk with during the event.

Anyway, as promised in my sessions, here are the materials I used. I hope you find them useful and thanks for coming!

  • SQL Server Locking & Blocking Made Simple (presentation)
  • Creating Data-Driven Subscriptions in Reporting Services (presentation)

Alternating Background Colors in Reporting Services

In most Microsoft SQL Server Reporting Services (SSRS) implementations, there are dozens, if not hundreds, of reports available for viewing. These reports provide glimpses into the sea of data that the organization collects. From these reports hopefully trends can be discovered and better decisions can be made. At least that’s the goal.

Don’t neglect presentation

There are many attributes of a good report. Broadly speaking the report must be based on accurate data, it must be delivered in a timely fashion, and it must be presented in a way that is easily consumed and interpreted by the users.

Unfortunately, many report developers focus exclusively on the first two elements – making sure the data is right and that the report doesn’t take too long to run. Those are important, of course, even essential. But often the presentation of the report is relegated to an afterthought. Data is slathered on a report form for the user to do with what he pleases.

Using alternating background colors

The topic of report presentation best practices is a broad one, far too broad to cover in just one post. Perhaps, I’ll tackle some of these issues in a series of posts in the coming months. In the meantime, there are minor and easily implemented improvements that can be added to reports that will make them immensely more readable.

For example, consider a report based on a table data region with row after row of information. Let’s say it’s an employee phone list report as shown below.

To make the report a little easier to read horizontally, we’d like to change the background color of every other row. To do so, let’s highlight the detail row of the data table in the layout tab.

In the properties window, find the BackgroundColor property for the highlighted row and choose <Expression…>. Add the following conditional formatting statement in the Edit Expression window.

Click Ok, and preview the report.

And there you go, a report that alternates the background color for each row. This makes it much easier to read.

But a word of caution: give some consideration to what the users will do with this report. If it’s likely to be printed, the alternating background color will consume additional toner or ink making the report more expense to print. In that case, consider using a single underline between each row.

What tips do you have for make reports easier to read?

Joe

Reporting Services resources

binoculars-2009-09-28

Thanks to everyone who attended my SQL Server Reporting Services class in Nashville over the last couple of weeks. As promised, here is a list of online resources that may prove useful to you as you continue to work with Reporting Services.

Additionally, the following links, though not wholly dedicated to Reporting Services, frequently have good Reporting Services articles and content.

And finally, the following book has been well received by its readers according to Amazon.

This list is far from comprehensive. So if you have some links that you’ve found worthwhile, please share them in the comments below.

Update: After I published this list, I was reminded of another great Reporting Services resource entitled Rules to Better SQL Reporting Services. It discusses some best practices for report design to give your users a better experience. It’s definitely worth the read.

Interacticely sorting columns in SQL Server 2005 Reporting Services

Business Intelligence Developers Studio (aka BIDS) for Microsoft SQL Server 2005 Reporting Services makes creating engaging and dynamic reports nearly trivial in some respects. You can easily create drill-down and drill-through reports with just a few mouse clicks.

A drill-down report allows your users to see more or less detail information by clicking on a row of data and having the report expand or collapse supporting detail rows. Drill-through reports take the user from one report to another supporting report via a mouse click. For example, by clicking on a Product Number in a Sales Order, the user will be taken to the Product Information report for that product.

Interactive Sorting

You can also create reports that provide interactive sorting. On a report that uses a table data region, users can click on a column heading and have the report auto-magically sort by that column. Let’s walk through a graphical example to demonstrate. Here’s an example of a Contact Phone List report created in BIDS. It lists contacts from the sample Adventureworks database using a table data region.

We want to allow users to sort the list by the First Name or the Last Name column. In the Layout view, click the table First Name header cell to select it, then right click to open a context-sensitive menu, and choose Properties as shown in the following illustration.

This opens the Textbox Properties window as shown below. Notice the 6th tab is called Interactive Sort. Check the “Add an interactive sort action to this textbox” option. Then click the down arrow in the Sort Expression combobox. Choose the field on which you like to sort when the user clicks this textbox. In our case this this Fields!FirstName.Value expression.

Repeat this process for each of the columns in the table data region that your users may want to sort by. In this example, I’ll enable interactive sorting all three columns, however in practice there’s probably little reason someone would want to sort by the Phone column. Once completed, preview the report. The report is still sorted in the default order. But you’ll notice the little arrows beside each column header.

By clicking on the little arrow beside the column header text, our users can change the sort order of the report. Clicking the same arrow for a second time reverses the sort order.

And there you have it. With only a few mouse clicks, we’ve enriched the experience our users have while viewing this report. Of course, this technique only works when the report is rendered in formats that support interactivity. When being viewed as a TIFF, for example, the user will not be able alter its presentation.

Got other techniques for providing a greater level of interactivity in your reports? I’d love to hear about them so feel free to share in the comments section below.

Book Review – Learning SQL Server 2008 Reporting Services


SSRS Book Image
I recently received a copy of “Learning SQL Server 2008 Reporting Services” by Jayaram Krishnaswamy published by Packt Publishing.

If you are relatively new to SQL Server Reporting Services and you’re a Tactile/Kinesthetic learner (one that learns best by doing rather than listening or reading), this may be the book to help you to quickly get up to speed on Reporting Services.

The author teaches by example throughout most of the book, using the Hands-On exercises to deliver much of the book’s content. I’d dare say that 70% to 80% of the book is in the form of Hands-On exercises.

The book is replete with screen shots to help those less familiar with Reporting Services follow along. For example, Hands-On Exercise 1.1 guides you through installing SQL Server 2008. The author goes to great lengths to ensure each step of the process is clear and unambiguous, using screen shots and commentary to guide the reader through each step. Exercise 1.1 is 25 pages.

If you are already well steeped in the SSRS, this book may be a bit on the remedial side for your tastes, though I suspect you’ll be able to glean some useful tidbits of information about the differences from prior version of the product.

The publisher has made a chapter of the book available for download if you’d like to take a peek before making your buying decision. EDIT: The link seemed to be for a limited time only so I’ve removed it from the blog post to eliminate confusion.

If you read this book, I’d love to hear you thoughts in the comments section below.

Cheers!

Joe

Follow

Get every new post delivered to your Inbox.

Join 31 other followers