Intelligent Layering: Leverage Conditional Formatting Logic from Analysis Services - Page 2

May 21, 2007

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.

5.  Click 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.

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