October 15, 2010 at 9:55 am
Hello,
I am kind of new to SSRS 2005.
I am trying to create a report that has a chart embedded into a table group (so the chart appears for each group). The chart has multiple series' that displays data points for the current year. There is a page split at the end of the table group.
This example isn't exactly what I'm trying to do, but this is simplified to make sense. In my current report, I have several levels (5 in total) of companies, each a parent to another. Then another parameter that allows you to select which company level to display.
Example:
Multi-valued parameter: Company
Single Select Parameter: Year
In the Year, I select 2010, and in company I select A and B.
The current report would show on page one a line chart showing Company A's numbers. On page 2, a chart with Company B's numbers. But the dates are only Jan - Oct. Since there is no data in Nov - Dec, it won't show those number.
Things that I have tried...
- Making the x-axis scalar: but since I have multiple series' it only displays a single series, not them all.
- Adding dummy data to my dataset to fill in the month of november and december. But when I did this, I would get the same report as above, with a third page showing zero's across the board for all months, Jan - Dec, associated to a blank company. But Company A and B still only have Jan - Oct displayed.
- I have thought about associating dummy data to each company, but that would cause a severe performance issue on the reporting server.
Any other suggestions?
Thanks,
Matt
October 15, 2010 at 10:13 am
In your query, start with a table that is a list of month numbers/names and from that left join to your table. If you don't have any data for months, you'll still get the months listed.
Here is an example:
DECLARE @test TABLE (MyDate datetime, Qty int);
INSERT INTO @test
SELECT '20100101',5 UNION ALL
SELECT '20100201',12 UNION ALL
SELECT '20100301',8 UNION ALL
SELECT '20100401',15 UNION ALL
SELECT '20100501',16 UNION ALL
SELECT '20100601',10 UNION ALL
SELECT '20100701',7 UNION ALL
SELECT '20100801',9;
;WITH Months (MonthNbr, Name) AS
(
SELECT 1, 'January' UNION ALL
SELECT 2, 'February' UNION ALL
SELECT 3, 'March' UNION ALL
SELECT 4, 'April' UNION ALL
SELECT 5, 'May' UNION ALL
SELECT 6, 'June' UNION ALL
SELECT 7, 'July' UNION ALL
SELECT 8, 'August' UNION ALL
SELECT 9, 'September' UNION ALL
SELECT 10, 'October' UNION ALL
SELECT 11, 'November' UNION ALL
SELECT 12, 'December'
)
SELECT m.MonthNbr, m.Name, Qty = IsNull(t1.Qty,0)
FROM Months m
LEFT JOIN @test t1
ON m.MonthNbr = month(t1.MyDate);
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 15, 2010 at 11:14 am
Hi Wayne,
Thank you for the suggestion.
I tried implementing the solution that you gave, but wasn't able to get the required results. The results were the same as using the method below. I have a little bit more information that may help the situation.
I have a static table called "Dates" within my database, that is basically a calendar table with every date listed from 2005 to 2030.
I tried doing a union all with that table for the year that I was working with and only the first day of every month. The result would give me my normal dataset plus a bunch of null rows at the end. Please view the attached image for clarification.
When I have the charts in a stand alone report, this work out well and gives me the desired result. But I have the chart within a table group, that allows the user to group the data based on either the Parent Company or the Company name.
Without the dates table attached, the report brings back a total of 2 groups (Big, Small) with charts for each if the parent company was chosen as the "Group By". Or 6 groups (AA, BB, CC, DD, EE, FF) with charts if company was chosen as the "Group By". All charts only display months in which they have data (Jan - Oct).
The the dates table attached, the report brings back a total of 3 groups (Big, Small, NULL) with charts for each if the parent company was chosen as the "Group By". Or 7 groups (AA, BB, CC, DD, EE, FF, NULL) with charts if company was chosen as the "Group By". All charts only display months in which they have data (Jan - Oct), except the null charts which has no data, but displays all months Jan - Dec.
I'm not sure if this helps, but I have attached two images.
1. What my sample dataset would look like.
2. My actual report parameters within my report with the group by.
Thanks,
Matt
October 15, 2010 at 12:02 pm
Can you post the table definition and sample data? Please see the first two links in my signature for how to do this. You should have, at a minimum, CREATE TABLE statements for each table, INSERT statements to put some sample data into the tables, and based on the sample data provided, what the expected results should be.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply