SSRS Quick Tip – An item with the same key has already been added

I was in the process of creating a new report in SQL Server Reporting Services today. I was loading my dataset from a stored procedure, and when I hit the “Refresh Fields” button I recieved the following error:

“Could not create a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct.”

When I clicked the details button I got this further information:

“An item with the same key has already been added.” Here’s a screen shot of my error.

Well this had me scratching my head, as I had made sure to run the stored procedure, and it executed with no errors. After doing some considerable research I finally found a question in the Technet forums that was tangentially related to the error. This gave me the clue to figure out what I had done.

In my stored procedure, I had inadvertantly included the same column name from two different tables. My query looked something like:

SELECT a.Field1, a.Field2, a.Field3, b.Field1, b.field99
FROM TableA a JOIN TableB b on a.Field1 = b.Field1

SQL handled it just fine, since I had prefixed each with an alias (table) name. But SSRS uses only the column name as the key, not table + column, so it was choking.

The fix was easy, either rename the second column, i.e. b.Field1 AS Field01 or just omit the field all together, which is what I did.

As it took me a while to figure this out, tought I’d pass it along to anyone else who might be looking.

SQL Server Integration Services for Developers

Today I presented SSIS For Developers, we looked at how SSIS, commonly used in Data Warehousing, can also be used by most developers to solve issues that frequently come up in the course of their job. Data conversion and exporting data are two good examples, and we also looked at how to call your new SSIS job from your .Net application.

There are two code demos used during the presentation, both available at my Code Gallery site. The first is the basic SSIS For Devs demo with the three packages. The second is the more complex example showing how to call SSIS from your .Net application.

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:

clip_image002[4]

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

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.

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.

clip_image002

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

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.

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.

clip_image002   clip_image004

        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.

 

clip_image006

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

Report Headers and Footers

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.

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:

clip_image002

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.

clip_image004

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:

clip_image006

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

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.

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.

clip_image002

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:

clip_image004

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.

image

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

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.

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.

clip_image002

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

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.

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

clip_image002

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:

clip_image004

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.

clip_image006

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.

clip_image008

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

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.

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

clip_image002

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:

clip_image004

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.

clip_image006

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.

clip_image008

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.

clip_image010

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

clip_image012

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

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.

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:

clip_image002

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.

clip_image004

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

image

Creating a Table Report With Subtotals 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 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:

clip_image002

Creating a Matrix Report 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.

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:

clip_image002

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.

clip_image004

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.

Follow

Get every new post delivered to your Inbox.

Join 100 other followers