Microsoft Reporting Services in Action: Extensions with custom code Microsoft SQL Server 2000 Reporting Services

Processing lack value

Once the getValue function can distinguish between NULLs and missing data in the report, we can use the following expressions as the basis of the TXTSAASALES and TXTNOORDERS data fields of the crosstab report:

= IIF (countrows () = 0, "n / a", code.getvalue (sum (fields! Sales.value))))

with

= IIF (countrows () = 0, "n / a", code.getValue (SUM (Fields! Noorders.Value))))))

.

The Countrows function is one of several native functions provided by Reporting Services, which can return the number of rows within the specified range. If there is no specified range, it will default to the most inoperative range, which is parsed in our example to define the value of the value in the data cell. Both expressions will be used to check the missing data (no line), and "N / A" will be displayed if the missing data is not found. Otherwise, they will call the GetValue embedded function to convert the NULL value.

I recommend you to use embedded code to write simple report dedicated and similar to the functionality of the utility. When your programming logic becomes more complicated, consider moving your code to an external assembly, we will discuss this in the next step.

Back to top

Use an external assembly

The second way to extend the report in programming is to use the prepacking logic in the external .NET program set, these assemblies can be written using any .NET supported. This ability to integrate a custom code in the report and an external program has significantly improved the selection of programming. For example, by using custom code, you can:

•

Using the .NET Framework's rich feature set - or examples, for example, you need a collection to store crosstab data in a matrix area to perform some calculations. You can "borrow". Net included, such as Array, ArrayList, Hashtable, etc.

•

Integrate your statement with your custom .NET assembly written by you or third party vendors. For example, in order to add a predictive function to the Sales by Product Category report in the second part, I use the OpenForeCast package for open source.

•

Writing code is simpler by using a powerful Visual Studio .NET IDE instead of the original code editor. Reference external assembly

To use the type located in an external program, you must first use the References tab in the Report Properties dialog to let the report designer know it, as shown in Figure 3.

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

Assuming that my report needs to use a custom AWC.RS.Library assembly (including in the source code of the article), I must first use the References tab to reference it. Although this tab allows you to browse and reference the assembly in any folder, please note that when the report is executed, the .NET Public Language Runture (CLR) will attempt to locate the assembly according to the CLR probe rule. Simply put, these rules provide you with two options for deploying custom assemblies:

•

Deploy assembly as private assemblies.

•

Deploy the assembly as a shared assembly in the .NET Global Jack Cache (GAC). As a prerequisite, you must have a strong name. For more information on how to do this, see the .NET document.

If you have selected the first option, you need to deploy the assembly to the Report Designer and Report Server, which references the assembly report will be successfully performed during the test, and is executed as a managed report. Assuming that you have accepted the default installation settings, then copy the assembly to the C: / Program Files / Microsoft SQL Server / 80 / Tools / Report Designer. Once you do this, you can build a report and present it in the preview mode of Visual Studio .NET.

As part of the report to the report directory, make sure you copy the assembly to the report server binary folder, its default location is C: / Program Files / Microsoft SQL Server / MSSQL / Reporting Services / ReportServer / bin.

Note that the custom assembly is copied to the correct position is only part of the deployment process. Based on the task of the code, you may also need to adjust the code access security policy so that the assembly code can be performed smoothly. If you need more information about deploying custom assemblies, see the "Using Custom Assemblies With Reports" section in the Reporting Services documentation.

Call sharing method

If you only need to call the sharing method in the program (also known as a static method in C #), you can do this because the sharing method is globally available in the report.

You can call the sharing method with a fully qualified type name by the following syntax:

. . (argument1, argument2, ..., argumentn)

For example, if I need to call the GetForeCastedSet sharing method 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)

Among them, AWC.RS.Library is a namespace, RSLibrary is type, getforecastedset is a method, and ForecastedSet and ForeCaStedMontHS are parameters. Call instance method

To call an instance method, you have some extra work to do. First, you must enumerate all instances (types) that need to be instantiated in the CLASSES grid. For each class, you must specify an instance name. In the background, Reporting Services will create a variable with this name to save references to this type of instance.

When you specify a class name in the Classes grid, make sure you entered a fully qualified type name (including namespace). In my example (Figure 3), the namespace is awc.rs.library, and the class name is RSLibrary. If you are not sure about the fully qualified class name, use the Visual Studio .Net Object Browser or other utility (such as a good LUTZ ROEDER .NET Reflector) to locate the class name and find its namespace.

For example, suppose I need to call an instance method in the AWC.RS.Library program set, so I must now declare an instance variable M_Library, as shown in Figure 3. In my example, this variable will save a reference to the RSLibrary class.

If you want to declare multiple variables to the same type, each variable needs to reference a separate instance of this type. In the background, when the report is processed, the Reporting Services will be the example of the reference type of the number of instance variables.

Once the reference setting is completed, you can call the instance method through the specified instance type name. Just like using embedded code, you can use the Code keyword to call the instance method. The difference between the sharing method and the instance method is that you use the variable name to call the method, not the class name.

For example, if the RSLibrary type has an instance method DummyMethod (), I can call it from an expression or embedded code, as shown below:

Code.m_library.dummymethod ()

After understanding the selection used as a developer, after programming the selection of the report function, look at how to put it into practice. In the next section, we will learn how to add advanced features to our reports using embedded code and external code.

Back to top

Custom code in operation: Realize report forecast

In this section, I will show you how to implant the prediction function in our report. Below is the design goal of the sample report we will create:

•

Let users generate a sales data crosstab report for any stage.

•

Let the user can specify the number of predicted columns.

•

Use a data external push method to predict sales data.

Here is our virtual case. Suppose your user requested a report to show the sales data in the ADVENTURE WORKS monthly predictive sales data. In order to make things more interesting, we will allow reporters to specify a data range to filter sales data and the number of forecasts. In order to achieve the above requirements, we will write a crosstab report Sales by Product Category, as shown in Figure 4.

Figure 4. Sales By Product Category predicts using embedded code and external custom code.

Users can enter the start date to filter sales data. In addition, users can specify how many months of predictive data will be displayed on the report. The report displays data in a crosstable method, displays the product category on the line, and the time is displayed. The data part of the report first displays the actual sales within the request time, and the prediction sales is displayed in bold. For example, if the user enters 4/30/2003 as the start date, enter 3/31/2004 as the deadline, then request to view three predictive months, then the report will display the forecast data from April, May and June 2004. (To save space, Figure 4 shows only one month predictive data).

You may also admit that you do your own prediction function is not a simple task. But what if you already have a pre-package code to do this? If this code can run on .NET, our report can be accessed as a custom code. Enter OpenForecast.

Predict with OpenForecast

The forecast itself is a science. In general, predicting is the process used to predict unknown. Predicting professionals use mathematical models to analyze data, discover trends and make a basis, rather than watching crystal balls. In our example, the Sales By Product Category report will predict future sales data through the data extraction method.

The well-known mathematical model used to external push a set of data, such as polynomial regression, simple index smoothization, etc. However, achieving these models is not a simple task. Conversely, for our sales prediction example, we will use the excellent open source OpenForeCast package written by Steven Gould. OpenForeCast is a universal package that contains Java-based prediction models that can be applied to any data series. This package does not require you to understand any prediction knowledge and support several mathematical prediction models, including single variable linear regression, multivariate linear regression, and more. To learn more about OpenForecast, please visit its home page http://openforecast.SourceForge.net/.

Now let's take a look at how to implement a predictive example and integrate with OpenForecast by writing some embedded code and external code.

Realize report forecasting

Creating a crosstree with prediction features requires the following implementation steps. Let us start from a high-level view of an expected method and then downgrade to implement the details.

Select a method of implementation

Figure 5 shows a logical architectural view of the solution.

Figure 5. Sales by Product Category Report Use embedded code to call the AWRSLibrary assembly, then call the J # OpenForeCast package.

Our reports will use embedded code to call a sharing method in a custom assembly (AWRSLIBRARY) and get predictive data. AWRSLIBRARY will load existing sales data into an OpenForecast data set and get a predictive model from OpenForecast. It then calls OpenForecast to get the predicted value of the requested month. AWRSLIBRARY returns the predictive data to the report and then display it.

We have at least two implementation options to deliver crosstab to AWRSLibrary.

•

Get the sales data from the database again. To achieve this, the report can pass the selected product category and monthly sales. Then, AWRSLIBRARY can make a database call to retrieve the matching sales data.

•

Use the embedded code inside the report to load the existing sales data into some type of structure and passed the structure to awrslibrary.

The advantages of the latter method are:

•

Custom code logic is independent. We don't have to query the database again.

•

Use the default custom code security policy. We don't have to increase the default code access security policy for the AWRSLIBRARY assembly. If we choose the first option, we cannot skip the default code access security settings because Reporting Services will only grant the power of custom assemblies "execution", which is not enough for database calls. In fact, for OpenForecast, I have to grant full trust in two assemblies, because any J # code needs full trust power to perform smoothly. However, if I choose C # as a programming language, it is not necessary to do this. •

No data synchronization is required. We don't have to consider synchronizing two data containers, matrix areas, and AWRSLIBRARY data sets.

For the above reasons, I chose the second method. In order to implement this method, we will use an expression to fill the matrix area data value. This expression will call our embedded code to load an array structure, which is maintained in the embedded code. Once the given row is loaded, we pass the array to AWRSLibrary to obtain predictive data.

Now let's discuss the implementation details from the transfer of OpenForecast to .NET.

Back to top

Migrate OpenForecast

OpenForecast is written in Java, so the first obstacle that I have to overcome is to integrate it with .NET. I have two choices:

•

I can use a third party Java to the .NET gateway to integrate two platforms. Due to the complexity of this method, I will soon give up it.

•

Convert OpenForecast to one of the languages ​​that support .NET. Microsoft provides two options for this. First, you can use Microsoft Java Language Conversion Assistant to convert Java language code to C #. Second, I can convert OpenForecast to J #. This will preserve Java syntax, although the code will be executed under the control of the .NET public language runtime (not Java virtual machine).

I decided to convert OpenForecast to J #. The advantage of this method is that open source developers can only maintain a Java-based OpenForecast version.

Convert OpenForecast to J # is simpler than I think. I created a new J # library project, named OpenForecast, and then loaded all * .java source files. I contain the .NET version of OpenForecast in the source code, this article is attached to the source code. I only need to take into account the compilation errors in several MultipleLineArregression. The actual result is that there are several Java hash table methods that are not supported in J #, such as KeySet (), entries (), and hash table clones. I also contain a Winform Application (Testharness), you can use it to test the converted OpenForecast.

Implement a WRSLibrary assembly

The next step is to create a custom .NET assembly AWRSLIBRARY, which will cross the report embedded code and OpenForecast. I implemented AWRSLIBRARY as a C # class library project. Among them, I created a class RSLibrary, which publicly has a static (shared) method getforcastedset. The AWRSLibrary code of this method is included in the sample code herein.

The GetForeCastedSet method receives existing sales data for a given product category in the form of a data set group, as well as the number of months requested to predict data. Next, there are five steps to integrate OpenForeCast:

Step 1: First, we create a new OpenForecast dataset and load it with existing data from the matrix line array. Step 2: Next, we get a given prediction mode. OpenForecast allows developers to achieve the best predictive mathematical model based on a given data collection by calling the GetBestForecast method. This method will check the dataset and try several predictive models to select one of the ideal. If the returned model is not very suitable, you can explicitly request a predictive model by instantiating any class found under the model project folder.

Step 3: Next, we prepare another data set to save the prediction data and use the elements that are as much as the number of predicted months to initialize the data set.

Step 4: Finally, we call the Forecast method to extrap push the data and return the forecast results.

Step 5: The remaining last thing is to load the predictive data back to the data set number, so that we can pass it back to the table embedded code.

After we have completed two .NET assemblies of AWRSLIBRARY and OpenForeCast, they need to deploy them.

Deploy custom assembly

We need to deploy custom assemblies to the binary folder of the report designer and report server. The deployment process of custom assembly consists of the following steps:

•

Copy the assembly to the binary folder of the report designer and report server.

•

If the custom code requires an improved code access to the security rights set, you need to adjust the code-based security.

To make AWRSLIBRARY and OpenForeCast are available when design, we must copy the awc.rs.library.dll and OpenForeCast.dll to the Report Designer folder, the default location is C: / Program Files / Microsoft SQL Server / 80 / Tools / Report Designer.

Similarly, to successfully present the deployed report under the report server, we must deploy two assemblies to the binary folder of the report server, its default location is C: / Program Files / Microsoft SQL Server / MSSQL / Reporting Services / Reportserver / bin. In fact, if the referenced custom program set is well deployed, the report server will not allow you to deploy reports from Visual Studio .NET IDE.

The default Reporting Services Code Access Security Policy grants permission to all custom assemblies by default. However, the J # assembly needs full trust code access. Because .NET Public Language Runtime Runout Call Stack The multi-pass multiflorsen, we need to enhance the code access security policies of the two assemblies to the full trust level. This will make changes to the security profile of the report designer and report servers.

To help you set up the code access security policy, I have a copy of the RSSrvPolicy.config in my config folder. On the place close to the file, you will see two CodeGroup XML elements, pointing to the AWRSLIBRARY and OpenForecast files, respectively. You need to copy these elements to the secure configuration file of the report server (RSSRVPolicy.config).

In addition, if you want to preview (run) reports in the preview window of the Report Designer, you also need to propagate these changes into the secure configuration file of the report designer.

After custom assembly deployment, we need to write some Visual Basic .NET embedded code in the report to call the AWRSLibrary assembly, which will be discussed in the next part.

Write report embedded code

To integrate the report with the AWRSLibrary, I have written the getValue function, as shown in Listing 2. Listing 2. Embedded GetValue function call 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 forcastedmonths as integer = reportparameters! ForcastedMonths.Value

IF (Forecastedset Is Nothing) THEN

Redim forcastedset (Dateinterval.month, StartDate, Enddate)

ForecastedMonths # 1

END IF

If Me.ProductCategoryId <> ProductCateGoryId the # 2

Me.ProductCategoryId = ProductCategoryID

Bnewseries = TRUE

Array.clear (ForecastedSet, 0, Foressset.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 exception

M_EXSTRING = "Exception:" & ex.Message

System.diagnostics.trace.writeline (ex.totring ())

Throw EX

END TRY

END IF

END IF

Return ForessSet (i)

END FUNCTION

Because the matrix area data cell uses an expression that references the getValue function, this function can be called by each data cell. Table 1 lists the input parameters employed by the getValue function.

Table 1. Each data cell in the matrix area will call the GetValue embedded function and pass the following input parameters. Parameter Use ProductCategoryID The ProductCateGoryID value of the RowProductCateGoryId group corresponding to the cell. ORDERDATE The OrderDate value of the colmonth column packet corresponding to the cell. SALES The total number of total sales of this cell. ReportParameters In order to calculate array dimensions, GetValue needs to report the value of the parameters. I passed a reference to the report parameter collection, rather than using parameters! Parametername.Value individual delivery parameters. TXTRANGE saves the variable of the error message to prevent an exception when it acquires the predictive data. To learn how GetValue work, please note that each data cell in the matrix area is from the ForessSet array. If the cell does not require prediction (its corresponding date is within the request date range), we only need to load the value of the cell in the array, and return it to display it in the matrix area. In order to implement this, we need to initialize the array to get a rank equal to the number of months plus the month. Once the matrix area moves to a new row and calls our function, we can predict the data by calling the awrslibrary: getforecastedset method.

Implement Sales by Product Category Cross-Table Report

The most difficult part of writing a report itself is to set its data to ensure that we always display the predicted columns through the correct column in the matrix area. By default, the matrix area will not display columns without data. This disrupts the calculation of the correct offset from the array to the cell.

Therefore, we must ensure that the database returns a record of the request data range. To achieve this, we need to prepare sales data in the database. This is the task of the SPGETFORECASTEDData stored procedure. During this stored procedure, I use a custom table with all the month cycles within the request data, as shown in Listing 3.

Listing 3. SpgetForeCastedData stored procedure to ensure that the returned row collection has the correct column number

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 products, soh.orderdate as date, sum (sod.Unitprice * sod.orderqty) as salesfrom 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 use the actual Transact-SQL statement that can obtain sales data, combined with all records of the table @Dateset (its sales column value set to null).

After setting the data set, the rest of the report is more simple. We use a matrix area for the cross-table part of the report. To learn how the matrix area is working and calls embedded getValue functions, you may have to replace the expression of the TXTSAles text box with the following expressions:

Figure 6 shows the appearance of Sales by Product Category when applying this expression.

Figure 6. How to aggregate data in the matrix area.

As you can see, we can easily get the corresponding row and column group values, and the matrix area will use these values ​​to calculate the aggregation values ​​in the area data cell. Now, we have a way to identify each data cell. The setting of the matrix area is shown in Table 2.

Table 2. Tips for filling the matrix area with the predicted value are based on expressions based on expressions. Matrix area name expressions row RowProductGroup = fields! ProductCategory.Value column

Colyear

Colmonth

= Fields! Date.Value.Year

= Fields! Date.Value.Month Data TXTSALES = Code.GetValue (Fields! ProductCategoryID.Value, Fields! Date.Value, Sum (Fields! Sales.Value), Parameters, ReportItems! Txtrange)

To achieve a predictive column (in bold display) condition format, I use the following expressions for the font attributes of the TXTsales text box:

= IIF (code.isforecasted (fields! Date.value, parameters! Enddate.value), "bold", "normal")

This expression calls the ISFORECASted function in the report embedded code. This function only compares the sales month date with the requested deadline, if the sales date is before the deadline, returns false.

Finally, the remaining last thing is to use the References tab to reference the AWRSLibrary assembly, as we see in Figure 3. Note that for this report, we don't need to set an instance name (do not need to enter anything in the Classes grid), because we do not call any instance method. Debug custom code

You may find that debug custom code is challenging. For this reason, I want to share several techniques for debug custom code.

There is not much choice for debug embedded code. So far, the only way I found is: When the report is presented in the report designer, use the MSGBOX function to output a message and variable value. Be sure to delete the call to MSGBox before deploying the report to the Report Server. If you have not deleted, all MSGBOX calls will result in exceptions. For some reason, use system.diagnostics.trace (OutputDebugstring API) tracking messages within embedded code, will be "swallowed" and will not be displayed in the Visual Studio .NET output window, and will not use an external Displayed when tracking tools.

When using an external assembly, you have at least two debug selection:

•

Output tracking message.

•

Use the Visual Studio .NET debugger to scroll through custom code.

track

For example, in the awrslibrary.getforecastedset method, I am using System.Dianogistics.trace.WriteLine output tracking message to display the observation value and predictive value. To view these messages when running a report in a Visual Studio .NET or report server, you can use the excellent debugview tool developed by Mark Russinovich, as shown in Figure 7.

Figure 7. Output tracking messages from external assemblies in DebugView.

Debug custom code

You can also pass custom assembly code by using the Visual Studio .NET debugger to the report designer process, as shown below:

•

Open the custom assembly 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 then set the debug mode to Wait to Attach to an External Process.

•

Open your business intelligence project in another instance of Visual Studio .NET.

•

Return to the custom assembly item, click the debug menu, and then click Processes .... Find the DeveVn process that carries business intelligence projects and attach it to it. In the Attach to Process dialog box, make sure the CommON Language Runtime check box is selected, and then click Attach. At this point, your Processes dialog should look similar to the dialog shown in Figure 8.

Figure 8. To debug the custom assembly, connect to the Visual Studio instance that carries the business intelligence project.

In my example, I want to call the AWRSLIBRARY program set when the Sales by Producuct Category report is called. For this reason, I connect to the AWREPORTER DEVENV process in the AWRSLibrary project.

•

In business intelligence projects, preview the report to call the custom assembly. Or, if you are already previewing the report, click the Refresh Report button on the Preview Tab toolbar. At this point, your breakpoint should be discovered by the Visual Studio .NET debugger. You will soon discover that if you need to change the code and recompile the custom assembly, you will cause the following exception when you try to redo it to the report designer folder:

Cannot Copy : It is being used by Another Person or Program.

The problem is that Visual Studio .NET IDE retains a reference to a custom assembly. You need to close Visual Studio .NET and redeploy the new assembly. To avoid this, you can use the Report Host to debug custom assembly code. To do this, perform the following steps:

•

Add a custom assembly to the Visual Studio .NET solution that includes a business intelligence project.

•

Change the starting item of the business intelligence project to call the custom code, as shown in Figure 9.

Figure 9. Using the Report Host debug option to avoid locking the assembly.

•

Press F5 to run the report in the preview window. Your breakpoint will be captured when the report calls the custom code.

When using a preview window method, Visual Studio .NET does not lock a custom assembly. This allows you to change the generated location of the assembly to the Report Designer folder so that it always contains the latest copy when you regenerate the assembly. Run the project in the preview window, which is a topic for the code accessed in the Report Designer Profile (RSPReviewPolicy.config).

Back to top

summary

In this article, we understand how to integrate the report with the custom code written by us or others.

For simple report dedicated programming logic, embedded Visual Basic .NET code can be used. When the complexity of the code is increased or when you want to use the programming language other than Visual Basic .NET, you can move your code to the external program set.

Using custom code is only one of several ways to extend Reporting Services. To learn more about the Reporting Services scalability, see the "Extending Reporting Services" section of the Reporting Services online book.