Sorting a dataset for excel export

martinher562

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 !

vremeni4

Hi,

It is a bit tricky to understand what you mean.
The key on the Excel buffer table is "Row No.,Column No.".

It looks like that the report reads the data in order "Location code, Item No., Variant Code, Cross-Dock Bin, Qty per Unit of measure, Bin Ranking' and it stores it in that order in Excel buffer. Hence the result.

In other words to correct this you only need to set the correct key (SETCURRENTKEY) to enforce the sorting, when you read the data from Bin Content.
This should be done onpreRepot or onPreItem or at the latest in after trigger.
On post report would be too late as all the work is already done.

I hope this helps.

vremeni4

Hi,

It is a bit tricky to understand what you mean.
The key on the Excel buffer table is "Row No.,Column No.".

It looks like that the report reads the data in order "Location code, Item No., Variant Code, Cross-Dock Bin, Qty per Unit of measure, Bin Ranking' and it stores it in that order in Excel buffer. Hence the result.

In other words to correct this you only need to set the correct key (SETCURRENTKEY) to enforce the sorting, when you read the data from Bin Content.
This should be done onpreRepot or onPreItem or at the latest in after trigger.
On post report would be too late as all the work is already done.

I hope this helps.

martinher562

@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

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

@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 !