Hi all
I'm generating a column chart, lets say its CustomerID as a category
and SUM(Sales.Value) as a values sorted by SUM(Sales.Value) DESC. That
might give 100 column bars so we filter the category to show, say, Top
20.
Can anyone see a way, convoluted or otherwise, of creating one more
column bar to represent SUM(Sales.Value) for the other 80.
I just want to be sure that I'm not missing something before I dive
into writing code that creates a temporary table to report on which is
the only way I can see.
Cheers
RossAFAIK there is no function for "others". Perhaps you can do this on your
query and then flush the result to the report. Raise your hand if you need
assitance with this.
HTH, Jens Süßmeyer
--
http://www.sqlserver2005.de
--
"Ross" <synergy56@.hotmail.com> schrieb im Newsbeitrag
news:6sgt515glf8uvkr8vfqqliv554im1l7pak@.4ax.com...
> Hi all
> I'm generating a column chart, lets say its CustomerID as a category
> and SUM(Sales.Value) as a values sorted by SUM(Sales.Value) DESC. That
> might give 100 column bars so we filter the category to show, say, Top
> 20.
> Can anyone see a way, convoluted or otherwise, of creating one more
> column bar to represent SUM(Sales.Value) for the other 80.
> I just want to be sure that I'm not missing something before I dive
> into writing code that creates a temporary table to report on which is
> the only way I can see.
> Cheers
> Ross
>|||Use two queries and union them together. The first query is your top 10.
The second query uses the first query as a subquery to return anly keys,
then do a NOT IN() against the key selection. This will return all but the
top 10 which you can aggregate into a single valued called "Other"
Basing your your chart on the union of these two queries will give you 11
data points; top 10 and other.
Paul Turley
"Ross" <synergy56@.hotmail.com> wrote in message
news:6sgt515glf8uvkr8vfqqliv554im1l7pak@.4ax.com...
> Hi all
> I'm generating a column chart, lets say its CustomerID as a category
> and SUM(Sales.Value) as a values sorted by SUM(Sales.Value) DESC. That
> might give 100 column bars so we filter the category to show, say, Top
> 20.
> Can anyone see a way, convoluted or otherwise, of creating one more
> column bar to represent SUM(Sales.Value) for the other 80.
> I just want to be sure that I'm not missing something before I dive
> into writing code that creates a temporary table to report on which is
> the only way I can see.
> Cheers
> Ross
>|||I did try that, however i'm storing my 'N' for the top N in a parameter, and
i see no way to use a parameter in a query to limit the rows. Likewise i see
no way to add a ranking number in the table itself. anybody?
"Paul Turley" wrote:
> Use two queries and union them together. The first query is your top 10.
> The second query uses the first query as a subquery to return anly keys,
> then do a NOT IN() against the key selection. This will return all but the
> top 10 which you can aggregate into a single valued called "Other"
> Basing your your chart on the union of these two queries will give you 11
> data points; top 10 and other.
> Paul Turley
>
> "Ross" <synergy56@.hotmail.com> wrote in message
> news:6sgt515glf8uvkr8vfqqliv554im1l7pak@.4ax.com...
> > Hi all
> >
> > I'm generating a column chart, lets say its CustomerID as a category
> > and SUM(Sales.Value) as a values sorted by SUM(Sales.Value) DESC. That
> > might give 100 column bars so we filter the category to show, say, Top
> > 20.
> >
> > Can anyone see a way, convoluted or otherwise, of creating one more
> > column bar to represent SUM(Sales.Value) for the other 80.
> >
> > I just want to be sure that I'm not missing something before I dive
> > into writing code that creates a temporary table to report on which is
> > the only way I can see.
> >
> > Cheers
> > Ross
> >
>
>|||You can define a dynamic TopN filter like this:
Filter expression: = Fields!A.Value
Filter operator: TopN
Filter value: = Parameters!Top.Value
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ard Goossens" <ArdGoossens@.discussions.microsoft.com> wrote in message
news:CE512986-BE7C-4189-BC4B-70165DE734F2@.microsoft.com...
>I did try that, however i'm storing my 'N' for the top N in a parameter,
>and
> i see no way to use a parameter in a query to limit the rows. Likewise i
> see
> no way to add a ranking number in the table itself. anybody?
>
> "Paul Turley" wrote:
>> Use two queries and union them together. The first query is your top 10.
>> The second query uses the first query as a subquery to return anly keys,
>> then do a NOT IN() against the key selection. This will return all but
>> the
>> top 10 which you can aggregate into a single valued called "Other"
>> Basing your your chart on the union of these two queries will give you 11
>> data points; top 10 and other.
>> Paul Turley
>>
>> "Ross" <synergy56@.hotmail.com> wrote in message
>> news:6sgt515glf8uvkr8vfqqliv554im1l7pak@.4ax.com...
>> > Hi all
>> >
>> > I'm generating a column chart, lets say its CustomerID as a category
>> > and SUM(Sales.Value) as a values sorted by SUM(Sales.Value) DESC. That
>> > might give 100 column bars so we filter the category to show, say, Top
>> > 20.
>> >
>> > Can anyone see a way, convoluted or otherwise, of creating one more
>> > column bar to represent SUM(Sales.Value) for the other 80.
>> >
>> > I just want to be sure that I'm not missing something before I dive
>> > into writing code that creates a temporary table to report on which is
>> > the only way I can see.
>> >
>> > Cheers
>> > Ross
>> >
>>|||Ard,
You can use paramters and variables with the SET ROWCOUNT command. You
could also build a string containing your TOP N query and execute dynamic
SQL, but this is not my preference.
Ted
"Ard Goossens" wrote:
> I did try that, however i'm storing my 'N' for the top N in a parameter, and
> i see no way to use a parameter in a query to limit the rows. Likewise i see
> no way to add a ranking number in the table itself. anybody?
> "Paul Turley" wrote:
> > Use two queries and union them together. The first query is your top 10.
> > The second query uses the first query as a subquery to return anly keys,
> > then do a NOT IN() against the key selection. This will return all but the
> > top 10 which you can aggregate into a single valued called "Other"
> >
> > Basing your your chart on the union of these two queries will give you 11
> > data points; top 10 and other.|||Robert
Do you know by any chance how to handle the cases when Top is null? Let's
say you want to give the users the option to select a Top N value or to leave
it null. Thx
"Robert Bruckner [MSFT]" wrote:
> You can define a dynamic TopN filter like this:
> Filter expression: = Fields!A.Value
> Filter operator: TopN
> Filter value: = Parameters!Top.Value
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Ard Goossens" <ArdGoossens@.discussions.microsoft.com> wrote in message
> news:CE512986-BE7C-4189-BC4B-70165DE734F2@.microsoft.com...
> >I did try that, however i'm storing my 'N' for the top N in a parameter,
> >and
> > i see no way to use a parameter in a query to limit the rows. Likewise i
> > see
> > no way to add a ranking number in the table itself. anybody?
> >
> >
> >
> > "Paul Turley" wrote:
> >
> >> Use two queries and union them together. The first query is your top 10.
> >> The second query uses the first query as a subquery to return anly keys,
> >> then do a NOT IN() against the key selection. This will return all but
> >> the
> >> top 10 which you can aggregate into a single valued called "Other"
> >>
> >> Basing your your chart on the union of these two queries will give you 11
> >> data points; top 10 and other.
> >>
> >> Paul Turley
> >>
> >>
> >> "Ross" <synergy56@.hotmail.com> wrote in message
> >> news:6sgt515glf8uvkr8vfqqliv554im1l7pak@.4ax.com...
> >> > Hi all
> >> >
> >> > I'm generating a column chart, lets say its CustomerID as a category
> >> > and SUM(Sales.Value) as a values sorted by SUM(Sales.Value) DESC. That
> >> > might give 100 column bars so we filter the category to show, say, Top
> >> > 20.
> >> >
> >> > Can anyone see a way, convoluted or otherwise, of creating one more
> >> > column bar to represent SUM(Sales.Value) for the other 80.
> >> >
> >> > I just want to be sure that I'm not missing something before I dive
> >> > into writing code that creates a temporary table to report on which is
> >> > the only way I can see.
> >> >
> >> > Cheers
> >> > Ross
> >> >
> >>
> >>
> >>
>
>|||There are two different approaches how to deal with this situation:
1. "Fake Parameter" approach:
* Create a "fake" hidden (non-prompted) parameter PSize to calculate the
dataset size. I.e. the parameter default value would be
=CountRows("ChartDataSetName")
* on the chart, the filter expression would look like this:
Filter expression: = Fields!A.Value
Filter operator: TopN
Filter value: = iif( Parameters!Top.Value is Nothing,
Parameters!PSize.Value, Parameters!Top.Value)
2. "Filter with Duplicates" approach:
Filter expression: = iif( Parameters!Top.Value is Nothing, 1,
Fields!A.Value)
Filter operator: TopN
Filter value: = iif( Parameters!Top.Value is Nothing, 1,
Parameters!Top.Value)
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Marco" <Marco@.discussions.microsoft.com> wrote in message
news:19869577-684A-4971-A89E-DCA199EC5141@.microsoft.com...
> Robert
> Do you know by any chance how to handle the cases when Top is null? Let's
> say you want to give the users the option to select a Top N value or to
> leave
> it null. Thx
> "Robert Bruckner [MSFT]" wrote:
>> You can define a dynamic TopN filter like this:
>> Filter expression: = Fields!A.Value
>> Filter operator: TopN
>> Filter value: = Parameters!Top.Value
>>
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Ard Goossens" <ArdGoossens@.discussions.microsoft.com> wrote in message
>> news:CE512986-BE7C-4189-BC4B-70165DE734F2@.microsoft.com...
>> >I did try that, however i'm storing my 'N' for the top N in a parameter,
>> >and
>> > i see no way to use a parameter in a query to limit the rows. Likewise
>> > i
>> > see
>> > no way to add a ranking number in the table itself. anybody?
>> >
>> >
>> >
>> > "Paul Turley" wrote:
>> >
>> >> Use two queries and union them together. The first query is your top
>> >> 10.
>> >> The second query uses the first query as a subquery to return anly
>> >> keys,
>> >> then do a NOT IN() against the key selection. This will return all but
>> >> the
>> >> top 10 which you can aggregate into a single valued called "Other"
>> >>
>> >> Basing your your chart on the union of these two queries will give you
>> >> 11
>> >> data points; top 10 and other.
>> >>
>> >> Paul Turley
>> >>
>> >>
>> >> "Ross" <synergy56@.hotmail.com> wrote in message
>> >> news:6sgt515glf8uvkr8vfqqliv554im1l7pak@.4ax.com...
>> >> > Hi all
>> >> >
>> >> > I'm generating a column chart, lets say its CustomerID as a category
>> >> > and SUM(Sales.Value) as a values sorted by SUM(Sales.Value) DESC.
>> >> > That
>> >> > might give 100 column bars so we filter the category to show, say,
>> >> > Top
>> >> > 20.
>> >> >
>> >> > Can anyone see a way, convoluted or otherwise, of creating one more
>> >> > column bar to represent SUM(Sales.Value) for the other 80.
>> >> >
>> >> > I just want to be sure that I'm not missing something before I dive
>> >> > into writing code that creates a temporary table to report on which
>> >> > is
>> >> > the only way I can see.
>> >> >
>> >> > Cheers
>> >> > Ross
>> >> >
>> >>
>> >>
>> >>
>>|||Thanks Robert this is very helpful
"Robert Bruckner [MSFT]" wrote:
> There are two different approaches how to deal with this situation:
> 1. "Fake Parameter" approach:
> * Create a "fake" hidden (non-prompted) parameter PSize to calculate the
> dataset size. I.e. the parameter default value would be
> =CountRows("ChartDataSetName")
> * on the chart, the filter expression would look like this:
> Filter expression: = Fields!A.Value
> Filter operator: TopN
> Filter value: = iif( Parameters!Top.Value is Nothing,
> Parameters!PSize.Value, Parameters!Top.Value)
> 2. "Filter with Duplicates" approach:
> Filter expression: = iif( Parameters!Top.Value is Nothing, 1,
> Fields!A.Value)
> Filter operator: TopN
> Filter value: = iif( Parameters!Top.Value is Nothing, 1,
> Parameters!Top.Value)
>
> --
> Robert M. Bruckner
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
> "Marco" <Marco@.discussions.microsoft.com> wrote in message
> news:19869577-684A-4971-A89E-DCA199EC5141@.microsoft.com...
> > Robert
> >
> > Do you know by any chance how to handle the cases when Top is null? Let's
> > say you want to give the users the option to select a Top N value or to
> > leave
> > it null. Thx
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> >> You can define a dynamic TopN filter like this:
> >> Filter expression: = Fields!A.Value
> >> Filter operator: TopN
> >> Filter value: = Parameters!Top.Value
> >>
> >>
> >> -- Robert
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >> "Ard Goossens" <ArdGoossens@.discussions.microsoft.com> wrote in message
> >> news:CE512986-BE7C-4189-BC4B-70165DE734F2@.microsoft.com...
> >> >I did try that, however i'm storing my 'N' for the top N in a parameter,
> >> >and
> >> > i see no way to use a parameter in a query to limit the rows. Likewise
> >> > i
> >> > see
> >> > no way to add a ranking number in the table itself. anybody?
> >> >
> >> >
> >> >
> >> > "Paul Turley" wrote:
> >> >
> >> >> Use two queries and union them together. The first query is your top
> >> >> 10.
> >> >> The second query uses the first query as a subquery to return anly
> >> >> keys,
> >> >> then do a NOT IN() against the key selection. This will return all but
> >> >> the
> >> >> top 10 which you can aggregate into a single valued called "Other"
> >> >>
> >> >> Basing your your chart on the union of these two queries will give you
> >> >> 11
> >> >> data points; top 10 and other.
> >> >>
> >> >> Paul Turley
> >> >>
> >> >>
> >> >> "Ross" <synergy56@.hotmail.com> wrote in message
> >> >> news:6sgt515glf8uvkr8vfqqliv554im1l7pak@.4ax.com...
> >> >> > Hi all
> >> >> >
> >> >> > I'm generating a column chart, lets say its CustomerID as a category
> >> >> > and SUM(Sales.Value) as a values sorted by SUM(Sales.Value) DESC.
> >> >> > That
> >> >> > might give 100 column bars so we filter the category to show, say,
> >> >> > Top
> >> >> > 20.
> >> >> >
> >> >> > Can anyone see a way, convoluted or otherwise, of creating one more
> >> >> > column bar to represent SUM(Sales.Value) for the other 80.
> >> >> >
> >> >> > I just want to be sure that I'm not missing something before I dive
> >> >> > into writing code that creates a temporary table to report on which
> >> >> > is
> >> >> > the only way I can see.
> >> >> >
> >> >> > Cheers
> >> >> > Ross
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
Showing posts with label sales. Show all posts
Showing posts with label sales. Show all posts
Wednesday, March 7, 2012
Charts slow report rendering to a crawl
I've searched the forums on this issue, haven't really found the answer.
I have several nifty little sales reports which crunch a ton of data quite efficiently and render in just a few seconds in Report Manager. I've pushed as much of the data processing back to the server as possible, use a stored procedure (with parameters) in a shared datasource, don't return unneccessary data, all that. It works great.
When I first developed the reports, I continued generating my charts (which use the same data as the reports, just grouped differently) in Excel and pasting them in as images. Now I want to stop that nonsense and use the SSRS charts. I fooled around with the charting function and got a reasonable facimile of my Excel charts, two per report, which use their own separate stored procedures and the same shared datasource.
Now, reports that used to render in 5-8 seconds may take 1-5 MINUTES. Help! It's definitely the charts--taking them back out fixes the problem.
I have complete control over the datasources--would it make more sense to use non-shared sources, or to create totally separate shared sources? I saw a post that recommended "making data calls non-synchronous," but I have no idea how to do that.
Thanks for any suggestions.
On further investigation, it appears that deleting EITHER ONE of the charts brings the rendering time down almost to the same time as no chart at all. It's apparent that having MULTIPLE charts on a page multiplies the rendering time exponentially (I'm gonna tell Edward Tufte!)
This happens whether I put the charts side-by-side (preferred) or one above the other on the page--they just take FOREVER to render.
Anybody...?
|||I know this post is a bit old but im having exactly the same problem.
I have a report that has around 150 charts on it once expanded and it is taking around 3 seconds to load each chart.
Is there a resolution for this?
Regards
Will
Charts slow report rendering to a crawl
I've searched the forums on this issue, haven't really found the answer.
I have several nifty little sales reports which crunch a ton of data quite efficiently and render in just a few seconds in Report Manager. I've pushed as much of the data processing back to the server as possible, use a stored procedure (with parameters) in a shared datasource, don't return unneccessary data, all that. It works great.
When I first developed the reports, I continued generating my charts (which use the same data as the reports, just grouped differently) in Excel and pasting them in as images. Now I want to stop that nonsense and use the SSRS charts. I fooled around with the charting function and got a reasonable facimile of my Excel charts, two per report, which use their own separate stored procedures and the same shared datasource.
Now, reports that used to render in 5-8 seconds may take 1-5 MINUTES. Help! It's definitely the charts--taking them back out fixes the problem.
I have complete control over the datasources--would it make more sense to use non-shared sources, or to create totally separate shared sources? I saw a post that recommended "making data calls non-synchronous," but I have no idea how to do that.
Thanks for any suggestions.
On further investigation, it appears that deleting EITHER ONE of the charts brings the rendering time down almost to the same time as no chart at all. It's apparent that having MULTIPLE charts on a page multiplies the rendering time exponentially (I'm gonna tell Edward Tufte!)
This happens whether I put the charts side-by-side (preferred) or one above the other on the page--they just take FOREVER to render.
Anybody...?
Friday, February 24, 2012
chart slows report render to crawl in Report Manager
I've searched the forums on this issue, haven't really found the answer.
I have several nifty little sales reports which crunch a ton of data quite
efficiently and render in just a few seconds in Report Manager. I've pushed
as much of the data processing back to the server as possible, use a stored
procedure (with parameters) in a shared datasource, don't return unneccessary
data, all that. It works great.
When I first developed the reports, I continued generating my charts (which
use the same data as the reports, just grouped differently) in Excel and
pasting them in as images. Now I want to stop that nonsense and use the SSRS
charts. I fooled around with the charting function and got a reasonable
facimile of my Excel charts, two per report, which use their own separate
stored procedures and the same shared datasource.
Now, reports that used to render in 5-8 seconds may take 1-5 MINUTES. Help!
It's definitely the charts--taking them back out fixes the problem.
I have complete control over the datasources--would it make more sense to
use non-shared sources, or to create totally separate shared sources? I saw
a post that recommended "making data calls non-synchronous," but I have no
idea how to do that.
Thanks for any suggestions.On further investigation, it appears that deleting EITHER ONE of the charts
brings the rendering time down almost to the same time as no chart at all.
It's apparent that having MULTIPLE charts on a page multiplies the rendering
time exponentially (I'm gonna tell Edward Tufte!)
This happens whether I put the charts side-by-side (preferred) or one above
the other on the page--they just take FOREVER to render.
Anybody...?|||RS 2005
I'm not sure how you mean your question about grouping. The charts each
have one grouping level, the table below them has three. Each component
(table, each chart separately) renders in roughly 5 seconds by itself. When
I put all three of them in the same report, the rendering time goes up to
well over one minute. Weird.
"Bruce L-C [MVP]" wrote:
> Hmmm, I don't know, I have a report with 3 charts and I don't see this
> issue. Do you have any grouping, anything special? Also, RS 2000 or RS 2005?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "elinde" <elinde@.discussions.microsoft.com> wrote in message
> news:301D52EC-E38F-423D-9A01-B4386A282B36@.microsoft.com...
> > On further investigation, it appears that deleting EITHER ONE of the
> > charts
> > brings the rendering time down almost to the same time as no chart at all.
> > It's apparent that having MULTIPLE charts on a page multiplies the
> > rendering
> > time exponentially (I'm gonna tell Edward Tufte!)
> >
> > This happens whether I put the charts side-by-side (preferred) or one
> > above
> > the other on the page--they just take FOREVER to render.
> >
> > Anybody...?
> >
>
>|||Is this deployed or in the IDE? If in the development environment try
deploying and see if that makes a difference in performance.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"elinde" <elinde@.discussions.microsoft.com> wrote in message
news:54603F35-85B8-405F-B979-DC03DD43AB16@.microsoft.com...
> RS 2005
> I'm not sure how you mean your question about grouping. The charts each
> have one grouping level, the table below them has three. Each component
> (table, each chart separately) renders in roughly 5 seconds by itself.
> When
> I put all three of them in the same report, the rendering time goes up to
> well over one minute. Weird.
> "Bruce L-C [MVP]" wrote:
>> Hmmm, I don't know, I have a report with 3 charts and I don't see this
>> issue. Do you have any grouping, anything special? Also, RS 2000 or RS
>> 2005?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "elinde" <elinde@.discussions.microsoft.com> wrote in message
>> news:301D52EC-E38F-423D-9A01-B4386A282B36@.microsoft.com...
>> > On further investigation, it appears that deleting EITHER ONE of the
>> > charts
>> > brings the rendering time down almost to the same time as no chart at
>> > all.
>> > It's apparent that having MULTIPLE charts on a page multiplies the
>> > rendering
>> > time exponentially (I'm gonna tell Edward Tufte!)
>> >
>> > This happens whether I put the charts side-by-side (preferred) or one
>> > above
>> > the other on the page--they just take FOREVER to render.
>> >
>> > Anybody...?
>> >
>>|||This is happening in deployment, unfortunately. The server is big, new &
fast--it's not that.
"Bruce L-C [MVP]" wrote:
> Is this deployed or in the IDE? If in the development environment try
> deploying and see if that makes a difference in performance.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "elinde" <elinde@.discussions.microsoft.com> wrote in message
> news:54603F35-85B8-405F-B979-DC03DD43AB16@.microsoft.com...
> > RS 2005
> >
> > I'm not sure how you mean your question about grouping. The charts each
> > have one grouping level, the table below them has three. Each component
> > (table, each chart separately) renders in roughly 5 seconds by itself.
> > When
> > I put all three of them in the same report, the rendering time goes up to
> > well over one minute. Weird.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Hmmm, I don't know, I have a report with 3 charts and I don't see this
> >> issue. Do you have any grouping, anything special? Also, RS 2000 or RS
> >> 2005?
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "elinde" <elinde@.discussions.microsoft.com> wrote in message
> >> news:301D52EC-E38F-423D-9A01-B4386A282B36@.microsoft.com...
> >> > On further investigation, it appears that deleting EITHER ONE of the
> >> > charts
> >> > brings the rendering time down almost to the same time as no chart at
> >> > all.
> >> > It's apparent that having MULTIPLE charts on a page multiplies the
> >> > rendering
> >> > time exponentially (I'm gonna tell Edward Tufte!)
> >> >
> >> > This happens whether I put the charts side-by-side (preferred) or one
> >> > above
> >> > the other on the page--they just take FOREVER to render.
> >> >
> >> > Anybody...?
> >> >
> >>
> >>
> >>
>
>|||Hmmm, I don't know, I have a report with 3 charts and I don't see this
issue. Do you have any grouping, anything special? Also, RS 2000 or RS 2005?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"elinde" <elinde@.discussions.microsoft.com> wrote in message
news:301D52EC-E38F-423D-9A01-B4386A282B36@.microsoft.com...
> On further investigation, it appears that deleting EITHER ONE of the
> charts
> brings the rendering time down almost to the same time as no chart at all.
> It's apparent that having MULTIPLE charts on a page multiplies the
> rendering
> time exponentially (I'm gonna tell Edward Tufte!)
> This happens whether I put the charts side-by-side (preferred) or one
> above
> the other on the page--they just take FOREVER to render.
> Anybody...?
>
I have several nifty little sales reports which crunch a ton of data quite
efficiently and render in just a few seconds in Report Manager. I've pushed
as much of the data processing back to the server as possible, use a stored
procedure (with parameters) in a shared datasource, don't return unneccessary
data, all that. It works great.
When I first developed the reports, I continued generating my charts (which
use the same data as the reports, just grouped differently) in Excel and
pasting them in as images. Now I want to stop that nonsense and use the SSRS
charts. I fooled around with the charting function and got a reasonable
facimile of my Excel charts, two per report, which use their own separate
stored procedures and the same shared datasource.
Now, reports that used to render in 5-8 seconds may take 1-5 MINUTES. Help!
It's definitely the charts--taking them back out fixes the problem.
I have complete control over the datasources--would it make more sense to
use non-shared sources, or to create totally separate shared sources? I saw
a post that recommended "making data calls non-synchronous," but I have no
idea how to do that.
Thanks for any suggestions.On further investigation, it appears that deleting EITHER ONE of the charts
brings the rendering time down almost to the same time as no chart at all.
It's apparent that having MULTIPLE charts on a page multiplies the rendering
time exponentially (I'm gonna tell Edward Tufte!)
This happens whether I put the charts side-by-side (preferred) or one above
the other on the page--they just take FOREVER to render.
Anybody...?|||RS 2005
I'm not sure how you mean your question about grouping. The charts each
have one grouping level, the table below them has three. Each component
(table, each chart separately) renders in roughly 5 seconds by itself. When
I put all three of them in the same report, the rendering time goes up to
well over one minute. Weird.
"Bruce L-C [MVP]" wrote:
> Hmmm, I don't know, I have a report with 3 charts and I don't see this
> issue. Do you have any grouping, anything special? Also, RS 2000 or RS 2005?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "elinde" <elinde@.discussions.microsoft.com> wrote in message
> news:301D52EC-E38F-423D-9A01-B4386A282B36@.microsoft.com...
> > On further investigation, it appears that deleting EITHER ONE of the
> > charts
> > brings the rendering time down almost to the same time as no chart at all.
> > It's apparent that having MULTIPLE charts on a page multiplies the
> > rendering
> > time exponentially (I'm gonna tell Edward Tufte!)
> >
> > This happens whether I put the charts side-by-side (preferred) or one
> > above
> > the other on the page--they just take FOREVER to render.
> >
> > Anybody...?
> >
>
>|||Is this deployed or in the IDE? If in the development environment try
deploying and see if that makes a difference in performance.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"elinde" <elinde@.discussions.microsoft.com> wrote in message
news:54603F35-85B8-405F-B979-DC03DD43AB16@.microsoft.com...
> RS 2005
> I'm not sure how you mean your question about grouping. The charts each
> have one grouping level, the table below them has three. Each component
> (table, each chart separately) renders in roughly 5 seconds by itself.
> When
> I put all three of them in the same report, the rendering time goes up to
> well over one minute. Weird.
> "Bruce L-C [MVP]" wrote:
>> Hmmm, I don't know, I have a report with 3 charts and I don't see this
>> issue. Do you have any grouping, anything special? Also, RS 2000 or RS
>> 2005?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "elinde" <elinde@.discussions.microsoft.com> wrote in message
>> news:301D52EC-E38F-423D-9A01-B4386A282B36@.microsoft.com...
>> > On further investigation, it appears that deleting EITHER ONE of the
>> > charts
>> > brings the rendering time down almost to the same time as no chart at
>> > all.
>> > It's apparent that having MULTIPLE charts on a page multiplies the
>> > rendering
>> > time exponentially (I'm gonna tell Edward Tufte!)
>> >
>> > This happens whether I put the charts side-by-side (preferred) or one
>> > above
>> > the other on the page--they just take FOREVER to render.
>> >
>> > Anybody...?
>> >
>>|||This is happening in deployment, unfortunately. The server is big, new &
fast--it's not that.
"Bruce L-C [MVP]" wrote:
> Is this deployed or in the IDE? If in the development environment try
> deploying and see if that makes a difference in performance.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "elinde" <elinde@.discussions.microsoft.com> wrote in message
> news:54603F35-85B8-405F-B979-DC03DD43AB16@.microsoft.com...
> > RS 2005
> >
> > I'm not sure how you mean your question about grouping. The charts each
> > have one grouping level, the table below them has three. Each component
> > (table, each chart separately) renders in roughly 5 seconds by itself.
> > When
> > I put all three of them in the same report, the rendering time goes up to
> > well over one minute. Weird.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Hmmm, I don't know, I have a report with 3 charts and I don't see this
> >> issue. Do you have any grouping, anything special? Also, RS 2000 or RS
> >> 2005?
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "elinde" <elinde@.discussions.microsoft.com> wrote in message
> >> news:301D52EC-E38F-423D-9A01-B4386A282B36@.microsoft.com...
> >> > On further investigation, it appears that deleting EITHER ONE of the
> >> > charts
> >> > brings the rendering time down almost to the same time as no chart at
> >> > all.
> >> > It's apparent that having MULTIPLE charts on a page multiplies the
> >> > rendering
> >> > time exponentially (I'm gonna tell Edward Tufte!)
> >> >
> >> > This happens whether I put the charts side-by-side (preferred) or one
> >> > above
> >> > the other on the page--they just take FOREVER to render.
> >> >
> >> > Anybody...?
> >> >
> >>
> >>
> >>
>
>|||Hmmm, I don't know, I have a report with 3 charts and I don't see this
issue. Do you have any grouping, anything special? Also, RS 2000 or RS 2005?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"elinde" <elinde@.discussions.microsoft.com> wrote in message
news:301D52EC-E38F-423D-9A01-B4386A282B36@.microsoft.com...
> On further investigation, it appears that deleting EITHER ONE of the
> charts
> brings the rendering time down almost to the same time as no chart at all.
> It's apparent that having MULTIPLE charts on a page multiplies the
> rendering
> time exponentially (I'm gonna tell Edward Tufte!)
> This happens whether I put the charts side-by-side (preferred) or one
> above
> the other on the page--they just take FOREVER to render.
> Anybody...?
>
Chart Series with Different Scales
Hi all,
If you have a chart and want to relate to different series, that have
different scales ie Number of Sales, and Revenue, per Day. Can you have
different axis scales so that 'number of sales ' is not scaled down to near
invisiblity.
I'm Working arround it by having two charts, but there should be a way to
have the two series on the same bar-chart.
AdrianSorry, secondary y-axis is currently not supported. Using two charts - as
you are already doing - is the best approach at this point.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"AdrianJMartin" <AdrianJMartin@.discussions.microsoft.com> wrote in message
news:2B313783-46EF-4942-80CE-AC193555E1D4@.microsoft.com...
> Hi all,
> If you have a chart and want to relate to different series, that have
> different scales ie Number of Sales, and Revenue, per Day. Can you have
> different axis scales so that 'number of sales ' is not scaled down to
> near
> invisiblity.
> I'm Working arround it by having two charts, but there should be a way to
> have the two series on the same bar-chart.
>
> Adrian|||I want to write an expression on major grid line how do i write
"Robert Bruckner [MSFT]" wrote:
> Sorry, secondary y-axis is currently not supported. Using two charts - as
> you are already doing - is the best approach at this point.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "AdrianJMartin" <AdrianJMartin@.discussions.microsoft.com> wrote in message
> news:2B313783-46EF-4942-80CE-AC193555E1D4@.microsoft.com...
> > Hi all,
> > If you have a chart and want to relate to different series, that have
> > different scales ie Number of Sales, and Revenue, per Day. Can you have
> > different axis scales so that 'number of sales ' is not scaled down to
> > near
> > invisiblity.
> >
> > I'm Working arround it by having two charts, but there should be a way to
> > have the two series on the same bar-chart.
> >
> >
> > Adrian
>
>|||I was able to develop a way to simulate a second y axis. In my case the data
source was a cube. I had two types of series, one with large, whole numbers,
and one with percentages, which caused the percentages to be at the zero
point when displayed with the other type of series. My solution was to write
a calculated member that found the maximum value of the whole number series
for all rows, and to round that value up to the next highest number.
Therefore, if the series returned 8,235 as the maximum value in any row, the
result of my calculated member would be 9,000. The 9,000 value of the
calculated member was returned for every row. In the graph I used the value
property of the percentage series to multiply the actual value by my
calculated member value. I used the label property to obtain the actual
percentage value. Thus, 80% became .8 * 9000 for plotting purposes, but the
data point label said 80%. The result was a successful combination of the
two very different types of series. The only thing that I could not do
without a true second y axis was having the tick marks and labels of a second
axis along the right side of the graph.
--
Brian C. Berg
MCSE+I, MCDBA
Berg Information Technology, Inc.
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Bruckner [MSFT]" wrote:
> Sorry, secondary y-axis is currently not supported. Using two charts - as
> you are already doing - is the best approach at this point.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "AdrianJMartin" <AdrianJMartin@.discussions.microsoft.com> wrote in message
> news:2B313783-46EF-4942-80CE-AC193555E1D4@.microsoft.com...
> > Hi all,
> > If you have a chart and want to relate to different series, that have
> > different scales ie Number of Sales, and Revenue, per Day. Can you have
> > different axis scales so that 'number of sales ' is not scaled down to
> > near
> > invisiblity.
> >
> > I'm Working arround it by having two charts, but there should be a way to
> > have the two series on the same bar-chart.
> >
> >
> > Adrian
>
>
If you have a chart and want to relate to different series, that have
different scales ie Number of Sales, and Revenue, per Day. Can you have
different axis scales so that 'number of sales ' is not scaled down to near
invisiblity.
I'm Working arround it by having two charts, but there should be a way to
have the two series on the same bar-chart.
AdrianSorry, secondary y-axis is currently not supported. Using two charts - as
you are already doing - is the best approach at this point.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"AdrianJMartin" <AdrianJMartin@.discussions.microsoft.com> wrote in message
news:2B313783-46EF-4942-80CE-AC193555E1D4@.microsoft.com...
> Hi all,
> If you have a chart and want to relate to different series, that have
> different scales ie Number of Sales, and Revenue, per Day. Can you have
> different axis scales so that 'number of sales ' is not scaled down to
> near
> invisiblity.
> I'm Working arround it by having two charts, but there should be a way to
> have the two series on the same bar-chart.
>
> Adrian|||I want to write an expression on major grid line how do i write
"Robert Bruckner [MSFT]" wrote:
> Sorry, secondary y-axis is currently not supported. Using two charts - as
> you are already doing - is the best approach at this point.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "AdrianJMartin" <AdrianJMartin@.discussions.microsoft.com> wrote in message
> news:2B313783-46EF-4942-80CE-AC193555E1D4@.microsoft.com...
> > Hi all,
> > If you have a chart and want to relate to different series, that have
> > different scales ie Number of Sales, and Revenue, per Day. Can you have
> > different axis scales so that 'number of sales ' is not scaled down to
> > near
> > invisiblity.
> >
> > I'm Working arround it by having two charts, but there should be a way to
> > have the two series on the same bar-chart.
> >
> >
> > Adrian
>
>|||I was able to develop a way to simulate a second y axis. In my case the data
source was a cube. I had two types of series, one with large, whole numbers,
and one with percentages, which caused the percentages to be at the zero
point when displayed with the other type of series. My solution was to write
a calculated member that found the maximum value of the whole number series
for all rows, and to round that value up to the next highest number.
Therefore, if the series returned 8,235 as the maximum value in any row, the
result of my calculated member would be 9,000. The 9,000 value of the
calculated member was returned for every row. In the graph I used the value
property of the percentage series to multiply the actual value by my
calculated member value. I used the label property to obtain the actual
percentage value. Thus, 80% became .8 * 9000 for plotting purposes, but the
data point label said 80%. The result was a successful combination of the
two very different types of series. The only thing that I could not do
without a true second y axis was having the tick marks and labels of a second
axis along the right side of the graph.
--
Brian C. Berg
MCSE+I, MCDBA
Berg Information Technology, Inc.
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Bruckner [MSFT]" wrote:
> Sorry, secondary y-axis is currently not supported. Using two charts - as
> you are already doing - is the best approach at this point.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "AdrianJMartin" <AdrianJMartin@.discussions.microsoft.com> wrote in message
> news:2B313783-46EF-4942-80CE-AC193555E1D4@.microsoft.com...
> > Hi all,
> > If you have a chart and want to relate to different series, that have
> > different scales ie Number of Sales, and Revenue, per Day. Can you have
> > different axis scales so that 'number of sales ' is not scaled down to
> > near
> > invisiblity.
> >
> > I'm Working arround it by having two charts, but there should be a way to
> > have the two series on the same bar-chart.
> >
> >
> > Adrian
>
>
Chart legend has too much data - how to only show 1 legend
I have created a hybrid bar chart / with line. Basically uses the bars to
show sales for each geographic region. A single line is plotted showing the
sales goal to be reached. The x axis is "Region" The y axis value 1 is total
sales $. I added y value 2 to show the sales goal $ and the legend
automatically got populated with series labels. I only want the x axis
labels to be in the legend.
How can I fix this?NP,
This is not a direct answer to your question, but I got tired of the report
legend and what seemed the lack of configuration and used a nice trick from
the Technical Article: Get More Out of SQL Server Reporting Services Charts
(http://msdn2.microsoft.com/en-us/library/aa964128.aspx) to create my own
legend. The document is excellent, five stars.
If you use the Custom Chart Color Palettes and Legends section from the doc
you will see what I mean. You are able to create your own legend and use what
you want and remove what you don't.
Hope this helps.
Reeves
"NP" wrote:
> I have created a hybrid bar chart / with line. Basically uses the bars to
> show sales for each geographic region. A single line is plotted showing the
> sales goal to be reached. The x axis is "Region" The y axis value 1 is total
> sales $. I added y value 2 to show the sales goal $ and the legend
> automatically got populated with series labels. I only want the x axis
> labels to be in the legend.
> How can I fix this?
show sales for each geographic region. A single line is plotted showing the
sales goal to be reached. The x axis is "Region" The y axis value 1 is total
sales $. I added y value 2 to show the sales goal $ and the legend
automatically got populated with series labels. I only want the x axis
labels to be in the legend.
How can I fix this?NP,
This is not a direct answer to your question, but I got tired of the report
legend and what seemed the lack of configuration and used a nice trick from
the Technical Article: Get More Out of SQL Server Reporting Services Charts
(http://msdn2.microsoft.com/en-us/library/aa964128.aspx) to create my own
legend. The document is excellent, five stars.
If you use the Custom Chart Color Palettes and Legends section from the doc
you will see what I mean. You are able to create your own legend and use what
you want and remove what you don't.
Hope this helps.
Reeves
"NP" wrote:
> I have created a hybrid bar chart / with line. Basically uses the bars to
> show sales for each geographic region. A single line is plotted showing the
> sales goal to be reached. The x axis is "Region" The y axis value 1 is total
> sales $. I added y value 2 to show the sales goal $ and the legend
> automatically got populated with series labels. I only want the x axis
> labels to be in the legend.
> How can I fix this?
Sunday, February 19, 2012
Chart Drill Through Bug?
I have a peculiar problem with a line chart that has a "jump to report"
action. The chart basically shows sales by month, with two different
lines for 2004 and 2005. When a user clicks on a specific month, it
jumps to a detailed report for that month. This works beautifully
except for one problem:
The first month is the starting point of the line graph, call it
December. Clicking on the line anywhere between December and January
jumps to the January detail report. In a way, I guess this sort of
makes sense because otherwise I guess you wouldn't be able to drill to
the last month on the graph. What I'd really like to be able to is not
make the "line" interactive at all. I'd rather define the drill
through in the point label itself. So that when you click on the point
label for December, then you go to the december report.
Does anyone know of way to do that? Maybe by directly defining it in
the rdl?Did you look into using datapoint "markers"? They can help to improve line
charts to make the drillthrough links more obvious (especially if the
markers are bigger than the line width).
The described behavior (the line is also interactive) is inherent to design
of the chart control and cannot be changed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"jordo" <jordan_mccall@.hotmail.com> wrote in message
news:1107300323.493686.4760@.z14g2000cwz.googlegroups.com...
> I have a peculiar problem with a line chart that has a "jump to report"
> action. The chart basically shows sales by month, with two different
> lines for 2004 and 2005. When a user clicks on a specific month, it
> jumps to a detailed report for that month. This works beautifully
> except for one problem:
> The first month is the starting point of the line graph, call it
> December. Clicking on the line anywhere between December and January
> jumps to the January detail report. In a way, I guess this sort of
> makes sense because otherwise I guess you wouldn't be able to drill to
> the last month on the graph. What I'd really like to be able to is not
> make the "line" interactive at all. I'd rather define the drill
> through in the point label itself. So that when you click on the point
> label for December, then you go to the december report.
> Does anyone know of way to do that? Maybe by directly defining it in
> the rdl?
>|||Brilliant! This works quite well. Thank you for your help!
action. The chart basically shows sales by month, with two different
lines for 2004 and 2005. When a user clicks on a specific month, it
jumps to a detailed report for that month. This works beautifully
except for one problem:
The first month is the starting point of the line graph, call it
December. Clicking on the line anywhere between December and January
jumps to the January detail report. In a way, I guess this sort of
makes sense because otherwise I guess you wouldn't be able to drill to
the last month on the graph. What I'd really like to be able to is not
make the "line" interactive at all. I'd rather define the drill
through in the point label itself. So that when you click on the point
label for December, then you go to the december report.
Does anyone know of way to do that? Maybe by directly defining it in
the rdl?Did you look into using datapoint "markers"? They can help to improve line
charts to make the drillthrough links more obvious (especially if the
markers are bigger than the line width).
The described behavior (the line is also interactive) is inherent to design
of the chart control and cannot be changed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"jordo" <jordan_mccall@.hotmail.com> wrote in message
news:1107300323.493686.4760@.z14g2000cwz.googlegroups.com...
> I have a peculiar problem with a line chart that has a "jump to report"
> action. The chart basically shows sales by month, with two different
> lines for 2004 and 2005. When a user clicks on a specific month, it
> jumps to a detailed report for that month. This works beautifully
> except for one problem:
> The first month is the starting point of the line graph, call it
> December. Clicking on the line anywhere between December and January
> jumps to the January detail report. In a way, I guess this sort of
> makes sense because otherwise I guess you wouldn't be able to drill to
> the last month on the graph. What I'd really like to be able to is not
> make the "line" interactive at all. I'd rather define the drill
> through in the point label itself. So that when you click on the point
> label for December, then you go to the december report.
> Does anyone know of way to do that? Maybe by directly defining it in
> the rdl?
>|||Brilliant! This works quite well. Thank you for your help!
Chart Axes Labels
I need to dynamically name the axes of a chart as the data plotted can be changed - Sliced. Ie. Sales dollars, Quantity, Profit etc.
The dataset that is used to plot the graph does not contain the measure description (it's a generic name - MeasureModifier). There is a second data set that contains all the descriptions that the report can be sliced on.
I have a text box that displays the description of the measure and I tried referencing the value as an expression from the Y axes title expression box. - =ReportItems!Parameter1_1.value
However it returns "Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope."
Does anybody know of any other way I can referrence values outside the dataset that the object is derived from?
Cheers
KevinI can actually reference a regular textbox value for the y axes label.
What I can't do - from the Y axes label or even from another text box is reference a value from a textbox that is inside a list box. The list box has a filter that only returns one row from the data set and the textbox displays the field from the dataset. I need to reference that value for the Y axes title but keep getting the error below when I use ReportItems!...
Any ideas how to overcome this?
Thanks
Kevin
"Kevin Wilson" wrote:
> I need to dynamically name the axes of a chart as the data plotted can be changed - Sliced. Ie. Sales dollars, Quantity, Profit etc.
> The dataset that is used to plot the graph does not contain the measure description (it's a generic name - MeasureModifier). There is a second data set that contains all the descriptions that the report can be sliced on.
> I have a text box that displays the description of the measure and I tried referencing the value as an expression from the Y axes title expression box. - =ReportItems!Parameter1_1.value
> However it returns "Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope."
> Does anybody know of any other way I can referrence values outside the dataset that the object is derived from?
> Cheers
> Kevin
>
The dataset that is used to plot the graph does not contain the measure description (it's a generic name - MeasureModifier). There is a second data set that contains all the descriptions that the report can be sliced on.
I have a text box that displays the description of the measure and I tried referencing the value as an expression from the Y axes title expression box. - =ReportItems!Parameter1_1.value
However it returns "Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope."
Does anybody know of any other way I can referrence values outside the dataset that the object is derived from?
Cheers
KevinI can actually reference a regular textbox value for the y axes label.
What I can't do - from the Y axes label or even from another text box is reference a value from a textbox that is inside a list box. The list box has a filter that only returns one row from the data set and the textbox displays the field from the dataset. I need to reference that value for the Y axes title but keep getting the error below when I use ReportItems!...
Any ideas how to overcome this?
Thanks
Kevin
"Kevin Wilson" wrote:
> I need to dynamically name the axes of a chart as the data plotted can be changed - Sliced. Ie. Sales dollars, Quantity, Profit etc.
> The dataset that is used to plot the graph does not contain the measure description (it's a generic name - MeasureModifier). There is a second data set that contains all the descriptions that the report can be sliced on.
> I have a text box that displays the description of the measure and I tried referencing the value as an expression from the Y axes title expression box. - =ReportItems!Parameter1_1.value
> However it returns "Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope."
> Does anybody know of any other way I can referrence values outside the dataset that the object is derived from?
> Cheers
> Kevin
>
Subscribe to:
Posts (Atom)