SSRS Chart skipping 0 values

• lein

  SSC Eights!

  Points: 974

  More actions

  February 1, 2017 at 9:20 am

  #340099

  
   i have this matrix that counts Month Name. Unfortunately when i create a chart out of it its skipping the 0 values. Is there a way that i can show all months in horizontal axis with data points for months with zero values?

• Thom A

  SSC Guru

  Points: 99134

  More actions

  February 1, 2017 at 9:41 am

  #1926133

  I can;I've tried to replicate your problem, however, I get 0 values showing fine.

  As it's only a picture of your report in Preview I can't tell, but is that actually a matrix? Or is it really a tabix with 12 columns, 1 for each month? I can't see a row group defined in your table, so I am suspecting the latter is true.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• lein

  SSC Eights!

  Points: 974

  More actions

  February 1, 2017 at 10:11 am

  #1926141

  The previous image is the preview of my report. This is my design view. Its a matrix with individual chart in each row(group - description). The chart should only show the total count of month (EncounterMonth).
  

  Thank you for taking your time to respond 🙂

• Thom A

  SSC Guru

  Points: 99134

  More actions

  February 1, 2017 at 10:40 am

  #1926150

  If you were to run the query in SSMS, would a row actually be returned for say, January for that specific description?

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• lein

  SSC Eights!

  Points: 974

  More actions

  February 1, 2017 at 10:46 am

  #1926151

  Oh to answer your question. No there is no January or those 0 values on my Data. Now i understand it. There is no data to show thats why its not showing those 0 values... I think my question now is is there a way to manipulate it in the Chart view?

• Thom A

  SSC Guru

  Points: 99134

  More actions

  February 1, 2017 at 11:03 am

  #1926156

  Yep, that's the problem! 🙂 As January doesn't exist for that row, it isn't displayed in the chart.

  You'll need to use some kind of Date or reference table. This is OVERLY simplified, however:
  CREATE TABLE #Months (MonthNum INT,
        CalenderName VARCHAR(20));

  INSERT INTO #Months
VALUES (1, 'January'),
   (2, 'February'),
   (3, 'March'),
   (4, 'April'),
   (5, 'May'),
   (6, 'June'),
   (7, 'July'),
   (8, 'August'),
   (9, 'September'),
   (10, 'October'),
   (11, 'November'),
   (12, 'December');

  CREATE TABLE #MyTable (ID INT IDENTITY,
        ItemDescription VARCHAR(50),
        MonthNum INT);

  INSERT INTO #MyTable (ItemDescription, MonthNum)
VALUES ('Desk', 3),
   ('Desk',12),
   ('Lamp',11),
   ('Chair', 10),
   ('Chair', 5);
GO

  SELECT M.CalenderName AS Month,
   MY.ItemDescription AS Description
FROM #Months M
  LEFT JOIN #MyTable MY ON M.MonthNum = MY.MonthNum;

  GO

  DROP TABLE #Months;
DROP TABLE #MyTable;

  Have a look at Bones of SQL - The Calendar Table for more information on how to build a proper table.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• lein

  SSC Eights!

  Points: 974

  More actions

  February 1, 2017 at 11:18 am

  #1926159

  Awesome! Thanks for all your help!