SQLSearcher
asked on
SSRS Chart data and selections with no entries
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
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
ASKER
Hello Nicobo
This is the SQL I have, but its not working as there is one category that is not coming back as zero.
Can you see where my error is?
Regards
SQLSearcher
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
Can you see where my error is?
Regards
SQLSearcher
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much for your help.
http://technet.microsoft.com/en-us/library/ms187518(v=sql.105).aspx