Adding Custom Code in Reporting Services 2005
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
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.
add the custom code to perform this conditional logic in the steps that follow.
Code to the Core Report
We will return
to the Layout view of the design environment to begin.
enter the Layout tab, as we did earlier. At this point, we will work
with Report Properties to accomplish our ends.
point on the Layout tab outside the canvas containing the Report Body,
similar to the point depicted in Illustration 20.
Click Outside the Report Body Layout Tab ...
from the context menu that appears, as shown in 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.
Click the Code
tab on the Report
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
Type (or cut
and paste) the following into the Custom code box:
PUBLIC FUNCTION NonUSCode (VALUE AS String) AS String
SELECT CASE VALUE
CASE "United Kingdom"
PUBLIC FUNCTION USCode (VALUE AS String) AS String
SELECT CASE VALUE
CASE "United States"
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
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.
to accept our input and to dismiss the Report Properties dialog.
return to Layout view.
the middle column label, which currently contains the expression =Fields!Sales_Territory_Country.Value.
... from the context menu that appears, as shown in Illustration 23.
Illustration 23: Editing
the Label Expression ...
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.
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.
Click OK to
accept our modifications, and to close the Expression editor.
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.
Click the Preview
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 ...
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.
-> Save All to save our work to this point.
-> Exit to leave the design environment,
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.