Suppose you have a boolean in your table, and you want to visualize it in your report as a checkbox. This is not really straight forward … but that doesn't mean it has to be difficult.
If you simply put the value of a boolean in a table, you get something like this as result (in this case, the field "Invoice" is a boolean):
This is not what you want in a report, is it? A checkbox would be lots "sexier" (for the geeks) then this … at least that is my (indeed .. a geek) opinion. Let's try to do that.The only thing you have to do is change the font of that checkbox in "Wingdings":
Then change the expression in something like this:
=IIF(Fields!TimesheetLedgerEntry_Invoice_.Value = "1",Chr(0254),"o")
The yellow part is going to change the "true" value into the characters within wingdings that represent a checked or unchecked checkbox. The result should be something like this:
A while ago, I blogged about the upcoming changes regarding 2010 VAT changes … and where to find information (here you can find my post).
Well, I just wanted to let you know there is an update … . You can find the new version of the files on Partnersource and Customersource.
Furthermore, for partners, there is a new dedicated page on partnersource, which you can find here.
Up to the next update...
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.
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 … ). 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 .
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 :-).
Did you ever wonder what has been changed from one version to another? Questions like: OK, my customer is on 3.10 .. What will he get when he upgrades to 4.00 .. or what if he upgrades to NAV 2009. In the old days, you had to rely on mails, change docs from one version to the next, your memory (which only contains the "cool" features, or only the features you have experience with), … . Now, we've got the "Feature Enhancement Documents" on partnersource. And the list of documents is quite explanatory…:
3.70 to 2009 Feature Enhancements Document
4.0 to 2009 Feature Enhancements Document
2.6 to 4.0 Feature Enhancements Document
3.01 to 4.0 Feature Enhancements Document
3.10 to 4.0 Feature Enhancements Document
3.60 to 4.0 Feature Enhancements Document
Keep in mind that the functionality in 5.0 is the same as in 2009. This means that with these documents, you cover versions from 3.10 to NAV 2009 SP1. How many years of database versions is that? About 10 years?
Next time your customer asks "Upgrade? What's in it for me?" … now you can give him a full, complete answer.
A few weeks ago, I attended a course about NAV 2009 Reporting: a course that was especially designed to explain what is possible in NAV 2009 reporting. It's an official course of Microsoft, written by the Belgian CPLS "Plataan" and guess what … given to us by that same dude of Plataan that wrote it: Steven Renders. Who's better for gettin' it from? Dude, I tell you … it's not my habit to advertise on my blog … and it's not my intention to do it, but dudes, I tell you … really … see it like a recommendation … but when you're planning to get yourself trained … think about flying Plataan over to get the course from.
During that course, I've been writing down some tips & tricks of which I'm going to "bother" you about in the coming blog posts (not all right after each other … but you can expect a number of small tips and tricks in the next months ). Last week, I met Claus Lundstrøm … Program Manager of all about NAV 2009 Reporting stuff, and author of the nav reporting blog (which by the way has been merged with the Microsoft Dynamics Nav Team Blog). I was talking to him that I noticed that many partners are choosing to "postpone" getting trained in NAV 2009 Reporting, just because of the fact that Microsoft added the possibility to print classic reports from the Role Tailored Client. I noticed it at the SP1 training course a few weeks ago, and I noticed it again at a recent gathering of partners. He encouraged me to do this series of small Tips & Tricks (just like he is doing on his blog for some time now).
OK, enough chit chat … where's the tip?
First tip is actually already mentioned on the blog of Claus, but may be a little bit hard to find (at least, I spent some time searching for it .. but that doesn't mean anything, off course … since I use bing, search take a little bit longer, it seems … or is it just me? ). In NAV 2009 SP1, they made it possible to use Visual Web Developer 2008 to design reports (which is free, by the way). If you're planning to use this, don't forget to install the "Microsoft Report Viewer Add-on for Visual Web Developer 2008 Express Edition", else, you'll have a damn hard time designing your reports in xml-format .
That's all. Very small, simple … yet useful tip. I chose to start off with this one, because … if you don't have an environment, you can't even start designing the reports … can you?
Up to the next!