Charts: Using Top N but need to show 'Others' as one

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
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>