Leverage Analysis Services - Based Conditional Formatting in Reporting Services
At this point, we are ready to
leverage our new calculated measures within Reporting Services to present
the conditional formatting our client colleagues have requested. Had we
established the pre-designed formatting options within the Color Expressions
and Font Expressions sections of the associated Internet Gross Profit Margin, as we did in Positing
the Intelligence: Conditional Formatting in the Analysis Services Layer,
and assuming that we were using a reporting solution that exposed these cell
properties, we might simply be able to reference the properties within the
associated formatting properties within the .rdl file that comprises the
respective report.
The difference we experience in using independent calculated
measures (due to the absence of support for cell properties within Reporting
Services) is simply that we reference both the Internet
Gross Profit Margin (as the report
value) and the calculated measures (via the associated value properties) independently
within our report. The only complication, and it is a minor one, is that each calculated
measure must return the respective format setting in the proper style, as
we shall see.
Use Analysis Services Calculated
Measures to Support Formatting in Reporting Services
A convenient report within which
to leverage the new cube-based conditional formatting is the sample Sales
Reason Comparisons report, which we have used in past practice sessions.
(As usual, I recommend that you make a copy of the Sales Reason Comparisons.rdl file with which to practice, so as to preserve the
original report for work with the online tutorials, and so forth).
1. Once
inside the report (whether you have added it to the already open Adventure
Works project within Business Intelligence Development Studio, or
have opened it independently), the first thing to do will be to modify the
existing ProductData dataset (on the Data tab) to include the
following calculated measures from the Adventure Works DW Analysis Services database,
with which we worked in the previous section:
-
Internet Gross Profit Margin
-
Internet GPM Fore_Color
-
Internet GPM Back_Color
-
Internet GPM Font_Flags
NOTE: I created a named set within the cube, into
which I placed the above-calculated measures. I then simply added the named
set to the column axis of the query. While it is quite all right to
add the calculated measures individually, of course – I often use named sets
in this manner for various reasons when creating Analysis Services-based
datasets within Reporting Services, as I discuss in many articles within
this series.
2.
Click the Refresh button in the Data tab toolbar,
above the Query pane.
3.
Click the Execute the Query button in the Data tab
toolbar to repopulate the Data grid.
4.
Click the Refresh Fields button to refresh the
fields within the ProductData report dataset within the Datasets
pane, which will appear, at this stage, similar to that depicted in Illustration
3.
Illustration
3: The Fields Appearing within the ProductData Report Dataset
A quick review of the Data grid allows us to see that the
information generated by the calculated members’ conditional formatting logic
is returned, as expected, for use within our report. A sample of the respective
data appears within the dataset partially shown in Illustration 4.
Illustration
4: A Sample of the Output of the Calculated Members ...
Having entrained conditional formatting “instructions” for each
value of the Internet Gross Profit Margin
into the report dataset, we can now associate these instructions with the
respective properties in the Layout tab.
Here we will add the new value upon which we wish to report, Internet Gross Profit Margin, to the existing matrix data region. Once
this new field is in place, we will apply our conditional formatting
“instructions” to its property settings.
6.
Click the Internet Gross Profit
Margin field, within the ProductData report dataset in the Datasets
pane, and then drag the field to the right edge of the (currently) rightmost
column within the matrix data region (until the pointer highlights the right
edge of the text box containing Internet Total), as depicted in Illustration
5.
Illustration
5: Adding Internet Gross Profit Margin to the Matrix ...
7. Drop
Internet Gross Profit Margin into the matrix.
A new column, whose heading label
reads “Internet Gross Profit Margin,” appears.
8. Click
the newly created textbox containing the label Internet
Gross Profit Margin to select it.
9.
Click the Center button in
the formatting group of the toolbar atop the design environment, as shown in Illustration
6.
Illustration
6: Centering the New Column Label ...
10. Right-click
the newly added Internet Gross Profit Margin value textbox (underneath
the label we have centered), and then select Properties from the context
menu that appears.
11. Click
the Format tab in the Textbox Properties dialog that opens.
12. Type
“P1” within the Format code box (in the upper left-hand corner of the Format
tab), to format the Internet Gross Profit Margin value as a percentage.
13. Click
OK to accept the new property setting and close the Textbox
Properties dialog.
Internet Gross Profit Margin has now become a member of the report.
14. Begin execution of the
report by clicking the Preview tab at this point. Select any Product
Category within the parameter dropdown selector, and then click the View
Report button, to ascertain that the newly added
Internet Gross Profit Margin value appears as expected.
Next, we will apply the
conditional format property settings we have retrieved from Analysis
Services to the associated property settings surrounding our new data
field.
15. Return
to the Layout tab, and select the Internet
Gross Profit Margin value textbox,
once again, if necessary.
16. Within
the Properties pane, (its default location is below the Solution
Explorer pane on the right side of the report design environment), click
the downward-pointing selector arrow within the box labeled Color, and
select the top entry, <Expression...>.
17.
Type the
following into the input box (replacing any pre-existing text) atop the
expression editor that appears next:
=FIRST(Fields!Internet_GPM_Fore_Color.Value)
The relevant portion of the expression editor appears with our
input as depicted in Illustration 7.
Illustration
7: Referencing the Fore Color Calculated Measure in the Respective Textbox
Property (Partial View)
Here we are referencing
the appropriate field from the underlying dataset, which supplies the color
value as conditionally generated by the Internet GPM Fore_Color calculated measure we added within
the Analysis Services layer.
18.
Click OK
to accept our input and to close the expression editor.
19. Again, within the Properties pane for the Internet
Gross Profit Margin value textbox, click the downward-pointing selector
arrow within the box labeled Background Color. Select the top
entry, <Expression...>, as before.
20.
Replace any
pre-existing text within the input box atop the expression editor with
the following string:
=FIRST(Fields!Internet_GPM_Back_Color.Value)
Here
we are referencing the field supplying the color value as conditionally
generated by the cube – based Internet GPM Back_Color calculated measure.
21.
Click OK
to accept our input and to close the expression editor.
22. Finally, and once again within the Properties
pane for the Internet Gross Profit Margin value textbox, expand the Font
properties group, just under the Color property with which we worked
earlier.
23. Click the downward-pointing selector arrow within the Font
properties box labeled FontWeight. Select the top entry, <Expression...>,
once again, to launch the expression editor
24.
Type the
following (replacing any pre-existing text, as before) into the input box
atop the expression editor:
=FIRST(Fields!Internet_GPM_Font_Flags.Value)
We have now referenced the calculated measures which we
have entrained into the report dataset for purposes of driving the required
format properties settings.
Verification: Preview
Conditional Formatting in Reporting Services
Now
that we have associated the conditional formatting logic with the respective
properties of the value to which they are to be applied, we are ready to preview
the report to ascertain expected results.
1.
Click the Preview tab, once
again.
2.
Select Bikes within the Product
Category parameter selector, and click the View Report button to
execute the report.
The report executes, generating
the expected formatting for the values contained within the new Internet
Gross Profit Margin column. A portion of the Preview results
appears in Illustration 8.
Illustration
8: Analysis Services – based Conditional Formatting at Work ... (Partial View)
As we can see, all three properties of the newly added Internet
Gross Profit Margin value appear to be functioning as specified and
expected. The conditional formatting which we have put into place within the Analysis
Services calculated measures, and which we have entrained into our
supporting report dataset, and then referenced within the respect properties of
the value under consideration, appear to be supporting both the color
(text and background) and the font weight properties that our client
colleagues have requested.
Because of the flexible, expression-based nature of Reporting
Services, we can support other format-related report properties after a
manner similar to that of our basic example. In so doing, we can leverage
cube-based conditional formatting, and enjoy the multiple benefits we have
enumerated, with ease. Through options such as those we have explored, it
becomes obvious that, although Reporting Services does not expose cell
properties in Analysis Services directly, there are other ways to
make cube-based formatting just as accessible to report authors. (I introduce
variations on this theme within various other articles of my MSSQL Server Reporting Services series at Database
Journal).
Conclusion
In this article, we
resumed a discussion that is no doubt familiar to anyone who regularly reads my
articles about the integrated Microsoft business intelligence solution. We
once again examined considerations surrounding the “placement of the
intelligence” within the layers of the solution, and then focused upon another
specific example of such a consideration here: the placement of the logic to
support conditional formatting in the Reporting layer. We emphasized
the fact that selection among the various layers for positing business
intelligence support can become extremely important in enterprise-level design
and implementation, and that “multi-level awareness” – and expertise - within
the team involved is required to optimize a multi-level solution.
We noted that, because most
enterprise reporting tools (such as MSSQL Server Reporting Services)
make conditional formatting easy to put into place, and because it is within
the deployed reports that the resulting data presentations are manifest, it is
often assumed that conditional formatting is naturally handled within the Reporting
layer. Because conditional formatting expressions can become complicated
and resource intensive, processing time and other attributes of report
processing can be adversely affected, and report maintenance can become overly
cumbersome.
As a possible means of optimizing
the overall business intelligence solution, we proposed that the Analysis
Services layer might serve as an optimal home for conditional formatting
support, even though cell properties which can be specified with
conditional formatting in mind are not directly supported by Reporting
Services. We proposed, as an alternative approach, that calculated members
within the cube can contain logic to support the conditional formatting of
report item properties. We examined three examples of creating calculated
measures within Analysis Services with conditional logic to support the
conditional formatting needs of a hypothetical client. Finally, we verified
the effectiveness of our approach via a modified sample report within Reporting
Services, where we enacted Analysis Services – based conditional
formatting within the Reporting layer.
»
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Reporting Services Forum.
» 