SSRS how to stop repeating year on a bar chart

Question

I was wondering if a dataset has a year and month for a whole year dataset. How can you have it so the year part isnt showing up for every month on the chart. so say if the report is ran for 01/04/2015 to 31/03/2016, the year part appears twice but not repeating 12 times for each month.

Just another quick question, in my chart I have formated the date i am using which the values looks like the following '2015-09-02 00:00:00.000' so in the chart category I have the date formated to show as (format(date,"MM/yy")) this is used in the group by as i need the data for monthly.

However by doing this it takes ages for the chart to load, is there any better way to do this for monthly, the result set is about 90,000 rows

P.Salmon P.Salmon · Accepted Answer · 2016-06-16T10:16:53

If you have a dataset query like

SELECT        RIGHT(CAST(MONTH(soh.OrderDate) + 100 AS char(3)), 2) + '/' + RIGHT(CAST(YEAR(soh.OrderDate) AS char(4)), 2) AS MMYY, YEAR(soh.OrderDate) AS YYYY, MONTH(soh.OrderDate) AS MM, SUM(sd.OrderQty) 
                         AS Qty
FROM            Sales.SalesOrderHeader AS soh INNER JOIN
                         Sales.SalesOrderDetail AS sd ON sd.SalesOrderID = soh.SalesOrderID
WHERE        (soh.OrderDate BETWEEN @StartDate AND @EndDate)
GROUP BY YEAR(soh.OrderDate), MONTH(soh.OrderDate)

For the first question set pick yyyy ,mm as category group

For the second question pick mmyy as category group then right click on category group properies, click sorting and add yyyy and mm in the change sorting options dialog.

SalesOrderDetils in Adventurworks2012 contains 121,000 records and the charts are returned preety quickly on my low spec pc.

SSRS how to stop repeating year on a bar chart

1 Answers