Post reply

Using Top N and Bottom N within same report

  • February 9, 2011 at 7:31 am

    #234719

    Hi all

    I've built a report that has two tables, one showing the top 5 sales per region, the other showing bottom 5 sales per region. Each tables is bound to a different dataset, each dataset has a filter (one for top N and the other for bottom N).

    This works fine, however, I want to add a report parameter to allow the user to choose the product for which they want to view the top 5/bottom 5 sales per region. When they change the parameter and click View Report, this should update both tables (top and bottom). At the moment this is only updating the top 5 table, not the bottom 5. Obviously I don't want to have two parameter drop-downs on the report...

    Anyone know how I can do this? Do i need to perhaps try populating a second - but hidden - report parameter based on the selection of the first so that the second table can get updated as well?

    Any assistance is appreciated!

    Thanks

    Doodles

  • February 9, 2011 at 8:16 am

    #1284505

    You're showing top 5 and bottom 5 for the same sales data? Why would you have different data sets?

    Use one data set for 2 tables, use a different filter (TOP 5 and BOTTOM 5) for each of them.


    My LinkedIn profile

  • February 9, 2011 at 8:51 am

    #1284546

    thanks for the response. i'm using two different tables because visually that's what the user wants.

    I managed to get this working in the end though! Basically the same set of parameters need to be created in both datasets - but when you check the checkbox to create the parameters in the second report, they don't create duplicate parameters, they just use the same ones as defined by the first!

    So just to clarify: you create identical datasets, with identical report parameters, but create separate filters for each dataset, one for top 5 sales, and one for bottom 5 sales. Yay!

    Thanks

    Doodles

  • February 9, 2011 at 9:45 am

    #1284613

    OR assuming that you don't have 1 000 000 sales man.

    Use 2 table objects on the report. 1 filters top 5 asc, then other one desc.

    Then in your sp you simply do 2 row_number

    WHERE COl1 BETWEEN 1 and 5 OR Col2 BETWEEN 1 and 5.

    and in the final select do a case to see if top or bottom 5 so you have an easy filter in your report.

    That returns only the 10 rows you need in 1 dataset. That also means that you scan your tables ONLY once.

  • February 9, 2011 at 10:19 am

    #1284627

    doodlingdba (2/9/2011)

    thanks for the response. i'm using two different tables because visually that's what the user wants.

    I managed to get this working in the end though! Basically the same set of parameters need to be created in both datasets - but when you check the checkbox to create the parameters in the second report, they don't create duplicate parameters, they just use the same ones as defined by the first!

    So just to clarify: you create identical datasets, with identical report parameters, but create separate filters for each dataset, one for top 5 sales, and one for bottom 5 sales. Yay!

    Thanks

    Doodles

    That really is not the way to work in an easy maintainable manner.

    What if you had to change some logic behind the data set? You have to do it twice. Why would you fill two times same data on each execution? That's twice the time you really need to run the report.

    Use one data set if not too large. If too large, try Ninja's approach and save on your bandwidth and time.


    My LinkedIn profile

  • February 9, 2011 at 10:25 am

    #1284630

    bulbanos (2/9/2011)

    doodlingdba (2/9/2011)

    thanks for the response. i'm using two different tables because visually that's what the user wants.

    I managed to get this working in the end though! Basically the same set of parameters need to be created in both datasets - but when you check the checkbox to create the parameters in the second report, they don't create duplicate parameters, they just use the same ones as defined by the first!

    So just to clarify: you create identical datasets, with identical report parameters, but create separate filters for each dataset, one for top 5 sales, and one for bottom 5 sales. Yay!

    Thanks

    Doodles

    That really is not the way to work in an easy maintainable manner.

    What if you had to change some logic behind the data set? You have to do it twice.

    Use one data set if not too large. If too large, try Ninja's approach and save on your bandwidth and time.

    I'm talking about using a single dataset. However in the design view of the report I'm talking about using 2 tables, or 2 matrices, that use the same base dataset.

    That way you query the data only once and most importantly, scan the sales documents table(s) only once. Which can the server save quite a bit of work in a big company.

    Moreover with my method you could always add an extra parameter for top X salesman. Row_number will be able to work with that to add or remove extra data without changing anything.

  • February 9, 2011 at 10:27 am

    #1284631

    Ninja's_RGR'us (2/9/2011)

    I'm talking about using a single dataset. However in the design view of the report I'm talking about using 2 tables, or 2 matrices, that use the same base dataset.

    That way you query the data only once and most importantly, scan the sales documents table(s) only once. Which can the server save quite a bit of work in a big company.

    Moreover with my method you could always add an extra parameter for top X salesman. Row_number will be able to work with that to add or remove extra data without changing anything.

    Did I suggest otherwise? One data set ís the way to go


    My LinkedIn profile

  • February 9, 2011 at 10:30 am

    #1284640

    Hmm, I guess I chose the wrong person to quote. I merely intended to further explain "our" point of view :w00t:.

  • February 10, 2011 at 4:03 am

    #1285063

    Thanks for all the ideas guys!!

    Maintaining two datasets is an inefficient way of doing things - but my data source is SSAS and i'm new to MDX so I would have to figure out how to write the query in the backend. That could take me a while and i needed a quick win...

    If you have ideas on the mdx syntax that would help!!

    Thanks

    Doodles

  • February 10, 2011 at 5:23 am

    #1285092

    doodlingdba (2/10/2011)

    Thanks for all the ideas guys!!

    Maintaining two datasets is an inefficient way of doing things - but my data source is SSAS and i'm new to MDX so I would have to figure out how to write the query in the backend. That could take me a while and i needed a quick win...

    If you have ideas on the mdx syntax that would help!!

    Thanks

    Doodles

    No idea, I have like 5 minutes experience in SSAS, none of in in syntaxe besides that it's completely different from t-sql.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply