creating a chart/graph

Good evening,

I was looking at creating a dynamic charts and graph and wanted to know if something like that would work for data being pulled from an SQL query?

SELECT category, COUNT(category) AS issue_count FROM ticket_view v GROUP BY category ORDER BY category

where i am just counting the records on the page and wanting to display them in a graph like below?

Graph Example:

Category:IS Security
Issues:2

Category:Maintenance
Issues:2

Category:Personnel
Issues:2

Category:Project
Issues:9

Category:Uptime
Issues:55

Hello,

Visit the following free chart controls

http://zedgraph.org/wiki/index.php?title=Main_Page

http://www.carlosag.net/Tools/WebChart/Default.aspx

Creating a chart from only one row of data

I am trying to generate a chart from only one row of data that contains the
twelve fields that represent the data-points for the chart ( the chart will
have 4 sets of two columns and a 4 point line graph for a total of twelve
points). I know which field is which datapoint but the chart object will not
let me define individual data-points. It expects to create a series from
multiple rows of data. Grouping and Series fields also rely on multiple rows
of data. I can put the fields into a table on the report, but cannot
generate a chart off of the table. I am not a programmer, meaning that I
only know how to manipulate properties - code samples will not help. I would
appreciate any help that could be offered to a layman report designerIt may be possible to write a query such that the columns in the table
appear as rows. For example, if A, B and C are columns of table FOO then
SELECT A FROM FOO UNION SELECT B FROM FOO UNION SELECT C FROM FOO will
return 3 rows even if the table has only 1 row.
--
Rajeev Karunakaran [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce Brien" <BruceBrien@.discussions.microsoft.com> wrote in message
news:9091FC94-37F7-4C6A-95E4-285B04AC980B@.microsoft.com...
>I am trying to generate a chart from only one row of data that contains the
> twelve fields that represent the data-points for the chart ( the chart
> will
> have 4 sets of two columns and a 4 point line graph for a total of twelve
> points). I know which field is which datapoint but the chart object will
> not
> let me define individual data-points. It expects to create a series from
> multiple rows of data. Grouping and Series fields also rely on multiple
> rows
> of data. I can put the fields into a table on the report, but cannot
> generate a chart off of the table. I am not a programmer, meaning that I
> only know how to manipulate properties - code samples will not help. I
> would
> appreciate any help that could be offered to a layman report designer|||A minor clarification: You'd have to convert all columns in the SELECTs in
the UNION to same datatype using Convert() function.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rajeev Karunakaran [MSFT]" <rajeevkarunakaran@.online.microsoft.com> wrote
in message news:eDCdqlthEHA.596@.TK2MSFTNGP11.phx.gbl...
> It may be possible to write a query such that the columns in the table
> appear as rows. For example, if A, B and C are columns of table FOO then
> SELECT A FROM FOO UNION SELECT B FROM FOO UNION SELECT C FROM FOO will
> return 3 rows even if the table has only 1 row.
> --
> Rajeev Karunakaran [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Bruce Brien" <BruceBrien@.discussions.microsoft.com> wrote in message
> news:9091FC94-37F7-4C6A-95E4-285B04AC980B@.microsoft.com...
> >I am trying to generate a chart from only one row of data that contains
the
> > twelve fields that represent the data-points for the chart ( the chart
> > will
> > have 4 sets of two columns and a 4 point line graph for a total of
twelve
> > points). I know which field is which datapoint but the chart object
will
> > not
> > let me define individual data-points. It expects to create a series
from
> > multiple rows of data. Grouping and Series fields also rely on multiple
> > rows
> > of data. I can put the fields into a table on the report, but cannot
> > generate a chart off of the table. I am not a programmer, meaning that
I
> > only know how to manipulate properties - code samples will not help. I
> > would
> > appreciate any help that could be offered to a layman report designer
>|||I am having a similar problem. I have 3 columns (cntY1, cntY2, cntY3)
I would like each one to be a separate bar on the graph. The stored
Procedure will only ever return one row.
How can you do this using Reporting Services? What should I put in the
category column?
Any help will be appreciated,
Rajeev Karunakaran [MSFT] wrote:
> It may be possible to write a query such that the columns in the
table
> appear as rows. For example, if A, B and C are columns of table FOO
then
> SELECT A FROM FOO UNION SELECT B FROM FOO UNION SELECT C FROM FOO
will
> return 3 rows even if the table has only 1 row.
> --
> Rajeev Karunakaran [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Bruce Brien" <BruceBrien@.discussions.microsoft.com> wrote in message
> news:9091FC94-37F7-4C6A-95E4-285B04AC980B@.microsoft.com...
> >I am trying to generate a chart from only one row of data that
contains the
> > twelve fields that represent the data-points for the chart ( the
chart
> > will
> > have 4 sets of two columns and a 4 point line graph for a total of
twelve
> > points). I know which field is which datapoint but the chart
object will
> > not
> > let me define individual data-points. It expects to create a
series from
> > multiple rows of data. Grouping and Series fields also rely on
multiple
> > rows
> > of data. I can put the fields into a table on the report, but
cannot
> > generate a chart off of the table. I am not a programmer, meaning
that I
> > only know how to manipulate properties - code samples will not
help. I
> > would
> > appreciate any help that could be offered to a layman report
designer|||Changing the stored procedure is not an option.|||Just create 3 values - one for each column, no categories or series group.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
<hovercraft2x@.gmail.com> wrote in message
news:1109017211.242947.155410@.c13g2000cwb.googlegroups.com...
>I am having a similar problem. I have 3 columns (cntY1, cntY2, cntY3)
> I would like each one to be a separate bar on the graph. The stored
> Procedure will only ever return one row.
> How can you do this using Reporting Services? What should I put in the
> category column?
> Any help will be appreciated,
> Rajeev Karunakaran [MSFT] wrote:
>> It may be possible to write a query such that the columns in the
> table
>> appear as rows. For example, if A, B and C are columns of table FOO
> then
>> SELECT A FROM FOO UNION SELECT B FROM FOO UNION SELECT C FROM FOO
> will
>> return 3 rows even if the table has only 1 row.
>> --
>> Rajeev Karunakaran [MSFT]
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "Bruce Brien" <BruceBrien@.discussions.microsoft.com> wrote in message
>> news:9091FC94-37F7-4C6A-95E4-285B04AC980B@.microsoft.com...
>> >I am trying to generate a chart from only one row of data that
> contains the
>> > twelve fields that represent the data-points for the chart ( the
> chart
>> > will
>> > have 4 sets of two columns and a 4 point line graph for a total of
> twelve
>> > points). I know which field is which datapoint but the chart
> object will
>> > not
>> > let me define individual data-points. It expects to create a
> series from
>> > multiple rows of data. Grouping and Series fields also rely on
> multiple
>> > rows
>> > of data. I can put the fields into a table on the report, but
> cannot
>> > generate a chart off of the table. I am not a programmer, meaning
> that I
>> > only know how to manipulate properties - code samples will not
> help. I
>> > would
>> > appreciate any help that could be offered to a layman report
> designer
>

Creating a chart based on data in a matrix

I am using a matrix to display current and historical financial data. To do this, I am using 5 different datasets. Each dataset contains 1 row of data for a specific year for a specific account.

Now that problem I am having is displaying this data in a chart. When I create a new chart, it asks which dataset I would like to use. I do not want to use any specific dataset, I want to get chart's data from the matrix. Is this possible?

Anybody have any ideas?

If that can't be done. Is it possible to create a chart based on multiple datasets?

|||

I think it should be possible. No matter what dataset it is theoretically pointed to, when you set up the value expressions, you can still use (First(...)) expressions to reference the other datasets, right? Considering that you only have one row per each, I mean.

>L<

|||

I can use the First() function and use multiple datasets, but this doesn't quite work the way I want it.

I have 5 seperate datasets, CurrentData, 1YearOldData, 2YearOldData, 3YearOldData, and 4YearOldData.

Lets say I want to display "Return on Assets" for the current year, and previous 3 years in my chart.

The forumla I need to use for the current ROA is... =First(Fields!NetIncome.Value, "CurrentData") / Avg(First(Fields!Assets, "CurrentData") + First(Fields!Assets, "1YearOldData")

The formula I need to use for the ROA 1 year ago is... =First(Fields!NetIncome.Value, "1YearOldData") / Avg(First(Fields!Assets, "1YearOldData") + First(Fields!Assets, "2YearOldData")

Etc...

I can't dynamically change the "Values" section of the report to display the data based on different datasets. If I enter each "value" seperately, they get grouped funny and the data isn't displayed how I need it. Is there anyway to use the data from a matrix, or any other workaround for this?

|||

Why don't you do a single dataset, with the data you need, as a UNION of the SELECTs that produce each of the single rows in the various current datasets, then? Isn't this really what you are after?

>L<

|||

Well I can do the select in a way that will give me 5 rows of data in the dataset. Each row representing the one year of data (instead of seperate datasets for each row). However, in my financial calculations, I need to use data from both the current year and the previous year (2 different rows in the dataset). I am unclear on how I can use a union statement to achieve this?

Row1 needs data for year 2006 and certain fields for 2005

Row2 needs data for year 2005 and certain fields for 2004

Row3 needs data for year 2004 and certain fields for 2003

and so on

This is now getting int T-SQL and not reporting services specifically. If its possible to use a matrix as the datasource for a chart, that is really what I want to do.

|||

Okey doke, sorry for suggesting something you don't want to get into. But...

*I* am unclear about why you think a matrix could be a "datasource". It's a representation of data, an evaluation of the data, an output, not a "source".

how to visualize this: you can talk to ReportItems("textboxWhatever").Value as a source for something in a chart. But that would be something you might do for a *label* -- an expression to provide *one* value, IOW, not a series of values. If you tried to do it as a value for the x- or y- axis, you wouldn't get what you were after. The matrix is more complex than a single textbox, but it is still an output, not something in the middle of a pipeline.

That's the best I can describe why it doesn't seem like the right approach for you to be taking. I would be trying to do this by handling the details of what I needed in SQL. Sorry if this answer is not what you're after and again I could be completely wrong.

>L<

|||

Lisa,

You are completely correct about this. I ended up making changes in my stored procedure to bring back all of the needed fields in 1 dataset.

The reason I was talking about a matrix being a "datasource" is because that is essentially how Excel works. You can highlight data in the spreadsheet and create a table from that. I wanted to know if it was possible to do the same thing in reporting services. Apparently it is not.

|||

FWIW... you're not the only one with this kind of expectation...

I could write a book about how the surface similarities between what people (users and developers both) see in Excel and what is actually going on in a reporting result (and not just a SQL Server Reporting Services reporting result!) provides unrealistic expectations.

It would be fun (not!) to write another book about how visual design tools mislead developers about the underlying architecture supporting the runtime behavior,so that when those developers try to actually WRITE SOME CODE THEMSELVES they tie themselves up in knots.

I have to stop now before this turns into a rant <g>.

>L<

Creating a chart based on data in a matrix

I am using a matrix to display current and historical financial data. To do this, I am using 5 different datasets. Each dataset contains 1 row of data for a specific year for a specific account.

Now that problem I am having is displaying this data in a chart. When I create a new chart, it asks which dataset I would like to use. I do not want to use any specific dataset, I want to get chart's data from the matrix. Is this possible?

Anybody have any ideas?

If that can't be done. Is it possible to create a chart based on multiple datasets?

|||

I think it should be possible. No matter what dataset it is theoretically pointed to, when you set up the value expressions, you can still use (First(...)) expressions to reference the other datasets, right? Considering that you only have one row per each, I mean.

>L<

|||

I can use the First() function and use multiple datasets, but this doesn't quite work the way I want it.

I have 5 seperate datasets, CurrentData, 1YearOldData, 2YearOldData, 3YearOldData, and 4YearOldData.

Lets say I want to display "Return on Assets" for the current year, and previous 3 years in my chart.

The forumla I need to use for the current ROA is... =First(Fields!NetIncome.Value, "CurrentData") / Avg(First(Fields!Assets, "CurrentData") + First(Fields!Assets, "1YearOldData")

The formula I need to use for the ROA 1 year ago is... =First(Fields!NetIncome.Value, "1YearOldData") / Avg(First(Fields!Assets, "1YearOldData") + First(Fields!Assets, "2YearOldData")

Etc...

I can't dynamically change the "Values" section of the report to display the data based on different datasets. If I enter each "value" seperately, they get grouped funny and the data isn't displayed how I need it. Is there anyway to use the data from a matrix, or any other workaround for this?

|||

Why don't you do a single dataset, with the data you need, as a UNION of the SELECTs that produce each of the single rows in the various current datasets, then? Isn't this really what you are after?

>L<

|||

Well I can do the select in a way that will give me 5 rows of data in the dataset. Each row representing the one year of data (instead of seperate datasets for each row). However, in my financial calculations, I need to use data from both the current year and the previous year (2 different rows in the dataset). I am unclear on how I can use a union statement to achieve this?

Row1 needs data for year 2006 and certain fields for 2005

Row2 needs data for year 2005 and certain fields for 2004

Row3 needs data for year 2004 and certain fields for 2003

and so on

This is now getting int T-SQL and not reporting services specifically. If its possible to use a matrix as the datasource for a chart, that is really what I want to do.

|||

Okey doke, sorry for suggesting something you don't want to get into. But...

*I* am unclear about why you think a matrix could be a "datasource". It's a representation of data, an evaluation of the data, an output, not a "source".

how to visualize this: you can talk to ReportItems("textboxWhatever").Value as a source for something in a chart. But that would be something you might do for a *label* -- an expression to provide *one* value, IOW, not a series of values. If you tried to do it as a value for the x- or y- axis, you wouldn't get what you were after. The matrix is more complex than a single textbox, but it is still an output, not something in the middle of a pipeline.

That's the best I can describe why it doesn't seem like the right approach for you to be taking. I would be trying to do this by handling the details of what I needed in SQL. Sorry if this answer is not what you're after and again I could be completely wrong.

>L<

|||

Lisa,

You are completely correct about this. I ended up making changes in my stored procedure to bring back all of the needed fields in 1 dataset.

The reason I was talking about a matrix being a "datasource" is because that is essentially how Excel works. You can highlight data in the spreadsheet and create a table from that. I wanted to know if it was possible to do the same thing in reporting services. Apparently it is not.

|||

FWIW... you're not the only one with this kind of expectation...

I could write a book about how the surface similarities between what people (users and developers both) see in Excel and what is actually going on in a reporting result (and not just a SQL Server Reporting Services reporting result!) provides unrealistic expectations.

It would be fun (not!) to write another book about how visual design tools mislead developers about the underlying architecture supporting the runtime behavior,so that when those developers try to actually WRITE SOME CODE THEMSELVES they tie themselves up in knots.

I have to stop now before this turns into a rant <g>.

>L<