Mastering OLAP Reports: Extend Reporting Services with Custom Code - Page 5

Procedure: Adding Custom Code in Reporting Services 2005

While there are many things we might do further, in the way of general aesthetics, we now have a “launch point” from which to implement the conditional labeling requested by our client colleagues. As it can see in Illustration 19 above, the report presents simple Internet Sales Amounts, by responsible Sales Territory Country, broken out by Sales Reasons classification.

As we noted earlier, the client team with which we are working has stated that they would like to assign codes to the Sales Territory Countries, for purposes of exploring the use of such codes in prospective reports. While we have confirmed that our colleagues are aware of the benefits of generating these codes in a lower layer of the integrated business intelligence solution, it is understood that our intent within this procedure is to demonstrate the use of custom coding within Reporting Services, using the immediate need as an illustration of the steps involved. In this specific case, the client has assured us that, once they ascertain that the appearance of the representative Country codes meet with approval, we will act together to embed the mechanics for the generation of these codes in a lower level.

We will add the custom code to perform this conditional logic in the steps that follow.

Add Custom Code to the Core Report

We will return to the Layout view of the design environment to begin.

We enter the Layout tab, as we did earlier. At this point, we will work with Report Properties to accomplish our ends.

The Report Properties dialog opens, defaulted to the General tab. Here we set properties for the overall report. Among the options available to us using Report Properties is the opportunity to add custom code to the report itself, or to add references to methods that exist in external, custom assemblies. This procedure will focus upon embedding code within a report, which we will accomplish simply by adding a code block directly. As we shall see, although there is a single place to do this, we can add multiple methods to the code block.

The Custom code input box appears on the Code tab. It is here that we type the methods to be used within the report. As we stated earlier, Reporting Services dictates that the code we add here must be rendered in Microsoft Visual Basic.

PUBLIC FUNCTION NonUSCode (VALUE AS String) AS String
   SELECT CASE VALUE   
   CASE "France"
      RETURN "EU-FR"
   CASE "Germany"
      RETURN "EU-GM"
   CASE "United Kingdom"
      RETURN "EU-UK"
   CASE "Canada"
      RETURN "NA-CA"
   CASE "Australia"
      RETURN "PC-AU"
   CASE ELSE
      RETURN VALUE
   END SELECT
END FUNCTION
PUBLIC FUNCTION USCode (VALUE AS String) AS String
   SELECT CASE VALUE
         CASE "United States"
            RETURN "USA"
         CASE ELSE
            RETURN "TBA"
      END SELECT
END FUNCTION

Our input appears, within the Custom code box, similar to that which is depicted in Illustration 22.

While there are multiple ways of accomplishing our conditional labeling, I have, in the above, illustrated a scenario where we use more than one method. To do so, we have input code to accomplish a couple of sets of conditional actions: In the first, we simply provide conditional logic to assign Country codes that consist of a Sales Territory Group prefix, to which we have appended a hyphen and a Country identifier, as specified by the client representatives. Within this method, we do not make allowance for Country “United States,” in effect leaving it to be treated as an exception, for which its initial value is returned. In the second method, we apply the different Country code format of three letters to Country United States, once again in compliance with the request of our client colleagues. We have assigned names to both functions, so as to allow us to reference them within Layout view, where we will apply the action of the embedded code to our Country labels, as we shall see.

We return to Layout view.

The Expression editor opens.

=""& vbcrlf & 
   IIF(Fields!Sales_Territory_Country.Value = "United States", 
      Code.USCode(Fields!Sales_Territory_Country.Value), 
         Code.NonUSCode(Fields!Sales_Territory_Country.Value))
             & vbcrlf &"  "& ""

Our input appears within the Expression editor, the relevant portion of which is depicted in Illustration 24.

Between a couple of carriage returns (the “vbcrlf” constructs), to simply position the label, we have applied conditional logic via the IIF() function, the upshot of which is to apply our custom code USCode for the Country value “United States.” The “else” half of the IIF() function directs the application of the NonUSCode method within the code we have embedded at the Report Properties level. In both outcomes, the custom function is preceded by “Code”.

We will execute the report, at this point, to ascertain that our modifications are adequate, and that we have met the basic requirement of the client representatives.

The Report is being generated message briefly appears, once again, and then the report displays. The enhanced report appears as shown in Illustration 25.

We see that the individual Country labels are presented in accordance with the business requirement. We confirm this with our client colleagues, who state that our demonstration has been helpful, both in meeting the immediate need and in illustrating the rudimentary steps involved in embedding custom code in our reports.

Conclusion ...

In this article we explored an approach to adding custom code to our reports, to demonstrate that we can embed Visual Basic .NET functions we define to control many report items. We touched upon the expanded capabilities that this option offers us, together with the benefits of reusability that also accrue. After generally discussing how and why we might use embedded custom code, we set out to gain some hands-on exposure to adding custom code within a sample OLAP report that we modified for our practice session.

After making structural modifications to the clone report, including direct modifications to the MDX query underlying the primary dataset, we created, within the Code tab of the Report Properties dialog, two custom function definitions to meet the business requirements of hypothetical information consumers. We next referenced the new custom functions from within properties of report items on the Layout tab. We then previewed the report to observe the conditional logic of the custom functions in action. Throughout the process of adding and testing our custom code, we discussed the interaction of the various components in supporting the runtime application of conditional logic as well as the results we obtained with our development techniques.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Reporting Services Forum.