How to display Category Axis labels in chart?
I am developing a chart in a SSRS 2008 report with this expression for the series data: =Fields!sum_enrollment.Value + RunningValue((Fields!total_enrollment.Value),Sum,Nothing) and for the Categories: =Fields!a_month.Value and "Fields!a_day.Value" where "a_day" is an integer value and "a_month" is a varchar value in the form of the first 3 letters of each month. (So maybe a_day should be Scalar Axis mode?) Also for the first category, both the label and the Group on =Fields!a_month.Value. Likewise it is both Label and Group on = Fields!a_day.Value for the second category. However when I view it, the day of the month category is not appearing; only the month displays. Also, I want each month to include all dates, even those that have no records. This means that my graph should show an equal amount of space for each month. But instead, my graph has months with more records taking more space than others. (See screenshot). Here is my stored proc: ALTER Proc [dbo].[rpt_rd_CensusWithChart] @program uniqueidentifier = NULL, @office uniqueidentifier = NULL AS DECLARE @a_date datetime SET @a_date = case when MONTH(GETDATE()) >= 7 THEN '7/1/' + CAST(YEAR(GETDATE()) AS VARCHAR(30)) ELSE '7/1/' + CAST(YEAR(GETDATE())-1 AS VARCHAR(30)) END if exists ( select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#ENROLLEES') ) DROP TABLE #ENROLLEES; if exists ( select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#DISCHARGES') ) DROP TABLE #DISCHARGES; declare @sum_enrollment int set @sum_enrollment = (select sum(1) from enrollment_view A join enrollment_info_expanded_view C on A.enrollment_id = C.enroll_el_id --join service_track_view k on k.people_id = A.people_id where (@office is NULL OR A.group_profile_id = @office) AND (@program is NULL OR A.program_info_id = @program) and (C.pe_end_date IS NULL OR C.pe_end_date > @a_date) AND C.pe_start_date IS NOT NULL and C.pe_start_date < @a_date) select A.program_info_id as [Program code], A.[program_name], A.profile_name as Facility, A.group_profile_id as Facility_code, A.people_id, 1 as enrollment_id, C.pe_start_date, C.pe_end_date, LEFT(datename(month,(C.pe_start_date)),3) as a_month, day(C.pe_start_date) as a_day, @sum_enrollment as sum_enrollment into #ENROLLEES from enrollment_view A join enrollment_info_expanded_view C on A.enrollment_id = C.enroll_el_id where (@office is NULL OR A.group_profile_id = @office) AND (@program is NULL OR A.program_info_id = @program) and (C.pe_end_date IS NULL OR C.pe_end_date > @a_date) AND C.pe_start_date IS NOT NULL and C.pe_start_date >= @a_date --select * from #ENROLLEES select A.program_info_id as [Program code], A.[program_name], A.profile_name as Facility, A.group_profile_id as Facility_code, A.people_id, -1 as enrollment_id, C.pe_start_date, C.pe_end_date, LEFT(datename(month,(C.pe_start_date)),3) as a_month, day(C.pe_start_date) as a_day, @sum_enrollment as sum_enrollment into #DISCHARGES from enrollment_view A join enrollment_info_expanded_view C on A.enrollment_id = C.enroll_el_id where (@office is NULL OR A.group_profile_id = @office) AND (@program is NULL OR A.program_info_id = @program) and (C.pe_end_date IS NOT NULL OR C.pe_end_date > @a_date) AND C.pe_start_date IS NOT NULL and C.pe_start_date >= @a_date order by C.pe_start_date, A.people_id select A.[Program code], A.[program_name], A.Facility, A.Facility_code, A.people_id, a.enrollment_id, a.pe_start_date, a.pe_end_date, a.a_month, a.a_day, a.sum_enrollment, CASE WHEN C.enrollment_id IS NULL THEN 1 ELSE a.enrollment_id + c.enrollment_id END total_enrollment from #ENROLLEES a left join #DISCHARGES c on a.pe_start_date = c.pe_start_date Ryan D
November 30th, 2011 10:35am

Hi ironryan77, In your case, please refer to the following steps to show all category labels on the x-axis: 1. Right-click on X-axis and select “Horizontal Axis Properties”. 2. Set the “Interval” property to 1. For more information, please see: http://technet.microsoft.com/en-us/library/dd239317.aspx Thanks, Bin Long TechNet Subscriber Supportin forum If you have any feedback on our support, please contact tnmff@microsoft.com Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Free Windows Admin Tool Kit Click here and download it now
December 1st, 2011 2:37am

Hi ironryan77, In your case, please refer to the following steps to show all category labels on the x-axis: 1. Right-click on X-axis and select “Horizontal Axis Properties”. 2. Set the “Interval” property to 1. For more information, please see: http://technet.microsoft.com/en-us/library/dd239317.aspx Thanks, Bin Long TechNet Subscriber Supportin forum If you have any feedback on our support, please contact tnmff@microsoft.com Please remember to mark the replies as answers if they help and unmark them if they provide no help.
December 1st, 2011 10:10am

Thanks Bin, this was helpful! I also had to add code to my SQL query, however, to include days without records.Ryan D
Free Windows Admin Tool Kit Click here and download it now
December 1st, 2011 11:46am

I am working on a report that looks similair to the one by ironryan77. How can I create a static line similar to the red line shown above? The Y axis values are fixed, as are the major & minor axis increments. I tried jsut drawing a line but it never shows up in the right place. Thanks, Steve
December 1st, 2011 2:15pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics