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:
2. Customized 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
--Try to provide enough space for parent category group(Quarter) in Column chart.3. Adjusted graph:
--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
No comments:
Post a Comment