Pareto chart in MS Reporting Services

Is MS Reporting Services capable of adding new kinds of charts like a pareto
chart? If so, i'd like to find out if there's one that plugs in to VS .NET
2003 designer and MS Reporting Services.
Dundas does make a pareto chart in their enterprise charting package. They
also designed the charts used MSRS. Does anyone know if this would help?
For those who aren't familiar with a pareto chart, it can be a mix of a bar
and a point (or line) chart that coorespond to two sets of Y axis. One Y
axis can represent an item count and the other Y axis represents the
cumulative percentage.
Thanks,
-ChrisYou may be interested in this related thread:
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/msg/2ccef00744a2f70d
If you want real Pareto charts, then the closest you can get in RS2000 is to
write a custom assembly which takes parameters and generates a chart (e.g.
with the Dundas Enterprise Edition) on-the-fly and returns it as byte array.
The custom assembly would need to retrieve the data for the chart on its own
(unless you can pass them all through parameters to the custom assembly
function). After you have implemented the custom assembly you would perform
these steps in report designer:
* Add a reference to the custom assembly through the VS menu: Report -
Report Properties - References
* Add an image to your report
* Set the image type to Database
* Set the image mimetype to e.g. image/png
* For the image value use an expression like
=MyCustomAssembly.GenerateChart(...)
Notes:
* the custom assembly has to return the image as byte[].
* you should follow the links provided below about custom assemblies and
code access security in RS because e.g. you will need to assert security
permission inside the custom assembly when using certain .NET classes and
methods for generating an image.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_rdl_6d0i.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/dngrfCodeAccessSecurityInSQLServer2000ReportingServices.asp
FYI: Dundas has plans to provide a deeply integrated design-time and
runtime-processing control of the Enterprise Edition chart for RS 2005. You
can still use familiar RS features (like grouping, aggregates, filters,
etc.) and will get full control over charting.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"TUA-Chris" <TUAChris@.discussions.microsoft.com> wrote in message
news:966F0AC6-8CB4-4FBE-AD11-4F709F77B9C7@.microsoft.com...
> Is MS Reporting Services capable of adding new kinds of charts like a
pareto
> chart? If so, i'd like to find out if there's one that plugs in to VS
.NET
> 2003 designer and MS Reporting Services.
> Dundas does make a pareto chart in their enterprise charting package.
They
> also designed the charts used MSRS. Does anyone know if this would help?
> For those who aren't familiar with a pareto chart, it can be a mix of a
bar
> and a point (or line) chart that coorespond to two sets of Y axis. One Y
> axis can represent an item count and the other Y axis represents the
> cumulative percentage.
> Thanks,
> -Chris|||I had to create a Pareto chart manually by generating both series of values within the SQL statement and then manually defining the representation of Series 1 with a bar and Series 2 with a line.
--
Message posted via http://www.sqlmonster.com|||William,
I'm trying to find how to set one series to use a bar chart and another
series to use a line, but can't seem to find anything applicable. It looks
like I can only use one kind of representation. Could you explain how I can
represent two series using different charting techniques (one bar, one line)?
Are there any on-line tutorials that show how this is done?
Thanks in advance!!
-Chris Davis
"William Nichols via SQLMonster.com" wrote:
> I had to create a Pareto chart manually by generating both series of values within the SQL statement and then manually defining the representation of Series 1 with a bar and Series 2 with a line.
> --
> Message posted via http://www.sqlmonster.com
>|||Please read this related posting which also includes a sample:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=c229b25c-b2dc-41ac-923d-decbb253dc6e&sloc=en-us
You may also be interested in this KB How-to article:
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B842422
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"TUA-Chris" <TUAChris@.discussions.microsoft.com> wrote in message
news:D4CEC1F0-4D96-42A6-A46C-25189C5E998C@.microsoft.com...
> William,
> I'm trying to find how to set one series to use a bar chart and another
> series to use a line, but can't seem to find anything applicable. It
looks
> like I can only use one kind of representation. Could you explain how I
can
> represent two series using different charting techniques (one bar, one
line)?
> Are there any on-line tutorials that show how this is done?
> Thanks in advance!!
> -Chris Davis
> "William Nichols via SQLMonster.com" wrote:
> > I had to create a Pareto chart manually by generating both series of
values within the SQL statement and then manually defining the
representation of Series 1 with a bar and Series 2 with a line.
> >
> > --
> > Message posted via http://www.sqlmonster.com
> >|||Hi Robert,
I found a way to do this without going into the RDL itself. Sorry if my
response below looks like a book, but I'm explaining in detail in case anyone
else reading this will find it useful.
I found out that in Report Designer, if a second set of data is added, you
can have it plot in the form of a line. I also found that I can add
functions to the report itself without having to jump through a bunch of
hoops to get there. Using these two features, I was able to accomplish what
I wanted.
Obviously, if I had just plotted the same data with a line, visually, you
would see a line connecting the top parts of each column in my graph. What I
wanted to see was a percentage value that showed what percentage of the
current column plus all previous columns were. This would create a line that
would increase in value up to 100%. Being new to Reporting Services, I
couldn't find a way to count the values of previous columns into a value
which would represent my percentage line point. I dug around and found that
I could create my own functions to calculate a value to graph. This was a
break-through for me, because instead of plotting the value of the field
itself, I could plot the return value of a custom function I could write.
Here's how I did it:
1) Obtain total number of items.
2) Go to Report drop-down meny and select Report Properties, then select the
Code tab
3) Enter the following code:
dim RunningPercentage = 0.0
dim TotalItems = 0;
' TotalItems gets populated with the total number of items elsewhere. I use
this
' to determine percentage.
' This is used when calculating the cumulative percentage of tickets per
category.
function RunningPercentage (accum)
RunningPercentage = RunningPercentage + (accum / TotalItems * 100)
' Trucate decimal values smaller than one-tenths of a percent...
return int (RunningPercentage * 10) / 10
end function
' This just returns RunningPercentage without accumulating...
function ShowRunningPercentage ()
' Trucate decimal values smaller than one-tenths of a percent...
return (int (RunningPercentage * 10) / 10) & "%"
end function
4) In my graph, I drag a duplicate field onto the top of the graph (Drop
data fields here)
5) Right-click the new dulpicate item, select Properties
6) For Series Label, I call it "Cumulative %"
7) For Value, I enter, =Code.RunningPercentage(Count(Fields!Item.Value))
8) Click Appearance tab and check "Plot data as line"
9) Click Point Labels tab. In the Data label field, I enter,
=Code.ShowRunningPercentage()
10) To make the percentage curve look nice, I right click on field in the
Category section of the graph (at bottom) and select Properties. Then I
click on the Sorting tab and in the Expression column, I add
=Count(Fields.Item.Value) then for Direction, I select Descending.
I'm done! Now you will see a graph sorted from largest item on the left to
smallest item on the right with an increasing curve showing the accumulative
percentage.
Thanks for your help!
-Chris
"Robert Bruckner [MSFT]" wrote:
> Please read this related posting which also includes a sample:
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=c229b25c-b2dc-41ac-923d-decbb253dc6e&sloc=en-us
> You may also be interested in this KB How-to article:
> http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B842422
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "TUA-Chris" <TUAChris@.discussions.microsoft.com> wrote in message
> news:D4CEC1F0-4D96-42A6-A46C-25189C5E998C@.microsoft.com...
> > William,
> >
> > I'm trying to find how to set one series to use a bar chart and another
> > series to use a line, but can't seem to find anything applicable. It
> looks
> > like I can only use one kind of representation. Could you explain how I
> can
> > represent two series using different charting techniques (one bar, one
> line)?
> > Are there any on-line tutorials that show how this is done?
> >
> > Thanks in advance!!
> >
> > -Chris Davis
> >
> > "William Nichols via SQLMonster.com" wrote:
> >
> > > I had to create a Pareto chart manually by generating both series of
> values within the SQL statement and then manually defining the
> representation of Series 1 with a bar and Series 2 with a line.
> > >
> > > --
> > > Message posted via http://www.sqlmonster.com
> > >
>
>|||Thanks for sharing your solution! Other people will certainly find it
useful.
Keep in mind, that your approach (calculating the "RunningValue" in code)
has limitations and will only work because you don't have category and
series grouping expressions in the chart. RS 2005 will enable the usage of
the RunningValue() function in charts which solves this for the general
case.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Yes, if you do the sorting and calculations in your dataset (through a
stored procedure)
"TUA-Chris" <TUAChris@.discussions.microsoft.com> wrote in message
news:A3DD2177-842E-40B2-A997-342F95CD4CA9@.microsoft.com...
> Hi Robert,
> I found a way to do this without going into the RDL itself. Sorry if my
> response below looks like a book, but I'm explaining in detail in case
anyone
> else reading this will find it useful.
> I found out that in Report Designer, if a second set of data is added, you
> can have it plot in the form of a line. I also found that I can add
> functions to the report itself without having to jump through a bunch of
> hoops to get there. Using these two features, I was able to accomplish
what
> I wanted.
> Obviously, if I had just plotted the same data with a line, visually, you
> would see a line connecting the top parts of each column in my graph.
What I
> wanted to see was a percentage value that showed what percentage of the
> current column plus all previous columns were. This would create a line
that
> would increase in value up to 100%. Being new to Reporting Services, I
> couldn't find a way to count the values of previous columns into a value
> which would represent my percentage line point. I dug around and found
that
> I could create my own functions to calculate a value to graph. This was a
> break-through for me, because instead of plotting the value of the field
> itself, I could plot the return value of a custom function I could write.
> Here's how I did it:
> 1) Obtain total number of items.
> 2) Go to Report drop-down meny and select Report Properties, then select
the
> Code tab
> 3) Enter the following code:
> dim RunningPercentage = 0.0
> dim TotalItems = 0;
> ' TotalItems gets populated with the total number of items elsewhere. I
use
> this
> ' to determine percentage.
> ' This is used when calculating the cumulative percentage of tickets per
> category.
> function RunningPercentage (accum)
> RunningPercentage = RunningPercentage + (accum / TotalItems * 100)
> ' Trucate decimal values smaller than one-tenths of a percent...
> return int (RunningPercentage * 10) / 10
> end function
> ' This just returns RunningPercentage without accumulating...
> function ShowRunningPercentage ()
> ' Trucate decimal values smaller than one-tenths of a percent...
> return (int (RunningPercentage * 10) / 10) & "%"
> end function
> 4) In my graph, I drag a duplicate field onto the top of the graph (Drop
> data fields here)
> 5) Right-click the new dulpicate item, select Properties
> 6) For Series Label, I call it "Cumulative %"
> 7) For Value, I enter, =Code.RunningPercentage(Count(Fields!Item.Value))
> 8) Click Appearance tab and check "Plot data as line"
> 9) Click Point Labels tab. In the Data label field, I enter,
> =Code.ShowRunningPercentage()
> 10) To make the percentage curve look nice, I right click on field in the
> Category section of the graph (at bottom) and select Properties. Then I
> click on the Sorting tab and in the Expression column, I add
> =Count(Fields.Item.Value) then for Direction, I select Descending.
> I'm done! Now you will see a graph sorted from largest item on the left
to
> smallest item on the right with an increasing curve showing the
accumulative
> percentage.
> Thanks for your help!
> -Chris
>
> "Robert Bruckner [MSFT]" wrote:
> > Please read this related posting which also includes a sample:
> >
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=c229b25c-b2dc-41ac-923d-decbb253dc6e&sloc=en-us
> >
> > You may also be interested in this KB How-to article:
> > http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B842422
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "TUA-Chris" <TUAChris@.discussions.microsoft.com> wrote in message
> > news:D4CEC1F0-4D96-42A6-A46C-25189C5E998C@.microsoft.com...
> > > William,
> > >
> > > I'm trying to find how to set one series to use a bar chart and
another
> > > series to use a line, but can't seem to find anything applicable. It
> > looks
> > > like I can only use one kind of representation. Could you explain how
I
> > can
> > > represent two series using different charting techniques (one bar, one
> > line)?
> > > Are there any on-line tutorials that show how this is done?
> > >
> > > Thanks in advance!!
> > >
> > > -Chris Davis
> > >
> > > "William Nichols via SQLMonster.com" wrote:
> > >
> > > > I had to create a Pareto chart manually by generating both series of
> > values within the SQL statement and then manually defining the
> > representation of Series 1 with a bar and Series 2 with a line.
> > > >
> > > > --
> > > > Message posted via http://www.sqlmonster.com
> > > >
> >
> >
> >