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

February 19, 2007

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.

1.  Click the Layout tab.

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

2.  Right-click a point on the Layout tab outside the canvas containing the Report Body, similar to the point depicted in Illustration 20.


Illustration 20: Click Outside the Report Body – Layout Tab ...

3.  Select Properties from the context menu that appears, as shown in Illustration 21.


Illustration 21: Select Properties from the Context Menu

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.

4.  Click the Code tab on the Report Properties dialog.

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.

5.  Type (or cut and paste) the following into the Custom code box:


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.


Illustration 22: Our Input within the Custom Code Box – Code Tab

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

6.  Click OK, to accept our input and to dismiss the Report Properties dialog.

We return to Layout view.

7.  Right-click the middle column label, which currently contains the expression “=Fields!Sales_Territory_Country.Value”.

8.  Select Expression ... from the context menu that appears, as shown in Illustration 23.


Illustration 23: Editing the Label Expression ...

The Expression editor opens.

9.  Within the upper input box, replace the existing expression with the following:


=""& 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.


Illustration 24: Our Replacement Expression within the Expression Editor ...

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

10.  Click OK to accept our modifications, and to close the Expression editor.

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.

11.  Click the Preview tab.

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


Illustration 25: The New Labels Appear on the Report ...

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.

12.  Select File -> Save All to save our work to this point.

13.  Select File -> Exit to leave the design environment, when ready.

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.

MSSQL Server Reporting Services
Introducing the Tablix Data Region: Basic Grouping Concepts
Introducing the Tablix Data Region in Reporting Services 2008
100% Stacked Column Chart for Analysis Services Data
XY (Scatter) Chart for Analysis Services Data
Simple Doughnut Chart for Analysis Services Data
Exploded Pie Chart for Analysis Services Data
Stacked Bar Chart for Analysis Services Data
Line Chart for Analysis Services Data
Stacked Column Chart for Analysis Services Data
A More Advanced Pie Chart for Analysis Services Data
Simple Pie Chart for Analysis Services Data
Simple Bar Chart for Analysis Services Data
Simple Column Chart for Analysis Services Data
Introducing Reporting Services Charts for Analysis Services
Mastering OLAP Reports: Parameterized Grouping
Mastering OLAP Reports: Parameterizing Number of "Top" Items with the MDX TopCount() Function, Part II
Mastering OLAP Reports: Parameterizing Number of "Top" Items with the MDX TopCount() Function, Part I
Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part II
Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part I
Support Parameterization from Analysis Services - Parameter Defaults
Parameterization from Analysis Services - Cascading Picklists
Support Parameterization from Analysis Services
Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets
Reporting Services: Customize Automatically Created Parameter Support Objects
Snapshot Reports II: SQL Server Management Studio Perspective
Snapshot Reports I: Report Manager Perspective
Report Execution Caching II: Report Manager Perspective
Report Execution Caching I: SQL Server Management Studio Perspective
Report Session Caching in Reporting Services 2005
Black Belt Administration: Reporting Services Configuration Manager
Intelligent Layering: Leverage Conditional Formatting Logic from Analysis Services
Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part II
Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part I
Mastering OLAP Reports: Extend Reporting Services with Custom Code
Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II
Black Belt Components: Support Simple Navigation with a Document Map
Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I
Black Belt Components: Interactive Sorts within a Matrix Data Region
BlackBelt Authoring: Conditional Drillthrough to Multiple Reports
Mastering OLAP Reporting: Prototype KPIs in Reporting Services
BlackBelt Administration: Linked Reports in SQL Server Management Studio
BlackBelt Administration: Linked Reports in Report Manager
Mastering OLAP Reporting: Reporting with Analysis Services KPIs
Report Builder: Creating a Report Model
Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part II
Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part 1
Mastering OLAP Reporting: Display a Dataset Field in a Report Page Header
Interactive Sorting Within Reporting Services
MSSQL Server Reporting Services: Mastering OLAP Reporting: Multiple Value Selection in a Parameter Picklist
MSSQL Server Reporting Services : Mastering OLAP Reporting: Drilling Through Using MDX
MSSQL Server Reporting Services: Mastering OLAP Reporting: Relationally-Based Picklists for OLAP Reporting
MSSQL Server Reporting Services: Mastering OLAP Reporting: Extending Conditional Formatting: SWITCH and Drilldown Defaults
MSSQL Server Reporting Services: Mastering OLAP Reporting: Percent of Total - Chart Presentation Nuances
MSSQL Server Reporting Services: Mastering OLAP Reporting: Percent of Total - Two Perspectives
MSSQL Server Reporting Services : Mastering OLAP Reporting: Ad Hoc TopCount and BottomCount Parameters
MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Sorting with Parameters
MSSQL Server Reporting Services: Black Belt Administration: "Governor" Capabilities: Report Execution Timeout
MSSQL Server Reporting Services : Black Belt Administration: Execution Log Performance and Audit Reports
MSSQL Server Reporting Services: Black Belt Administration: Prepare the Execution Log for Reporting
MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Conditional Formatting for OLAP Reports
MSSQL Server Reporting Services : Black Belt Components: Manage Nulls in OLAP Reports
MSSQL Server Reporting Services: Reporting Services Basics: Create a Reusable Template Report
MSSQL Server Reporting Services: Master Chart Reports: Track Exchange Rates in a Line Chart
MSSQL Server Reporting Services: Master Chart Reports: Pie Charts in Reporting Services
MSSQL Server Reporting Services: Mastering OLAP Reporting: Cascading Prompts
MSSQL Server Reporting Services: Managing Reporting Services: Data-driven Subscriptions, and External Data Sources for Subscriber Data
MSSQL Server Reporting Services: Managing Reporting Services: Report Execution and Standard Subscriptions
MSSQL Server Reporting Services: Managing Reporting Services: Data Connections and Uploads
MSSQL Server Reporting Services: The Authoring Phase: Overview Part II
MSSQL Server Reporting Services: The Authoring Phase: Overview Part I
MSSQL Server Reporting Services: A New Paradigm for Enterprise Reporting








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers