|
|
You're in the
sql server reporting services
group:Charts: Using Top N but need to show 'Others' as one
sql server reporting services:
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 [quoted text, click to view] "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 >
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
AFAIK 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... [quoted text, click to view] > 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? [quoted text, click to view] "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. [quoted text, click to view] "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 [quoted text, click to view] "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 [quoted text, click to view] "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. [quoted text, click to view] "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
[quoted text, click to view] "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 > >> >> > > >> >> > >> >> > >> >> > >> > >> > >> > >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |