Solved

How to produce a report with a graph or chart per group of data

Posted on 2010-09-16
21
559 Views
Last Modified: 2012-08-13
I'm trying to write a report to display total revenue and target per office with an individual graph per office. Not written charts in SSRS before and I'm really struggling with it. Anyone able to help please?  GraphExample.xls
0
Comment
Question by:Grffster
  • 8
  • 5
  • 5
21 Comments
 
LVL 19

Assisted Solution

by:Bhavesh Shah
Bhavesh Shah earned 50 total points
ID: 33690526
0
 

Author Comment

by:Grffster
ID: 33691318
Those were great thanks. However, with regards my query re: how to build something to report a graph per office, I need it to be dynamic. Whereas I have 4 offices in my example, we may get more. I don't want to hard-code a chart with a filter set to each office within each chart, as, each time we add another office I'd need to add extra charts on to the report and apply the filter to that new office. Can I make the report self-sufficient by reporting a chart per office in the same way you can report a table for a group on separate pages?
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33691554
Hi,


I dont have much idea about SSRS so m providing u link.
sory :-)

check out this link.

http://www.sqldev.org/sql-server-reporting-services/dynamic-chart-in-ssrs-43099.shtml
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33691575

check out this pdf too.....


RDL-spec-08.pdf
0
 

Author Comment

by:Grffster
ID: 33691827
Mmm...I don't think that's going to help me as it's checking for the value of a parameter rather than what's in the dataset. Can't understand how it works anyway...thanks for your assistance though. The introductory section you gave me was very useful. If you're unable to help any further, how do I pass this on to an ssrs expert? Do I press the Request Attention button?
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33691896
yes..

Please select that option.

n ask experts itcouple,rrjegan17,cyberkiwi,ralmada,mwvisa1

they are surely going to assist u.
0
 

Author Comment

by:Grffster
ID: 33691910
Ok, thanks Brichsoft. Will award you some points for the first few replies.
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33691961
thanks....

award only if my post helped u.....(not for quick replies)

hope u will get ur answer sooon.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 33698689
To produce an individual chart per office, here's what you can try.

Create a table that groups your data per office.  Then drag a chart into one of the cells on the grouped level and configure it as you need.

0
 

Author Comment

by:Grffster
ID: 33699431
Tried this now but it's behaving a little strangely...may have got my grouping wrong though.
I've put my TotalReceived and Target in the Data Values section of the chart, and Date in the category group section. In the table properties I've set Page Breaks to "Insert a page break after this table" on the general tab, and on the Groups tab I've added Office as a group but it doesn't seem to be working. Seems to sum up the data for all offices in to one graph i.e. the TotalReceived line is the total of TotalReceived for all the offices. With 4 offices it displays the same graph for all 4! Have I missed a certain setting here?
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 33699671
It sounds like you've misunderstood the grouping slightly.  Try to set it up like this:

Add a table and drag your Office field into the first column.  That connects the table to the dataset.
In Row Groups, open the dropdown and select Group Properties.  Add a group on Office.

Now drag in one of your numeric fields into the second column on the table and render the report.  That should give you the sum per office.

Now you're ready to start the chart.  Add it into the third column, set your Data and Category fields as you did before.  That should do the trick.

Good luck!
Valentino.
0
 

Author Comment

by:Grffster
ID: 33699826
Sorry Valentino, to clarify...

"Add a table and drag your Office field into the first column.  That connects the table to the dataset."

I've added the Office in to the first detail cell of the table.

With your next statement:

"In Row Groups, open the dropdown and select Group Properties.  Add a group on Office."

Do you mean click on the row of the table and click on "Insert Group" and then in the "Group On:" expression drop down select "Fields!Office.Value"?

When you say:

"Now drag in one of your numeric fields into the second column on the table and render the report.  That should give you the sum per office."

This sounds like I want to get the sums per office. I don't really want to show on the graph the total sum received by that office. The Total received is the cumulative total up until that date. i.e. totalreceived for 1st of feb 2010 would be totalreceived for 1st of Jan 2010 plus totalreceived for the month of January if you see what I mean. The graph would just show an increase. Similarly with the target (forgot to populate the target in my example spreadsheet by the way!).

Your last paragraph:

"Now you're ready to start the chart.  Add it into the third column, set your Data and Category fields as you did before.  That should do the trick."

Do you mean add in to the third column of the table the value from the dataset corresponding to the other data field i.e. target here, and then into the fourth column of the table the category field i.e. date?
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 33699871
"Do you mean click on the row of the table and click on "Insert Group" and then in the "Group On:" expression drop down select "Fields!Office.Value"?"

No, I'm referring to the section below the report design, see the following screenshot and look for "Row Groups" - http://www.accelebrate.com/sql_training/SSRS_2008_tutorial_files/image006.jpg


0
 

Author Comment

by:Grffster
ID: 33699894
Ah! I don't see that...you're in SSRS 2008. I'm using SSRS 2005. Don't see this view in 2005.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 33700064
Ow, I see.  Well, you can right-click on the grey square in front of the details row, then select Edit Group.  The point is that you should group the details part on Office.
0
 

Author Comment

by:Grffster
ID: 33700129
Ok. That's what I had I think. OK then, on to the rest of my points. Can you help with these please?

When you say:

"Now drag in one of your numeric fields into the second column on the table and render the report.  That should give you the sum per office."

This sounds like I want to get the sums per office. I don't really want to show on the graph the total sum received by that office. The Total received is the cumulative total up until that date. i.e. totalreceived for 1st of feb 2010 would be totalreceived for 1st of Jan 2010 plus totalreceived for the month of January if you see what I mean. The graph would just show an increase. Similarly with the target (forgot to populate the target in my example spreadsheet by the way!).

Your last paragraph:

"Now you're ready to start the chart.  Add it into the third column, set your Data and Category fields as you did before.  That should do the trick."

Do you mean add in to the third column of the table the value from the dataset corresponding to the other data field i.e. target here, and then into the fourth column of the table the category field i.e. date?
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 450 total points
ID: 33727357
Regarding the following:

"Do you mean add in to the third column of the table the value from the  dataset corresponding to the other data field i.e. target here, and then  into the fourth column of the table the category field i.e. date?"

No.  What I mean is that you need to drag a Chart object from the Toolbox into the "detail" cell of the third column.  That adds a chart to the table on the level where you need it.  This allows you to display data on that chart, with the chart repeating for each office.
How the data appears is totally up to you, and depends on how the chart is configured: type of chart, what does it show as Data, Categories, ...

Have a look at the following article, that explains how to get started with charts in 2005: http://www.simple-talk.com/sql/reporting-services/beginning-sql-server-2005-reporting-services-part-3-the-chart-control/
0
 

Author Comment

by:Grffster
ID: 33732878
Ah, that seems to have worked. Many thanks. Only niggles now like the office has to appear on the left hand side of the chart, I can't put the office name as a header on the chart, and on some charts I have too many gridlines, and when I set the interval to 4, on some I don't have enough! Sure I can fiddle around with these though. Thanks.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to increase the row limit in Jasper Server.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question