Add Charts to a SQL Report

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
LVL 2
brian_appliedcpuAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ValentinoVConnect With a Mentor BI ConsultantCommented:
"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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
brian_appliedcpuAuthor Commented:
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
 
brian_appliedcpuAuthor Commented:
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
 
ValentinoVBI ConsultantCommented:
That's nice to hear, thanks!  Good luck with the project!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.