Due to many and frequent questions on the topic of performance when exporting to MS Excel on RTC, I've summarized some of the answers in this blog to try and explain the impact of running MS Office automation objects on RTC .

Let's start with Stefan's excellent blog on automation objects and considerations running server/client side:

http://blogs.msdn.com/b/nav/archive/2008/12/03/automation-objects-in-microsoft-dynamics-nav-2009.aspx.

As you can see, it means that office automation must run client side, and to reiterate recommendation given:

General

• It is good practice to design your Automation interfaces to get as much work done in a single method invocation. And when consuming a component, try to use it in way that minimizes chattiness. This is utmost importance for the client side automation scenario.

Currently (and traditionally), some export to excel functions use office automation and table 370 (Excel Buffer), where export is run on row-by-row basis, meaning each row is exported and formatted in a separate call. If you are exporting hundreds or thousands of row, you're in effect maximizing 'chattiness', which will result in considerably slower performance when exporting data to excel (using standard application functionality and especially when running on RTC). This applies to functions like reports Aged Accounts Recevaibles (120) (export to excel), Customer/Item Sales (export to excel), then Budget: Export to Excel, Account Schedules: Export to Excel, Trial Balance: Export to Excel, RIM: Export to excel...  these will all run slower on RTC, as office automation is invoked for each row exported.

This does not apply to export using Send-to option (Send-to Excel), as this uses msxml6.0. This also doesn't include export of Analysis by Dimensions, as here all data are exported to a flat file (then downloaded to client), which is then formatted in few calls using office automation. This will not strain performance and will run about as quickly from RTC as from classic.

So what to do about functions that do see significant drop in performance (specially for larger data amount)? Well, there has to be several ways around this, here are some:

• If you are going to export large amounts of data to excel, why not try Jet Reports for your own reporting?
• Then, MSXML can run serverside (and thus runs fast) and you can create spreadsheet format file for excel. Example:  RIM functionality offers export to xml or Excel.  Both (by design) run client side as RIM is really intended for setup data, and that normally means a small amount of data and should run fine client side. But should you want to, it takes only few lines of code to switch to serverside running for xml, and then you can export large amounts of data to xml in same time classic client would run, then download and open generated xml file with excel (and import the same way back).
• You can potentialy use SAVEASEXCEL, and it is maybe the simplest solution, but this will have a limitation in number of records you can export (65K), so you can't use it for larger export. Also you'll need to modify each report separately and also imoprt if you want to use it to export budgets.
• Follow the example from Analysis by dimensions (codeunit 424), which exports data to a flat file, that is then afterwards downloaded to client and opened with excel. Excel file formatting is done in only few additional calls, so the performance on RTC is similar to that on classic client. Also, as a flat file size will only increase slowly with amount of data, you can process and export large amount of records quite quickly, created file is downloaded to client and office automation invoked only for file formatting and opening.
• ...

Window.OPEN(

  Text005 +

  '@1@@@@@@@@@@@@@@@@@@@@@@@@@\');

Window.UPDATE(1,0);

XlApp.ActiveWorkbook.Close(FALSE);

c := 9;

Tab[1] := c;

XlContinuous := 1;

XLLandscape := 2;

XLLinestyleNone := -4142;

XlEdgeBottom := 9;

CRLF := 10;

TotalRecNo := COUNTAPPROX + InfoExcelBuf.COUNTAPPROX;

NoOfRows := 0;

InitCharTables;

TextFile.CREATETEMPFILE;

FileName := TextFile.NAME + '.txt';

TextFile.CLOSE;

TextFile.CREATE(FileName);

TextFile.TEXTMODE(TRUE);

IF FINDFIRST THEN BEGIN

  FOR i := 1 TO STRLEN(xlColID) DO

    CurrAnsiCode := CurrAnsiCode + xlColID[i];

  RecNo := xlRowID;

END;

IF FIND('-') THEN BEGIN

  REPEAT

    IF xlRowID <> RecNo THEN BEGIN

      EVALUATE(CurrXlRow,RecNo);

      EVALUATE(NextXlRow,xlRowID);

      FOR i := 1 TO (NextXlRow -CurrXlRow) DO

      BEGIN

        Line[1] := CONVERTSTR(Line[1],CharsNavision,CharsWindows);

        TextFile.WRITE(Line[1]);

        Line[1] := '';

      END;

      CurrAnsiCode := 65;

      AnsiCode := 0;

      NewLine := TRUE;

    END;

    Window.UPDATE(1,ROUND(NoOfRows / TotalRecNo * 10000,1));

    FOR i := 1 TO STRLEN(xlColID) DO

      AnsiCode := AnsiCode + xlColID[i];

    IF ((AnsiCode - CurrAnsiCode) > 1) OR (((AnsiCode - CurrAnsiCode) > 0) AND NewLine) THEN

      FOR i := 1 TO  (AnsiCode - CurrAnsiCode) DO

        Line[1] := Line[1] + Tab;

    IF NumberFormat = '' THEN BEGIN

      IF EVALUATE(DecVal,"Cell Value as Text") THEN

        Line[1] := Line[1] + FORMAT(DecVal) + Tab

      ELSE

        Line[1] := Line[1] + "Cell Value as Text" + Tab

    END ELSE

      Line[1] := Line[1] + "Cell Value as Text" + Tab;

    IF Comment <> '' THEN

      Line[1] := Line[1] + Comment + Tab;

    NoOfRows := NoOfRows + 1;

    RecNo := xlRowID;

    CurrAnsiCode := 0;

    AnsiCode := 0;

    FOR i := 1 TO STRLEN(xlColID) DO

      CurrAnsiCode := CurrAnsiCode + xlColID[i];

    NewLine := FALSE;

  UNTIL NEXT = 0;

END;

TextFile.CLOSE;

FileName := ThreeTierMngt.DownloadTempFile(FileName);

XlApp.Workbooks.OpenText(FileName);

XlWrkSht := XlApp.ActiveSheet;

xlSheetName := SheetName;

xlSheetName := CONVERTSTR(xlSheetName,' -+','___');

XlWrkSht.Name := xlSheetName;

...

where following are local variables:

Name                                                     DataType             Subtype                Length

Tab                                                         Text                                                      1

c                                                             Char                      

CRLF                                                       Char                      

Window                                                  Dialog                   

TextFile                                                   File                        

FileName                                                Text                                                      1024

Line                                                        Text                                                      1024

Line2                                                      Text                                                      1024

ThreeTierMngt Codeunit                       3-Tier Automation Mgt.               

xlSheetName                                          Text                                                      100

i                                                               Integer                

CurrXlRow                                               Integer                

DecVal                                                    Decimal               

NextXlRow                                              Integer                

CurrXlCol                                                Integer                

AnsiCode                                                Integer                

CurrAnsiCode                                         Integer                

NewLine                                                 Boolean              

And that should be it really. Now all the export functionalities mentioned above (and the other exports using this table), should export to flat file (that will in the end result in an Excel export file), and relatively fast. Keep in mind this is just illustrating example, tested on W1 and only on the exports that are mentioned above.

One note thoguh, countries with 'space' as digit grouping symbol (some of  these are Norway, France, and more....) might experience the old issue of numbers being exported to text (as digit grouping symbol is not a proper space and ANSI sign for that is different then ASCII, so the resulting export is treated as text). If your regional settings have this digit grouping symbol (looks like a space), and you're using something similar to the code above, add the following lines to the above code to resolve that: (in same trigger, CreateCSV), add following lines:

...

TotalRecNo := COUNTAPPROX + InfoExcelBuf.COUNTAPPROX; 

NoOfRows := 0; 

ansispace[1] := 32;  //ADDED LINE

asciispace[1] := 160;  //ADDED LINE

...

   BEGIN

        Line[1] := CONVERTSTR(Line[1],CharsNavision,CharsWindows);

        Line[1] := CONVERTSTR(Line[1],asciispace,ansispace);  //ADDED LINE

        TextFile.WRITE(Line[1]);

        Line[1] := '';

        ...

where ansispace and asciispace are local variables Type Text, Length 1.

Now while fiddling with this I also run accross couple of things i'd like to comment on in std application, i'm just adding them here to be thorough.

IF Option = Option::"Update Workbook" THEN BEGIN
  IF NOT ISSERVICETIER THEN BEGIN
    FileNameEnable := TRUE;
    SheetNameEnable := TRUE;
  END ELSE BEGIN   //MODIFIED  LINE
  IF Option = 1 THEN show := TRUE ELSE  //ADDED LINE
    show := FALSE;  //ADDED LINE
  END;  //ADDED LINE
END ELSE BEGIN //MODIFIED  LINE
  FileName := '';
  UploadedFileName := '';
  SheetName := '';
  IF NOT ISSERVICETIER THEN BEGIN
    FileNameEnable := FALSE;
    SheetNameEnable := FALSE;
  END ELSE //MODIFIED  LINE
  BEGIN  //ADDED LINE
    IF Option = 1 THEN show := TRUE ELSE //ADDED LINE
    show := FALSE;  //ADDED LINE
  END; //ADDED LINE
END;

                    and request page should work as request from does on classic, avoiding automation errors if you're not familiar with this report's usage.

// Copyright © Microsoft Corporation. All Rights Reserved.

// This code released under the terms of the 

// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)

Just a small advert for the Microsoft All-In-One Code site. I will let the site introduce itself:

• Are you frustrated by the lack of code samples for a certain programming task?
• Have you ever struggled to quickly get started with a technique?
• Have you expected someone to write code samples for you based on your requests for free?
• Is a one-stop code sample library for all Microsoft development technologies attractive to you?

If the answer of any of these questions is "Yes", then read the same questions (and the answers! (or at least some of them!)) here:

Microsoft All-In-One Code Framework (http://1code.codeplex.com/) 

The main features of the site are:

•   - Get Code: Browse the site and take a look. Download the whole Framework and browse 100s of projects. Or install the "Sample Browser" which lets you search projects.
•   - Request Code: Submit sample ideas for what sample code you would like to see.

Follow news and updates from this blog:

http://blogs.msdn.com/b/codefx

Of course the main focus is on core MS programming languages like c#. But there is one NAV sample there too: A project with NAV Web Service Sample code. It contains sample code for all the standard functions you get with a NAV Web service, to copy and paste and hopefully help when you forget the exact syntax in c#. You can download this sample from this link:

Call Dynamics NAV Web Services (CSDynamicsNAVWebServices)

Lars Lohndorf-Larsen

Dynamics NAV Support EMEA

Hi everyone! Have you used a Knowledge Base (KB) article to help you answer a question? Check out this list of the KB articles that are most viewed for Microsoft Dynamics NAV:

978857 Incorrect VAT entries are generated when you post a credit memo to apply an invoice that contains unrealized VAT in Microsoft Dynamics NAV

2266182 The planning results are illogical when you run the "Calculate Regenerative Plan" function on a planning worksheet if you use safety stock in Microsoft Dynamics NAV 2009 SP1

979109 Error message when you try to invoice a sales order in Microsoft Dynamics NAV 2009 Service Pack 1: "Currency Code '' does not exist"

2250508 The document number is not incremented on a journal if the first line is an empty line in Microsoft Dynamics NAV

940807 Overview of platform rollup updates that are available for Microsoft Business Solutions - Navision 3.7 through Microsoft Dynamics NAV 2009

By the way, you’ll find it’s easier than ever to locate KB articles for Microsoft Dynamics NAV. You can continue to use PartnerSource and CustomerSource to find KB articles. In addition, you can now search, find, and view Application Hotfixes and Problem/Error/Resolution KB articles using a search engine like Bing. Please note that Platform Hotfixes are only available from CustomerSource and PartnerSource.

Here are a few search tips for you. Use the words “Microsoft Dynamics NAV” in your search to help narrow down articles to only the NAV product and no others. Also, if you want to limit your search to Knowledge Base articles, use the abbreviation “kb” in your search. For example, if I want to find information about inventory adjustment posting dates in Microsoft Dynamics NAV, in Bing I’d search on “Microsoft Dynamics NAV kb inventory adjustment”. When I do that, one of the first things that pops up is KB article #952996 titled “Information about inventory adjustment posting dates in Microsoft Dynamics NAV”. 

Try it and see what you think!

Lynn Mueller

Microsoft Dynamics

Lead Knowledge Engineer

We are pleased to announce that Jet Reports Express for Microsoft Dynamics NAV will be released September 1^st and be available for download at Partner Source and Customer Source at the below listed links.

Do you know what makes this new ad hoc reporting tool really great?  It’s that it’s so easy to use for just about anyone who is familiar with Microsoft Excel. The intuitive interface of Jet Reports Express and simple formulas in Excel make it easy to create high-impact reports quickly.

Let me share with you a few of the things customers can do.

First, it can make life easier for finance managers. Now they can easily create detailed profit and loss reports with updates on a daily, monthly, or quarterly basis. Or they can quickly monitor accounts receivable KPIs in terms of days outstanding, past collection performance and more. And, they can run financial reports from the General Ledger by using the GL function and distribute them on a daily, weekly or monthly basis.

Second, sales managers can more quickly build product sheets that pull current pricing and specifications, and instantly update and release important changes. They can also create a sales forecast calendar that tracks sales on a monthly basis, with budget projections for future sales.

Third, management can view key performance metrics across all areas of the organization in real time and track consolidated profit and loss by division and company and follow projected income compared to budget and forecasts. Best of all, it’s easy for partners to demo all of these great capabilities for prospects.

September 1^st, 2011 – you will find links to the product download from these two pages:

• Partner Source: Jet Reports Express for Microsoft Dynamics NAV
• Customer Source: Jet Reports Express for Microsoft Dynamics NAV

If you provide hosting services for your customer and license Microsoft Dynamics NAV using a Services Provider License Agreement (SPLA), by default your customer will not have their own customer source or a VOICE account. Depending on how your customer signed up for Dynamics Online payment services, a VOICE account may be required for them to use Dynamics Online beyond an initial 30-day trial period.  If this is the case, the customer will see the  following message in the Microsoft Dynamics Customer Portal when they click Service list:

Your services will be suspended within 30 days unless your support and service plan for Microsoft Dynamics is purchased.

To obtain VOICE access for the customer, contact Voice@microsoft.com and request that the customer account be VOICE-enabled. In the request, indicate that your customer is a Microsoft Dynamics NAV SPLA license customer using the Dynamics Online payment service and indicate that their payment service account requires VOICE-enabling to work with this service. VOICE-enabling is provided at no additional charge.

Recently I run into a problem where NAV service did shut down every time I did add or delete a user.

The event log explain the problem like bellow. So it obvious that we of some reason can’t connect to the database.
Type: System.Data.SqlClient.SqlException
Class: 14
LineNumber: 1
Number: 916
State: 1
Source: .Net SqlClient Data Provider
ErrorCode: -2146232060
Message: The server principal "XX\YY" is not able to access the database "ZZ" under the current security context.

When looking at the security for database “ZZ” I found the same problem as the event log was pointing at, no trace of user “XX\YY” with gives the NAV service access to the database. It turns out that when adding or deleting a NAV user all user privileges is re-synced, and any user not part of the new list is deleted. And if the NAV service account is not part of the user list, NAV service can’t longer connect to database.

The solution is very simple. You have to make sure that the service account is added as a user in NAV under Tools - Security - Windows Logins. Followed by syncing logins.