Solved

SSRS Chart data and selections with no entries

Posted on 2014-10-02
4
365 Views
Last Modified: 2014-10-03
Hello Experts Exchange
I am developing a SSRS Report that has categories and a sum of Total hours.  I select the data, that is OK, but I have categories that have no data against them but I want them to show up on the chart as a zero value.

I have a table of data that lists all the categories.

Is there a way I can select the categories that have no data for them and set the value to zero and still keep my categories with data too?

Regards

SQLSearcher
0
Comment
Question by:SQLSearcher
  • 2
  • 2
4 Comments
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 40358842
I suppose the data is in a SQL Server database. I think the easiest way is to use an outer join in your query to make sure all the categories are always returned in your dataset (with a zero value, if there is no data). This can be done with an outer join. See:
http://technet.microsoft.com/en-us/library/ms187518(v=sql.105).aspx
0
 

Author Comment

by:SQLSearcher
ID: 40358872
Hello Nicobo
This is the SQL I have, but its not working as there is one category that is not coming back as zero.

SELECT   a.[Category], Isnull(SUM(b.[Total Lost Hours]),0) AS [Total Lost Hours]
FROM  [dbo].[MachineLosses_Level1] a       
left outer join MachineLosses_New b on a.[Category] = b.[level 1]
WHERE     ([File Name] IN ('Htr01 (581)', 'Htr04 (584)', 'Htr3 (583)', 'Htr9 (589)', 'UKL (593)'))
GROUP BY a.[Category]
ORDER BY [Total Lost Hours] DESC

Open in new window


Can you see where my error is?

Regards

SQLSearcher
0
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 500 total points
ID: 40359169
The outer join makes sure all the categories are in the result. But the where clause filters the empty categories out of the result. You can solve this by using a subquery. Something like this:
select
    [Category], 
    ISNULL([Total Lost Hours],0) AS [Total Lost Hours]
from
    [dbo].[MachineLosses_Level1] cat left join
    (SELECT   a.[Category], Isnull(SUM(b.[Total Lost Hours]),0) AS [Total Lost Hours]
    FROM  [dbo].[MachineLosses_Level1] a       
    left outer join MachineLosses_New b on a.[Category] = b.[level 1]
    WHERE     ([File Name] IN ('Htr01 (581)', 'Htr04 (584)', 'Htr3 (583)', 'Htr9 (589)', 'UKL (593)'))
    GROUP BY a.[Category]
    ) sub on cat.[Category] = sub.[Category]
ORDER BY 
    [Total Lost Hours] DESC

Open in new window


Google for "derived table subquery sql server" for more information on the subject. There is an example on this page:
http://msdn.microsoft.com/en-us/library/ms177634.aspx
It's example I.
0
 

Author Closing Comment

by:SQLSearcher
ID: 40359292
Thank you very much for your help.
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question