Solved

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

Posted on 2010-09-16
21
553 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
 
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

747 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now