Report Builder 2.0 - Hide Series1

 

Dunno about you guys but I've searched the WWW for a solution and couldn't find it.  The work around I've documented is a "solution" to my situation and environment.
 
Our setup is:
  • SQL Server 2008
  • Report Builder 2.0
  • BMC Service Desk Express 9.8
 
DataSet1:
  • Incident #
  • Group Name
  • Close Date & Time
  • Assigned to Full Name
  • Incident Type
 
The objective of this article is to explain how to hide "Series1" from appearing in your SQL Server Reporting Service (SSRS) report.  I am guessing that Series1 is the data series from an outer join where the joining index value is null.

The chart below has 3 series or data fields:

  • Count the total number of incidents logged (Y-axis - data fields)
  • Invidividual the incident(s) are assigned to (Labels - series fields)
  • Months the incident(s) were closed (X-axis - category fields)


My SQL Query is:

copyraw
SELECT
  _SMDBA_.Incident.[Incident #]
  ,_SMDBA_.Incident.[Group Name]
  ,_SMDBA_.Incident.[Close Date & Time]
  ,_SMDBA_.Incident.[Assigned To Full Name]
  ,_SMDBA_.Incident.[Incident Type]
FROM
  _SMDBA_.Incident
WHERE
  _SMDBA_.Incident.[Group Name] LIKE @GroupName
  1.  SELECT 
  2.    _SMDBA_.Incident.[Incident #] 
  3.    ,_SMDBA_.Incident.[Group Name] 
  4.    ,_SMDBA_.Incident.[Close Date & Time] 
  5.    ,_SMDBA_.Incident.[Assigned To Full Name] 
  6.    ,_SMDBA_.Incident.[Incident Type] 
  7.  FROM 
  8.    _SMDBA_.Incident 
  9.  WHERE 
  10.    _SMDBA_.Incident.[Group Name] LIKE @GroupName 

alt

 

Based on the assumption that "Series1" displays on my chart because of values that are not matched (not assigned to a current individual) in the joining tables:

  1. Return to the Design interface (F8)
  2. Right-click on your dataset (in my case: DataSet1)
  3. Select 'Dataset Properties'
  4. Add a condition to the WHERE clause
  5. Specify that the field name in the series fields is not blank


So I've added the AND line to the above statement yielding:

copyraw
SELECT
  _SMDBA_.Incident.[Incident #]
  ,_SMDBA_.Incident.[Group Name]
  ,_SMDBA_.Incident.[Close Date & Time]
  ,_SMDBA_.Incident.[Assigned To Full Name]
  ,_SMDBA_.Incident.[Incident Type]
FROM
  _SMDBA_.Incident
WHERE
  _SMDBA_.Incident.[Group Name] LIKE @GroupName
AND
  _SMDBA_.Incident.[Assigned To Full Name]  ''
  1.  SELECT 
  2.    _SMDBA_.Incident.[Incident #] 
  3.    ,_SMDBA_.Incident.[Group Name] 
  4.    ,_SMDBA_.Incident.[Close Date & Time] 
  5.    ,_SMDBA_.Incident.[Assigned To Full Name] 
  6.    ,_SMDBA_.Incident.[Incident Type] 
  7.  FROM 
  8.    _SMDBA_.Incident 
  9.  WHERE 
  10.    _SMDBA_.Incident.[Group Name] LIKE @GroupName 
  11.  AND 
  12.    _SMDBA_.Incident.[Assigned To Full Name]  '' 

Note that this excludes all the data that doesn't have a matching label.  Which can be a major part of your charts if this is a normal part of your database.  In my example, the team members could have changed and that a person doing most of the jobs suddenly left or moved to another team.

 

To exclude the rest and only show "Series1"

Took me a while to figure this out but modify the above statement along the lines of 

copyraw
SELECT
  _SMDBA_.Incident.[Incident #]
  ,_SMDBA_.Incident.[Group Name]
  ,_SMDBA_.Incident.[Close Date & Time]
  ,_SMDBA_.Incident.[Login ID Closed By]
  ,_SMDBA_.Incident.[Assigned To Full Name]
  ,_SMDBA_.Incident.[Incident Type]
FROM
  _SMDBA_.Incident
WHERE
  _SMDBA_.Incident.[Group Name] LIKE @GroupName
AND
 _SMDBA_.Incident.[Assigned To Full Name] IS NULL
  1.  SELECT 
  2.    _SMDBA_.Incident.[Incident #] 
  3.    ,_SMDBA_.Incident.[Group Name] 
  4.    ,_SMDBA_.Incident.[Close Date & Time] 
  5.    ,_SMDBA_.Incident.[Login ID Closed By] 
  6.    ,_SMDBA_.Incident.[Assigned To Full Name] 
  7.    ,_SMDBA_.Incident.[Incident Type] 
  8.  FROM 
  9.    _SMDBA_.Incident 
  10.  WHERE 
  11.    _SMDBA_.Incident.[Group Name] LIKE @GroupName 
  12.  AND 
  13.   _SMDBA_.Incident.[Assigned To Full Name] IS NULL 

Category: SQL Server Reporting Services :: Article: 262

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2022 Joel Lipman .com. All Rights Reserved.