Linked Subreports in SQL Server 2008 Reporting Services

Note, before getting started with this lesson there are some prerequisites you should know about. Please read my post Getting Started with SQL Server 2008 to ensure you have everything setup correctly, otherwise you will be missing objects required to get the code here to work correctly.

The previous lesson showed how to include a subreport into another report. This could be used to link independent reports together into a single report. It can also be useful to have a related subreport. A subreport whose data is driven by that of the main report. This can be accomplished by the use of paramenters.

For this lab we’ll create a subreport that returns category totals for the region passed in from the main report. Note that this is a greatly simplified example to illustrate the technique. Even though in this sample everything comes from the same database, each report could just as easily come from completely different data sources. Subreports would be a great technique for combining data from different systems.

Step 1. Create the subreport.

Use Contoso as the shared datasource. For the query, enter:

SELECT [Region]
     , [ProductCategoryName]
     , SUM([TotalAmount]) AS ProductTotal
  FROM [ContosoRetailDW].[Report].[V_SubcategoryRegionTotalsByYear]
 WHERE [Region] = @pRegion
 GROUP BY [Region], [ProductCategoryName]
 ORDER BY [Region], [ProductCategoryName]
Use a tabular report, move everything into the details area, Generic for the table style, and for the report name use “Subreport – Region Category Totals”.

Step 2. Cleanup the subreport.

Click the edge Region texbox in the header (so it’s selected instead of being edited), and press delete. Repeat with the [Region] textbox in the detail row. We won’t need it since Region will be displayed on the parent report.

Change the other headers to Category and Total. Make them wider, make what had been the Region column smaller but leave it, it will give a nice indent padding when included on the parent report. In the textbox properties for ProductTotal, make sure to set the Number to Currency, and in the Alignment area change the Horizontal to right align.

Remove the “Subreport – Region Category Totals” text box

Click on the main table grid, then move it to top of body. Collapse the body to fit the table.

Step 3. Add the parameter.

In the Report Data window, right click on Parameters and pick Add Parameter. Name the property Region. For the prompt, enter “Region – Hidden”. Since the prompt will never be visible, it really doesn’t matter, but making a habit of entering the name and the word Hidden will give a clear indicator that this parameter is a hidden one.

Leave the data type set to text, and check on “Allow blank value”. If you don’t, the report will error out when used as a subreport. Next, set the visibility to Hidden. This means it won’t appear if you run the report, but you can still pass in parameters, from another report or via a URL. Click OK to close the properties window.

Finally, we need to bind the parameter to the parameter the dataset needs. Right click on the dataset and go to properties. On the parameters area @pRegion should already be present (remember, it was part of the WHERE clause in the SQL query). Pick @Region in the drop down for Parameter Value.

Step 4. Create the main report.

Add a new report, using Contoso as the shared datasource. For the query, use:

SELECT [RegionCountryName]
  FROM [Report].[V_Regions]
Use a tabular report, move the RegionCountryName to the details area, and pick Corporate for the style. Finally, for the name use “Regional Report”.

Step 5. Layout the report.

Since there’s only one column, expand it to take up the width of the body.

Right click on the row selector (the gray box with the lines on the left of the table) and pick Insert Row->Inside Group Below.

Into that area, drag a Subreport control from the toolbox. Note in this case there is only one column, but if there were multiple cells you could highlight them, right click and pick Merge Cells.

Step 6. Setup the subreport.

Right click on the subreport control.

Under “Use this report as a subreport” select the “Subreport – Region Category Totals”. Under the parameters area, click Add. Select Region under Name, and for the Value select RegionCountryName.

Step 7. Preview the report

Preview the report to see your results:

Notes

Just a few notes. In this report, we left the table headers in the subreport (Category and Total). Often these are removed, to make the subreport blend in more with the parent.

Here only one parameter was passed, however you can pass multiple parameters if you need to.

Adding Charts to SQL Server Reporting Services Reports

Often adding a chart can help give perspective mere numbers cannot. A quick glace can allow users to quickly focus on the important areas of data. Reporting Services supports many chart types, all of which are highly customizable. In this lab we’ll add a simple chart to the executive summary report we created in the previous lesson.

Step 1. Clone an existing report

Often we start new reports from an existing one. In solution explorer, right click on the “Subreport – Executive Summary” and pick Copy. Now go to the base of the project tree and pick Paste. In the Solution Explorer you’ll now see “Copy of Subreport – Executive Summary”. Rename it “Subreport – Executive Summary with Graph”. (Note, if you don’t have this you’ll need to do yesterday’s lesson, or download the code from the Getting Started post, mentioned at the top of this post).

Step 2. Make room for the chart.

Expand the body to double the width.

Step 3. Add the chart control.

In the toolbox, grab the Chart and drag it into the empty area in the body. You’re then asked what type of chart to create, pick a pie chart (it will be the top right one in the “Shape” category). Click OK.

Click inside the pie chart. You’ll now see small boxes slide out to the side and top/bottom of the chart control. Go to the Report Data tab. In the Contoso Dataset1 click on CategoryTotal and drag it above the chart to the area that reads “Drop data fields here”.

Next, click on the ProductCategoryName and drop it below the chart where it reads “Drop category fields here”.

Right click on the title, and pick “Title properties”, then for title text click the fx expression button. For the expression enter:

=Sum(Fields!CurrentYear.Value, "CurrentFiscalYear") & " Values"

(To see an explanation on the logic for this see the previous lesson).

Step 4. Preview the report

Click on the Preview tab. You should now see your chart, linked to the data.

There are many more customizations you can do to a chart, this lab demonstrated how quick and easy it is to add charts of all types to your reports.

Using Subreports as Areas of Your SQL Server Reporting Services Report

Subreports are an incredibly useful concept within Reporting Services. They allow you to compartmentalize complex logic. They also allow you to create reports that can be used in many different parent reports.

In this lab, we’ll look at how to create a subreport and use it as a region within a parent report. For this example, we’ll create a base report, then a subreport that will function as an executive summary which we can place at the top of the report body. These types of summaries are commonplace in the reporting world.

Let’s get started by creating our base report. This will be identical to the base report used in other labs.

Step 1. Add the main report

As with our other reports, right click on the Reports branch in Solution Explorer, pick Add New Report, and (if you haven’t already disabled it) click next to move past the welcome screen.

Step 2. Set the data source.

Pick the Contoso shared data source, or setup a new source to Contoso, and click Next.

Step 3. Setup the query.

In the query builder, we’ll be using one of our views. Enter this SQL statement:

SELECT [FiscalYear]

,[ProductCategoryName]

,[ProductSubcategory]

,[Region]

,[TotalAmount]

FROM [ContosoRetailDW].[Report].[V_SubcategoryRegionTotalsByYear]

and click next.

Step 4. Set the format.

For the report type we’ll use the simple Tabular format, so just click Next.

Step 5. Determine field placement in the report.

To keep this simple we’ll not use any groups on this report, so just put all report fields into the Details section. You can do it in one easy step by clicking on the top most item (FiscalYear), holding down the shift key, and clicking the bottom item (TotalAmount). This will select all of the fields, just click the Details button to move them. Then click Next.

Step 6. Select the formatting Style

Once again we’ll go with Corporate for the style and click Next.

Step 7. Name the report.

Finally we’ll give the report a name of “Regional Sales by Subcategory Subreports as Report Areas” and click Finish.

Step 8. Format report columns

To make the report a little easier to read, expand the width of the columns and format the Total Amount as Currency. (See the previous labs if you don’t recall how to accomplish this.)

Step 9. Add the subreport

It’s now time to create the subreport. Just like with a regular report, right click on the Reports branch in Solution Explorer, pick Add New Report, and (if you haven’t already disabled it) click next to move past the welcome screen.

Step 10. Set the data source.

Pick the Contoso shared data source, or setup a new source to Contoso, and click Next.

Step 11. Setup the query.

In the query builder, we’ll be using one of our views. Enter this SQL statement:

SELECT [ProductCategoryName]

,[CategoryTotal]

FROM [ContosoRetailDW].[Report].[V_ProdcutCategoryExecutiveSummary]

ORDER BY [ProductCategoryName]

and click next.

Step 12. Set the format.

For the report type we’ll use the simple Tabular format, so just click Next.

Step 13. Determine field placement in the report.

To keep this simple we’ll not use any groups on this report, so just put all report fields into the Details section.

Step 14. Select the formatting Style

Unlike other reports, we will pick Generic for the style and click Next.

Step 15. Name the report.

Finally we’ll give the report a name of “Subreport – Executive Summary” and click Finish. Note that is common to start the names of subreports with the name “Subreport” to make them easier to find.

Step 16. Format subreport columns and body

To make the report a little easier to read, expand the width of the columns and format the Total Amount as Currency. (See the previous labs if you don’t recall how to accomplish this.)

In addition, we don’t need the body to be any wider that what is needed. Click on the text box that has the body title “Subreport – Executive Summary” and shrink it to match the width of the table. Then hover the mouse over the right side of the report and drag it over to bump against the right side of the table.

Gotcha: If you try and shink the body first, it will not go. The right edge of the body can never be less than the right edge of the widest object (or the object whose right edge is farthest to the right).

Step 17. Setup the detail header

Start by changing the titles of the detail grid to “Product Category” and “Total”. Now highlight the entire row by clicking the gray row selector square to the left of the row.

We can change the fore and background color of this row to match those of the main report. You can pick from standard colors, or enter your own color value. As an example of the first, go to the Color property, and from the drop down pick the color white. You will see the property name change to “White”. You could also have chose to just type in the word White.

You can also enter a hexadecimal value for the color. Click on the Background Color property and enter “#1c3a70”. (No quotes, but make sure to include the # so the entered value will be understood as hex and not a standard color, such as “White”.

Note that you can also change the values of each textbox independently, using the same technique. Most commonly though you will want to set the entire row.

Step 18. The “Green Bar” effect

Once upon a time, in a computer room in the distant past, all reports were printed on paper that had alternating blocks of green and white background. This was known as “Green Bar” paper. The color made it easy to follow long lines of text across the page.

It’s possible to setup the same effect within our report today. Highlight the detail row, then in the Background Color of the properties window, click the drop down, then instead of a color pick the Expression option. For the expression, enter:

=iif((RowNumber(NOTHING) MOD 2) = 0, "LightBlue", "White")

Using the MOD function we determine if it’s an odd or even row, and set the background color accordingly. For the colors any color constant or hexadecimal value would work.

Step 19. Add a value for the body header.

When a report is used as a subreport, any headers or footers are ignored. It can be useful to have a nice title though, so in this step we’ll create one.

19.1                Hover over the bottom of the body, and drag it down to expand the body height.

19.2                Now click on the grid. When the grid row/column bars appear, click on the one in the very upper left corner. When you do, the row/column bars hide themselves, and the grid sizing handles appear. In the upper left is an icon that points up/down/left/right. Click on it and drag the grid down, leaving space at the top for a textbox. Also leave a little space at the bottom that can serve as a gap between it and other items that might appear on the main report we place this subreport on.

19.3                Next drag a textbox from the toolbox onto the top of the page. Expand the textbox to take up the width of the body. Increase the font size to 12, make the font bold, and center it.

19.4                We have a place now to put our title, lets grab some data to put there. Add a new dataset by right clicking on the Contoso data connection in the Report Data window.

19.5                Name it “CurrentFiscalYear”, for the query text enter:

SELECT MAX(FiscalYear) AS CurrentYear

   FROM [Report].[V_ProdcutCategoryExecutiveSummary]

Click OK to save this new dataset.

19.6                Returning to the textbox, right click and pick Expression. For the expression text, enter:

="Executive Summary for " & Sum(Fields!CurrentYear.Value, "CurrentFiscalYear")

To build the center part of the string, click on the Datasets option under category. Then click on the CurrentFiscalYear dataset. In the Values area, one item appears, Sum(CurrentYear). Click on it to add the text to the current expression.

There is an oddity with getting fields from other datasets then the main one that supplies data to the body, they must be an aggregate expression such as Sum. However, since we are SUMing one value, the subreport will look like.

Design Mode Preview Mode

Step 20. Add subreport to main report.

Adding the subreport is quite simple. First, expand the body to make room above the grid similar to what was done in the above step. Then, drag the subreport from the Solution Explorer onto a blank area of the body.

Positioning it can be a bit of a pain, there’s no nice “put in the center” button. But with a little math you can accomplish it.

Return to the subreport a moment, and click on the grid which should take up the entire width of the body. In the properties window, expand the Size property to see the width. In this case it’s 2.3 inches.

Back in the main report, repeating the procedure with the main report’s grid, we see the width is 6.58 inches. Now it’s easy, (6.58 – 2.3) / 2 yields 2.14 inches. Use this for the left property of the subreport. The width isn’t that important, just set it wide in this case.

Step 21. Preview the report.

As you see, you now have an attractive subreport that you can reuse in multiple reports.

Report Headers and Footers

A common feature to most reports are headers and footers that describe the report, and supply additional information such as the page numbering or print date. In this lab we’ll look at ways to customize the header and footer.

We’ll start by creating a basic report, then adding the headers and footers to it.

Step 1. Add the report

As with our other reports, right click on the Reports branch in Solution Explorer, pick Add New Report, and (if you haven’t already disabled it) click next to move past the welcome screen.

Step 2. Set the data source.

Pick the Contoso shared data source, or setup a new source to Contoso, and click Next.

Step 3. Setup the query.

In the query builder, we’ll be using one of our views. Enter this SQL statement:

SELECT [FiscalYear]
      ,[ProductCategoryName]
      ,[ProductSubcategory]
      ,[Region]
      ,[TotalAmount]
FROM [ContosoRetailDW].[Report].[V_SubcategoryRegionTotalsByYear]

and click next.

Step 4. Set the format.

For the report type we’ll use the simple Tabular format, so just click Next.

Step 5. Determine field placement in the report.

To keep this simple we’ll not use any groups on this report, so just put all report fields into the Details section. You can do it in one easy step by clicking on the top most item (FiscalYear), holding down the shift key, and clicking the bottom item (TotalAmount). This will select all of the fields, just click the Details button to move them. Then click Next.

Step 6. Select the formatting Style

Once again we’ll go with Corporate for the style and click Next.

Step 7. Name the report.

Finally we’ll give the report a name of “Regional Sales by Subcategory Headers and Footers” and click Finish.

Step 8. Format report columns

To make the report a little easier to read, expand the width of the columns and format the Total Amount as Currency. (See the previous labs if you don’t recall how to accomplish this.)

Previewing the report shows our data. There’s a lot of it, so let’s say we are the sales manager and we want to apply filters so we are only looking at pieces of our sales.

Step 9. Add a header area.

To add a header area to the report, simply right click anywhere outside the report body and select “Add Page Header”.

Step 10. Add a title.

A blank, white canvas should appear above your report body. Here you can create a header. Go to the toolbox, and drag in a Text Box. In it enter “Regional Sales Report”. Click on the text box and grab the sizing handles to enlarge it. Sometimes this can be a little tricky, if you click inside the text box it assumes you want to enter or edit the text and puts you in edit mode. You have to click right on the edge of the text box area to make the sizing handles appear.

Now add some visual impact. Either right click to access the fonts or use the toolbar above the design area. Make the font bold, and bump it up a few sizes, 16 generally works well.

Step 11. Add page numbers.

Drag another text box into the area. This time instead of static text we’ll use an expression to put in page numbers. Position the text box in the upper right corner of the report.

Right click on the text box, and in the pop up menu pick “Expression”.

In the expression builder you have a blank slate, only the beginning = is supplied for you. Similar to Excel, all expressions must start with an = sign.

The expression builder is very full featured and powerful, you can do a lot of complex things with it. It uses a VB.Net like language. In this lab though we’ll do something similar and concatenate some static text and build in variables to form a Page x of xx expression.

After the = sign enter “Page “ then an ampersand “&”. Page is simply static text, and the & will be used to join together our return value.

In the lower half of the Expression dialog you will see a Category and Item area, these are designed to make it easier to build expressions. Click on the “Built in Fields” Category. On the right the Item area will populate with the valid fields. Click on PageNumber.

Return to the upper area where it says “Set expression for Value” and after the page number type in & “ of “ & . Then go back to the Item list and click TotalPages. Your Expression dialog should now look like:

Click OK to close the Expression builder.

Step 12. Format the page number.

Select the text box for the page number by clicking on the edge, then using the toolbar right align the page number box. Page numbers are typically quite small on the header, so let’s bump down the font to 8 point.

Step 13. Resize the header.

In this example our header isn’t very large, but when we added it SSRS gave us a considerable amount of space. Let’s resize this to something more appropriate.

Hover over the dotted line between the header and report body with your mouse. It should turn into the up/down sizing handle. When it does, click and drag it up.

As an alternative, you could click in the empty area of the header, then in the Properties pane of VS/BIDS enter an explicit Height value. This is useful for situations where you have specific requirements that the header must be of an exact size. This often occurs with things like pre-printed forms or paper with the letterhead already printed on it.

Step 14. Preview the header.

OK, all done with this part. Switch to the Preview tab to see the header in action.

Step 15. Add the footer.

Working with footers are identical to working with headers. Start by right clicking in an empty spot in the design area and pick “Add Page Footer”.

Step 16. Add content.

Drag a text box onto the footer. Expand it to take up the entire width of the report, then enter the Expression dialog as you did before, right click and pick Expression from the menu.

It’s common for a business to want to copyright their intellectual property, so enter this as your expression:

="Copyright " & Year(Now()) & " ArcaneCode."

Hint: If you select Common Functions, Date & Time in the Category area of the Expression builder, you’ll see many common functions. When you click on one helpful hints will appear to the right.

Since we have a lot of unused space, we’ll again shrink the footer like we did the header. This time though hover over the bottom of the footer to make the resizing mouse icon appear, then drag it up to shrink it.

Step 17. Test in the Preview pane.

Once again, return to the Preview tab, scroll down and the footer should look something like:

Other ideas.

The things you can do in the header and footers are nearly infinite. Images, such as your corporate logo can be used. Trademarks, warning notices of intellectual property, print dates, the report name and URL, and the list of parameters used to generate the report are all common things that may appear in the header.

Interactive Sorting in SQL Server 2008 Reporting Services Reports

Often users want the ability to sort the data in various ways. They have gotten used to tools like Microsoft Excel that let you sort on column headers. Fortunately this is a fairly simple ability to implement in SQL Server Reporting Services. Let’s start by creating a base report. (Note this is the same basic report we’ve used in other posts.)

Step 1. Add the report

As with our other reports, right click on the Reports branch in Solution Explorer, pick Add New Report, and (if you haven’t already disabled it) click next to move past the welcome screen.

Step 2. Set the data source.

Pick the Contoso shared data source, or setup a new source to Contoso, and click Next.

Step 3. Setup the query.

In the query builder, we’ll be using one of our views. Enter this SQL statement:

SELECT [FiscalYear]
     , [ProductCategoryName]
     , [ProductSubcategory]
     , [Region]
     , [TotalAmount]
  FROM [ContosoRetailDW].[Report].[V_SubcategoryRegionTotalsByYear]

and click next.

Step 4. Set the format.

For the report type we’ll use the simple Tabular format, so just click Next.

Step 5. Determine field placement in the report.

To keep this simple we’ll not use any groups on this report, so just put all report fields into the Details section. You can do it in one easy step by clicking on the top most item (FiscalYear), holding down the shift key, and clicking the bottom item (TotalAmount). This will select all of the fields, just click the Details button to move them. Then click Next.

Step 6. Select the formatting Style

Once again we’ll go with Corporate for the style and click Next.

Step 7. Name the report.

Finally we’ll give the report a name of “Regional Sales by Subcategory” and click Finish.

Step 8. Format report columns

To make the report a little easier to read, expand the width of the columns and format the Total Amount as Currency. (See the previous labs if you don’t recall how to accomplish this.)

Step 9. Apply sorting to the column headers.

Here’s where the magic happens. Right click on the first column of the report header, which should be Fiscal Year, and pick Text Box Properties.

In the Text Box Properties dialog, first navigate to the Interactive Sorting area. Next, check on “Enable interactive sorting on this text box”.

Under “Choose what to sort” we’ll take the default of details. If we wanted to sort our groups, we could have picked the Groups option then picked the name of a group.

Next in the “Sort By” pick the FiscalYear field. Your dialog should now look like:

Click OK to save.

Repeat this step for the other columns, selecting the data associated with that column in the “Sort by” area.

Step 10. Preview your work

You should now be able to switch to the Preview tab. At the top right of each column you will now see a set of tiny up/down arrows, which when clicked will cause the report to sort by that column.

SQL Server 2008 Reporting Services and the “Select All” Parameter Issue

In yesterday’s post, “Adding Query Parameters to SQL Server 2008 Reporting Services Reports”, we looked at how to add a Query Parameter to a report dataset. The steps hide a potentially fatal issue though: “Select All”. When you allow the user to select more than one parameter, they can also select all of them. Behind the scenes SSRS converts this to a long delimited list. Thus the query, when sent to the server, looks like:

SELECT [FiscalYear] 
     , [ProductCategoryName]
     , [ProductSubcategory]
     , [Region]
     , [TotalAmount]
  FROM [ContosoRetailDW].[Report].[V_SubcategoryRegionTotalsByYear]
 WHERE [Region] IN ('Armenia', 'Austrailia', 'Canada', 
                    --Rest of long list goes here

If you are certain beyond a doubt that you will only have a limited number of items in the list, then this is a nonissue and you can stop reading this now. However, what if you have a potentially unlimited or even just very large number of items in the list? Then you can easily exceed the buffer length SQL allows for a query string and cause a fatal error. Working around that requires several steps.

Step 1. Add a count dataset

First, we need an additional dataset, whose purpose is to count the number of items in the dataset used to supply values to the parameter list. In this example it will count the number of rows in the Region dataset.

Start by right clicking on the Contoso branch of the Report Data window and pick “Add Dataset”. Name it RegionCount, and give it a Query of:
SELECT COUNT(*) AS RegionCnt
  FROM [ContosoRetailDW].[Report].[V_Regions]
That’s all you need here, just click OK to close.

Step 2. Add an Internal Parameter

Next we need to add a parameter that will hold the result of the RegionCount Dataset. Right click on the parameters branch of the Report Data window and chose to add one. When the dialog appears start by giving it a good name, here we can use RegionCnt.

Now in the Data type, change it to an “Integer” since a count will always be an integer.

This parameter is one we never want the users to be able to see or change. Thus under parameter visibility “Visible” isn’t a good choice for us. That leaves us with “Hidden” and “Internal”. Hidden parameters are not visible in the user interface; however they can be updated when you call a report via its URL. Again, this is not a desirable option for us. Thus we will use Internal, which is similar to a private variable, only for use within the report.

Once you have the properties filled out click OK to close.

Step 3. Add the Query Parameters to the report dataset.

Now we fix our @pRegion parameter that we use in our SQL statement.

Right click on the main report dataset, pick dataset properties, and then go to the parameters area. If it already exists go to the @pRegion parameter (it should, but if not create it). Now we will need to change its value, and we’ll use an expression to do so. Click on the fx button to the right of the parameter value drop down and enter the follow text:
=iif(Parameters!Region.Count>=Parameters!RegionCnt.Value, "SELECTALL", Parameters!Region.Value)
The iif is pretty obvious. The RegionCnt.Value represents the number of items from the RegionCnt parameter, which came from the SELECT COUNT query. The Region.Count represents the number of items the user selected in the drop down. If they picked SELECT ALL, then all of them will be checked.

Thus, if the number of items checked is equal to or greater than the number of possible items it will return SELECTALL as the value (which we’ll use as a flag in just a moment). Otherwise it will simply return a list of the items selected.

Just to be clear, there’s nothing magical about “SELECTALL”, it’s just a string that makes it obvious what the purpose is. You could have used “ALL”, “allrows”, or “ArcaneCode”. Just as long as you use the same value here and in the SQL query, which you’ll see next.

Now we need to fix our SQL statement. While still in the Dataset Properties window, go back to the Query area and update your SQL Query to read:
SELECT [FiscalYear] 
     , [ProductCategoryName]
     , [ProductSubcategory]
     , [Region]
     , [TotalAmount]
  FROM [ContosoRetailDW].[Report].[V_SubcategoryRegionTotalsByYear]
 WHERE ( ('SELECTALL' IN (@pRegion)) OR ([Region] IN (@pRegion)) )
The first part of the WHERE clause will check to see if our SELECTALL is in the @pRegion parameter, which it will be if the user did a Select All in the parameter drop down in the user interface. If so that portion of the WHERE evaluates to true and all rows are returned. Otherwise it then checks the Region against the specific list returned in @pRegion.

Not Foolproof

Be aware that this solution is not foolproof. If the user has a long list of items and picks all but one item in the list the potential is still there to overflow the SQL query string buffer. In that situation you should reconsider the use of that column as a parameter, finding another means to limit the report. You could also use the expression language to check the length of the items selected (in this case Parameters!Region.Value) and if the length is too long either truncate it or replace it with the SELECTALL value.

Adding Query Parameters to SQL Server 2008 Reporting Services Reports

In yesterday’s post “Adding Filter Parameters to Reports”, we explored how to add a filter to the report dataset. This had the advantage of being very fast when you wanted to look through different sets of data. The disadvantage to this method is that it brings back all possible rows for the report at once. Thus it can be very slow and database intensive and unnecessary when the user only wants to look at a subset of the data.

An alternative to Filters are Query Parameters. Query Parameters are applied at the SQL statement level, before the call to the SQL Server (or other database platform) is made. This limits the number of rows brought back from the server, increasing the speed and reducing the memory footprint required on the reporting server.

To begin, let’s create a base report. We’ll do the same as steps 1 to 10 in the “Adding Filter Parameters to Reports” lab, but in case you have not worked through it yet I will repeat them here.

Step 1. Add the report

As with our other reports, right click on the Reports branch in Solution Explorer, pick Add New Report, and (if you haven’t already disabled it) click next to move past the welcome screen.

Step 2. Set the data source.

Pick the Contoso shared data source, or setup a new source to Contoso, and click Next.

Step 3. Setup the query.

In the query builder, we’ll be using one of our views. Enter this SQL statement:

SELECT [FiscalYear]
     , [ProductCategoryName]
     , [ProductSubcategory]
     , [Region]
     , [TotalAmount]
  FROM [ContosoRetailDW].[Report].[V_SubcategoryRegionTotalsByYear]

and click next.

Step 4. Set the format.

For the report type we’ll use the simple Tabular format, so just click Next.

Step 5. Determine field placement in the report.

To keep this simple we’ll not use any groups on this report, so just put all report fields into the Details section. You can do it in one easy step by clicking on the top most item (FiscalYear), holding down the shift key, and clicking the bottom item (TotalAmount). This will select all of the fields, just click the Details button to move them. Then click Next.

Step 6. Select the formatting Style

Once again we’ll go with Corporate for the style and click Next.

Step 7. Name the report.

Finally we’ll give the report a name of “Regional Sales by Subcategory” and click Finish.

Step 8. Format report columns

To make the report a little easier to read, expand the width of the columns and format the Total Amount as Currency. (See the previous labs if you don’t recall how to accomplish this.)

Previewing the report shows our data. There’s a lot of it, so let’s say we are the sales manager and we want to apply filters so we are only looking at pieces of our sales.

Step 9. Add a new dataset to act as a source for the filter parameter

There are several choices we can use for creating filters. We could allow the user to simply key a value to use into a text box. It is also possible to hard code a list of values, for example “Yes” and “No”. In most cases though, you will want to create a filter based on a set of values in the database, which is what we’ll do in this lab.

Here we’ll apply a filter for the Region. To do so we’ll first need to create a dataset to supply this list from the database. In the Report Data window (if it’s not visible pick View->Report Data from the main BIDS (aka Visual Studio) menu) right click on Contoso and pick “Add Dataset”.

Give the new data source a good name. Here we can use Region.

We have several choices for a data source; here take the default of Text (which means we’ll just enter a query).

In the Query area we have many choices. Even though there is a designer built in, the best way is still to use SQL Server Management Studio to create and test your query, then paste it in here.

SELECT [RegionCountryName]
  FROM [ContosoRetailDW].[Report].[V_Regions]
 ORDER BY [RegionCountryName]

Once you’ve entered the above, click OK.

Step 10. Add the Region parameter

Now that we have a new dataset, we need to add a parameter to apply to our filter. Still in the Report Data window, right click on the Parameters and pick “Add Parameter”.

Give the parameter a good name. This is the variable name you’ll use elsewhere to refer to this item. Remember it, as you’ll need it later! Make sure it has no spaces and follows other typical guidelines for naming variables. For this example we can use the word Region.

Next you want to supply a prompt. This is the message shown on the report beside the parameter selection control. For our example let’s use “Select a Region to work with:”

You should now indicate the data type for the parameter. There are only a few you can pick from, for this though the default of Text will do fine.

Users often want to see multiple items on a report, but not all, so we’ll allow them to pick more than one by checking on the “Allow multiple values” check box. Your parameter window should now look like:

Next we need to tell the report where to get the data from. On the Available Views area, select the “Get values from a query” option. Then pick the new dataset we created, the Region one.

Below this you will see the Value field and Label field options. Frequently when dealing with data we have primary key data, such as an INT, that is needed to link data together. But we also need a human readable value, something that the users can see and understand.

A good example is the classic Employee table. You have an EmployeeID and an EmployeeName. For the value field, you’d pick the EmployeeID, but for the Label field you use the EmployeeName.

In this particular case, we are using the same field for both, and it’s perfectly valid to do so. Just pick “RegionCountryName” for both Value and Label drop downs.

For the rest of this example, know that we won’t do anything with Default Values or the Advanced Area. In the Default Values we can pick or set a value to be the default, and the Advanced lets us determine how often we need to refresh the source data for our parameter.

Step 11. Adding the Region Parameter to the report dataset

Next we’ll need to do two things to add the parameter to the dataset. First, right click on the main reports dataset and go to Dataset Properties.

Since we want to apply this parameter at the database level, we’ll need to add it as a parameter to our SQL query. In the query area of the Dataset Properties window enter:

SELECT [FiscalYear] 
     , [ProductCategoryName]
     , [ProductSubcategory]
     , [Region]
     , [TotalAmount]
  FROM [ContosoRetailDW].[Report].[V_SubcategoryRegionTotalsByYear]
 WHERE [Region] IN (@pRegion)
Next, we need to tell the query where the @pRegion parameter comes from. Go to the Parameters area of the Dataset Properties dialog. If Visual Studio / BIDS has not already done so, click add to add the @pRegion. Then in the drop down pick the @Report parameter object. Click OK to save the changes.

Return to the Preview tab and view the report several times, using various items in the list. Everything should update fine.

Except… having the Select All can cause some issues, which are covered in the next post.

Adding Filter Parameters to SQL Server 2008 Reporting Services Reports

Often when a user looks at a report, they don’t want the entire report but just subsets of the data. To assist those users we have two methods for reducing the amount of data on a report, Filter Parameters (often just called Filters) and Query Parameters.

Query Parameters are applied before the query to get data is sent to the database, and only the data the fits the criteria from the parameter is brought back. This is ideal for situations where most of the time a user is going to look at only one set of data the report can provide. A salesman, for example, who only wants to look at his sales data. Each time a query filter is applied a round trip to the database occurs to update the data in the report.

Filters, which we’ll work with in this lab, are applied after the data is returned from the server. All possible data is returned from the database, and then the filter is applied to the data being seen. This means as a user switches from one filter to another it is very fast, as all the data is already present in memory and only data being displayed is changing. A sales manager would be a good example, one who will want to see the data for all his or her sales people, but only one at a time.

Let’s start our look at Filters by creating a fairly simple report.

Step 1. Add the report

As with our other reports, right click on the Reports branch in Solution Explorer, pick Add New Report, and (if you haven’t already disabled it) click next to move past the welcome screen.

Step 2. Set the data source.

Pick the Contoso shared data source, or setup a new source to Contoso, and click Next.

Step 3. Setup the query.

In the query builder, we’ll be using one of our views. Enter this SQL statement:

SELECT [FiscalYear]
     , [ProductCategoryName]
     , [ProductSubcategory]
     , [Region]
     , [TotalAmount]
  FROM [ContosoRetailDW].[Report].[V_SubcategoryRegionTotalsByYear]

and click next.

Step 4. Set the format.

For the report type we’ll use the simple Tabular format, so just click Next.

Step 5. Determine field placement in the report.

To keep this simple we’ll not use any groups on this report, so just put all report fields into the Details section. You can do it in one easy step by clicking on the top most item (FiscalYear), holding down the shift key, and clicking the bottom item (TotalAmount). This will select all of the fields, just click the Details button to move them. Then click Next.

Step 6. Select the formatting Style

Once again we’ll go with Corporate for the style and click Next.

Step 7. Name the report.

Finally we’ll give the report a name of “Regional Sales by Subcategory” and click Finish.

Step 8. Format report columns

To make the report a little easier to read, expand the width of the columns and format the Total Amount as Currency. (See the previous labs if you don’t recall how to accomplish this.)

Previewing the report shows our data. There’s a lot of it, so let’s say we are the sales manager and we want to apply filters so we are only looking at pieces of our sales.

Step 9. Add a new dataset to act as a source for the filter parameter

There are several choices we can use for creating filters. We could allow the user to simply key a value to use into a text box. It is also possible to hard code a list of values, for example “Yes” and “No”. In most cases though, you will want to create a filter based on a set of values in the database, which is what we’ll do in this lab.

Here we’ll apply a filter for the Region. To do so we’ll first need to create a dataset to supply this list from the database. In the Report Data window (if it’s not visible pick View->Report Data from the main BIDS (aka Visual Studio) menu) right click on Contoso and pick “Add Dataset”.

Give the new data source a good name. Here we can use Region.

We have several choices for a data source; here take the default of Text (which means we’ll just enter a query).

In the Query area we have many choices. Even though there is a designer built in, the best way is still to use SQL Server Management Studio to create and test your query, then paste it in here.

SELECT [RegionCountryName]
  FROM [ContosoRetailDW].[Report].[V_Regions]
 ORDER BY [RegionCountryName]

Once you’ve entered the above, click OK.

Step 10. Add the Region parameter

Now that we have a new dataset, we need to add a parameter to apply to our filter. Still in the Report Data window, right click on the Parameters and pick “Add Parameter”.

Give the parameter a good name. This is the variable name you’ll use elsewhere to refer to this item. Remember it, as you’ll need it later! Make sure it has no spaces and follows other typical guidelines for naming variables. For this example we can use the word Region.

Next you want to supply a prompt. This is the message shown on the report beside the parameter selection control. For our example let’s use “Select a Region to work with:”

You should now indicate the data type for the parameter. There are only a few you can pick from, for this though the default of Text will do fine.

Users often want to see multiple items on a report, but not all, so we’ll allow them to pick more than one by checking on the “Allow multiple values” check box. Your parameter window should now look like:

Next we need to tell the report where to get the data from. On the Available Views area, select the “Get values from a query” option. Then pick the new dataset we created, the Region one.

Below this you will see the Value field and Label field options. Frequently when dealing with data we have primary key data, such as an INT, that is needed to link data together. But we also need a human readable value, something that the users can see and understand.

A good example is the classic Employee table. You have an EmployeeID and an EmployeeName. For the value field, you’d pick the EmployeeID, but for the Label field you use the EmployeeName.

In this particular case, we are using the same field for both, and it’s perfectly valid to do so. Just pick “RegionCountryName” for both Value and Label drop downs.

For the rest of this example, know that we won’t do anything with Default Values or the Advanced Area. In the Default Values we can pick or set a value to be the default, and the Advanced lets us determine how often we need to refresh the source data for our parameter.

Step 11. Bind the parameter to the main reports Dataset Filter

Now that the parameter is setup, we need to bind it to the Dataset for our main report. By default, the report wizard named it DataSet1 when it setup our report. Right click on it and pick Dataset Properties.

In “the real world” we should have already renamed the default name of DataSet1 to something more meaningful. Let’s do so now, and call it MainReportData.

Next, you might be tempted to click on the Parameters area. But in this case what we will be doing is using the Region parameter as a Filter. When we use it as a filter, Reporting Services reads in the entire dataset, and then applies the value in our parameter before displaying it. This means as we change the value of the parameter, the switch from one set of data to another goes very fast.

Let’s go down to the Filters area, and click Add. In the Expression, pick [Region] from the drop down. This is the Region field from our query, not the parameter. Since we want to allow multiple regions, change the Operator to “In”. Finally for the value, enter the name of the parameter preceded by an @ sign. [@Region] is what we’ll type in.

OK all done, click OK to complete your task.

Step 12. Preview your work

After you save your work, click on the Preview tab. You will now see a new area above the report with the prompt you entered.

Open the drop down and pick a few of the items.

Over on the right side you’ll see the View Report button. Click it to generate the report. As you select different countries, you’ll see the view of the report data updates very quickly.

Enhancing A SQL Server 2008 Reporting Services report: Formatting Numbers and Collapsible Groups

Looking back at the report we generated in yesterdays post, there are some things we can do to further enhance its appearance. We can format the look of the total amount, and we can also hide the subcategory details. If you don’t have the report open already, open it and go to the Design tab.

Step 1. Format numeric values.

Right click on the Total Amount area of the details line, and pick “Text Box Properties” on the menu.

In the properties window, click the Number menu option on the left. Then change the Category to “Currency”. Now check on the “Use 1000 separator” option.

When we work with values as big as those that will be on the report, only dollars are typically shown. Reduce the decimal places to zero. You can also change the symbol to one appropriate to your location, put a space after it, or move it behind the dollar field. In this case we’ll just click OK.

Repeat the above steps for the SUM(TotalAmount) text boxes on the ProductCategoryName and FiscalYear lines.

Step 2. Add interactivity to hide sections of the report.

Next we can add interactivity to the report by allowing the user to essentially hide the subcategories, collapsing them into the categories.

Click on the grid, to bring up the grid row and headers. Then right click on the row selector, as pointed to by the red arrow in the following figure:

From the pop up menu, pick “Row Visibility”.

Click the Hide option. Then check on the “Display can be toggled by this report item” and pick “ProductCategoryName’ in the drop down, and click OK.

Step 3. Preview your work.

Save your work then click on the Preview tab. Your should now see a report where the Subcategories are rolled up into the Categories. Each category should have the + symbol to reveal the details behind it.

Click on one of the + signs to expand will reveal the details.

Creating a Table Report With Subtotals in SQL Server 2008 Reporting Services

The most common type of report in existence is a simple table like report, that lists lines of data from your data source. In this lab we’ll create just such a report, and include subtotals.

Step 1. Add the report.

Start by right clicking on the Reports branch of Solution Explorer and adding a new report.

Once you click past the welcome screen, select your shared data source, or create a new one for this report. When you have done so, click the Next button.

For the query, we’ll use:
SELECT FiscalYear
     , ProductCategoryName
     , ProductSubcategory
     , TotalAmount
  FROM Report.V_SubcategoryTotalsByYear
On the next screen, leave the report type as Tabular and click next.

Place FiscalYear and ProductCategoryName in the Ggroup area, and ProductSubcategory and TotalAmount in the Details area, and click next.

On the table layout screen, you can leave it as Stepped, but put a check by the “Include subtotals” option.

Chose a table style, to be consistent with previous labs we’ll again go with Corporate. Click Next.

Give the report a good name. For this lab we can use “Subcat Totals”. Click Finish to complete the report generation.

Step 2. Format the report

In the report designer, you’ll see the columns are all narrow and on the left. Click inside the report grid and the report tables row and column sizing handles will appear. Place your mouse cursor between two of the columns and drag to expand its width.

Now preview the report and you will should see:

Creating a Matrix Report in SQL Server 2008 Reporting Services

Matrix Reports are a special report similar to a pivot table. Matrix reports are not uncommon, and are useful for measuring trends. In this lab we’ll walk through the basic steps of creating a Matrix report.

Step 1. Add the new report.

1.1 Right Click on the Reports branch of Solution Explorer.

1.2 Select Add New Report, then click Next to go past the welcome screen.

1.3 Select your shared data source, or create a new one for this report. When you have done so, click the Next button.

Next you will need to enter the query to supply data to the report. I generally recommend using a tool like SQL Server Management Studio refined your query. For this lab enter the following query:
SELECT [FiscalYear]
     , [ProductCategoryName]
     , [ProductSubcategory]
     , [Product]
     , [TotalAmount]
  FROM [ContosoRetailDW].[Report].[V_ProductTotalsByYear]
Now the Report Wizard will ask what type of report we want. Since we are creating a matrix report select the Matrix option and click Next to continue.

It is now time to design the layout of our matrix report. Click on FiscalYear, then click on the Columns button to move it into the column area. Next click on ProductCategoryName and click the Rows button. Repeat with ProductSubcategory and Product. Finally, click TotalAmount and put it in the details area by clicking the Details button. Your screen should now look like:

Click Next once your screen is complete.

Now the wizard offers to format the report for you. To do so yourself later, picking generic will leave you with plain black and white, no coloring. For this example, let’s pick Corporate and click Next.

Finally we need to give this report a good name. Let’s enter “Product Total By Year Matrix” and click Finish.

Step 2. Preview your work.

The report will now be generated and brought into your editor. Click the Preview tab to see the result of your work. A sample of the report is shown below.

Getting Started with SQL Server Reporting Services 2008

I’ve been working on a new presentation on SQL Server Reporting Services. Over the next few weeks I’ll be creating posts with examples on basic techniques around SSRS. Prior to beginning the posts that will follow, there are a few basic setups you will need to do. Some assumptions are:

· You have SQL Server 2008 Developer Edition SP1 installed on your PC, and are doing the labs there.

· As part of the Developer Edition install, you have installed BIDS (Business Intelligence Development Studio, a series of add-ons for Visual Studio).

· You are familiar with using SQL Server Management Studio.

If you are still on SQL Server 2005, the majority of the techniques demonstrated here will still work. There have been some changes between 2005 and 2008 in the layouts of the BIDS SSRS user interface that you will need to adjust to, many of the screen shots would be different under 2005.

Step 1. Download and install ContosoDW

Contoso is a fictional company created by Microsoft. Similar to AdventureWorks, it provides a repository of retail data. ContosoDW is the Data Warehouse for Contoso, optimized for analysis and reporting. You can download it from:

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=868662dc-187a-4a85-b611-b7df7dc909fc

Once downloaded run the installer to create the ContosoDW database in your SQL Server.

Step 2. Create and run the custom scripts for this lab.

There are a series of scripts needed to create the custom views and tables the labs will be reporting from. The easiest way to get them is to download the complete SQL Server Management Studio project along with all the sample labs and other documentation at my Code Gallery site:

http://code.msdn.microsoft.com/getstartedSSRS2008

Step 3. Open BIDS and create a reporting services project.

Open BIDS. (Start, All Programs, Microsoft SQL Server 2008, SQL Server Business Intelligence Development Studio).

Once in BIDS, create a new project. Under project type select “Business Intelligence Projects”, then Report Server Project Wizard”.

Step 4. Setup the shared data source.

To make the transition of reports from development to test to production easier, BIDS allows you to setup a shared data source. The reports can point to this data source, then as the reports migrate among environments all that is needed is to point this shared data source at the appropriate database.

4.1 Right click on the Data Source branch in Solution Explorer and select “Add New Data Source”.

4.2 Click Next to go past the opening screen of the wizard.

4.3 On the next screen you will need to define the connection. Click New, and it will let you fill out a common dialog to create the database connection string. Do so, creating a connection to the ContosoDW database. Once it is in the Data Connections area, highlight it and click Next.

4.4 Give the data source a good name. For this lab I am simply using “Contoso”.

Ready set go!

At this point you should have the basics completed and are ready to start creating reports. Stay tuned to future posts to see a variety of techniques for SSRS.

Pragmatic Works Free SQL Server Training

Just wanted to mention the Pragmatic Works company did a week of free webinars. You can view all of these which cover a wide variety of subjects. The sessions include:

Introduction to Managing a SQL Server Database by Jorge Segarra
Beginning T-SQL by Patrick LeBlanc
The Modern Resume: Building Your Brand by Brian Knight
How to Become An Exceptional DBA by Brad McGehee
Fundamentals of SSIS by Brian Knight
0 to Cube in 60 Minutes (SSAS) by Brian Knight
Trouble Shooting SQL Server by Christian Bolton
Introduction to SQL Server Reporting Services by Devin Knight

To get access to the seminars, simply go to:

http://www.pragmaticworks.com/resources/webinars/February2010Webinar.aspx

Alabama Code Camp Mobile 2010

Last Saturday was the Alabama Code Camp, held in Mobile AL. For those unfamiliar with the Alabama Code Camps, we hold on average two a year, and they shift from city to city with different user groups acting as the host group. Other cities include Huntsville, Birmingham, and Montgomery. This time though the Lower Alabama Dot Net User Group under the leadership of Ryan Duclos hosted, and what a great event it was. Everything ran smoothly, there was plenty of drinks and pizza to go around, and some good swag to boot. A big congrats to Ryan and his team of volunteers for a great event, also thanks to Microsoft for sponsoring and the University of South Alabama for the venue.

I was kept busy at this code camp, doing three sessions. The first session was “Introduction to Microsoft PowerPivot”. The slide deck can be found at https://arcanecode.files.wordpress.com/2010/01/powerpivot_long.pdf. To see all my PowerPivot posts, simply pick it in the categories to the right or use this link: https://arcanecode.com/category/powerpivot/.

My second session was on Full Text Searching. You can find code samples and the PDF for the presentation at my code gallery site, http://code.msdn.microsoft.com/SqlServerFTS.

The final presentation was an introduction to Business Intelligence and Data Warehousing. Here is the link to the presentations slides in PDF format. As promised in the session I added the additional information for the Kimball Group book.

A quick apology for my delay in posting, a nasty head cold has had me in Zombie land since I got back. Thanks to all who attended, I appreciate you being very interactive, lots of questions, and very attentive. I look forward to the next time Mobile hosts the Alabama Code Camp.

Introducing Microsoft PowerPivot

What is PowerPivot? Well according to Microsoft:

“PowerPivot is Microsoft Self-Service Business Intelligence”

I can see from the glazed looks you are giving your monitor that was clear as mud. So let’s step back a bit and first define what exactly is Business Intelligence.

Business Intelligence

Business Intelligence, often referred to as simply “BI”, is all about taking data you already have and making sense of it. Being able to take that information and turn it from a raw jumble of individual facts and transform it into knowledge that you can take informed actions on.

In every organization there is already someone who is doing BI, although they may not realize it. Microsoft (and many IT departments) refer to this person as “that guy”. A power user, who grabs data from anyplace he (or she) can get it, then uses tools like Excel or Access to slice it, dice it, and analyze it. This person might be an actual Business Analyst, but more often it’s someone for who BI is not their main job. Some common examples of people doing their own BI today are production managers, accountants, engineers, or sales managers, all who need information to better do their job. Let’s look at an illustration that will make it a bit clearer.

In this example, put yourself in the role of a sales manager. You have gotten IT to extract all of your sales orders for the last several years into an Excel spreadsheet. In order to determine how well your sales people are doing, you need to measure their performance. You’ve decided that the amount sold will be a good measure, and use Excel to give you totals.

In BI terms, the column “Total Sales” is known as a measure, or sometimes a fact, as it measures something, in this case the sales amount. The grand total sales amount is often called an aggregation, as it totals up the individual rows of data that IT gave us. But now you might be wondering why Andy’s sales are so low? Well, now you want to dig deeper and look at sales by year.

In BI terms, the names of the sales people are a dimension. Dimensions are often either a “who” (who sold stuff) or a “what” (what stuff did we sell). Places (where was it sold) and dates (when was it sold) are also common dimensions. In this case the sales dates across the top (2007, 2008, 2009) are a date dimension. When we use two or more dimensions to look at our measures, we have a pivot table.

Now we can see a picture emerging. It’s obvious that Andy must have been hired as a new salesperson in late 2008, since he shows no sales for 2007 and very small amount in 2008. But for Paul and Kimberly we can look at something called trends in the BI world. Kimberly shows a nice even trend, rising slowly over the last three years and earns a gold star as our top performer.

By being able to drill down into our data, we spot another trend that was not readily obvious when just looking at the grand totals. Paul has been trending downward so fast the speed of light looks slow. Clearly then we now have information to take action on, commonly known as actionable intelligence.

So remind me, why do we need PowerPivot?

As you can see in the above example, “that guy” in your company clearly has a need to look at this data in order to do his job. Not only does he need to review it, he also has the issue of how to share this information with his co-workers. Unfortunately in the past the tools available to “that guy” have had some drawbacks. The two main tools used by our analyst have been either Excel, or a complete BI solution involving a data warehouse and SQL Server Analysis Services.

Excel’s main limitations center around the volume of data needed to do good analysis. Excel has limits to the number of rows it can store, and for large datasets a spreadsheet can consume equally large amounts of disk space. This makes the spreadsheet difficult to share with coworkers. In addition mathematical functions like aggregations could be slow. On the good side, Excel is readily available to most workers, and a solution can be put together fairly quickly.

A full blown BI solution has some major benefits over the Excel solution. A data warehouse is created, and then SQL Server Analysis Services (often abbreviated as SSAS) is used to precalculate aggregations for every possible way an analyst might wish to look at them. The data is then very easy to share via tools like Excel and SQL Server Reporting Services. While very robust and powerful solution, it does have some drawbacks. It can take quite a bit of time to design, code, and implement both the data warehouse and the analysis services pieces of the solution. In addition it can also be expensive for IT to implement such a system.

Faster than a speeding bullet, more powerful than a locomotive, it’s PowerPivot!

PowerPivot combines the best of both worlds. In fact, it’s not one tool but two: PowerPivot for Microsoft Excel 2010, and PowerPivot for SharePoint 2010. What’s the difference you ask? Good question.

PowerPivot for Microsoft Excel 2010

PowerPivot acts as an Add-on for Excel 2010, and in many ways is quite revolutionary. First, it brings the full power of SQL Server Analysis Services right into Excel. All of the speed and power of SSAS is available right on your desktop. Second, it uses a compression technology that allows vast amounts of data to be saved in a minimal amount of space. Millions of rows of data can now be stored, sorted, and aggregated in a reasonable amount of disk space with great speed.

PowerPivot can draw its data from a wide variety of sources. As you might expect, it can pull from almost any database. Additionally it can draw data from news feeds, SQL Server Reporting Services, other Excel sheets, it can even be typed in manually if need be.

Another issue that often faces the business analyst is the freshness of the data. The information is only as good as the date it was last imported into Excel. Traditionally “that guy” only got extracts of the database as IT had time, since it was often a time consuming process. PowerPivot addresses this through its linked tables feature. PowerPivot will remember where your data came from, and with one simple button click can refresh the spreadsheet with the latest information.

Because PowerPivot sits inside Microsoft Excel, it not only can create basic pivot tables but has all the full featured functionality of Excel at its disposal. It can format pivot tables in a wide array of styles, create pivot charts and graphs, and combine these together into useful dashboards. Additionally PowerPivot has a rich set of mathematical functionally, combining the existing functions already in Excel with an additional set of functions called Data Analysis eXpressions or DAX.

PowerPivot for SharePoint 2010

PowerPivot for Excel 2010 clearly solves several issues around the issue of analysis. It allows users to quickly create spreadsheets, pivot tables, charts, and more in a compact amount of space. If you recall though, creation was only half of “that guys” problem. The other half was sharing his analysis with the rest of his organization. That’s where PowerPivot for SharePoint 2010 comes into play.

Placing a PowerPivot Excel workbook in SharePoint 2010 not only enables traditional file sharing, but also activates several additional features. First, the spreadsheet is hosted right in the web browser. Thus users who might not have made the transition to Excel 2010 can still use the PowerPivot created workbook, slicing and filtering the data to get the information they require.

Data can also be refreshed on an automated, scheduled basis. This ensures the data is always up to date when doing analysis. Dashboards can also be created from the contents of a worksheet and displayed in SharePoint. Finally these PowerPivot created worksheets can be used as data sources for such tools as SQL Server Reporting Services.

Limitations

First, let me preface this by saying as of this writing all of the components are either in CTP (Community Technology Preview, a pre-beta) or Beta state. Thus there could be some changes between now and their final release next year.

To use the PowerPivot for Excel 2010 components, all you have to have is Excel 2010 and the PowerPivot add-in. If you want to share the workbook and get all the rich functionality SharePoint has to offer, you’ll have to have SharePoint 2010, running Excel Services and PowerPivot 2010 Services. You’ll also have to have SQL Server 2008 R2 Analysis Services running on the SharePoint 2010 box. Since you’ll have to have a SQL Server instance installed to support SharePoint this is not a huge limitation, especially since SSAS comes with SQL Server at no extra cost.

One thing I wish to make clear, SharePoint 2010 itself can run using any version of SQL Server from SQL Server 2005 on. It is the PowerPivot service that requires 2008 R2 Analysis Services.

One other important item to note: at some point the load upon the SharePoint 2010 server may grow too large if especially complex analysis is being done. Fortunately SharePoint 2010 ships with several tools that allow administrators to monitor the load and plan accordingly. At the point where the load is too big, it is a clear indication it’s time to transition from a PowerPivot solution to a full BI solution using a data warehouse and SQL Server Analysis Services.

What does PowerPivot mean for business users?

For business users, and especially “that guy”, it means complex analysis tools can be created in a short amount of time. Rich functionality makes it easier to spot trends and produce meaningful charts and graphs. It also means this information can be shared with others in the organization easily, without imposing large burdens on the corporate e-mail system or local file sharing mechanisms.

No longer will users be dependent on IT for their analysis, they will have the power to create everything they need on their own, truly bringing “self service BI” to fruition.

What does PowerPivot mean for Business Intelligence IT Pros?

The first reaction many BI developers have when hearing about PowerPivot is “oh no, this is going to put me out of a job!” Far from it, I firmly believe PowerPivot will create even more work for BI Professionals like myself.

As upper management grows to rely on the information provided by PowerPivot, they will also begin to understand the true value BI can bring to an organization. Selling a new BI solution into an organization where none currently exists can be difficult, as it can be hard to visualize how such a solution would work and the value it brings. PowerPivot allows BI functionality to be brought into an organization at a low development cost, proving the value of BI with minimal investment. Thus when there is a need to implement a larger, traditional BI project those same managers will be more forthcoming with the dollars.

Second, as users pull more and more data, they are going to want that data better organized than they will find in their current transactional business systems. This will in turn spur the need to create many new data warehouses. Likewise the IT department will also want data warehouses created, to reduce the load placed on those same transactional business systems.

I also foresee PowerPivot being used by BI Pros themselves to create solutions. The database structure of many transactional database systems can be difficult to understand even for experienced IT people, much less users. BI Pros can use PowerPivot to add a layer of abstraction between the database and the users, allowing business analysts to do their job without having to learn the complexity of a database system.

BI Pros can also use PowerPivot to implement quick turnaround solutions for customers, bringing more value for the customer’s dollar. When a BI Pro can prove him (or her) self by providing rich functionality in a short time frame it’s almost always the case they are brought back in for multiple engagements.

PowerPivot also provides great value to BI Pros who are employed full time in an enterprise organization. They can create solutions much quicker than before, freeing them up to do other valuable tasks. In addition PowerPivot solutions can provide a “stop gap” solution, pushing the date at which the organization needs to spend the dollars for a full blown BI solution and allowing IT to plan better.

Finally I see great value in PowerPivot as a prototyping tool for larger BI projects. Now users can see their data, interact with it, analyze it, and ensure the required measures and dimensions are present before proceeding with the larger project.

I’ll reiterate, if anything I believe PowerPivot will create an explosion of work for the Business Intelligence Professional.

Where can I learn more?

Well right here for one. I have become quite interested in PowerPivot since seeing it at the SQL PASS 2009 Summit. I think it will be a valuable tool for both myself and my customers. This will be the first of many blog posts to come on PowerPivot. I am also beginning a series of presentations on PowerPivot for local user groups and code camp events. The first will be Saturday, November 21^st 2009 at the SharePoint Saturday in Birmingham Alabama, but there will be many more to come. (If you’d like me to come speak at your group just shoot me an e-mail and we’ll see what we can arrange.)

There’s also the PowerPivot site itself:

http://powerpivot.com/

I’ve also found a small handful of blogs on PowerPivot, listed in no particular order:

Summary

Thanks for sticking with me, I know this was a rather long blog post but PowerPivot has a lot of rich functionality to offer. While PowerPivot is still in the CTP/Beta stage as of this writing, I see more and more interest in the community, which will continue to grow as PowerPivot moves closer to release. I hope this post has set you off on the right step and you’ll continue to come back for more information.