Top N using XML Dataset and Datasource


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


    Tuesday, July 09, 2013 5:50 AM


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_PPM 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_PPM Friday, July 12, 2013 3:40 AM
    • Unmarked as answer by SJ_PPM 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 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!


    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