Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Nov 17, 2004

MSSQL Server Reporting Services : Black Belt Components: Manage Nulls in OLAP Reports - Page 9

By William Pearson

Filter the FoodMart Sales Report DataSet to Meet Business Requirements

When we filter a DataSet, or other components within Reporting Services, we find that the presence of nulls can complicate the already unusual workings of filters. Null filters present no real obstacles when we are attempting to restrict nulls for fields with a string data type, but when we find ourselves in a position of needing to filter data whose data type is numeric, integer and other non-string values, we have to take extra steps to create effective filters. The issue seems to partially lie within a conceptual conflict between zero and "empty:" when we implant logic that is making a comparison between a given non-string value and "empty," versus zero.

We might handle the problem, of course, by managing the nulls at the cube level - and I often do so for various reasons. However, we will assume, for purposes of this lesson, that we have a need to manage nulls within Reporting Services. Moreover, that need, in the present case, is driven by a larger need to filter by a certain value for the reporting purposes of the intended audience.

We will set up a filter that might represent the "intuitive" attempt to filter the nulls under consideration in our hypothetical business scenario in this section. We will see that the approach does not work, and we will examine a couple of approaches to accomplish our objectives with the filters we need. The second alternative is the best solution, as it provides an effective filter and manages nulls effectively at the same time. Covering both avenues will perhaps make an impression upon us, so that, should we come across the same scenario within our own business environments, we will recall the dilemma and the solution that we activated within our practice exercise.

First, let's set about the initial approach to the construction of the filter.

1.  Click the Data tab to get to the DataSet we altered earlier.

2.  Ensuring that the ProductData DataSet appears in the DataSet selector, click the ellipses ("...") to its right.

The DataSet Properties dialog opens, defaulting to the Query tab, as shown in Illustration 31.


Illustration 31: DataSet Properties Dialog - ProductData DataSet

3.  Click the Filters tab on the DataSet Properties dialog.

4.  Select (using the selector button to its right) or type into the respective column of the first line of the Filters section (leave the fourth column of the Filters definition area at default) the details depicted in Table 2.

Expression

Operator

Value

=Fields!Store_Sales.Value

>

=0

Table 2: Input for the Respective Columns of the Filter Definition

The DataSet Properties dialog, Filters tab, appears as shown in Illustration 32.


Illustration 32: DataSet Properties Dialog - Filters Tab

5.  Click OK to save the Filter setting and close the DataSet Properties dialog.

6.  Click Preview to begin report execution.

The report begins to process, but is interrupted when the Processing Errors dialog, depicted in Illustration 33, appears.


Illustration 33: Processing Errors Dialog - Comparison Failure

The Processing Errors dialog indicates that a "comparison failure" has occurred, and that the filter expression is the culprit. The same failure occurs if we attempt the filter with quotes (of either variety) surrounding the 0, with removal of the "=" sign, the substitution of the ">" sign for the "=" sign in the value cell, or if we attempt to substitute <Blank>, which can be selected for the Value setting on the row. The reason for the failure is that null is not the same as zero, and requires special syntax in a Reporting Services filter when it is a consideration. Couple this with the fact that Reporting Services handles string and non-string comparisons differently, and the confusion only increases.

There are several ways to manage non-string comparisons of the sort we have encountered, and we will look at a couple of approaches to the management of filtering nulls for a non-string value. Let's return to the DataSet filter and get a feel for these solutions.

7.  Click OK to close the Processing Errors dialog.

8.  Click the Data tab.

9.  Ensuring that the ProductData DataSet appears in the DataSet selector, click the ellipses ("...") to its right.

The DataSet Properties dialog opens once again, defaulting to the Query tab, as before.

10.  Click the Filters tab on the DataSet Properties dialog.

11.  Replace the contents of each of the respective columns of the first line of the Filters section with the details depicted in Table 3 (again, nothing need be done with the fourth column).

Expression

Operator

Value

=Cstr(Fields!Store_Sales.Value)

>

="0"

Table 3: Input for the Respective Columns of the Filter Definition

The DataSet Properties dialog, Filters tab, appears as shown in Illustration 34.


Illustration 34: DataSet Properties Dialog - Filters Tab: New Input



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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


















Thanks for your registration, follow us on our social networks to keep up-to-date