Interactive Sorting on NAV 2009 Reports

In this blogpost I would like to explain what Interactive sorting is all about … and give you a few tips to go "beyond" .. uhm .. I would actually just like to explain how you can use this on grouped data as well. Claus Lundstrom brought it to my attention, and indeed quite interesting to blog about.. .

All of you that have had a presentation about Dynamics NAV 2009 Reporting, has probably seen "interactive sorting" on reports. For those of you that don't have a clue of what I'm talking about .. well, just print the Customers Top 10 List from the default Order Processor's Role Center. On that preview, you get a list of customers, and the possibility to "interactively" sort the list by pushing the indicated icons on the report:

In the report above, you can sort on "No.", "Name", "Sales (LCY)" and "Balance (LCY)". This is a really cool feature, but …

How does it actually work?
How do I do this on my own list? Well, let's create a very simple report. A list on the Item Ledger Entry. My report sections in the C/SIDE report designer look like this:

(I assume you all know this is going to determine my dataset … ?)

I just do a "suggest layout", as this is quite easy to get started … I'm a lazy developer, and this avoids me for dragging the fields to my report myself… . You'll see that the suggested layout is quite the same in Visual Studio:

It creates a table with the fields I put on my sections, and that's it. To add the interactive sorting on my first column ("Item No."), I right click on the heading (first row), and go to the tab "Interactive Sort" (what's in the name…):

As you can see, I check the checkbox to add an interactive sort action to this textbox (which means, pressing that icon on that textbox, will sort an expression). Which field has to be sorted, I specify in the next textbox. I select the Value of "Item No.".

Note: a lot of people pick the Caption by mistake (because the name of the field is almost the same as the value… ). Be aware of this.

That's it. Save all and run your report (remember, you can run your report with the shortcut "DynamicsNAV:////runreport?report=60000" for report 60000). Press the icon, and you'll see the different sortings.

Unsorted list

Sorted ascending

Sorted descending




The complete list looks like this:

Anyway, a lot of explanation and screenshots for a very simple thing to do. Too many, actually, because you can do this in a matter of less then a minute, can't you?

Now, what about grouped reports? Am I able to put a sorting on that as well?

That's slightly more complicated. Let's start by adding a group to my report. I'm going to group my Item Ledger Entry on "Item No.", and I want a total per Item. You can do this in various ways. I always try to do it using the properties of my control (in this case the table). I noticed that this way, groupings in matrix boxes just works a lot better… (but that's for a later blog post). If you select the table, and get it's properties, you'll find a "Groups" tab, where you can do your grouping:

Just click add, and fill in the "Item No" value (not the caption!) in the expression like this:

Press OK until you go back to design. You'll see a grouping row was added to your table. Now, in stead of printing my "Item no." on record level, I want to print it on Group Level. That's why I remove that field from the lowest level, and place it on group level. I also drag the Quantity field to group level to have my total (notice it automatigically add's "Sum" in front - which is good)). This is the end result:

OK, save it all, and test your report!

As you can see, my grouping is working (except for the fact that I forgot to change the font of my Item No., but anyway … Smile). But try pressing that interactive sorting again. No luck, huh? Well, interactive sorting on groups just doesn't work ..




Kidding! (lame, I know).
It does work, but it needs some more attention. Just go back to design, and to the properties of the interactive sorting on that textbox. You need to specify the scope (being the grouping):

Try again and you'll see it works.

And what if I want to interactively sort on my grouped totals?

What do I mean with this? Well, in my example, I have a total of the quantity per Item. I want to sort my list, not on quantity, but on the totals of the quantity … on group level that is. Well, let's go back to the design, and to the text box where I want to activate the interactive sorting on (being the header of the quantity-field).

The trick here is that you fill in the right expression. It's not just selecting the Quantity field, but the Sum of the quantity. Exactly the same expression as you can find on group level in the table. This is the result on the report:

Just like expected and wanted. The sort on Item and quantity works. The only thing the report might need, is a little bit of design changes. Add some colors (may be conditional colors?), stuff like that … . But that's up to you, because I'm SOOO bad in all that has to do with desigin. You can download the report here and start working on it Smile.

By the way … I might not be using the right terms, words, vocabulary regarding all this reporting/rdlc/… - stuff. Well, as you know, I'm not a reporting guru. I just love to play with it :-).

Comment List