Solved

Add Charts to a SQL Report

Posted on 2013-12-02
5
2,423 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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Written by Valentino Vranken. A while ago I wrote an article called Chart Optimization Tips (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Chart-Optimization-Tips.html).  This article explained how …
How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

828 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