Export (0) Print
Expand All
Creating Audit Tables, Invoking COM Objects, and More
Exception-handling Techniques
Exploring SQL Server Triggers: Part 2
Deliver User-Friendly Reports from Your Application with SQL Server Reporting Services
SQL Server: Display Your Data Your Way with Custom Renderers for Reporting Services
Updating Data in Linked Servers, Information Schema Views, and More
XML Features in SQL Server 2000
Expand Minimize
0 out of 9 rated this helpful - Rate this topic

Microsoft Reporting Services in Action: Extending Microsoft SQL Server 2000 Reporting Services with Custom Code

SQL Server 2000
 

Teodor Lachev

September 2004

Applies To:
   SQL Server 2000 Reporting Services

Summary: This article presents an excerpt from the book, Microsoft Reporting Services in Action, by Teodor Lachev. Learn how to implement advanced report functionality using custom code. (28 printed pages)

Get the sample code for this article by downloading the Code.zip sample code.

Contents

Extending Microsoft SQL Server 2000 Reporting Services with Custom Code
Writing Embedded Code
Using External Assemblies
Custom Code in Action: Implementing Report Forecasting
Migrating OpenForecast
Summary

Extending Microsoft SQL Server 2000 Reporting Services with Custom Code

Microsoft released Microsoft SQL Server 2000 Reporting Services (Reporting Services) in the beginning of 2004 to provide developers with a complete reporting platform, which can be easily integrated with all types of applications regardless of the targeted platform or development language. One of the most prominent features of Reporting Services, which many developers including myself will appreciate, is its extensible nature. Just about any aspect of Reporting Services can be extended or replaced, including data, delivery, security, and report rendering features. For example, one way you can extend the capabilities of your reports is to integrate them with custom .NET code that you or somebody else wrote.

In this article, I will show you how to leverage the Reporting Services unique extensible architecture to supercharge your report capabilities. First, I will explain how embedded and custom code options work. Next, I will show you how you can leverage custom code to author an advanced report with sales forecasting features.

I will assume that you have a basic knowledge about Reporting Services and you know how to author reports with expressions. If you are new to Reporting Services, please visit its official website. The code examples and sample reports discussed in this article are included with the article source code. The sample reports use as their data source the AdventureWorks2000 database, which can be installed from the Reporting Services setup program.

Writing Embedded Code

As its name suggests, embedded code gets saved inside the report definition (RDL) file; it is scoped at a report level. You can write embedded code in Microsoft Visual Basic .NET only. Once the code is ready, you can call it in your report expressions by using the globally defined Code member. For example, if you have authored an embedded code function called GetValue, you can call it from your expressions by using the following syntax:

=Code.GetValue()

With the exception of shared methods, your embedded code can include any Visual Basic .NET-compliant code. In fact, if you think of the embedded code as a private class inside your project, you won't be far away from the truth. You can declare class-level members and constants, private or public methods, and so on.

You can write embedded code to create reusable utility functions that can be called from several expressions in your report. For example, consider the Territory Sales Crosstab report shown in Figure 1.

Aa902641.erscstcode01(en-us,SQL.80).gif

Figure 1. You can use embedded code to implement useful utility functions scoped at a report level.

This report uses an embedded function called GetValue to display "N/A" when data is missing (no report data exists for a given row-column combination). In addition, GetValue differentiates between missing data and NULL values. When the underlying value is NULL, the embedded code translates it to zero.

Using the Code Editor

To write custom embedded code, you use the Report Designer Code Editor, which you can find on the Code tab of the Report Properties dialog, as shown in Figure 2.

Aa902641.erscstcode02(en-us,SQL.80).gif

Figure 2. Use the Code Editor for writing embedded code. The GetValue function, shown in the Editor, determines whether a value is missing or NULL.

Granted, the above function can easily be replaced with an Iif-based expression. However, encapsulating the logic in an embedded function has two advantages. First, it centralizes the logic of the expression in one place instead of using Iif functions for every field in the report. Second, it makes the report more maintainable because, if you decide to make a logical change to your function, you do not have to track down and change every Iif function in the report.

The Report Designer saves embedded code under the <Code> element in the report definition file. When doing so, the Report Designer URL-encodes the text. Be aware of this if you decide to change the Code element directly for some reason.

Handling Missing Values

Once the GetValue function is ready, to differentiate between NULL and missing data in our report, we could base the txtSales and txtNoOrders data fields of the crosstab reports on the following expressions:

=Iif(CountRows()=0, "N/A", Code.GetValue(Sum(Fields!Sales.Value)))

and

=Iif(CountRows()=0, "N/A", Code.GetValue(Sum(Fields!NoOrders.Value)))

respectively.

The CountRows function is one of the several native functions provided by Reporting Services and returns the count of rows within a specified scope. If no scope is specified, it defaults to the innermost scope, which in our case resolves to the static group that defines the values in the data cells. Both expressions first check for missing data (no rows) by using CountRows and display "N/A" if no missing data is found. Otherwise, they call the GetValue embedded function to translate NULL values

I recommend you use embedded code for writing simple report-specific, utility-like functions. When your programming logic gets more involved, consider moving your code to external assemblies, as we will discuss next.

Using External Assemblies

The second way of extending reports programmatically is by using pre-packaged logic located in external .NET assemblies that can be written in any .NET-supported language. The ability to integrate reports with custom code in external assemblies increases your programming options dramatically. For example, by using custom code, you can:

  • Leverage the rich feature set of the .NET framework—For example, let's say you need a collection to store crosstab data of a matrix region in order to perform some calculations. You can "borrow" any of the collection classes that come with .NET, such as Array, ArrayList, Hashtable, and so on.
  • Integrate your reports with custom .NET assemblies, written by you or third-party vendors. For example, to add forecasting features to the Sales by Product Category report in section 2, I leveraged the open source OpenForecast package.
  • Write code a whole lot easier by leveraging the powerful Visual Studio .NET IDE instead of the primitive Code Editor.

Referencing External Assemblies

To use types located in an external assembly, you have to first let the Report Designer know about it by using the References tab in the Report Properties dialog, as shown in Figure 3.

Aa902641.erscstcode03(en-us,SQL.80).gif

Figure 3. Use the Report Properties dialog to reference an external assembly.

Assuming that my report needs to use the custom AWC.RS.Library assembly (included with the article source code), I must first reference it using the References tab. While this tab allows you to browse and reference an assembly from an arbitrary folder, note that when the report is executed, the .NET Common Language Runtime (CLR) will try to locate the assembly according to CLR probing rules. In a nutshell, these rules give you two options for deploying the custom assembly:

  • Deploy the assembly as a private assembly.
  • Deploy the assembly as a shared assembly in the .NET Global Assembly Cache (GAC). As a prerequisite, you have to strong-name your assembly. For more information about how to do this, please refer to the .NET documentation.

If you choose the first option, you need to deploy the assembly both to the Report Designer and Report Server, so the reports that reference the assembly will execute successfully during testing and as managed reports respectively. Assuming that you have accepted the default installation settings, to deploy the assembly to the Report Designer binary folder, copy the assembly to C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer. Once you have done this, you can build and render the report in a preview mode inside Visual Studio .NET.

As a part of deploying the report to the report catalog, make sure you copy the assembly to the Report Server binary folder, which by default is C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin.

Please note that copying the custom assembly to the right location is only half of the deployment story. Depending on what your code does, you may also need to adjust the code access security policy so the assembly code can execute successfully. If you need more information about deploying custom assemblies, please refer to the "Using Custom Assemblies with Reports" section in the Reporting Services documentation.

Calling Shared Methods

If you need to call only shared methods (also called static in C#) inside the assembly, you are ready to go because shared methods are available globally within the report.

You can call shared methods by using the fully qualified type name using the following syntax:

<Namespace>.<Type>.<Method>(argument1, argument2, ..., argumentN)

For example, if I need to call the GetForecastedSet shared method located in the RsLibrary class (AWC.RS.Library assembly) from an expression or embedded code, I will use the following syntax:

=AWC.Reporting Services.Library.RsLibrary.GetForecastedSet(forecastedSet, forecastedMonths)

where AWC.RS.Library is the namespace, RsLibrary is the type, GetForecastedSet the method, and forecastedSet and forecastedMonths are the arguments.

Calling Instance Methods

To invoke an instance method, you have some extra work left. First, you have to enumerate all instance classes (types) that you need to instantiate in the Classes grid. For each class, you have to assign an instance name. Behind the scenes, Reporting Services will create a variable with that name to hold a reference to the instance of the type.

When you specify the class name in the Classes grid, make sure that you enter the fully qualified type name (namespace included). In my example (Figure 3), the namespace is AWC.RS.Library, while the class name is RsLibrary. When you are in doubt as to what the fully qualified class name is, use the Visual Studio .NET Object Browser or other utilities, such as the excellent Lutz Roeder's .NET Reflector, to browse to the class name and find out its namespace.

For example, assuming that I need to call an instance method in the AWC.RS.Library assembly, now I have to declare an instance variable m_Library, as shown in Figure 3. In my case, this variable will hold a reference to the RsLibrary class.

If you declare more than one variable pointing to the same type, each will reference a separate instance of that type. Behind the scenes, when the report is processed, Reporting Services will instantiate as many instances of the referenced type as the number of the instance variables.

Once you are done with the reference settings, you are ready to call the instance methods by means of the instance type name that you specified. Just like with embedded code, you use the Code keyword to call an instance method. The difference between a shared and instance method is that instead of using the class name, you use the variable name to call the method.

For example, if the RsLibrary type had an instance method DummyMethod(), I can invoke it from an expression or embedded code like this:

Code.m_Library.DummyMethod()

Having seen what options we have as developers for expanding programmatically our report features, let's see how we can apply them in practice. In the next section, we will find out how embedded and external code can be used to add advanced features to our reports.

Custom Code in Action: Implementing Report Forecasting

In this section, I will show you how we can incorporate forecasting capabilities in our reports. Here are the design goals of the sample report that we are going to create:

  • Allow the user to generate a crosstab report of sales data for an arbitrary period.
  • Allow the user to specify the number of forecasted columns.
  • Use data extrapolation to forecast the sales data.

Here is our fictional scenario. Imagine that your users have requested a report that shows the Adventure Works forecasted monthly sales data grouped by product category. To make things more interesting, let's allow the report users to specify a data range to filter the sales data, as well as the number of forecasted months. To accomplish the above requirements, we will author a crosstab report, Sales by Product Category, as shown in Figure 4.

Aa902641.erscstcode04(en-us,SQL.80).gif

Figure 4. The Sales by Product Category uses embedded and external custom code for forecasting.

The user can enter a start and end date to filter the sales data. In addition, the user can specify how many months of forecasted data will be shown on the report. The report shows the data in a crosstab fashion, with product categories on rows, and time on columns. The data portion of the report shows first the actual sales within the requested period, followed by the forecasted sales in bold font.

For example, if the user enters 4/30/2003 as a start date and 3/31/2004 as an end date, and requests to see three forecasted months, the report will show the forecasted data for April, May, and June 2004 (to conserve space, Figure 4 shows only one month of forecasted data).

As you would probably agree, implementing forecasting features on your own is not an easy undertaking. But what if there is already pre-packaged code that does this for us? If this code can run on .NET, our report can access it as custom code. Enter OpenForecast.

Forecasting with OpenForecast

Forecasting is a science of itself. Generally speaking, forecasting is concerned with the process used to predict the unknown. Instead of looking at a crystal ball, forecasting practitioners use mathematical models to analyze data, discover trends, and make educated conclusions. In our example, the Sales by Product Category report will predict the future sales data by the method of data extrapolating.

There are a number of well-known mathematical models to extrapolate a set of data, such as polynomial regression, simple exponential smoothing, and so on. Implementing one of those models, though, is not a simple task. Instead, for the purposes of our sales forecasting example, we will use the excellent open source OpenForecast package, written by Steven Gould. OpenForecast is a general purpose package which includes Java-based forecasting models that can be applied to any data series. The package requires no knowledge of forecasting and supports several mathematical forecasting models, including single variable linear regression, multi-variable linear regression, and so on. To learn more about OpenForecast, visit its home page at http://openforecast.sourceforge.net/.

Let's now see how we can implement our forecasting example and integrate with OpenForecast by writing some embedded and external code.

Implementing Report Forecasting Features

Creating a crosstab report with forecasting capabilities requires several implementation steps. Let's start with a high-level view of our envisioned approach and then drill down into the implementation details.

Choosing an Implementation Approach

Figure 5 shows the logical architecture view of our solution.

Aa902641.erscstcode05(en-us,SQL.80).gif

Figure 5. The Sales by Product Category report uses embedded code to call the AwRsLibrary assembly, which in turns calls the J# OpenForecast package.

Our report will use embedded code to call a shared method in a custom assembly (AwRsLibrary) and get the forecasted data. AwRsLibrary will load the existing sales data into an OpenForecast dataset and obtain a forecasting model from OpenForecast. Then, it will call down to OpenForecast to get the forecasted values for the requested number of months. AwRsLibrary will return the forecasted data to the report which in turn will display it.

We have at least two implementation options to pass the crosstab sales data to AwRsLibrary.

  • Fetch the sales data again from the database. To accomplish this, the report could pass the selected product category and month values on row basis. Then, AwRsLibrary could make a database call to retrieve the matching sales data.
  • Load the existing sales data in a structure of some kind using embedded code inside the report and pass the structure to AwRsLibrary.

The advantages of the latter approach are:

  • The custom code logic is self-contained. We don't have to query the database again.
  • Use the default custom code security policy. We don't have to elevate the default code access security policy for the AwRsLibrary assembly. If we choose the first option, we won't be able to get away with the default code access security setup, because Reporting Services will grant our custom assemblies only Execution rights, which are not sufficient to make a database call. Actually, in the case of OpenForecast, I had to grant both assemblies FullTrust rights because any J# code requires FullTrust to execute successfully. However, I wouldn't have to do this if I chose C# as a programming language.
  • No data synchronization required. We don't have to worry about synchronizing both data containers, the matrix region and the AwRsLibrary dataset.

For the above reasons, I choose the second approach. To get it implemented, we will use an expression to populate the matrix region data values. The expression will call our embedded code to load an array structure maintained in the embedded code on a row-by-row basis. Once a given row is loaded, we will pass the array to AwRsLibrary to get the forecasted data.

Now, let's discuss the implementation details starting with converting OpenForecast to .NET.

Migrating OpenForecast

OpenForecast is written in Java, so one of the first hurdles that I had to overcome was to integrate it with .NET. I had two options:

  • I could have used a third-party Java-to-.NET gateway to integrate both platforms. Given the complexities of this approach, I quickly dismissed it.
  • Port OpenForecast to one of the supported .NET languages. Microsoft provides two options for this. First, you can use the Microsoft Java Language Conversion Assistant to convert Java-language code to C#. Second, I could convert OpenForecast to J#. This would have preserved the Java syntax, although that code will execute under the control of the .NET Common Language Runtime instead of Java Virtual Machine.

I decided to port OpenForecast to J#. The added benefit to this approach is that the Open Source developers could maintain only one, Java-based version of OpenForecast.

Porting OpenForecast to J# turned out to be easier than I thought. I created a new J# library project, named it OpenForecast, and loaded all *.java source files inside it. I included the .NET version of OpenForecast in the source code, which comes with this article. I had to take care of only a few compilation errors inside the MultipleLinearRegression, as a result of the fact that several Java hashtable methods were not supported in J#, such as keySet(), entries(), and hashtable cloning. I also included a WinForm application (TestHarness), which you can use to test the converted OpenForecast.

Implementing the AwRsLibrary Assembly

The next step was to create the custom .NET assembly, AwRsLibrary, that will bridge the report embedded code and OpenForecast. I implemented AwRsLibrary as a C# class library project. Inside it I created a class, RsLibrary, which exposes a static (shared) method, GetForecastedSet. The AwRsLibrary code for this method is included in the sample code for this article.

The GetForecastedSet method receives the existing sales data for a given product category in the form of dataSet array, as well as the number of the requested months for forecasted data. Next, integrating with OpenForecast is a matter of five steps:

Step 1: First, we create a new OpenForecast dataset and load it with the existing data from the matrix row array.

Step 2: Next, we obtain a given forecasting mode. OpenForecast allows developers to get the optimal forecasting mathematical model based on the given data series by calling the getBestForecast method. This method will examine the dataset and will try a few forecasting models to select the most optimal. If the returned model is not a good fit, you can request a forecasting model explicitly by instantiating any of the classes found under the models project folder.

Step 3: Next, we prepare another dataset to hold the forecasted data and initialize it with as many elements as the number of forecasted months.

Step 4: Finally, we call the forecast method to extrapolate the data and return the forecasted results.

Step 5: The only thing left is to load the forecasted data back to the dataSet array so we can pass it back to the report embedded code.

Once we are done with both AwRsLibrary and OpenForecast .NET assemblies, we need to deploy them.

Deploying Custom Assemblies

We need to deploy custom assemblies to both the Report Designer and Report Server binary folders. The custom assembly deployment process consists of the following steps:

  • Copying the assemblies to the Report Designer and Report Server binary folders.
  • Adjusting the code-based security if the custom code needs an elevated set of code access security permissions.

To make both assemblies, AwRsLibrary and OpenForecast, available during design time, we have to copy AWC.RS.Library.dll and OpenForecast.dll to the Report Designer folder, which by default is C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer.

Similarly, to render successfully the deployed report under the Report Server, we have to deploy both assemblies to the Report Server binary folder, which by default is C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin. In fact, the Report Server will not let you deploy a report from within the Visual Studio .NET IDE if all referenced custom assemblies are not already deployed.

The default Reporting Services code access security policy grants execution rights to all custom assemblies by default. However, J# assemblies require full trust code access rights. Since the .NET Common Language Runtime walks up the call stack to verify that all callers have the required permission set, we need to elevate the code access security policy for both assemblies to full trust. This will require changes to the Report Designer and Report Server security configuration files.

To help you setting up code access security policy, I provided a copy of my rssrvpolicy.config in the Config folder. Toward the end of the file, you will see two CodeGroup XML elements that point to the AwRsLibrary and OpenForecast files. You need to copy these elements to the Report Server security configuration file (rssrvpolicy.config).

In addition, if you want to preview (run) the report in the Preview Window from the Report Designer, you need to propagate the changes to the Report Designer security configuration file (rspreviewpolicy.config) as well.

Once the custom assemblies are deployed, we will need to write some Visual Basic .NET embedded code in our report to call the AwRsLibrary assembly, as we will discuss next.

Writing Report Embedded Code

To integrate the report with AwRsLibrary I wrote the GetValue function, as shown in Listing 2.

Listing 2. The Embedded GetValue Function Calls the AwRsLibrary Assembly

Dim forecastedSet() As Double  ' array with sales data
Dim productCategoryID As Integer = -1
Dim bNewSeries As Boolean = False
Public Dim m_ExString = String.Empty  ' holds the error message, if any

Function GetValue(productCategoryID As Integer, orderDate As DateTime, sales As Double, reportParameters as Parameters, txtRange as TextBox) As Double
        Dim startDate as DateTime = reportParameters!StartDate.Value
        Dim endDate as DateTime = reportParameters!EndDate.Value
        Dim forecastedMonths as Integer = reportParameters!ForecastedMonths.Value
       
If (forecastedSet Is Nothing) Then
               ReDim forecastedSet(DateDiff(DateInterval.Month, startDate, endDate) +
              forecastedMonths)          #1
       End If

        If Me.productCategoryID <> productCategoryID Then    #2
            Me.productCategoryID = productCategoryID
            bNewSeries = True
            Array.Clear(forecastedSet, 0, forecastedSet.Length - 1)
        End If

        Dim i = DateDiff(DateInterval.Month, startDate , orderDate)

        ' Is this a forecasted value?
        If orderDate <= endDate Then
            ' No, just load the value in the array
            forecastedSet(i) = sales
        Else
            If bNewSeries Then
                   Try
                       AWC.RS.Library.RsLibrary.GetForecastedSet(forecastedSet, forecastedMonths)  #3
                       bNewSeries = False
                    Catch ex As Exception
                           m_ExString  = "Exception: " & ex.Message
                           System.Diagnostics.Trace.WriteLine(ex.ToString())
                          throw ex
                    End Try
            End If
        End If
        Return forecastedSet(i)
    End Function

Because the matrix region data cells use an expression that references the GetValue function, this function gets called by each data cell. Table 1 lists the input arguments that the GetValue function takes.

Table 1. Each data cell inside the matrix region will call the GetValue embedded function and pass the following input arguments.

ArgumentPurpose
productCategoryIDThe ProductCategoryID value from the rowProductCategory row grouping corresponding to the cell.
orderDateThe OrderDate value from the colMonth column grouping corresponding to the cell.
salesThe aggregated sales total for this cell.
reportParametersTo calculate the array dimensions, GetValue needs the values of the report parameters. Instead of passing the parameters individually using Parameters!ParameterName.Value, I pass a reference to the report Parameters collection.
txtRangeA variable that holds the error message in case an exception occurs when getting the forecast data.

To understand how GetValue works, please note that each data cell inside the matrix region is fed from the forecastedSet array. If the cell doesn't need forecasting (its corresponding date is within the requested date range), we just load the cell value in the array and pass it back to display it in the matrix region. To get this working, we need to initialize the array to have a rank equal to the number of requested months plus number of forecasted months. Once the matrix region moves to a new row and calls our function, we are ready to forecast the data by calling the AwRsLibrary:GetForecastedSet method.

Implementing the Sales by Product Category Crosstab Report

The most difficult part of authoring the report itself was setting up its data to ensure that we always have the correct number of columns in the matrix region to show the forecasted columns. By default, the matrix region won't show columns that don't have data. This will upset calculating the right offset to feed the cells from the array.

Therefore, we have to ensure that the database returns records for all months within the requested data range. To implement this, we need to pre-process the sales data at the database. This is exactly what the spGetForecastedData stored procedure does. Inside the stored procedure, I pre-populate a custom table with all monthly periods within the requested date range, as shown in Listing 3.

Listing 3. The spGetForecastedData Stored Procedure Ensures That the Returned Rowset Has the Correct Number of Columns

CREATE  PROCEDURE spGetForecastedData (  
  @StartDate smalldatetime, 
  @EndDate smalldatetime
)
AS
DECLARE @tempDate smalldatetime
DECLARE @dateSet TABLE   (       #1
  ProductCategoryID   tinyint,    
  OrderDate    smalldatetime    
  )              

SET   @tempDate = @EndDate

WHILE (@StartDate <= @tempDate)      #2
BEGIN              
  INSERT INTO @dateSet         
  SELECT ProductCategoryID,  @tempDate    
  FROM ProductCategory        
  SET @tempDate = DATEADD(mm, -1, @tempDate)
END

SELECT      DS.ProductCategoryID, PC.Name as ProductCategory, OrderDate AS Date, NULL AS Sales
FROM      @dateSet DS INNER JOIN ProductCategory PC ON DS.ProductCategoryID=PC.ProductCategoryID
UNION ALL            #3
SELECT     PC.ProductCategoryID, PC.Name AS ProductCategory, SOH.OrderDate AS Date, SUM(SOD.UnitPrice * SOD.OrderQty) AS Sales
FROM         ProductSubCategory PSC INNER JOIN
             ProductCategory PC ON PSC.ProductCategoryID = PC.ProductCategoryID INNER JOIN
             Product P ON PSC.ProductSubCategoryID = P.ProductSubCategoryID INNER JOIN
             SalesOrderHeader SOH INNER JOIN
             SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID ON P.ProductID = SOD.ProductID
WHERE     (SOH.OrderDate BETWEEN @StartDate AND @EndDate)
GROUP BY SOH.OrderDate, PC.Name, PC.ProductCategoryID
ORDER BY PC.Name, OrderDate

Finally, I union all records from table @dateSet (its Sales column values set to NULL) with the actual Transact-SQL statement that fetches the sales data.

Once the dataset is set, authoring the rest of the report is easy. We use a matrix region for the crosstab portion of the report. To understand how the matrix region magic works and invokes the embedded GetValue function, you might want to replace the expression of the txtSales textbox with the following expression:

= Fields!ProductCategoryID.Value & "," & Fields!Date.Value  
& "," &  Format(Fields!Sales.Value, "C")

Figure 6 shows what the Sales by Product Category looks like when this expression is applied.

Aa902641.erscstcode06(en-us,SQL.80).gif

Figure 6. How the matrix region aggregates data.

As you can see, we can easily get to the corresponding row and column group values that the matrix region uses to calculate the aggregate values in the region data cells. Now we have a way to identify each data cell. The matrix region is set up as shown in Table 2.

Table 2. Trick to get the matrix region populated with forecasted values is to base its data cells on an expression.

Matrix AreaNameExpression
RowsrowProductGroup=Fields!ProductCategory.Value
ColumnscolYear

colMonth

=Fields!Date.Value.Year

=Fields!Date.Value.Month

DatatxtSales=Code.GetValue(Fields!ProductCategoryID.Value, Fields!Date.Value, Sum(Fields!Sales.Value), Parameters, ReportItems!txtRange)

To implement conditional formatting for the forecasted columns (show them in bold), I used the following expression for the font property of the txtSales textbox:

=Iif(Code.IsForecasted(Fields!Date.Value, Parameters!EndDate.Value), "Bold", "Normal")

This expression calls the IsForecasted function located in the report embedded code. The function simply compares the sales monthly date with the requested end date and, if the sales date is before the end date, returns false.

Finally, the only thing left is to reference the AwRsLibrary assembly using the report References tab, as we've seen in Figure 3. Please note that for the purposes of this report, we don't need to set up an Instance Name (no need to enter anything in the Classes grid), since we don't call any instance methods.

Debugging Custom Code

You may find debugging custom code challenging. For this reason, I would like to share with you a few techniques that I have found useful for custom code debugging.

There aren't many options for debugging embedded code. The only one I have found out so far is to use the MsgBox function to output messages and variable values when the report is rendered inside the Report Designer. Make sure to remove the calls to MsgBox before deploying the report to the Report Server. If you don't, all MsgBox calls will result in an exception. For some reason, trace messages using System.Diagnostics.Trace (OutputDebugString API) inside embedded code get "swallowed" and don't appear either in the Visual Studio .NET Output window, or when using an external tracing tool.

When working with external assemblies, you have at least two debugging options:

  • Output trace messages.
  • Use the Visual Studio .NET debugger to step through the custom code.

Tracing

For example, in the AwRsLibrary.GetForecastedSet method, I am outputting trace messages using System.Dianogistics.Trace.WriteLine to display the observed and forecasted values. To see these messages when running the report inside Visual Studio .NET or Report Server, you can use the excellent DebugView tool by Mark Russinovich, shown in Figure 7.

Aa902641.erscstcode07(en-us,SQL.80).gif

Figure 7. Outputting trace messages from external assemblies in DebugView.

Debugging Custom Code

You can also step through the custom assembly code using the Visual Studio .NET debugger by attaching to the Report Designer process, as follows:

  • Open the custom assembly that you want to debug in a new instance of Visual Studio .NET. Set breakpoints in your code as usual.
  • In your custom assembly project properties, select Configuration Properties->Debugging and set Debug Mode to Wait to Attach to an External Process.
  • Open your business intelligence project in another instance of Visual Studio .NET.
  • Back to the custom assembly project, click on the Debug menu and then Processes... Locate the devevn process that hosts the Business Intelligence project and attach to it. In the Attach To Process dialog make sure that the Common Language Runtime checkbox is selected, and click Attach. At this point, your Processes dialog should look like the one shown in Figure 8.

        Click here for larger image.

    Figure 8. To debug custom assemblies, attach to the Visual Studio instance that hosts your Business Intelligence project.

In my case, I want to debug the code in the AwRsLibrary assembly when it is invoked by the Sales by Product Category report. For this reason, in the AwRsLibrary project I attach to the AWReporter devenv process.

  • In the Business Intelligence project, preview the report that calls the custom assembly. Or, if you have already been previewing the report, hit the Refresh Report button on the Preview Tab toolbar. At this point, your breakpoints should be hit by the Visual Studio .NET debugger.

As you will soon find out, if you need to make code changes and recompile the custom assembly, trying to re-deploy it to the Report Designer folder results in the following exception:

Cannot copy <assembly name>: It is being used by another person or program.

The problem is that Visual Studio .NET IDE holds a reference to the custom assembly. You will need to shut down Visual Studio .NET and then re-deploy the new assembly. To avoid this, you could debug the custom assembly code by using the Report Host (Preview Window). To do this, follow these steps:

  • Add the custom assembly to the Visual Studio .NET solution that includes your Business Intelligence project.
  • Change the Business Intelligence project start item to the report that calls the custom code, as shown in Figure 9.

        Click here for larger image.

    Figure 9. Use the Report Host debug option to avoid locking assemblies.

  • Hit F5 to run the report in the Preview Window. When the report calls the custom code, your breakpoints will be hit.

When using the Preview Window approach, Visual Studio .NET doesn't lock the custom assemblies. This allows you to change the build location of your assembly to the Report Designer folder so it always includes the most recent copy when you rebuild the assembly. Running your projects in the Preview Window is a subject of the code access security policy settings specified in the Report Designer configuration file (rspreviewpolicy.config).

Summary

In this article we learned how to integrate our reports with custom code we or someone else wrote.

For simple report-specific programming logic, use embedded Visual Basic .NET code. When the code complexity increases or you prefer to use programming languages other than Visual Basic .NET, move your code to external assemblies.

Using custom code is just one of the several ways developers can extend Reporting Services. To find out more about Reporting Services extensibility, read the "Extending Reporting Services" section in Reporting Services Books Online.

For More Information:

http://www.microsoft.com/sql/

Related Books:

Microsoft Reporting Services in Action

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.