I have a report I'm writing and have got a problem that has stumped me. The data the report is based in is in this format: Date Type 1/1/08 A 1/3/08 B 3/1/08 C 3/5/08 B 3/10/08 A 3/12/08 C 3/20/08 A Management wants a report showing a line chart that summarizes the data by Month and by Type. So, there are 2 series depending on the Type and data values are based on the count of each Type. So, in the example above the x-axis group would be month, the values would be count(Type) and there would be 2 series Series 1. A, B and Series 2. C Ive defined the x-axis to be Month(Fields!DateOccured.Value). However, they want the x-axis to show every month for the entire year not just what is in the database. So, I defined the x-axis to have a scale of 1 to 12 and the major interval is set to 1. This displays 1 through 12 on the x-axis. The problem is when there is no data (null) for a particular month. Instead of showing 0, the line chart does not plot anything and the line is drawn to the next displayed point. So, for example on the data above the line chart would plot: January Series 1 Qty. 2 Series 2 Qty. 0 February Null March Series 1 Qty. 3 Series 2 Qty. 2 The line chart would draw a line from January to March skipping February. I need to display the null values as 0 and not null indicating no occurrences for the month rather than no data present. Thanks!

in your query that returns the data you could put isnull(datepart(mm,DateOccured),0) as Month

Thanks for the reply. I'm not sure if that will do anything or not. Here's the queury I'm using: SELECT DateOccured, AccidentClassFROM InjuryDataWHERE (DATEPART(yy, DateOccured) = DATEPART(yy, GETDATE())) AND (DeptOccured IN (@Dept))ORDER BY DateOccured The problem occurs when certain departmens are selected, only there may be only records for certain months e.g. Jan, March, May So it's not a null result but there's no record returned.

Anyone?

You should be able to do this by setting the chart's axis to scalar. See http://technet.microsoft.com/en-us/library/aa964128.aspx#moressrscharts_xaxis. See the Section titled "X-Axis Category Mode and Scalar Mode" -Sean

&n******sp; Thanks for the reply Sean. According to that article: &n******sp; "Gaps in the data (for example, you use a DateTime category grouping and you only have data for July and Septem******er) are shown on the x-axis, as the categories are scaled either to a numeric or a DateTime axis." &n******sp; I'm trying to find away around the gaps. On a line chart, if there is a gap it will draw the line across to the next valid point instead of going to zero. I'm trying to force the chart to display the missing result as zero.

Oh, I see. To do that you are going to need to return 0 in your query or you may want to consider the Dundas controls for Reporting Services 2005. -Sean

I recently had a similar request. To solve this I had to make a control table. The control table had a record for every month for each data element I was interested in capturing. In your case, you'd have a record for every Type for every month. The value for each record is zero. After creating the control table, I changed my query by adding a UNION clause. Basically, I had my initial query (which returned only those data elements where there was activity) and the UNION returned all the data that was missing. In your case, the Control table would look like this... Date Type Qty 1/1/08 A 0 1/1/08 B 0 1/1/08 C 0 2/1/08 A 0 2/1/08 B 0 2/1/08 C 0 3/1/08 A 0 3/1/08 B 0 3/1/08 C 0 Then your query would change from this (which only returns data where data exists).... SELECT Type, Date, sum(Qty) FROM myTypeTable GROUP BY Type, Date To this.... SELECT Type, Date, sum(Qty) FROM myTypeTable GROUP BY Type, Date UNION SELECT Type, Date, sum(Qty) FROM myControlTable GROUP BY Type, Date

Thanks phikappa. That seems to have worked. Too bad MS didn't include this feature as Dundas has. btw - is phikappa Phi Kappa Tau?

I'm glad it worked for you, Hiro. No, phikappa means something else.

Hi phikappa I don't suppose you know how to achieve the same outcome but using Report Builder 2.0's query designer rather than straight SQL? RB2 is the only tool available to me and I have the same problem as HiroPro. Many thanks in advance p