Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 19, 2007

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

By William Pearson

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:

   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 "United States"
            RETURN "USA"
         CASE ELSE
            RETURN "TBA"

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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM