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: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience 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 bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.