none
Top N using XML Dataset and Datasource

    Question

  • Hi

    I have XML dataset in SSRS and query runs fine, but I want to limit result to 5 only.

    When I add the result to a table/matrix in ssrs and use Top N on the feild I want to get top 5 result, it doesn't seem to work.

    Is there a way I can do Top N in XML dataset?

    Thanks!

    Tuesday, July 09, 2013 5:50 AM

Answers

All replies

  • Add an ID field in the XML. Then you can use the Filter option as below,


    Regards, RSingh

    Tuesday, July 09, 2013 7:26 AM
  • Hmm...I already have ID field in my dataset - this ID field is the Unique Identifer for dataset records. This will result only first 5 records showing and not based on the field I want to show top 5.

    I have dataset with a list of risks based on risk exposure rating. I want to show top 5 risks regardless of its ID.

    Tuesday, July 09, 2013 7:42 AM
  • Another option is to use Expression.

    Add a new column ID (leftmost column) in the table with cell expression as =RowNumber("DataSet1").

    Dataset1 is the name of my Dataset. Next select the row(bold rectangle) as below, right click and properties. Expand Visibility property and set the expression as  =IIf(RowNumber("DataSet1") > 5, True, False)

    Refer the below screenshot on the right.


    Regards, RSingh

    • Marked as answer by SJ_EPM Friday, July 12, 2013 3:41 AM
    Tuesday, July 09, 2013 8:55 AM
  • Thank you, but that only returns two rows.

    Tuesday, July 09, 2013 12:24 PM
  • Apologies! That worked like a charm. Waaaayyy too late for me in the day to be doing this.

    Thank you thank you thank you!!!

    • Marked as answer by SJ_EPM Friday, July 12, 2013 3:40 AM
    • Unmarked as answer by SJ_EPM Friday, July 12, 2013 3:41 AM
    Tuesday, July 09, 2013 1:01 PM
  • Hi again,

    I noticed one thing though...there is a gap between the tables. Risk table is confgured as above and there is another table after that. When I deploy the report, I can see a bit of a gap between the two tables...is it because Risk table only hides the data, but the data is still there and moves the table accordingly?

    Is there any way around this? As I know project risk can grow up to 50-100 in this organisation...I really don't want 45-95 row gap!

    Thanks!

    Wednesday, July 10, 2013 2:38 AM
  • Apologies for the late response. That works! I didn't need to add a rectangle though just the row visibility config.

    Thank you very much!

    Thursday, July 11, 2013 7:05 AM
  • How do I mark this answered??? I can't sem to find this feature anymore?
    Thursday, July 11, 2013 7:32 AM