Search code examples
sql-serverreporting-servicesreportbuilder

SSRS Report showing incorrect Legend and data

I have this script in SQL here that will calculate the date.

CASE
    WHEN Overdue = 1 THEN
    (CASE
        WHEN DATEDIFF(DAY, A.TARGET_DATE, GETDATE()) <= 30 THEN '0-1 Month'
        WHEN DATEDIFF(DAY, A.TARGET_DATE, GETDATE()) <= 182 THEN '1-6 Months'
        WHEN DATEDIFF(DAY, A.TARGET_DATE, GETDATE()) <= 365 THEN '6-12 Months'
        ELSE '> 12 Months'
    END)
END AS Category,

but when I try to show it on a chart it will show like this:

chart

The result I desire is to show the legend based on my category script. Even though there are datas in the resultset that meet the conditions I set, the output of the chart shows a different data. I don't even know where the 98% come from and it doesn't show the other legend that I set on the category script

Solution

  • The reason for the total of 101% was because the data on my table are mostly Null hence the 98%.

    I fixed this by removing the rows that has a null value in order not to have an inconsistent value on the chart.