June 10, 2015 at 9:48 am
I am using a bar chart to show SUM(Tickets) by subcategory per year.
Category Group Is Month
Series Group Is SubCategory
The Chart follows a pattern of Sub Category and if a SubCategoryis missing it leaves a blank for that.
So, If the SubCategory is in The form : Acc , HR , Prod, ENvironment
Then The chart will leave a blank in between the bars if it doesn't have tickets for HR. Which is kind of ODD
Is there a better way to do this?
June 10, 2015 at 1:46 pm
I would probably force the existence of the missing values by using an outer join in the query this is based on. Then coalesce the missing values to zero. An inner join will remove any records where there is no data (so the categories without child records will "fall out"). To preserve them, change the join from INNER to LEFT, and you'll get nulls for the missing values, which you can then coalesce to zero.
Maybe something like this:
SELECT Month
,c.SubCategory
,SUM(Tickets)
FROM Category c LEFT JOIN SourceTable
GROUP BY Month, SubCategory
The LEFT JOIN will force all values of Subcategory to appear in the results, where an INNER ("lossy") join will eliminate any records on both sides that are missing values in the other table.
hth,
Pieter
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply