2013-09-14

SSRS - Column Chart - Provide enough space for parent category group

There is a Column chart, in the Values section: Sales, and there are two Category Groups: Project and Quarter. Here is the situation, the chart doesn't provide enough space for some Quarter groups which they don't have enough projects displayed in the chart, see the following initial graph:



As you can see from the graph above, there are two quarters: 2014.2 and 2014.3, which they aren't displayed complete label. I have tried many ways to fix the issue, only one substitute method could work out it, which is to adjust the width of the chart to extreme large, but that is not practical if users want to print the report.

I start to think that if I can't change it in Report Designer, then how about change the result of dataset as long as provides enough items in the Project group. Please see the following logic and explanation of the query:

1. Original query:

SELECT  Region,
                 Project,
                 [Quarter]    = MIN(0.1 * DATEPART(QUARTER, Trans_Date) + DATEPART(YEAR, Trans_Date)),
                 [Sales]        = SUM(Cost)
FROM      dbo.Sales
GROUP BY Region, Project
 2. Customized query:
--Try to provide enough space for parent category group(Quarter) in Column chart.
--Concepts: Insert extra data row for child category group(Project).
DECLARE @history_table TABLE
        (
            region        VARCHAR(50),
            project        VARCHAR(300),
            [quarter]    FLOAT,
            sales        FLOAT
        )

INSERT INTO @history_table
SELECT  Region,
                 Project,
                 [Quarter]    = MIN(0.1 * DATEPART(QUARTER, Trans_Date) + DATEPART(YEAR, Trans_Date)),
                 Sales        = SUM(Cost)
FROM      dbo.Sales
GROUP BY Region, Project

--SELECT * FROM @history_table

--Gather number of projects included for each quarter
DECLARE @count_table TABLE
        (   
            counts        INT,
            [Quarter]    FLOAT
        )
INSERT INTO @count_table
SELECT    COUNT([quarter]),
                   [quarter]
FROM       @history_table
GROUP BY [Quarter]

--SELECT * FROM @count_table

DECLARE @counts             INT,
                   @quarter            VARCHAR(10),
                   @empty_space  VARCHAR(50)

SET @empty_space = ' '

--The length of Quarter's label is 6, in this case, 5 project rows for each should be enough for label's space.
--Identify which quarters' projects are under 5, if so, provide virtual projects to the quarter.
--Note: It doesn't work for the virutal prjoects with same empty space string or NULL, it has to be different space string to represent they are different projects.
DECLARE cur_group_counts CURSOR FOR SELECT * FROM @count_table WHERE counts < 5
OPEN cur_group_counts
FETCH NEXT FROM cur_group_counts INTO @counts, @quarter
WHILE @@FETCH_STATUS = 0
BEGIN
    WHILE @counts < 5
    BEGIN
        INSERT INTO @history_table (project, [quarter])
        VALUES(@empty_space, @quarter)
       
        SET @empty_space = @empty_space + ' '
        SET @counts = @counts + 1
    END
    FETCH NEXT FROM cur_group_counts INTO @counts, @quarter
END
CLOSE cur_group_counts
DEALLOCATE cur_group_counts

SELECT * FROM @history_table
3. Adjusted graph:

No comments:

Post a Comment