Mastering OLAP Reports: Extend Reporting Services with Custom Code

Monday Feb 19th 2007 by William Pearson
Share:

Extend the capabilities of your reports with embedded custom code. In this article, BI Architect Bill Pearson provides hands-on practice creating and referencing embedded custom code, to meet special presentation needs within an OLAP report.

About the Series ...

This article is a member of the series MSSQL Server Reporting Services. The series is designed to introduce MSSQL Server Reporting Services (“Reporting Services”), presenting an overview of its features, with tips and techniques for real-world use. For more information on the series in general, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting. For the software components, samples and tools needed to complete the hands-on portion of this article, see BlackBelt Administration: Linked Reports in Report Manager, another article within this series.

About the Mastering OLAP Reporting Articles ...

One of the first things that become clear to “early adopters” of Reporting Services is that the “knowledgebase” for Analysis Services reporting with this tool is, to say the least, sparse. As I stated in my article, Mastering OLAP Reporting: Cascading Prompts (where I treated the subject of cascading parameters for Reporting Services 2000), the purpose of the Mastering OLAP Reporting subset of my Reporting Services series is to focus on techniques for using Reporting Services for OLAP reporting. In many cases, which I try to outline in my articles at appropriate junctures, the functionality of well-established, but expensive, solutions (such as various applications offered by Cognos, Business Objects, and the like) can be met, and exceeded in most respects, by Reporting Services – at a tiny fraction of the cost.

The vacuum of documentation in this arena, even taking into consideration the release of several books surrounding Reporting Services 2005 in recent months, continues to represent a serious “undersell” of Reporting Services, from an OLAP reporting perspective. I hope to contribute to making this arena more accessible for everyone, and to share my implementation and conversion experiences as the series evolves. In the meantime, we can rest assured that the OLAP potential in Reporting Services will contribute significantly to the inevitable commoditization of business intelligence, via the integrated Microsoft BI solution.

For more information about the Mastering OLAP Reporting articles, see the section entitled “About the Mastering OLAP Reporting Articles” in my article Ad Hoc TopCount and BottomCount Parameters.

Overview

As I have emphasized throughout the articles of the series, the most powerful characteristic of Reporting Services is the unprecedented flexibility it offers us in creating reports specifically tailored to our business environments. It affords us the capability to innovatively employ one or more datasets in supporting myriad options, to use data groups and report items in all manner of combinations, and to extend data reporting with many features, from basic to advanced, including calculations, conditional formatting, and other options.

Because our reports are expression-based, we have a great deal of control in getting the precise operation and presentation that we need. Moreover, when the business requirements call for even greater horsepower, we can design reports to process more complex logic through the introduction of custom functions, which we can leverage from within property expressions to obtain just the results that we desire.

In this article, we will explore one approach to adding custom code to our reports. We can embed Visual Basic .NET functions that we define to control a large number of report items in the manner that we will explore. Along with the expanded capabilities that this option offers us, the benefits of reusability also accrue: we can reference embedded code from multiple places in the report. (Even more extensive options are available when we access .NET assemblies: these external custom assemblies can be shared by multiple reports via references that we add to the report properties. .NET assemblies can also be built with any .NET language option, and are thus not limited to Visual Basic .NET. We explore the use of .NET assemblies within other articles of this series.)

In this article we will gain some familiarity with using embedded custom code – how and why we might turn to this option - and then get some hands-on exposure to adding custom code within a sample report that is available to anyone who installs Reporting Services 2005, along with the supporting Analysis Services 2005 Adventure Works DW sample database. As a part of our examination of embedded code in this article, we will:

  • Open the sample Report Server project, AdventureWorks Sample Reports, and ascertain connectivity of its shared Analysis Services data source;
  • Create a clone of an existing sample OLAP report, containing a Matrix data region, with which to perform our practice exercise;
  • Make structural modifications to the clone report, including direct modifications to the MDX query underlying the primary dataset, to prepare for our practice exercise session with custom code within the Reporting Services development environment;
  • Create, within the Code tab of the Report Properties dialog, two custom function definitions to meet the business requirements of hypothetical information consumers;
  • Reference the new custom functions from within properties of report items on the Layout tab;
  • Preview the report to observe the conditional logic of the custom functions in action;
  • Discuss the interaction of the various components in supporting the runtime application of conditional logic resulting in effects that the end consumer sees;
  • Discuss the results obtained with the development techniques that we exploit.

Extending Reporting Services with Embedded Custom Code

Objective and Business Scenario

As any regular reader of my MSSQL Server Reporting Services series is aware, I often evangelize about the highly flexible capabilities of Reporting Services, and about how the myriad options exposed within the application make it the leader among enterprise reporting tools. While many of the once-dominant applications robustly supported either relational (for example, Cognos Impromptu) or OLAP (such as Cognos PowerPlay / Transformer) reports, none provided for both types of data sources (much less several of both types) within a single report. Among many other limitations, these expensive solutions were often limited in the types of custom coding that could be easily added into the mix to obtain a precise result to meet specific information consumer needs. Although most allowed the addition of some external functions, the number of properties exposed within their report development environments – individual properties to which such external coding might be applied - did not approach the number of accessible properties within even the simplest Reporting Services scenarios.

The expression-based nature of the majority of report items in Reporting Services (and “accessible” is the key concept here – that is, “individually exposed for control by custom code”), means we are able to meet myriad needs within simply selected value expressions, with which we can combine large libraries of functions especially designed to meet specific needs. In cases where our needs exceed even the wide range of expressions available within the commonest reports, however, Reporting Services can be extended yet further in a couple of ways. In short, we can add custom functions to our report or we can add references to functions in external assemblies. In both cases, this extended code can be used to perform complex functions or functions that need to be performed multiple times within a given report. Moreover, access to code assemblies can mean sharing sophisticated functions across multiple reports – code that can be managed centrally, and which shares a single point of maintenance.

Our focus in this article will be upon the former option, the use of embedded code. To set the stage for our practice session, we offer the following hypothetical scenario and business requirement: Let's say that representatives of the Controller's Group of our client, the Adventure Works organization, have asked us to assist them in gathering business requirements to meet a new report presentation need. Various members of management have asked that a clone of the existing Sales Reasons report (with which many consumers are already pleased) be modified to make it more compact and focused. They have asked that the new report reflect only the single measure, Internet Sales Amount, and that the columns for the report, currently reflecting Sales Territory Groups, display the underlying Sales Territory Countries instead.

Moreover, because they realize that the “drill down” of the column headings to Countries will mean expanded report width, the managers have asked that the labeling for the associated Sales Territory Countries be shortened to a code – a simple two-letter abbreviation. In addition, management agrees that it would like to see a two-letter abbreviation for the respective Sales Territory Group prefixing each Country code, separated from it by a single hyphen (“-“). The representatives of the Controller's Group tell us that they will produce a list of the codes as they are typically used within other presentations.

Part of the reason that we wish to generate the proposed codes within the modified report is to support the confirmation of the business requirement from management. To assist in gaining their concurrence with the appearance of the column headers within the report, we propose that we add logic to the report to generate the new labels. More specifically, we suggest that we use embedded code within this OLAP report as a means of generating a prototype to confirm with management, as well as to demonstrate the process for using embedded code to apply this conditional logic within the report.

We emphasize to the client representatives that, once we have determined that the codes meet the requirement, we should place the codes themselves, or perhaps the logic that generates them, at a lower level within the integrated business intelligence solution. While embedding code is great for generating conditional outcomes such as this at the report level, especially for prototypical purposes (or in scenarios where we have no choice but to work within the report layer, such as cases where we might not be able to access the database or OLAP layers), report processing would almost certainly be more optimal were we to install the logic or the name in a lower layer. Upon making our convictions clear, in this specific case, the client assures us that, once they ascertain that the representative Country codes fit the requirement, they will seek our assistance in embedding the mechanics for the generation of those codes in a lower level.

Another consideration that arises, and for which we have a convenient opportunity to demonstrate an approach, will be will be a need to add more than one function via embedded code within the relevant report properties: one exception to the general assignment of the specified number of characters to each Sales Territory Country, our client colleagues tell us, is that management prefers to see the label for home operations (Sales Territory Country United States) as simply “USA.” This means that we can provide conditional logic to assign Country codes to all Countries, except the United States, within the first function we create. Within our first function, 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 / function, we will apply the different Country code format of three letters for that specific exception. Finally, having assigned names to both functions, we will reference them within Layout view, where we will apply the action of the embedded code to our Country labels, as we shall see.

Once we obtain a verbal description of the labeling needs contained within the immediate report specification, we seek to confirm our understanding by requesting that the client produce a quick list of the Sales Territory Countries, alongside their corresponding proposed labels. The result of this exercise is presented within Table 1.

Sales Territory Country

Desired Label

France

EU-FR

Germany

EU-GM

United Kingdom

EU-UK

Canada

NA-CA

United States

USA

Australia

PC-AU


Table 1: The Proposed Sales Territory Country Labels

Practice

Our first objective is to create a copy of the Sales Reason Comparisons sample report, within which we can implement basic Custom Code. The focus of our efforts will be the addition of code to meet a specific business need, into an OLAP report containing a Matrix data region (the mechanics behind adding the capability, not the design of the report itself). Because of time limitations, we will be working with a simple, pre-existing sample report – in reality, the business environment will typically require more sophistication. The process of adding embedded code is the same in real world scenarios, with perhaps a more complex set of underlying considerations.

We will perform our practice session from inside the MSSQL Server Business Intelligence Development Studio. For more exposure to the Business Intelligence Development Studio itself, and the myriad design, development and other evolutions we can perform within this powerful interface, see other articles in this series, as well as within my Database Journal series Introduction to MSSQL Server Analysis Services. In this article, we will be commenting only on the features relevant to our immediate practice exercise, to allow us to keep to the focus of the article more efficiently.

Preparation: Create a Clone Report within the Reporting Services Development Environment

For purposes of our practice session, we will create a copy of the Sales Reason Comparisons report, one of several samples that are available with (and installable separately from) the MSSQL Server 2005 integrated business intelligence suite. Making preparatory modifications, and then making the enhancements to the report to add the functionality that forms the subject of our lesson, can be done easily within the Business Intelligence Studio environment. Working with a copy of the report will allow us the luxury of freely exploring our options, and will leave us with a working example of the specific approach we took, to which we can refer in our individual business environments.

Open the Sample Report Server Project

For purposes of our practice session, we will open the AdventureWorks Sample Reports project, which contains the sample reports that ship with the Reporting Services component of the MSSQL Server 2005 suite. We will complete our practice session within the sample project so as to save the time required to set up a development environment from scratch within the Business Intelligence Development Studio.

To open the AdventureWorks Sample Reports project, please see the following procedure in the References section of my articles index:

Open the Sample Report Server Project

Ascertain Connectivity of the Shared Data Source

Let’s ensure we have a working data source. Many of us will be running “side-by-side” installations of MSSQL Server 2000 and MSSQL Server 2005. This means that our installation of the latter will need to be referenced as a server / instance combination, versus a server name alone. (The default for the Adventure Works DW project sample’s connection is localhost, which will not work correctly in such a side-by-side installation, as MSSQL Server 2000 will have assumed the identity of the local PC by default.)

If you do not know how to ascertain or modify connectivity of the Analysis Services data source, please perform the steps of the following procedure in the References section of my articles index:

Ascertain Connectivity of the Analysis Services Data Source

Create a Copy of the Sales Reason Comparisons Report

We will begin with a copy of the Reporting Services 2005 Sales Reason Comparisons OLAP report, which we will use for our practice exercise. Creating a “clone” of the project means we can make changes to select contents (perhaps as a part of later exploration with our independent solution), while retaining the original sample in a pristine state for other purposes, such as using it to accompany relevant sections of the Books Online, and other documentation, as a part of learning more about Reporting Services (particularly an OLAP report using an Analysis Services data source), and other components of the Microsoft integrated business intelligence solution in general.

If you do not know how to create a copy of an existing report, please perform the steps of the following procedure in the References section of my articles index:

Create a Copy of a Sample OLAP Report

We now have a clone OLAP report file within our Reporting Services 2005 Project, with which we can proceed in the next section to make modifications for our subsequent practice session.

Preparation: Modify the OLAP Report for Use within Our Practice Session

We will next make a few modifications to prepare the report for our practice session. Our objective will be to begin the session with a simple OLAP report that contains no Parameters. Let’s open the report and make the necessary settings to place it into such a state from which we can commence our practice steps.

1.  Right-click DBJ_OLAP_Report.rdl (or your own choice of a similar report) in the Solution Explorer.

2.  Select Open from the context menu that appears, as shown in Illustration 1, as necessary.


Illustration 1: Opening the New Report ...

DBJ_OLAP_Report.rdl opens in Layout view.

We will start with the Data tab.

3.  Click the Data tab.

We enter the Data tab, where we will remove a handful of components that we do not need for our practice session. We will accomplish this from the perspective of the MDX Query Builder, the main components of which are labeled in Illustration 2 below.


Illustration 2: The MDX Query Builder

To save time, and to maintain our focus upon our current topic, the use of custom code within Reporting Services, we will dispense with the graphical design option within the MDX Query Designer, substituting a simple MDX query for the existing query. Our intent in doing so is to supply a dataset that will support a modified report, whose elements lend themselves to our hypothetical custom code requirement.

4.  Click the Design Mode button, within the Data tab toolbar, to toggle from the graphical design view to the query view, as shown in Illustration 3.


Illustration 3: Toggle from Design View ...

The MDX Query Designer shifts to query view, as depicted in Illustration 4 below.


Illustration 4: The MDX Query Builder in Query View

Here we see the MDX syntax within the Query pane, as likely generated from the design view of the MDX Query Designer.

5.  Replace the existing query (cutting and pasting is fine) with the following syntax:


--Modified query for purposes of practice session, DB Journal RS038
SELECT NON EMPTY 
   { [Measures].[Internet Sales Amount]} ON AXIS(0), 
   NON EMPTY { ([Sales Reason].[Sales Reason].[Sales Reason].ALLMEMBERS * 
      DESCENDANTS({[Sales Territory].[Sales Territory].[All Sales Territories]}, 
         [Sales Territory].[Sales Territory].[Sales Territory Country], 
      SELF_AND_BEFORE))}ON AXIS(1) FROM [Adventure Works]

The query appears within the Query pane, as shown in Illustration 5 below.


Illustration 5: The New Query in the Query Pane

6.  Click the Execute Query button (!), to the left of the Design Mode button in the toolbar, to run the query and populate the Results pane, as partially depicted in Illustration 6.


Illustration 6: Execute the Query to a Populate the Results Pane (Partial View)

Having modified the query to one which is more appropriate to our practice session, we will now make further adjustments to meet our ends. First, we will remove a query parameter reference, to complete our alterations of the ProductData dataset.

7.  Click the ellipses (...) button to the right of the Dataset selector (currently displaying ProductData).

8.  Click the Parameters tab on the Dataset dialog that opens.

9.  Click the box containing ProductCategory, in the Name column of the top row of the Parameters list (the only populated row).

10.  Click the Delete (“X”) button to the right of the Parameters list, to delete the sole Parameter reference, as shown in Illustration 7.


Illustration 7: Click Delete to Discard the Parameter Reference ...

11.  Click OK, to accept our removal of the query parameter reference, and to dismiss the Dataset dialog.

We will next remove the ProductList dataset, whose mission in life is primarily to provide picklist support for the Report Parameter within our current report – a parameter which we will soon remove as a part of preparation.

12.  Select ProductList within the Dataset selector atop the Data tab.

13.  Click the Delete Selected Dataset button to the right of the Dataset selector, as depicted in Illustration 8.


Illustration 8: Click the Delete Selected Dataset Button ...

14.  Click Yes on the Microsoft Report Designer that appears, confirming our wish to delete the dataset.

The ProductList dataset is deleted. Next, we will remove the sole Report Parameter in the report, as we will have no need for it within the scenario we are preparing for our practice session.

15.  From our current position within the Data tab, select Report -> Report Parameters from the main menu of the design environment, as shown in Illustration 9.


Illustration 9: Select Report -> Report Parameters from the Main Menu

16.  Select the sole Report Parameter (“ProductCategory”) listed within the Parameters pane of the Report Parameters dialog, which appears next.

17.  Click Remove to delete the ProductCategory Report Parameter, as depicted in Illustration 10.


Illustration 10: Click Remove to Delete the Parameter ...

18.  Click OK, to accept our removal of the Report Parameter, and to dismiss the Report Parameters dialog.

This completes our modifications to the report from the Data tab. We will execute the query for the remaining dataset, once more, to ascertain that all is in working condition.

19.  Click the Execute Query button (!) in the toolbar, as we did earlier, to run the query once again.

The Results pane is populated once more, and appears as partially shown in Illustration 11.


Illustration 11: The Result Pane is Populated (Partial View)

We will now transit to the Layout tab, where we will wrap up our preparatory modifications.

20.  Click the Layout tab.

21.  Click the left value textbox within the Matrix data region, which contains =Sum(Fields!Internet_Order_Quantity.Value)”, and whose Background Color is Lavender.

22.  Press the Delete key on the keyboard, to delete the Internet Order Quantity measure (depicted in Illustration 12), along with the column containing it, from the matrix.


Illustration 12: Deleting the Internet Order Quantity Measure ...

The column disappears, leaving two remaining columns.

23.  Click the right value textbox remaining within the Matrix data region, which contains =Sum(Fields!Internet_Total_Product_Cost.Value)”, and whose Background Color is Transparent (and for which,therefore, the White background color appears).

24.  Press the Delete key on the keyboard, to delete the Internet Total Product Cost calculated value, along with the column containing it, from the Matrix, as we did for the measure before.

The modified Matrix data region, now presenting a single measure column, appears on the Layout tab as shown in Illustration 13.


Illustration 13: The Modified Data Region

Next, we will modify grouping within the Matrix data region.

25.  Click a point within the Matrix data region, to give it the focus, and to cause its gray handles to appear.

26.  Right-click the upper left-hand corner of the Matrix data region (the gray square).

The gray column and row bars disappear, as a light, patterned outline forms around the Matrix data region, and the context menu appears.

27.  Select Properties from the context menu, as depicted in Illustration 14.


Illustration 14: Select Properties from the Context Menu ...

The Matrix Properties dialog appears, defaulted to the General tab.

28.  Click the Groups tab.

29.  In the Columns section of the Groups tab (in the lower half of the tab), select the middle group, named matrix1_Sales_Territory_Group.

30.  Click the Edit button, as shown in Illustration 15.


Illustration 15: Editing the Matrix1_Sales_Territory_Group Column Group ...

The Grouping and Sorting Properties dialog appears, defaulted to its General tab.

31.  Atop the General tab, change the existing Name to the following:

matrix1_Sales_Territory_Country

32.  Click the row containing the Expression value (currently the expression is “Fields!Sales_Territory_Group.Value”), within the Group on list, to enable the selector.

33.  Select =Fields!Sales_Territory_Country.Value within the selector, as depicted in Illustration 16.


Illustration 16: Replacing the Existing Group Expression ...

34.  Click OK, to accept our modifications, and to close the Grouping and Sorting Properties dialog.

35.  Click OK on the Groups tab, to close the Matrix Properties dialog.

36.  Once again on the Layout tab, within the Matrix data region, right-click the textbox appearing above the Internet Sales column heading (it currently contains the expression “=Fields!Sales_Territory_Group.Value”).

37.  Select Properties on the context menu that appears, as shown in Illustration 17.


Illustration 17: Modifying Properties for the Column Value ...

38.  On the Textbox Properties dialog that next appears (defaulted to the General tab), change the existing Name to the following:

Sales_Territory_Country

39.  Within the Value selector, modify the existing expression to the following:

=Fields!Sales_Territory_Country.Value.

Our modifications within the Textbox Properties dialog – General tab, appear as depicted in Illustration 18.


Illustration 18: Our Modifications within the Textbox Properties Dialog – General Tab

40.  Click OK to accept our modifications, and to close the Textbox Properties dialog.

We will execute the report, at this point, to ascertain that our modifications are complete, and that we have a working report for the practice session that follows.

41.  Click the Preview tab.

The Report is being generated message briefly appears, and then the report displays. The modified report appears as shown in Illustration 19.


Illustration 19: The Modified Report

Our report is now ready for the practice session, which we will begin in the next section.

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.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved