RTC reports and upgrades

Just got back from Claus Lundstrom’s RDLC class. I learned a lot of interesting tips and techniques. Overall NAV 2013 report development is a huge improvement over NAV 2009 R2, but there are a few gotchas in Visual Studio 2010 that work differently from VS 2008 and take a little more work.

One example of this is repeat report headers. The obvious place no longer works, and your new pages do not have headers. Instead you have to bring up the advanced mode in the groups area of the layout. I discovered this by search for it in google under Visual Studio. Since this is Visual Studio, you can search under NAV or Visual Studio for your answers. Claus covered this topic as well in his class.

There were many other things I had never thought of. I do not want to give away his class for free, so I will just add, that the knowledge he shared, will make all the attendees much more productive.

I hope Claus won’t mind if I mention one simple trick. As long as you keep the report layout up, you can undo your changes all the way back to the beginning of your report layout session.

I will be heading out to Claus’s RDLC class in Atlanta where I am looking forward to learning advanced techniques from the expert, plus meeting fellow-minded developers.

I have really enjoyed using NAV 2013 reporting, but there is more to learn.

The class is being held in Lanham’s offices which I have not been to since 2007, so I am looking forward to that as well.

Based on Claus’s blog entries to date, I am thinking I need to bite the bullet and change all my document forms to use his method - I hope to accomplish that during his class.

(http://mibuso.com/blogs/clausl/2012/10/03/simplifying-document-reports-in-nav-2013/)

Since the layout of NAV 2013 reports uses Visual Studio, you can use code in the report - Visual Basic, not C/AL. In addition to the embedded examples, you can use any supported Visual Basic functions and code as well, within the limitations of the report designer.

I have found a few sources, I will mention one now (http://msdn.microsoft.com/en-us/library/dd220516.aspx). I will compare notes with the others and provide an update when I get back.

Claus is a great of example of what an expert can do. I hope to be a good example of what a developer can do if he/she listens to and learns from the experts.

I have been spending quite a bit of time self-training on the new NAV 2013 reporting, and it really is a huge upgrade from the previous version.

One of the things I like best, is we are now tied into the relentless Microsoft SQL Server upgrade and improvement cycle. Every time NAV updates the version of Visual Studio it supports, we will get huge improvements to our reporting capabilities.

We also have to get used to having our documentation in multiple places.

For reporting we have:

NAV 2013 DEV I training manual - one chapter.

NAV 2013 Reports training manual (multiple chapters and exercises).

SSRS online materials and numerous books.

For online for SQL Server 2008R2: http://msdn.microsoft.com/en-us/library/hh995057(v=sql.10).aspx

SQL Server 2012: http://msdn.microsoft.com/en-us/library/hh965699.aspx

We will also need to consult VB materials for added code.

One thing to keep in mind with the code stored in the report properties code section is that it operates much like calling a function in NAV [function name (parm1, parm2, …). You then use these in fields and properties in the report layout.

For example, you could use a function to examine a phone number and return a pleasing format - e.g. 5554441111 –> (555) 444-1111.

I am impressed by how easy it is to generate a basic tabular or matrix report with totals, drill downs, etc.

There is much to learn if you are used to developing Classic reports, but well worth it, and once you are up to speed, it is so much easier and faster.

I will be posting some more report samples soon.

Sales Item HistorySales Item HistoryIn Classic NAV, combining posted invoices and credit memos typically involved the use of a temporary table to build the sales lines from the sales invoice line table and the sales cr.memo line tables.

Because the NAV uses a flat result set, it is possible to combine the lines without the use of a temporary table.

What my example has, is a customer dataitem followed by 3 indented dataitems (sales invoice line, sales cr.memo line, and the sales line).

I have one column in each (otherwise we end up with just 1 row per customer), and then added global variables to handle the common dataitem fields (item no., quantity, unit price, and line amount. In each dataitem, I populate these fields with a negative for the credit quantities and amount.

Since there is one row produced per combination of customer/sales invoice line, customer/sales cr.memo line, and customer/sales line, we now have a report by customer of all the items that customer has ordered - including invoices, credits, open orders, etc.

https://docs.google.com/open?id=0BwJ-6_iDMdRONVZYOGJ2NnRIMzQ

https://docs.google.com/open?id=0BwJ-6_iDMdROTElBc0dJWTJFa3M

report 50003 text and object

Much to my disappointment, I discovered that reports did not allow using query as a data item. So I started thinking on how to get around it.

Then I remembered our old friend, the integer table.

If you build a report using the integer as the data item, you can then build code to process a query one row at a time for each integer. After the query has read the last row, you simply execute a CurrReport.BREAK.

I have attached a simple sample query and report that show how easy this is to do.

Step 1: choose or design a query

Step 2: design new report

Step 3: Add DataItem Integer - change the name to something meaningful (CustomerOrderTotals)

Step 4: define global variables to be used for each query field you will be using in the report and the Query Name

Step 5: under DataItem define your columns using the above defined global variables

Step 6: View C/AL Code (F9)

Step 7: OnPreDataItem trigger add code - QueryName.OPEN;

Step 8: OnAfterGetRecord trigger - add code

IF NOT QueryName.READ THE CurrReport.BREAK;

Variable1:=QueryName.Variable1;

Variable1:=QueryName.Variable2;

repeat lines for remaining fields

Step 9: exit code and view layout

Step 10: design your report in the Visual Layout editor, exit, and save, then save and compile your report and run.

For the past few weeks I have been immersed in service manager  - a NAV module that is 90% genius and 10% sheer stupidity.

One of the most “curious” aspects is the three tier service tables implemented as two pairs of two tiers - service header and service item line, and service header and service line.

The service lines are grouped by the same service item line which means it is possible to break NAV by inserting 16 serice lines for the same service item line. Extrememly bad design. I have not looked at NAV 2013 to see if they fixed it.

When you post a service order - the service shipment gets the header and service item line, the invoice (if you invoice) gets the header and service line.

You don’t get invoices if you consume.

Good training manual - explains a lot of how it works, except for the technical details.

Don’t implement without a devloper - a techno functional person is best who can handle the implementation and development changes.

I went to technical training on NAV-X last week.

What is NAV-X? It is a package of various ISV add-ons to make a “all in one” distribution offering for NAV.

Will it succeed? I don’t know - I am not handling the marketing and sales side of it. It is an interesting idea though. Group the “best” distribution add-ons and offer them as a package, then get leading solution partners around the country (USA) to sign up. If it succeeds, then I expect we will see similar packaging for other vertical markets.

Since the demo uses Windows Server 2008, I used Virtual Box from Oracle as my 64 bit virtual machine on my Windows 7 64 bit machine. It works really well, if you are looking for a 64 bit virtual machine that will run on 64 bit operating systems under Windows 7.

I understand that Windows 8 will offer the 64 bit virtual machine environment. Since I don’t have a touch screen, I am going to request one when Windows 8 is released to the OEM market.

My biggest takeaway from the training is that we in the partner channel need to virtual machine savvy since I believe this will be one of our most important tools in dealing with multiple client and sales environments.

I got home too late on Wednesday to post, so here is my final report.

Keynote by Colin Powell - very good. If you weren’t there, I recommend you find a way to listen to it. A few interesting IT points:

1) The army sent him to study data processing for his advanced degree

2) When he arrived at the State Department they were still using Wang which had been out of business by then for 8 years, so he got them upgraded.

3) He insisted on having the web sites up to date.

(Subtle dig at current administration - Libya was still showing Gadaffi in charge.)

Very entertaining and interesting overall. Great choice Microsoft!

The best session for me for the day was the very last one on reporting. By that time only the people interested in NAV 2013 were left, so most of the time was spent on the upcoming release.

One of the key takeaways, is that many reports need to be redesigned to take advantage of the new environment and minimize the speed problems of the multi-tier.

If you can persuade users not to print everything, look at having summarize layers that you toggle individually to expand.

If you have a 1000 page accounts receivable report and show the summary lines, then the user can toggle on the detail when needed.

You can do dashboards in the role center - you can also do them in reports. I can see reports being very useful in preview mode only. (Charts, drill downs)

David Worthington was the presenter and did an excellent job.

I also attended sessions on costing (BOM gets some improvements) and role tailoring (nothing new).

Last session proves that it is worthwhile staying to the end.

Dan Rimmelzwaan, Jason, and Kim hosted a session on database optimzation & best practises. There were some very knowledgeable users, but some with some meaningful questions. There were many useful tips - I refer you to Dan’s Youtube videos for more details - http://www.youtube.com/user/DenSterClips?ob=0&feature=results_main

Other than configuration, the biggest operational items that came up were: don’t shrink log files (avoids fragmentation) and index tuning. Since this is an overview, I will refer you to Denster’s Youtube channle for more details.

More NAV discussion - OData for web services - even for those not developing the web services, we should learn all about this and how and where to use it.  http://www.odata.org/

Microsoft is committed to supporting compettive devices like the iPhone, iPad, Android.

NAS will be part of the service tier and the job queue will be used as a background processor - implemented properly, this will reduce table locking problems.

Web services can use pages, codeunits, and now queries (Navision defined queries in the object browser). Queries look very easy to create, but limited compared to what we can do directly in SQL Server, but because they are designed in NAV, they understand NAV business logic.

Final presentation of the day was NAV 7 and the Sharepoint Client.

The Sharepoint client reminds me of the Employee Portal in how you set  it up in Sharepoint, but it lets you use all the pages (if I understand it right).

The Sharepoint Client and the browser client are more limited than the Windows (formerly RTC) client, but still very functional. (I have not seen pricing yet.)

The web client supports multiple browsers including IE. Microsoft is getting really open these days!

Monday keynote at Convergence.

Main message is how Windows8 will run across all devices - phones, tablets, and PCs.

Call me a skeptic. Microsoft tried this 10 years ago and failed. Last time they pushed Windows from the PCs (with it’s 90% market share) into handheld devices. Now they are pushing the phone look and feel (with it’s 1% market share) into tablets and PCs.

As a person who makes his living developing and supporting Microsoft software, I sure hope they get it right and don’t ruin Windows.

But I am not giving up my iPhone, Kindle, and will probably buy the new iPad.

Convergence is dominated by AX this year. Kind of disappointing for NAV, though I guess the Solomon crowd is used to this by now.

NAV “7″ is now NAV 2013 and it still looks on time for Q4. We have been promised a beta by May. They did not specify partner only, so I wonder if they will make this available on Customersource as well.

Since nothing was presented under NDA, I can reveal all - which is not as much as Directions, but still a good amount.

Forms - we can import them, but only for conversion.

XMLports have been upgraded so they should cover all the old dataport requirements as well.

Processing reports will use the new RTC designer, but like Classic, have no layout (sections).

Queries - a little limited this release. Can run them from the browser and use them to generate data for ODATA (web guys can explain this better).

Report designer - Visual Studio 10 or equivalent - can now put data fields directly in the headers instead of the kluge job of burying them in hidden fields and then using them as reportitems in the header.