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 Aug 15, 2005

MSSQL Server Reporting Services: Mastering OLAP Reporting: Extending Conditional Formatting: SWITCH and Drilldown Defaults - Page 5

By William Pearson

Modify the Foodmart Sales Report to Prepare for Our Procedural Steps

The sample objects that come along with an installation of Reporting Services are often the best to use in a practice example such as the one that follows, primarily because virtually everyone who has installed Reporting Services has access to these samples. The use of copies of pre-fabricated reports, among other sample objects, in this and other of my articles has saved significant amounts of time in preparing for practice sessions, allowing us to focus upon the specific techniques which the article is contrived to address. One of the disadvantages of working with readily available sample objects is that they are just that – samples - and, as most of have become aware, the samples provided with even enterprise-level applications can be quite simplistic with regard to their reflection of business reality.

We have before us an example of this that we will need to manage with a minor adjustment: one of the specifications of the information consumers includes the conditional formatting of the existing Store Profit values. The consumers have stated that they wish to have negative values formatted in a manner that attracts attention. To perform the steps necessary to demonstrate an approach to doing this requires one thing that is not found in the sample data – a negative Store Profit number! It would seem that FoodMart never meets with anything but a profit, even at a granular level, with any product it sells.

We will adjust the Store Cost value, within the Store Profit calculated field, with a simple multiplier to generate a few negative numbers, to make the conditional formatting portion of our practice session possible. To do this, as well to prepare the report further for our exercises, we will take the following steps:

1.  Within the RS020 project tree in the Solution Explorer, double-click the new SWITCH_CondFormat report, to open it.

The report opens within the Report Designer, and the Layout View appears, as depicted in Illustration 12.

Illustration 12: The Report Clone – Layout View

2.  Right-click the Store_Profit field in the Report Designer Field List. (If the Field List does not appear, resurrect it by selecting View --> Fields from the main menu).

3.  Select Edit... from the context menu that appears, as shown in Illustration 13.

Illustration 13: Select Edit ... from the Context Menu

The Edit Field dialog opens.

4.  Click the Expression Editor (Fx) button that appears at the immediate right of the Calculated field box, in the lower portion of the dialog, as depicted in Illustration 14.

Illustration 14: Click the Function Button ...

The Expression Editor opens.

5.  Replace the expression within the Expression box with the following:

=Fields!Store_Sales.Value - 2.5*(Fields!Store_Cost.Value)

The Expression box appears, with the new expression, as partially shown in Illustration 15.

Illustration 15: The Replacement Expression in the Expression Box (Partial View)

6.  Click OK to accept our modification, and to close the Expression Editor.

We have now adjusted the Store Profit calculated field to generate some negative numbers in the report. We will, of course, be forced to "suspend disbelief" in the accuracy of the Store Profit values (no longer simply Store Sales minus Store Cost), but this will be a simple sacrifice to allow anyone with the standard samples to perform the exercises that follow. (We can be confident that calculated fields can generate accurate values in simple subtraction scenarios like this in the real world – our focus here is conditional formatting, and this is simply a way to make the process possible).

7.  Click OK to close the Edit field dialog.

We are returned to the Layout view for the report. To conclude our preparation steps, we will eliminate the existing Product Family parameter from the report, in accordance with the expressed wishes of the information consumers.

8.  Select Report --> Report Parameters (click a point within the Layout view of the report to enable the Report menu item, if it does not already appear) from the main menu atop the Report Designer, as depicted in Illustration 16.

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

The Report Parameters dialog, where we define parameters for the report, appears, as shown in Illustration 17.

Illustration 17: The Report Parameters Dialog

9.  In the Parameters list, on the left side of the dialog, click the single entry, ProductFamily, to select it.

10.  Click the Remove button underneath the Parameters list.

The ProductFamily parameter is removed from the list.

11.  Click OK to accept removal of the parameter.

The now empty Report Parameters dialog closes, and we are returned to the Layout view of the report. We must now delete a reference to the parameter we have removed, which we can access via the Properties dialog for the matrix.

12.  Click at some point within the title textbox of the report (containing the label Foodmart Sales), to make the row and column headers of the matrix data region visible.

13.  Right-click the upper left corner of the matrix. (If the headers disappear as you touch them with the cursor, you should still see a faint outline of the matrix.)

14.  Select Properties from the context menu that appears, as depicted in Illustration 18.

Illustration 18: Accessing the Matrix Properties

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

15.  Click the Filters tab.

16.  Click the Value field of the single occupied row to select it.

17.  Click the Delete button to delete the reference to the parameter, as indicated in Illustration 19.

Illustration 19: Select and Delete the Parameter Reference

The remaining reference to the now-deleted parameter is itself deleted.

18.  Click OK to accept our changes and to close the Matrix Properties dialog.

We are now ready to proceed with modifications to our report to meet the conditional formatting and other presentation requirements of the information consumers.

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