Filter and show TOP N rows

Hi all,
I´m designing a report with a kind of Ranking. I have a list of products and
I have to show the totals by product.
What I need to show in my ranking table is only the Top 10 records so I need
to filter by something like "Top 10". My questions are:
- Which is the correct expresion to write in the group filter?
- Is it possible to add a column to show the row number?
(Additional information)
My result should be something like:
Position > Product > Total
1 > Product1 > 1000
2 > Product2 > 900
3 > Product3 > 850
4 > Product4 > 725
5 > Product5 > 700
6 > Product6 > 680
7 > Product7 > 500
8 > Product8 > 330
9 > Product8 > 210
10 > Product10 > 200
I know how to do it creating a new Dataset using SQL syntax, but I can´t
touch this. The dataset available contains the complete list with all the
values, I mean, my dataset is like:
Product > Value
Product1 > 3
Product1 > 4
Product2 > 1
Product9 > 6
Product3 > 3
Product5 > 4
Product8 > 1
Product4 > 6
Product10 > 3
Product6 > 4
Product6 > 1
Product7 > 6
Product9 > 6
Product3 > 3
Product5 > 4
Product8 > 1
Product4 > 6
Product10 > 3
Product6 > 4
Product6 > 1
Product7 > 6
Product9 > 6
Product3 > 3
Product5 > 4
Product8 > 1
Product4 > 6
Product10 > 3
[more records...]
So I have to group data and sum values to show totals.
Could anybody help me?
Thank you in advanced.
MónicaOn Aug 29, 11:18 am, "M=F3nica" <monica.d...@.augure.com> wrote:
> Hi all,
> I=B4m designing a report with a kind of Ranking. I have a list of product=s and
> I have to show the totals by product.
> What I need to show in my ranking table is only the Top 10 records so I n=eed
> to filter by something like "Top 10". My questions are:
> - Which is the correct expresion to write in the group filter?
> - Is it possible to add a column to show the row number?
> (Additional information)
> My result should be something like:
> Position > Product > Total
> 1 > Product1 > 1000
> 2 > Product2 > 900
> 3 > Product3 > 850
> 4 > Product4 > 725
> 5 > Product5 > 700
> 6 > Product6 > 680
> 7 > Product7 > 500
> 8 > Product8 > 330
> 9 > Product8 > 210
> 10 > Product10 > 200
> I know how to do it creating a new Dataset using SQL syntax, but I can=B4t
> touch this. The dataset available contains the complete list with all the
> values, I mean, my dataset is like:
> Product > Value
> Product1 > 3
> Product1 > 4
> Product2 > 1
> Product9 > 6
> Product3 > 3
> Product5 > 4
> Product8 > 1
> Product4 > 6
> Product10 > 3
> Product6 > 4
> Product6 > 1
> Product7 > 6
> Product9 > 6
> Product3 > 3
> Product5 > 4
> Product8 > 1
> Product4 > 6
> Product10 > 3
> Product6 > 4
> Product6 > 1
> Product7 > 6
> Product9 > 6
> Product3 > 3
> Product5 > 4
> Product8 > 1
> Product4 > 6
> Product10 > 3
> [more records...]
> So I have to group data and sum values to show totals.
> Could anybody help me?
> Thank you in advanced.
> M=F3nica
Right-click the table/matrix control and select 'Properties' -> select
the 'Groups' tab -> select the 'Edit...' button -> select the
'Filters' tab -> below 'Expression' select '=3DFields!Product.Value' ->
below 'Operator' select 'Top N' -> below 'Value' enter =3D10. Based on
your sort order you can control which items are top 10. To get the row
number you can use the expression =3DRowNumber(Nothing) in a new
column.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thank you!
Top N filter works but not the RowNumber function :(.
If I add =RowNumber(Nothing) in a new column it shows the product RowNumber,
counting all the rows grouped.
i.e.
Product > Total > RowNumber
Product1 > 73 > 73
Product2 > 12 > 85
Product3 > 12 > 97
Product4 > 7 > 104
Product5 > 3 > 107
Product6 > 3 > 110
Product7 > 3 > 113
Product8 > 2 > 115
Product9 > 1 > 116
Product10 > 1 > 117
Note that every RowNumber is the sum of the 2 previous totals, so we can say
it shows the row number of the product, counting all the rows grouped.
How can I show the ranking position (1, 2, 3, 4, 5, etc.) instead of the
product RowNumber shown now (73, 85, 97, 104, 107, etc.)'
Another question about this. I´ve tried to show the total in the table
footer but the total shown does not filter my subtotals.
What I really need is to have the following:
Ranking >Product > SubTotal
1 > Product1 > 73
2 > Product2 > 12
3 > Product3 > 12
4 > Product4 > 7
5 > Product5 > 3
6 > Product6 > 3
7 > Product7 > 3
8 > Product8 > 2
9 > Product9 > 1
10 > Product10 > 1
TOTAL TOP 10 > 117
TOTAL OTHER > 550
TOTAL > 667
Could anybody tell me how to do it?
My needs are:
- To be able to SUM only filtered rows
- To be able to show RowNumber as a Ranking position
- To be able to SUM products not included in the filter
(TOTAL is the only field I have with no problem :D)
Thank you
Regards,
Mónica
"EMartinez" <emartinez.pr1@.gmail.com> escribió en el mensaje
news:1188430691.619390.58280@.57g2000hsv.googlegroups.com...
On Aug 29, 11:18 am, "Mónica" <monica.d...@.augure.com> wrote:
> Hi all,
> I´m designing a report with a kind of Ranking. I have a list of products
> and
> I have to show the totals by product.
> What I need to show in my ranking table is only the Top 10 records so I
> need
> to filter by something like "Top 10". My questions are:
> - Which is the correct expresion to write in the group filter?
> - Is it possible to add a column to show the row number?
> (Additional information)
> My result should be something like:
> Position > Product > Total
> 1 > Product1 > 1000
> 2 > Product2 > 900
> 3 > Product3 > 850
> 4 > Product4 > 725
> 5 > Product5 > 700
> 6 > Product6 > 680
> 7 > Product7 > 500
> 8 > Product8 > 330
> 9 > Product8 > 210
> 10 > Product10 > 200
> I know how to do it creating a new Dataset using SQL syntax, but I can´t
> touch this. The dataset available contains the complete list with all the
> values, I mean, my dataset is like:
> Product > Value
> Product1 > 3
> Product1 > 4
> Product2 > 1
> Product9 > 6
> Product3 > 3
> Product5 > 4
> Product8 > 1
> Product4 > 6
> Product10 > 3
> Product6 > 4
> Product6 > 1
> Product7 > 6
> Product9 > 6
> Product3 > 3
> Product5 > 4
> Product8 > 1
> Product4 > 6
> Product10 > 3
> Product6 > 4
> Product6 > 1
> Product7 > 6
> Product9 > 6
> Product3 > 3
> Product5 > 4
> Product8 > 1
> Product4 > 6
> Product10 > 3
> [more records...]
> So I have to group data and sum values to show totals.
> Could anybody help me?
> Thank you in advanced.
> Mónica
Right-click the table/matrix control and select 'Properties' -> select
the 'Groups' tab -> select the 'Edit...' button -> select the
'Filters' tab -> below 'Expression' select '=Fields!Product.Value' ->
below 'Operator' select 'Top N' -> below 'Value' enter =10. Based on
your sort order you can control which items are top 10. To get the row
number you can use the expression =RowNumber(Nothing) in a new
column.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Aug 30, 9:51 am, "M=F3nica" <monica.d...@.augure.com> wrote:
> Thank you!
> Top N filter works but not the RowNumber function :(.
> If I add =3DRowNumber(Nothing) in a new column it shows the product RowNu=mber,
> counting all the rows grouped.
> i.e.
> Product > Total > RowNumber
> Product1 > 73 > 73
> Product2 > 12 > 85
> Product3 > 12 > 97
> Product4 > 7 > 104
> Product5 > 3 > 107
> Product6 > 3 > 110
> Product7 > 3 > 113
> Product8 > 2 > 115
> Product9 > 1 > 116
> Product10 > 1 > 117
> Note that every RowNumber is the sum of the 2 previous totals, so we can =say
> it shows the row number of the product, counting all the rows grouped.
> How can I show the ranking position (1, 2, 3, 4, 5, etc.) instead of the
> product RowNumber shown now (73, 85, 97, 104, 107, etc.)'
> Another question about this. I=B4ve tried to show the total in the table
> footer but the total shown does not filter my subtotals.
> What I really need is to have the following:
> Ranking >Product > SubTotal
> 1 > Product1 > 73
> 2 > Product2 > 12
> 3 > Product3 > 12
> 4 > Product4 > 7
> 5 > Product5 > 3
> 6 > Product6 > 3
> 7 > Product7 > 3
> 8 > Product8 > 2
> 9 > Product9 > 1
> 10 > Product10 > 1
> TOTAL TOP 10 > 117
> TOTAL OTHER > 550
> TOTAL > 667
> Could anybody tell me how to do it?
> My needs are:
> - To be able to SUM only filtered rows
> - To be able to show RowNumber as a Ranking position
> - To be able to SUM products not included in the filter
> (TOTAL is the only field I have with no problem :D)
> Thank you
> Regards,
> M=F3nica
> "EMartinez" <emartinez...@.gmail.com> escribi=F3 en el mensajenews:118843=0691.619390.58280@.57g2000hsv.googlegroups.com...
> On Aug 29, 11:18 am, "M=F3nica" <monica.d...@.augure.com> wrote:
>
> > Hi all,
> > I=B4m designing a report with a kind of Ranking. I have a list of produ=cts
> > and
> > I have to show the totals by product.
> > What I need to show in my ranking table is only the Top 10 records so I
> > need
> > to filter by something like "Top 10". My questions are:
> > - Which is the correct expresion to write in the group filter?
> > - Is it possible to add a column to show the row number?
> > (Additional information)
> > My result should be something like:
> > Position > Product > Total
> > 1 > Product1 > 1000
> > 2 > Product2 > 900
> > 3 > Product3 > 850
> > 4 > Product4 > 725
> > 5 > Product5 > 700
> > 6 > Product6 > 680
> > 7 > Product7 > 500
> > 8 > Product8 > 330
> > 9 > Product8 > 210
> > 10 > Product10 > 200
> > I know how to do it creating a new Dataset using SQL syntax, but I can==B4t
> > touch this. The dataset available contains the complete list with all t=he
> > values, I mean, my dataset is like:
> > Product > Value
> > Product1 > 3
> > Product1 > 4
> > Product2 > 1
> > Product9 > 6
> > Product3 > 3
> > Product5 > 4
> > Product8 > 1
> > Product4 > 6
> > Product10 > 3
> > Product6 > 4
> > Product6 > 1
> > Product7 > 6
> > Product9 > 6
> > Product3 > 3
> > Product5 > 4
> > Product8 > 1
> > Product4 > 6
> > Product10 > 3
> > Product6 > 4
> > Product6 > 1
> > Product7 > 6
> > Product9 > 6
> > Product3 > 3
> > Product5 > 4
> > Product8 > 1
> > Product4 > 6
> > Product10 > 3
> > [more records...]
> > So I have to group data and sum values to show totals.
> > Could anybody help me?
> > Thank you in advanced.
> > M=F3nica
> Right-click the table/matrix control and select 'Properties' -> select
> the 'Groups' tab -> select the 'Edit...' button -> select the
> 'Filters' tab -> below 'Expression' select '=3DFields!Product.Value' ->
> below 'Operator' select 'Top N' -> below 'Value' enter =3D10. Based on
> your sort order you can control which items are top 10. To get the row
> number you can use the expression =3DRowNumber(Nothing) in a new
> column.
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
RowNumber should give you what you need. You just want to set the
scope for it: RowNumber(Scope). To get the correct sums, you will want
to create separate datasets and then reference them via: =3DSum(Fields!
Total.Value, "DataSetName") as the expression. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Yes, I know... The problem is that I can not create new Datasets or touch
the one I have.
I'll try a workarround... :(
Thank you anyway.
"EMartinez" <emartinez.pr1@.gmail.com> escribió en el mensaje
news:1188526929.514501.144250@.i13g2000prf.googlegroups.com...
On Aug 30, 9:51 am, "Mónica" <monica.d...@.augure.com> wrote:
> Thank you!
> Top N filter works but not the RowNumber function :(.
> If I add =RowNumber(Nothing) in a new column it shows the product
> RowNumber,
> counting all the rows grouped.
> i.e.
> Product > Total > RowNumber
> Product1 > 73 > 73
> Product2 > 12 > 85
> Product3 > 12 > 97
> Product4 > 7 > 104
> Product5 > 3 > 107
> Product6 > 3 > 110
> Product7 > 3 > 113
> Product8 > 2 > 115
> Product9 > 1 > 116
> Product10 > 1 > 117
> Note that every RowNumber is the sum of the 2 previous totals, so we can
> say
> it shows the row number of the product, counting all the rows grouped.
> How can I show the ranking position (1, 2, 3, 4, 5, etc.) instead of the
> product RowNumber shown now (73, 85, 97, 104, 107, etc.)'
> Another question about this. I´ve tried to show the total in the table
> footer but the total shown does not filter my subtotals.
> What I really need is to have the following:
> Ranking >Product > SubTotal
> 1 > Product1 > 73
> 2 > Product2 > 12
> 3 > Product3 > 12
> 4 > Product4 > 7
> 5 > Product5 > 3
> 6 > Product6 > 3
> 7 > Product7 > 3
> 8 > Product8 > 2
> 9 > Product9 > 1
> 10 > Product10 > 1
> TOTAL TOP 10 > 117
> TOTAL OTHER > 550
> TOTAL > 667
> Could anybody tell me how to do it?
> My needs are:
> - To be able to SUM only filtered rows
> - To be able to show RowNumber as a Ranking position
> - To be able to SUM products not included in the filter
> (TOTAL is the only field I have with no problem :D)
> Thank you
> Regards,
> Mónica
> "EMartinez" <emartinez...@.gmail.com> escribió en el
> mensajenews:1188430691.619390.58280@.57g2000hsv.googlegroups.com...
> On Aug 29, 11:18 am, "Mónica" <monica.d...@.augure.com> wrote:
>
> > Hi all,
> > I´m designing a report with a kind of Ranking. I have a list of products
> > and
> > I have to show the totals by product.
> > What I need to show in my ranking table is only the Top 10 records so I
> > need
> > to filter by something like "Top 10". My questions are:
> > - Which is the correct expresion to write in the group filter?
> > - Is it possible to add a column to show the row number?
> > (Additional information)
> > My result should be something like:
> > Position > Product > Total
> > 1 > Product1 > 1000
> > 2 > Product2 > 900
> > 3 > Product3 > 850
> > 4 > Product4 > 725
> > 5 > Product5 > 700
> > 6 > Product6 > 680
> > 7 > Product7 > 500
> > 8 > Product8 > 330
> > 9 > Product8 > 210
> > 10 > Product10 > 200
> > I know how to do it creating a new Dataset using SQL syntax, but I can´t
> > touch this. The dataset available contains the complete list with all
> > the
> > values, I mean, my dataset is like:
> > Product > Value
> > Product1 > 3
> > Product1 > 4
> > Product2 > 1
> > Product9 > 6
> > Product3 > 3
> > Product5 > 4
> > Product8 > 1
> > Product4 > 6
> > Product10 > 3
> > Product6 > 4
> > Product6 > 1
> > Product7 > 6
> > Product9 > 6
> > Product3 > 3
> > Product5 > 4
> > Product8 > 1
> > Product4 > 6
> > Product10 > 3
> > Product6 > 4
> > Product6 > 1
> > Product7 > 6
> > Product9 > 6
> > Product3 > 3
> > Product5 > 4
> > Product8 > 1
> > Product4 > 6
> > Product10 > 3
> > [more records...]
> > So I have to group data and sum values to show totals.
> > Could anybody help me?
> > Thank you in advanced.
> > Mónica
> Right-click the table/matrix control and select 'Properties' -> select
> the 'Groups' tab -> select the 'Edit...' button -> select the
> 'Filters' tab -> below 'Expression' select '=Fields!Product.Value' ->
> below 'Operator' select 'Top N' -> below 'Value' enter =10. Based on
> your sort order you can control which items are top 10. To get the row
> number you can use the expression =RowNumber(Nothing) in a new
> column.
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
RowNumber should give you what you need. You just want to set the
scope for it: RowNumber(Scope). To get the correct sums, you will want
to create separate datasets and then reference them via: =Sum(Fields!
Total.Value, "DataSetName") as the expression. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Aug 31, 2:20 am, "M=F3nica" <monica.d...@.augure.com> wrote:
> Yes, I know... The problem is that I can not create new Datasets or touch
> the one I have.
> I'll try a workarround... :(
> Thank you anyway.
> "EMartinez" <emartinez...@.gmail.com> escribi=F3 en el mensajenews:118852=6929.514501.144250@.i13g2000prf.googlegroups.com...
> On Aug 30, 9:51 am, "M=F3nica" <monica.d...@.augure.com> wrote:
>
> > Thank you!
> > Top N filter works but not the RowNumber function :(.
> > If I add =3DRowNumber(Nothing) in a new column it shows the product
> > RowNumber,
> > counting all the rows grouped.
> > i.e.
> > Product > Total > RowNumber
> > Product1 > 73 > 73
> > Product2 > 12 > 85
> > Product3 > 12 > 97
> > Product4 > 7 > 104
> > Product5 > 3 > 107
> > Product6 > 3 > 110
> > Product7 > 3 > 113
> > Product8 > 2 > 115
> > Product9 > 1 > 116
> > Product10 > 1 > 117
> > Note that every RowNumber is the sum of the 2 previous totals, so we can
> > say
> > it shows the row number of the product, counting all the rows grouped.
> > How can I show the ranking position (1, 2, 3, 4, 5, etc.) instead of the
> > product RowNumber shown now (73, 85, 97, 104, 107, etc.)'
> > Another question about this. I=B4ve tried to show the total in the table
> > footer but the total shown does not filter my subtotals.
> > What I really need is to have the following:
> > Ranking >Product > SubTotal
> > 1 > Product1 > 73
> > 2 > Product2 > 12
> > 3 > Product3 > 12
> > 4 > Product4 > 7
> > 5 > Product5 > 3
> > 6 > Product6 > 3
> > 7 > Product7 > 3
> > 8 > Product8 > 2
> > 9 > Product9 > 1
> > 10 > Product10 > 1
> > TOTAL TOP 10 > 117
> > TOTAL OTHER > 550
> > TOTAL > 667
> > Could anybody tell me how to do it?
> > My needs are:
> > - To be able to SUM only filtered rows
> > - To be able to show RowNumber as a Ranking position
> > - To be able to SUM products not included in the filter
> > (TOTAL is the only field I have with no problem :D)
> > Thank you
> > Regards,
> > M=F3nica
> > "EMartinez" <emartinez...@.gmail.com> escribi=F3 en el
> > mensajenews:1188430691.619390.58280@.57g2000hsv.googlegroups.com...
> > On Aug 29, 11:18 am, "M=F3nica" <monica.d...@.augure.com> wrote:
> > > Hi all,
> > > I=B4m designing a report with a kind of Ranking. I have a list of pro=ducts
> > > and
> > > I have to show the totals by product.
> > > What I need to show in my ranking table is only the Top 10 records so= I
> > > need
> > > to filter by something like "Top 10". My questions are:
> > > - Which is the correct expresion to write in the group filter?
> > > - Is it possible to add a column to show the row number?
> > > (Additional information)
> > > My result should be something like:
> > > Position > Product > Total
> > > 1 > Product1 > 1000
> > > 2 > Product2 > 900
> > > 3 > Product3 > 850
> > > 4 > Product4 > 725
> > > 5 > Product5 > 700
> > > 6 > Product6 > 680
> > > 7 > Product7 > 500
> > > 8 > Product8 > 330
> > > 9 > Product8 > 210
> > > 10 > Product10 > 200
> > > I know how to do it creating a new Dataset using SQL syntax, but I ca=n=B4t
> > > touch this. The dataset available contains the complete list with all
> > > the
> > > values, I mean, my dataset is like:
> > > Product > Value
> > > Product1 > 3
> > > Product1 > 4
> > > Product2 > 1
> > > Product9 > 6
> > > Product3 > 3
> > > Product5 > 4
> > > Product8 > 1
> > > Product4 > 6
> > > Product10 > 3
> > > Product6 > 4
> > > Product6 > 1
> > > Product7 > 6
> > > Product9 > 6
> > > Product3 > 3
> > > Product5 > 4
> > > Product8 > 1
> > > Product4 > 6
> > > Product10 > 3
> > > Product6 > 4
> > > Product6 > 1
> > > Product7 > 6
> > > Product9 > 6
> > > Product3 > 3
> > > Product5 > 4
> > > Product8 > 1
> > > Product4 > 6
> > > Product10 > 3
> > > [more records...]
> > > So I have to group data and sum values to show totals.
> > > Could anybody help me?
> > > Thank you in advanced.
> > > M=F3nica
> > Right-click the table/matrix control and select 'Properties' -> select
> > the 'Groups' tab -> select the 'Edit...' button -> select the
> > 'Filters' tab -> below 'Expression' select '=3DFields!Product.Value' ->
> > below 'Operator' select 'Top N' -> below 'Value' enter =3D10. Based on
> > your sort order you can control which items are top 10. To get the row
> > number you can use the expression =3DRowNumber(Nothing) in a new
> > column.
> > Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
> RowNumber should give you what you need. You just want to set the
> scope for it: RowNumber(Scope). To get the correct sums, you will want
> to create separate datasets and then reference them via: =3DSum(Fields!
> Total.Value, "DataSetName") as the expression. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
Another alternative for the sums might be to group the dataset based
on the top N and bottom N and then try to use the InScope function in
a sum expression. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant