Sorting a dataset for excel export

martinher562
martinher562 Member Posts: 50
in NAV Three Tier
Hello NAV experts,

I have a report that in VS, I have it sorted by Item and then Bin Code. Please note that this report is using Bin Content table.

When I do an export to excel using the excel buffer table, it sort it differently because table sort is by: Location code, Item No., Variant Code, Cross-Dock Bin, Qty per Unit of measure, and then Bin Ranking.

After I run the report - in the about page, I can view the data set and the excel export goes by the order of that dataset.

My question is: how do i sort the final dataset ? Maybe I can sort it in the OnPostReport.

In SQL and VB syntax - all I would do is Order by at the end of the statement/function/project code.

Thank you !

Best Answer

Answers

  • martinher562
    martinher562 Member Posts: 50
    @vremeni4 Thank you for the tip. It did work and sorted what I need. However, I keep running into problems not being able to manipulate the final /report data.

    When you run a report in NAV2015 - if you preview the report. In the report viewer, you can go to about this page and you can view the actual data being used for the report to be generated. In SQL or VB world. I can just do order by or where field = or <> and the dataset is moified. however, NAV makes it so difficult.

    Please see below to what I mean to viewing the dataset.
    https://msdn.microsoft.com/en-us/dynamics/nav/dn848439.aspx
  • vremeni4
    vremeni4 Member Posts: 323
    Hi,

    You have two options to change the sorting-order of a report or to add a filter.
    You can do this
    1. in NAV on the dataset level, or
    2. you can do it in "Report Viewer" in report layout.

    Which option is better depends on the individual case.
    For example :
    report shows the data sorted on the column Item, but you want to change to be Description. This is easier to do In report layout
    2. the reports is sorted on Item, but you want to use Bin content which is another table.
    In that case changing dataset in NAV might be easier.
  • martinher562
    martinher562 Member Posts: 50
    @vremeni4 Modifying and formatting the data in the report layout I do know how to do, but I run into a problem when I am trying to export to excel using the excel buffer table. This is where I need the actual data set to be formatted a certain way and the filtering on the dataset level at the dataset designer does not allow much flexibility (or maybe it does, but my lack of knowledge does not allot me to move forward). Thank you !

Categories