Solved

Add Charts to a SQL Report

Posted on 2013-12-02
5
2,416 Views
Last Modified: 2013-12-04
Does anyone know whether this can be done dynamically, on-the-fly, during runtime (with or without programming code)?

I am designing a report whose data source is a SQL database that collects data from other computers within a network environment. The number of these computers is not necessarily known. It could be 1 computer, it could be 100 computers; there is no way to know at the time the SQL report is deployed (to the reporting server) exactly how many computers will be sending data to the SQL database. Regardless, I want the report to be able to display a different chart for each possible computer.

Is the only way that I can do this is to go ahead and add 100 charts to the design of the report and then hide the charts that are not needed? Or is there a way to dynamically add the exact number of charts to the report at runtime? If so, how?

Thank you
0
Comment
Question by:brian_appliedcpu
  • 2
  • 2
5 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39690692
>Regardless, I want the report to be able to display a different chart for each possible computer.
For starters, all of the data entry activities done by 'these computers' will have to have a UserID of some sort inserted as a column in the tables where you want to generate reports 'by user'.

Also, expand on 'different chart'.  Same chart but populated with a different user's data?  Different charts (bar, pie) for different users?
0
 
LVL 2

Author Comment

by:brian_appliedcpu
ID: 39690845
Yes, every time one of these computers sends data to the SQL Server database, the computer includes a unique identifier along with its data. That's not a problem.

I know how to use a single chart, along with parameters, to determine which data is displayed during report execution. I could, for example, have a parameter that allows only data from Computer#1 to be viewed and then switch the parameter to Computer#2, and re-execute the report. I also know how to use a multi-select parameter to filter the chart, if desired. But this is *not* what I am looking for.

What I want is to have a separate chart for each computer that writes data to the database. The type of chart (column, line, pie, whatever) does not concern me.

Because I am not sure how many computers there will be, I am not sure how many charts I will need. So I want to know how to spawn charts on-the-fly (programmatically, if necessary) during report execution. Is this do-able and if so, how? Or am I doomed (required) to embed the maximum number of possible charts in the report during the development or design phase?

Thank you
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 39692644
"I know how to use a single chart, along with parameters, to determine which data is displayed during report execution. I could, for example, have a parameter that allows only data from Computer#1 to be viewed and then switch the parameter to Computer#2, and re-execute the report."

You're almost there!  What you're looking for is a tablix with a chart nested into it.  Yes, you can do that :)

So drag a table into the report with the working chart and connect it with the dataset used by the chart.  You'll need to group the table on the field that represents what computer the record belongs to.  This assumes your dataset returns data for multiple computers.  Now put your chart inside one of the cells in the table (with the necessary resizing of the row/column).  Because your table is grouped on "computer" you should get one chart per computer as returned by the dataset.

I hope you're still with me here (don't know how much experience you've got with tablixes)?  If not let me know!
0
 
LVL 2

Author Closing Comment

by:brian_appliedcpu
ID: 39695823
ValentinoV,

Thank you greatly. Embedding charts into a tablix, where the tablix is grouped by a unique ID allowed me to accomplish exactly what I wanted; it successfully generates a different chart for each computer (unique ID). With great pleasure these points are awarded to you.

With my upmost Thanks....!
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39695878
That's nice to hear, thanks!  Good luck with the project!
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Steps to solve SSRS SQL 2008 R2 User Access Control (UAC) Permission Error With the introduction of SQL Server 2008 R2 and Vista (Windows 7 as well) came new enhanced security features. One of the features included was User Access Control (UAC) t…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

777 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