There are certain key words in a T-SQL query than cannot be parameterized, including the TOP statement.  Consider the followong attempt to parameterize the number of rows returned from the query results:

Select Top @TopCustomerCount
      F.CustomerKey, F.OrderDateKey,
      Case When DC.MiddleName Is Null Then 
         FirstName + ' ' + LastName
      Else FirstName + ' ' + MiddleName + '.' + ' ' + 
         LastName End CustomerName,
      Convert(Char(11),DT.FullDateAlternateKey,106) OrderDate,
      Sum(F.SalesAmount) SalesAmount
From
      FactInternetSales F, DimTime DT, DimCustomer DC
Where
      F.OrderDateKey = DT.TimeKey
      And F.CustomerKey = DC.CustomerKey
      And Convert(Int,DT.CalendarYear) = @Year
Group By
      F.CustomerKey, F.OrderDateKey, DC.MiddleName, 
      DC.FirstName, DC.LastName, DT.FullDateAlternateKey
Order By
      F.OrderDateKey Desc, CustomerName

This query will not work because the query designer can't resolve the TopCustomerCount parameter.  To solve this problem, build an expression to concatonate the entire query, with parameterized literal values like the following example:

This ame technique can be used to resolve complex decision structures in code before building the SQL Statement string.  For even more flexibility, create a custom function in the report properties Code window and use Visual Basic.NET code to build and return the entire query string.

Creating one report to meet multiple business requirements can save a lot of work down the road. A common scenario is for different users to ask for similar reporting metrics that may just be variations of existing reports. Using SQL Server Reporting Services, you can achieve this goal with a little planning and creative report design.

The following technique in Reporting Services for SQL Server 2008 uses a parameterized expression to modify a dataset so that it returns a different set of columns. Conditional logic in the report is used to include only columns in a table which correspond to the columns returned by the query. The same technique will work in earlier versions of Reporting Services but the designer user interface will be a little different.

This example uses a parameter to change the stored procedure that will be executed to return the report data. Conditional query logic could also be applied to generate a dynamic SQL or MDX statement.

To begin, use SQL Server Management Studio to connect to the AdventureWorksDW2008 sample database and then execute the following script to create two new stored procedures:

create proc spResellerSales1
    @DateFrom    int,
    @DateTo    int
as
    select
        d.CalendarYear, d.CalendarQuarter, d.EnglishMonthName as Month
        , sum(fs.OrderQuantity) as OrderQuantity
        , sum(fs.SalesAmount) as SalesAmount
    from
        FactResellerSales fs inner join DimDate d on fs.OrderDateKey = d.DateKey
        inner join DimProduct p on fs.ProductKey = p.ProductKey
        inner join DimProductSubcategory ps
on p.ProductSubcategoryKey = ps.ProductSubcategoryKey
        inner join DimProductCategory pc
on ps.ProductCategoryKey = pc.ProductCategoryKey
    where d.DateKey between @DateFrom and @DateTo
    group by d.CalendarYear, d.CalendarQuarter, d.EnglishMonthName
, d.MonthNumberOfYear
    order by d.CalendarYear, d.CalendarQuarter, d.MonthNumberOfYear
;
go
----------------------------
create proc spResellerSales2
    @DateFrom    int,
    @DateTo    int
as
    select
        d.CalendarYear, d.EnglishMonthName as Month
        , sum(fs.OrderQuantity) as OrderQuantity
, sum(fs.SalesAmount) as SalesAmount
    from
        FactResellerSales fs inner join DimDate d on fs.OrderDateKey = d.DateKey
        inner join DimProduct p on fs.ProductKey = p.ProductKey
        inner join DimProductSubcategory ps
on p.ProductSubcategoryKey = ps.ProductSubcategoryKey
        inner join DimProductCategory pc
on ps.ProductCategoryKey = pc.ProductCategoryKey
    where d.DateKey between @DateFrom and @DateTo
    group by d.CalendarYear, d.EnglishMonthName, d.MonthNumberOfYear
    order by d.CalendarYear, d.MonthNumberOfYear
;
go

Note that the only significant difference between the spResellerSales1 and spResellerSales2 stored procedures is that the first one returns the CalendarQuarter column and the second one does not. Remember that this is a simple example for the sake of demonstration. You could have as many different procedures or query variations as you like as long as one of them returns all possible columns and the others return a subset of columns in the same order.

The report contains parameters, two that will be automatically generated from the DateFrom and DateTo parameters in the procedure, and another parameter named DataSource that I added myself. Note that I've provided default values for the DateFrom and DateTo parameters since they're really not part of the demonstration scenario.

To populate the dataset Fields collection, configure the dataset to use a stored procedure and select the first procedure (the one that returns all of the columns) as you normally would without using an expression. The DataSource parameter (not to be confused with a report data source) is use to pass and swap the stored procedure names to the report's main dataset. Manually add this parameter to the report:

The available values for this parameter include the two stored procedure names:

This parameter is referenced in the report's main dataset using the expression: =Parameters!DataSource.Value

A table is added to the report and each dataset field is added as a column to the table:

Since the CalendarQuarter column may not be available when the spResellerSales2 stored procedure is used in the parameterized dataset, this column must be hidden when the column is not returned in the result set. This is accomplished by changing the Hidden property of the table column. Right-click the column header for the Calendar Quarter column and choose Column Visibility… from the menu. Choose Show or hide based on an expression and then click the expression button to use the Expression Builder to create the following expression for this property: =Fields!CalendarQuarter.IsMissing.

Remember that there is a difference between a report dataset and the query that it references. When a query is written, (or in this case, when a stored procedure is selected) field definitions are added to the dataset object and will be there whether the query returns a column or not. If that query doesn't return a corresponding column for the field, the field's IsMissing property returns True.

You're all done!

Preview the report and choose the first stored procedure in the parameter list. The report returns columns for all fields, including the Calendar Quarter:

Change the Data Source parameter to use the second stored procedure and click the View report button on the toolbar. Now the report returns columns for all fields except the Calendar Quarter:

A copy of this sample SSRS 2008 report is included for download.

 Dynamic Columns.rdl (351.00 bytes)

Let's say that you have a column named some_id in an outer-joined table that will return a value if a record exists and will return null if a record doesn't exist...

1. Add an image item to a table cell.
2. Add two embedded images to the report; a check mark and a blank white square the same size.  Let's call the image files Check.png and Blank.png (or JPG, GIF, BMP, TIF, etc.)
3. For the Value property of the image report item, use an expression like:
   =IIF(IsNothing(Fields!some_id.Value), "Blank", "Check")

In the table or group footer, use a COUNT function with the same field: =COUNT(Fields!some_id.Value)
This will only count the existing values.

Report designers often revert to overly complex report designs and often use subreports unnecessarily. Building a report on a single dataset query to return all related rows gives you greater flexibility to group and filter the data.

The following sample report contains a single dataset using the following query:

select 1 as StoreID, 'Freds Store' as StoreName
     , convert(date, '2009-01-15') as TransDate, 'Chicken' as Product
     , 14.99 as Price
union
select 1 as StoreID, 'Freds Store' as StoreName
     , convert(date, '2009-01-25') as TransDate, 'Beef' as Product
     , 19.99 as Price
union
select 1 as StoreID, 'Freds Store' as StoreName
     , convert(date, '2009-02-15') as TransDate, 'Chicken' as Product
     , 15.99 as Price
union
select 2 as StoreID, 'Bobs Store' as StoreName
     , convert(date, '2009-01-15') as TransDate, 'Tuna' as Product
     , 17.99 as Price
union
select 2 as StoreID, 'Bobs Store' as StoreName
     , convert(date, '2009-02-15') as TransDate, 'Chicken' as Product
     , 15.99 as Price

A list data region is grouped on StoreID

Table1 (in the list) has columns bound to TransDate, Product & Price
Filter for this table is: TransDate Between '2009-01-01' And '2009-01-31'

Table2 (in the list) has columns bound to TransDate, Product & Price
Filter for this table is: TransDate Between '2009-02-01' And '2009-02-28'

Select a table and choose Filters in the Properties Window to open the Tablix Properties dialog (in SSRS 2008.) This shows the filter for the first table:

*In this simple example, the date values are hard-coded but could be parameterized and/or based in expressions, such as using the DateAdd() function to compare the current month sale to the prior month, etc.

The report produces this output:

Reporting Services supports recursive hierarchies and Analysis Services supports parent-child dimensions…
…and these two powerful features ought to just work together seamlessly, right? Well, they can if you know what to do.

To follow this example I assume that you have a fundamental knowledge of columnar report design in Reporting Services and that you know how to define groups and to set group and report item properties.

Parent-Child Hierarchies

In SQL Server Analysis Services 2005 and 2008, a parent-child hierarchy is based on a set of dimensional attributes that are related through parent key and primary key values present in the underlying data. This means that the arrangement of the members into their respective levels in a hierarchy is dependent on the data and won't be known at design time. Common recursive and parent-child hierarchies are commonly found in business scenarios like a financial chart of accounts, employee/supervisor assignments and product material assemblies. In each of these scenarios, every item "belongs to" a parent member, which in-turn belongs to another member until everything rolls-up into the top-level member. This top-of-the-food chain member may be the general ledger, CEO or final product assembly – using the prior examples.

In a data warehouse or data mart, a single dimension table may contain the records for all of these members with records inter-related through the primary key and a parent key column participating in a foreign key constraint or logical relationship. Note the following example in the AdventureWorksDW2008 sample database:

The Employee dimension in the corresponding sample Analysis Services database uses these same fields to provide key values. For the Employee attribute, the KeyColumns property is based on the EmployeeKey field and, for the parent-child Employees attribute, the KeyColumns property is based on the ParentEmployeeKey field:

Creating the Report Dataset

Creating the dataset is very simple. When you create the data source, choose Microsoft SQL Server Analysis Services for the Type and then connect to your Analysis Services database. When you define a dataset, the graphical MDX query designer is displayed. Choose the appropriate cube and then drag the parent-child hierarchy into the query design pane along with any measures, calculated members and KPIs. The following example shows a dataset query using the Employees hierarchy in the Adventure Works cube:

…of course, you can add additional dimension members and filtering expressions to the query in a more involved scenario.

Report Design

To visualize the hierarchal data, add a Table data region to the report and drag fields from the dataset to the details row. Define a group only on the details row of the table. The following example is a report created in Reporting Services for SQL Server 2008 but the same technique applies to SQL Server 2005. The design interface is just a little different.

When the MDX query is generated by the query designer, special report field properties are defined and mapped to attribute member properties that are available through the OLAP metadata returned by the query. You will use two of these field properties to enable the recursive hierarchy in the report.

In the details group, create a group expression referring to the UniqueName property of the Employees field. Use the Expression Builder to create the expression =Fields!Employees.UniqueName.

The Recursive Parent property (called the Parent Group property in SSRS 2005) should be set to the ParentUniqueName property of the Employees field.

On the Advanced page (General page in SSRS 2005) of the Group Properties dialog, use the Expression Builder to create the expression =Fields!Employees.ParentUniqueName.

Click OK to accept these settings. Any other report design elements are not specific to reporting on OLAP cube sources.

The Level function is used to determine the position of a row within the recursive hierarchy derived from the parent-child hierarchy of the dimension. Pass the group name as a string. If you would like to indent the items in the table to show employees' relative position within the recursive hierarchy, use the following expression to set the Left Padding property of the textbox used to display the Employees field:

=((LEVEL("table1_Details_Group") * 20) + 2).ToString & "pt"

The final rendered report shows employees arranged in the organization hierarchy and indented to indicate their position and reporting structure. This example uses the typical technique to define drill-down functionality by changing the group visibility and toggle item properties:

Download a copy of this sample report:

Parent Child.rdl (13.56 kb)

In cases where you want the data or content in a report to be different for each user, there are a couple different ways to approach this.  This will depend on whether you want to return user-specific data or whether you want to use logic to hide and show objects in the report depending on a user's region.  The first technique is to filter rows of data by user within the query.  To do this, you will need to store the network user names of all your users in a table within your database.  Using an example of users in regions A or B you could store the user name and their region in a UserRegion table like this:

...then you would reference this table in the dataset query and use a parameter to filter records, which would be something like this:

SELECT col1, col2. col3, etc
FROM
Sales s INNER JOIN Region r ON s.RegionID = r.RegionID
INNER JOIN UserRegion ur ON r.RegionID = ur.RegionID
WHERE ur.UserID = @UserID

In the dataset properties Parameters page, change the mapping for the UserID query parameter to use the UserID report global object, as in: =Globals!UserID.  You can also delete the UserID report parameter that was autogenerated by the dataset.
The other technique is to dynamically hide and show objects, such as a data region, report item, a data region group or an individual table row.  You can do this for each user but you'd have to build specific logic into the report for every user.  You could also leverage the same database/query technique as above to get the current user's region and then dynamically hide or show an object by setting its Hidden property to an expression like:

=(FIRST(Fields!Region.Value, "Dataset1") <> "Region A")

In the above example, the row or object would be hidden if the user's region was not Region A.

There are certain key words in a T-SQL query than cannot be parameterized, including the TOP statement.  Consider the followong attempt to parameterize the number of rows returned from the query results:

Select Top @TopCustomerCount
      F.CustomerKey, F.OrderDateKey,
      Case When DC.MiddleName Is Null Then 
         FirstName + ' ' + LastName
      Else FirstName + ' ' + MiddleName + '.' + ' ' + 
         LastName End CustomerName,
      Convert(Char(11),DT.FullDateAlternateKey,106) OrderDate,
      Sum(F.SalesAmount) SalesAmount
From
      FactInternetSales F, DimTime DT, DimCustomer DC
Where
      F.OrderDateKey = DT.TimeKey
      And F.CustomerKey = DC.CustomerKey
      And Convert(Int,DT.CalendarYear) = @Year
Group By
      F.CustomerKey, F.OrderDateKey, DC.MiddleName, 
      DC.FirstName, DC.LastName, DT.FullDateAlternateKey
Order By
      F.OrderDateKey Desc, CustomerName

This query will not work because the query designer can't resolve the TopCustomerCount parameter.  To solve this problem, build an expression to concatonate the entire query, with parameterized literal values like the following example:

This ame technique can be used to resolve complex decision structures in code before building the SQL Statement string.  For even more flexibility, create a custom function in the report properties Code window and use Visual Basic.NET code to build and return the entire query string.

I want to return the field value for a specific row to a textbox below a table in my report.  To pull this off, I have to be a little creative as there isn't a simple way to return a value from a specific row in a dataset outside of a data region other then using the FIRST() or LAST() functions.  One technique is to write a custom function and then pass values on each row of a table through it to capture the row ID and value that you will want to return to a textbox outside the table.  here's an example:

The report contains a parameter named RowNumber used to specify the row whose value I want to return.

In the report properties Code window, I've entered the following VB.NET code:

In the textbox in the detail row of my table that shows my field value, I use the following expression:

...and in the textbox below the table where I want to see the value for the row specified by my RowNumber parameter, I use the following expression:

Displaying every-other row with an alternating background color can help the reader follow the contents of a tabular report but sometimes it may be more important to differentiate between groups of data rather than just the rows. This variation of the "green bar" report creates bands of rows with alternating colors by group level. This technique displays all of the cells for every-other grouped value in a different color.

Product Versions

• Reporting Services 2000
• Reporting Services 2005
• Reporting Services 2008

What You'll Need

 

• A grouped Table
• An in-line expression to set the BackgroundColor property for textbox cells in the Table

Designing the Report

This example uses the AdventureWorksDW for SQL Server 2005 or 2008 sample database and simple grouped query to return the product category, subcategory and an aggregated business measure.  The first example uses the Reporting Services 2008 designer with an example of the Reporting Services 2005 designer at the end.

Start by designing a data source for the AdventureWorksDW or AdventureWorksDW08 sample databases.

Create a dataset query to include the FactResellerSales, DimProduct, DimProductSubcategory and DimProductCategory tables.

SELECT

DimProductCategory.EnglishProductCategoryName AS Category,

DimProductSubcategory.EnglishProductSubcategoryName AS Subcategory,

SUM(FactResellerSales.OrderQuantity) AS Qty

FROM

DimProduct INNER JOIN DimProductSubcategory

ON DimProduct.ProductSubcategoryKey

= DimProductSubcategory.ProductSubcategoryKey

INNER JOIN DimProductCategory

ON DimProductSubcategory.ProductCategoryKey

= DimProductCategory.ProductCategoryKey

INNER JOIN FactResellerSales

ON DimProduct.ProductKey = FactResellerSales.ProductKey

GROUP BY

DimProductCategory.EnglishProductCategoryName,

DimProductSubcategory.EnglishProductSubcategoryName

ORDER BY

Category, Subcategory

Add a Table report item to the report body.

Drag the Category field into the Row Groups list above the Details row. This creates a row group header textbox for the Category field.

Drag the Subcategory and Qty fields into the second and third columns, respectively, into the Details row.

The query is already grouped by Subcategory so there is no need to define a group expression for the Details group.

This simple example uses the Category group for altering the background shading. In production, any group at any level within all the groups may be used for this purpose.

The report designer should look like figure 1-1.

Figure 1-1

Note the name of the field used for grouping and to start a new background color. We will be referring to the field name and not the group name in the expression. In this example, they are both named Category.

Next, select all the cells in the row by clicking the row handle (Figure 1-2.)

If you intend to alternate the background shading for the rows of a group below the top level group defined in the table, you may want to exclude the parent group header cells in the selection.

Figure 1-2

Take a look at the properties window to see the common properties for all selected textboxes.

Scroll to the BackgroundColor property, drop-down the list using the arrow button and select the Expression link below the color picker, shown in Figure 1-3. This opens the Expression dialog shown in Figure 1-4.

Figure 1-3

The expression will call two nested Visual Basic functions and a mathematical operator. The RunningValue function will return an incremental integer for the distinct count of Category field values. This means that for each new Category, this counter will increment by one. Next, we need to decide if that counter is an odd or even number and then assign one of two colors based on that result. This is performed using the MOD or Modulus operator, which returns the divisional remainder of two numbers. If the MOD of a counter divided by 2 returns 1, the counter is an odd number.

Three arguments are passed to the RunningValue function; the field value expression, the aggregate function to apply for the running total and a scope argument to limit the range of rows. In this case, the keyword Nothing or the name of the dataset will apply the function to all rows returned by the dataset query. The IIF or immediate IF function is used to make a decision based on the outcome of the first statement and return the appropriate color value. Essentially this expression can be translated to read "If the distinct count of unique Category field values is 1 (and is therefore an odd number,) return the color "Gainsboro", otherwise return the color "white". The following expression achieves this goal:

Type this code as one line without carriage returns into the Expression window. Use the code completion, color coding and parentheses matching Intellisense features to validate this expression as shown in Figure 1-4.

Click the OK button when completed.

Figure 1-4

Preview the report to test the results.

As you can see in Figure 1-5, rows in the table are now grouped with alternating background colors.

Figure 1-5

Designing the Report for Reporting Services 2005

In Reporting Services 2005, the pattern is relatively the same.  A grouped table in SSRS 2005 uses a separate row for the group header rather than the inline style typically used in SSRS 2008.  Selecting the row in the report design using the row handle selects the TableRow object rather than a collection of individual textboxes (see Figure 1-6.)  Simply set the BackgroundColor property for both the group header and/or footer row and the detail row to the same expression used in the 2008 example.  The affect is the same, with each group displayed with an alternating background color.

Figure 1-6

Many additional enhancements are possible by applying the same or similar expression to modify other visual elements.  For example, you could separate the gray detail rows with a white border and the white rows with a gray border by settling the bottom border color for the detail row cells.

Credits & Related References

Thanks to Robert Bruckner for suggesting this technique

A similar technique is demonstrated on Chris Hays's blog titled Greenbar Matrix

If you have created multiple drill-through reports, you know that the report navigation works in one direction and that some users struggle to find the best method to navigate back to a previous report. The report toolbar contains navigation controls including a Back button for the previous report in a chain of linked reports but many users don't find this intuitive. A common user experience paradigm in many well-designed web applications places a series of hyperlinks at the top of a page indicating both the path that brought the user to the page they're viewing and a path for navigating back to any page along that path. This is a breadcrumb trail of hyperlinks. Reporting Service provides a means to navigate to any other report deployed to the server but it does not have any type of HTML report item that would allow you to build a series of embedded hyperlinks or anchor tags.

This technique shows you how to build a series of breadcrumb hyperlinks that will allow your users to see where they've been and to navigate back to any report along the trail.

Product Versions

• Reporting Services 2000
• Reporting Services 2005
• Reporting Services 2008

*The example used in this recipe is based on SSRS 2008 but this technique will work in earlier versions.

What You'll Need

• Series of reports with report navigation actions
• Report parameter used to track the breadcrumb trail
• Custom code function to build an ad-hoc query
• Dataset used to provide the breadcrumb data
• Matrix data region containing textbox report actions

Figure XX-1 shows an example of a breadcrumb trail in the report header of a detail report. Note the progressive drill-through path of reports the user opened en route to the current report.

Figure XX-1

In the production implementation of this technique, report actions could be implemented using any of the compatible report items, including textboxes, images, a grouped table, matrix, list or chart data points as the report action source object. The purpose of this recipe is to demonstrate the breadcrumb technique and not the complexities of embedded report actions. As such, we will be using a simple textbox on the report body to navigate between sample reports. These reports will not have any data sources or datasets other than the one required for this technique.

Designing the Report

In the end, you will have four almost identical reports designed to demonstrate the technique. You will start by designing one report with the base functionality for all reports and then create three copies of this report, rename them and make the necessary modifications.

1. Create a new report named Breadcrumb Report 1.

2. Drag and drop the report name from the Built-in Fields to create a title textbox and style the report header as you typically would with a large, bold font, horizontal line, etc, as you see in Figure 1.

3. In the report body, about an inch or three centimeters below the line, add a textbox to use as the report action source object. The user will click this text to navigate to the next report. Add some appropriate text to this textbox to indicate that it's a link to the second report.

In a more sophisticate production report, this textbox would be replaced with other report items or data regions with appropriate report actions.

Figure XX-2

A report parameter is used to pass the report navigation history to the target report. With each new report in the navigation chain, the name of that target report will be added to a comma-separated string passed in the parameter. This parameter will only be used internally and not shown to the user.

4. Add a report parameter named CallingReports.

5. Set the parameter to accept a Null value and set the visibility to be Hidden.

Figure XX-3

A dataset query will be used to drive a matrix data region to contain the report links. This query will be built using an expression and custom code function. Before we put this part into place, we must create a static query to generate the metadata for the dataset. This dataset will not actually consume any real data but a dataset must have a valid data source, And of course a data source must have a valid connection string to satisfy this requirement.

6. Create a new dataset and name it LinksQuery.

7. Any valid shared or embedded data source may be used for this dataset because data will not actually be read from a database. Create or select a data source and select any local or remote server and any valid database. I've used the localhost server and the Master database in this example.

8. For the query, type:

SELECT Null As Link

This is only a temporary placeholder to generate a field object named Link. The actual query string will be generated in a custom code function and expression.

Figure XX-4

A Matrix will be used to display each of the report links, each separated by a right angle bracket, >. Only the column header cells will be used in the Matrix.

9. Add a matrix data region to header area of the report body.

10. Drag the Link field to the Column Groups list to create a new group and group header.

11. Add a new column to the right, inside the group.

12.

13. Right-click in the header cell for this column and add the following expression:

=IIF(Fields!Link.Value = Last(Fields!Link.Value, "LinksQuery"), "", ">")

14. Choose both of these cells and set the Color to Blue.

15. Set the text in the first cell to be underscored to make it look like a hyperlink.Compare the matrix shown in Figure XX-5 to validate your design

Figure XX-5

16. Select the entire matrix and remove all borders using the toolbar or ribbon.

17. Resize the second row containing the data cells to reclaim this unused vertical space. Reduce the height of this row to make it as small as possible.

18. Add a textbox to body of the report.

19. Right-click and choose Textbox Properties.

20. On the Actions tab, create a report action by choosing the radio button Go to report.

21. The destination report doesn't exist yet so you will just type this value into the drop-down list. Type Breadcrumbs Report 2 into the Specify a report property.

22. Click the Add button to add a new target report parameter. If this report has previously been created, this parameter would be available for selection. Type CallingReports in the Name column.

23. Click the expression button (fx) next to the Value column and type the following into the Expression window:

=IIF(IsNothing(Parameters!CallingReports.Value), Globals!ReportName, Parameters!CallingReports.Value & ", " & Globals!ReportName)

24. Close and save the Expression dialog and use Figure XX-6 to verify these settings.

Figure XX-6

25. Click OK to save these settings and close the Text Box Properties dialog.

26. Edit the report properties and add the following code to the Code window:

Function ListToLinksSQL(List As String) As String

    Dim sTargetReport() As String

    Dim sReport As String

    Dim sOut As String

    sTargetReport = Split(List, ", ")

    For Each sReport In sTargetReport

       If sOut <> "" Then sOut &= " UNION "

       sOut &= "SELECT '" & sReport & "' AS Link"

    Next

    Return sOut

End Function

27. Edit the dataset properties.

28. Replace the query command text with the following expression:

=Code.ListToLinksSQL(Parameters!CallingReports.Value)

29. Save and close the report.

30. Create three copies of the Breadcrumbs Report 1, named:

Breadcrumbs Report 2
Breadcrumbs Report 3
Breadcrumbs Report 4

If you are using BIDS, the easiest way to add copies of a report to the project is to select the first report in the Solution Explorer and then use Ctrl-C and Ctrl-V to copy and paste a new file, then rename it. In Report Builder 2.0, use the Save As? feature to save copies of the file to the same folder as the original.

31. Modify each reports so that the report action targets the next report in the sequence and the last report back to the first.

The Breadcrumbs Report 2 report action should navigate to Breadcrumbs Report 3. Breadcrumbs Report 3 should navigate to Breadcrumbs Report 4, and Breadcrumbs Report 4 should navigate back to Breadcrumbs Report 1.

Preview the first report and click the link to navigate to the second report. Continue to follow the links on each subsequent report and note the accumulated breadcrumb links in the report header. Click any of the links to navigate to any of the reports in the series.

Figure XX-7

Several variations are possible.  This technique will display one link per distinct report.  If the user were to navigate from Report 1 to Report 2 and then back to Report 1,  A link for Report 1 would be displayed only once.  Adding another parameter to capture the current date and time for each drill-through would provide a mechanism to group on each duplicate report and to sort the list in the order of navigation.

Breadcrumbs Reports examples.zip (8.90 kb)