• Microsoft Dynamics NAV Team Blog

    How to create/read xml file from Microsoft Dynamics NAV without using xmlports

    • 9 Comments

    Periodically we receive support requests about XMLport errors, which really are missing features in xmlport's functionality.
    However most of these requests are rejected because: xmlports are not supposed to fulfill all possible xml standard scenarios.
    So there always will be xml file which can't be created or readed by NAV xmlport.
    Possible workaround could be to use XML DOM automations.

    Create it by codeunit:

    ----------------------------------------

     OBJECT Codeunit 50052 xml create
    {
      OBJECT-PROPERTIES
      {
        Date=03/24/10;
        Time=[ 1:33:31 PM];
        Modified=Yes;
        Version List=;
      }
      PROPERTIES
      {
        OnRun=BEGIN
                CREATE(xmlDoc);
                xmlMgt.SetNormalCase;
                xmlProcessingInst:=xmlDoc.createProcessingInstruction('xml','version="1.0" encoding="UTF-8" standalone="yes"');

                CurrNode := xmlDoc.appendChild(xmlProcessingInst);
                CurrNode := xmlDoc.createElement('soapenv:Envelope');
                CurrNode := xmlDoc.appendChild(CurrNode);


                xmlMgt.AddAttribute(CurrNode,'xmlns:soapenv','http://schemas.xmlsoap.org/soap/envelope/');
                xmlMgt.AddAttribute(CurrNode,'xmlns:mbs','http://www.microsoft.com/mbs/xml');

                xmlMgt.AddElement(CurrNode,'soapenv:Header','','soapenv',NewChild);
                  CurrNode:=NewChild; //One level deeper
                  xmlMgt.AddElement(CurrNode,'soapenv:Body','','soapenv',NewChild);
                       CurrNode:=NewChild; //one level deeper
                       xmlMgt.AddElement(CurrNode,'mbs:enumeration','','mbs',NewChild);
                            CurrNode:=NewChild; //one level deeper
                            xmlMgt.AddElement(CurrNode,'mbs:table','Customers','mbs',NewChild);

                         recCustomer.SETRANGE("No.", '10000','20000'); //Filter only few records
                         IF recCustomer.FINDFIRST THEN BEGIN
                           REPEAT
                            vName   :=recCustomer.Name;
                            vNo     :=recCustomer."No.";
                            vContact:=recCustomer.Contact;
                              recCustomer.CALCFIELDS("Balance (LCY)");
                              vBalance:= FORMAT(recCustomer."Balance (LCY)");
                            vSPcode :=recCustomer."Salesperson Code";


                            xmlMgt.AddElement(CurrNode,'mbs:Customer','','mbs',NewChild);
                                  CurrNode1:=NewChild; //One level deeper, but keep current level too
                                  xmlMgt.AddElement(CurrNode1,'mbs:CustomerAuthentication','','mbs',NewChild);
                                         CurrNode2:=NewChild; //One level deeper to sublevel
                                         xmlMgt.AddElement(CurrNode2,'mbs:No',vNo,'mbs',NewChild);
                                         xmlMgt.AddElement(CurrNode2,'mbs:Name',vName,'mbs',NewChild);

                                  xmlMgt.AddElement(CurrNode1,'mbs:CustomerData','','mbs',NewChild);
                                         CurrNode2:=NewChild; //One level deeper to sublevel
                                         xmlMgt.AddElement(CurrNode2,'mbs:Balance',vBalance,'mbs',NewChild);
                                         xmlMgt.AddElement(CurrNode2,'mbs:SalespersonCode',vSPcode,'mbs',NewChild);
                                         xmlMgt.AddElement(CurrNode2,'mbs:Contacts','','mbs',NewChild);
                                             CurrNode1:=NewChild;//One level deeper
                                             xmlMgt.AddElement(CurrNode1,'mbs:Contact',vContact,'mbs',NewChild);


                            CLEAR(vName);
                            CLEAR(vNo)  ;
                            CLEAR(vContact);
                            CLEAR(vBalance);
                            CLEAR(vSPcode);

                           UNTIL recCustomer.NEXT=0;


                           xmlDoc.save('D:\xmlFile.xml');
                           CLEARALL;
                           MESSAGE('xmlFile.xml is created');
                         END;
              END;

      }
      CODE
      {
        VAR
          xmlDoc@1000 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 6.0:{F6D90F11-9C73-11D3-B32E-00C04F990BB4}:'Microsoft XML, v6.0'.DOMDocument";
          CurrNode@1003 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 6.0:{2933BF80-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v6.0'.IXMLDOMNode";
          CurrNode1@1005 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 6.0:{2933BF80-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v6.0'.IXMLDOMNode";
          CurrNode2@1013 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 6.0:{2933BF80-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v6.0'.IXMLDOMNode";
          NewChild@1004 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 6.0:{2933BF80-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v6.0'.IXMLDOMNode";
          xmlProcessingInst@1001 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 6.0:{2933BF89-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v6.0'.IXMLDOMProcessingInstruction";
          xmlMgt@1002 : Codeunit 6224;
          "---- Variables----"@1006 : Integer;
          recCustomer@1012 : Record 18;
          vName@1007 : Text[30];
          vNo@1008 : Text[30];
          vContact@1009 : Text[30];
          vBalance@1010 : Text[30];
          vSPcode@1011 : Text[30];

        EVENT xmlDoc@1000::ondataavailable@198();
        BEGIN
        END;

        EVENT xmlDoc@1000::onreadystatechange@-609();
        BEGIN
        END;

        BEGIN
        END.
      }
    }

    ---------------------------------------

    And can be read by codeunit: 

    OBJECT Codeunit 50050 xml read
    {
      OBJECT-PROPERTIES
      {
        Date=03/24/10;
        Time=12:25:26 PM;
        Modified=Yes;
        Version List=;
      }
      PROPERTIES
      {
        OnRun=BEGIN
                ffile.OPEN('D:\XmlFile.xml'); //this must be your file name
                ffile.CREATEINSTREAM(strInStream);

                IF ISCLEAR(xmldomDoc) THEN CREATE(xmldomDoc);

                xmldomDoc.load(strInStream);

                xmlNodeList1 := xmldomDoc.getElementsByTagName('mbs:Customer');

                ii:=xmlNodeList1.length();

                FOR i:=0 TO xmlNodeList1.length()-1 DO BEGIN
                 xmldomElem1:= xmlNodeList1.item(i); //mbs:Customer
                 IF xmldomElem1.hasChildNodes() THEN
                    BEGIN
                      xmlNodeList2:= xmldomElem1.childNodes();
                      IF NOT ISCLEAR(xmlNodeList2) THEN
                      xmldomElem2:= xmlNodeList2.item(0); //mbs:CustomerAuthentication
                      IF NOT ISCLEAR(xmldomElem2) THEN
                      IF xmldomElem2.hasChildNodes() THEN
                         BEGIN
                         xmlNodeList3:= xmldomElem2.childNodes();
                         IF NOT ISCLEAR(xmlNodeList3) THEN
                          xmldomElem3:= xmldomElem2.firstChild();//mbs:No
                         IF NOT ISCLEAR(xmldomElem3) THEN
                          txtNo:=xmldomElem3.text();
                         xmldomElem3:=xmlNodeList3.item(1); //mbsName
                         IF NOT ISCLEAR(xmldomElem3) THEN
                          txtName:=xmldomElem3.text();

                      xmldomElem2:= xmlNodeList2.item(1); //mbs:CustomerData
                      IF NOT ISCLEAR(xmldomElem2) THEN
                      IF xmldomElem2.hasChildNodes() THEN
                         BEGIN
                         xmlNodeList3:= xmldomElem2.childNodes();
                         IF NOT ISCLEAR(xmlNodeList3) THEN
                          xmldomElem3:= xmldomElem2.firstChild();//mbs:Balance
                         IF NOT ISCLEAR(xmldomElem3) THEN
                          txtBalance:=xmldomElem3.text();
                         xmldomElem3:=xmlNodeList3.item(1); //mbsSalesPersonCode
                         IF NOT ISCLEAR(xmldomElem3) THEN
                          txtSPcode:=xmldomElem3.text();
                         END;

                         xmldomElem3:=xmlNodeList3.item(2); //mbs:Contacts
                         IF NOT ISCLEAR(xmldomElem3) THEN
                            txtContact:=xmldomElem3.text();

                     END;
                END;
                      MESSAGE('This is record "%1"\No "%2"\Name "%3"\Contact "%4"\Balance "%5"\Salesperson code "%6"\of Total "%7"',
                      FORMAT(i+1),
                      txtNo,
                      txtName,
                      txtContact,
                      txtBalance,
                      txtSPcode,
                      ii);

                END;
                ffile.CLOSE;
                CLEARALL;
              END;

      }
      CODE
      {
        VAR
          xmldomDoc@1000 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 6.0:{F6D90F11-9C73-11D3-B32E-00C04F990BB4}:'Microsoft XML, v6.0'.DOMDocument";
          xmlNodeList1@1005 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 6.0:{2933BF82-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v6.0'.IXMLDOMNodeList";
          xmlNodeList2@1017 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 6.0:{2933BF82-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v6.0'.IXMLDOMNodeList";
          xmlNodeList3@1019 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 6.0:{2933BF82-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v6.0'.IXMLDOMNodeList";
          xmldomElem1@1007 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 6.0:{2933BF86-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v6.0'.IXMLDOMElement";
          xmldomElem2@1010 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 6.0:{2933BF86-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v6.0'.IXMLDOMElement";
          xmldomElem3@1011 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 6.0:{2933BF86-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v6.0'.IXMLDOMElement";
          txtNo@1001 : Text[30];
          txtName@1002 : Text[30];
          txtContact@1009 : Text[30];
          txtBalance@1012 : Text[30];
          txtSPcode@1013 : Text[30];
          ffile@1003 : File;
          strInStream@1004 : InStream;
          i@1006 : Integer;
          ii@1018 : Integer;

        EVENT xmldomDoc@1000::ondataavailable@198();
        BEGIN
        END;

        EVENT xmldomDoc@1000::onreadystatechange@-609();
        BEGIN
        END;

        BEGIN
        END.
      }
    }

    That's all
    Special thanks to
    Rainer Kuhnlein

    Gedas Busniauskas (gediminb)
    Microsoft Customer Service and Support (CSS) EMEA

     

  • Microsoft Dynamics NAV Team Blog

    More Charts

    • 9 Comments

    This is a follow up to this post:

    Using Client Extensibility in NAV 2009 SP1 to create Charts

    News in this post are:

    2-series line charts. For example showing number of new Orders and Quotes for the last 10 days:

    2SeriesChart

    Item Sales Chart (basically identical to the Customer Sales Chart in the previous post):

    ItemSales

    Doughnut charts have been shined up a bit to make them look better:

    Don

    Double-click callback to NAV: When you double-click a chart, it will call the ControlAddin trigger. The example below will open up a list of Cust. Ledger Entries when double-clicking on a chart on the Customer Card page:

    Sales (LCY) - OnControlAddIn(Index : Integer;Data : Text[1024])
    CustLedgEntry.SETCURRENTKEY("Customer No.");
    CustLedgEntry.SETRANGE("Customer No.","No.");
    PAGE.RUN(0,CustLedgEntry);

    All you need is attached at the end of this post. It contains .dlls, including their source code, and a NAV codeunit. To implement some examples:

    Implement the charts add-on:

    1)  If you want to modify the attached c# project you must install "Microsoft Chart Controls Add-on for Microsoft Visual Studio 2008".

    2)  On any client machine where you want to run the charts, you must install "Microsoft Chart Controls for Microsoft .NET Framework 3.5".

    3)  Detach and unzip the attached file.

    4)  From the unzipped file, copy the files from \XTCharts\bin\Debug\ into the "Add-ins" sub folder of the RoleTailored Client (RTC).

    5)  And import the codeunit from XTChart.fob in Object Designer.

    6)  In a Classic Client, run table 2000000069 "Client Add-in" and enter one line (if it doesn't already exists from the previous post):

    Control Add-in Name:    XTChart
    Public Key Token:    d1a25808afd603da

    (making sure to have exactly these values, in the right case)

    Adding charts to pages:

    On the page where you want a chart, insert a new line of type Field. In the property "ControlAddIn", select the XTChart (XTChart;PublicKeyToken=d1a25808afd603da). This adds the control itself. To send data to the control you must specify SourceExpression too. Declare a Global variable called XTChartUtil, Type = Codeunit, Sub Type = Codeunit 75550 "XT Chart Util" to use in SourceExpression. If the chart is on a Customer Card page, then specify this SourceExpression:

    For a line-chart, showing "Sales (LCY)" for the last 6 months:  XTChartUtil.CustPointsChart(Rec)

    For a Doughnut chart, showing "Balance (LCY)" percentage of "Credit Limit": XTChartUtil.CustDoughnutChart(Rec)

    On an Item Card page, for a line chart showing Item "Sales (LCY)" for the last 6 months, have SourceExpressions XTChartUtil.ItemPointsChart(Rec)

    On the main Role Centre, to have a 2-series chart showing number of Quotes and Orders created in the past 10 days, I added the chart in Page 9060 with this SourceExpression: ChartUtil.SalesCueChart

    Then, for each example above, if you want to trigger any activity when the user double clicks the chart, just put some C/AL code on the corresponding OnControlAddIn-trigger.

    You can add charts in many other places, except for pages of the following types which cannot handle Extensibility components:

    • Under a Repeater Control (i.e. a list page)
    • Action Pane
    • Command Bar
    • Filter Pane

    Note:

    In the example here, I added a chart to the main Role Centre. In a production environment this is maybe not such a good idea, for these two reasons:

    1)  If a chart goes wrong and crashes, for example if a user has not installed the Chart Controls for .NET (link above), then it may crash the whole RTC. If this happens from some page which shows a chart, then at least the user knows that such and such page has a problem. If it happens on the Role Centre, then RTC will crash as soon as it is opened, and the user cannot use RTC at all, and it may not be obvious why RTC crashes.

    2)  Performance is not great in the SalesCueChart-example here, counting Sales Header records filtering on "Document Date". If a company has 1.000s of orders, then this would have some impact on performance. So, at least only calculate the chart on request, and not on the main page whether the user actually needs it or not.

    Development notes

    If you want to extend this to create new charts, then this is how the attached example works:

    Chart Data is generated in xml format in codeunit 75550 "XT Chart Util". It has a function for each type of chart. The xml data must have this format:

    - <Chart>

    <ChartType>1</ChartType> <-- Chart Type 1 = Lines, 2 = Doughnut and 3 = 3D lines

    - <Data Title="Orders"> <-- Title is optional. If specified, it will show as legend on the chart

    <REC1 Title="16/01/11">0</REC1> <-- Name the elements REC1, REC2, etc.

    <REC2 Title="17/01/11">2</REC2>

    <REC3 Title="18/01/11">0</REC3>

    <REC4 Title="19/01/11">2</REC4>

    </Data>

    - <Data2 Title="Quotes"> <-- Data2 section is optional. Used for making 2-series charts. If there is a Data2 section, then it must have the same number of elements as Data, and with the same names.

    <REC1 Title="16/01/11">0</REC1>

    <REC2 Title="17/01/11">0</REC2>

    <REC3 Title="18/01/11">0</REC3>

    <REC4 Title="19/01/11">1</REC4>

    </Data2>

    </Chart>

    In case of a Doughnut chart, just generate an xml document like this:

    - <Chart>

    <ChartType>2</ChartType>

    - <Data>

    <REC1 Title="Balance">9.20</REC1>

    <REC2 Title="CreditLimit">90.80</REC2>

    </Data>

    </Chart>

    with 2 REC-elements which total up to 100.

    The c#-code is all inside of one big try{} catch{} - structure. If anything goes wrong, then it will just show the data that was received in a MessageBox, but not what actually went wrong. Only if you remark the try{} and catch{} - section, it will throw the actual error message to tell you what went wrong (it should log it in the Application log). But the downside of this is, that RTC is also likely to crash.

    Lars Lohndorf-Larsen

    CSS EMEA

    This posting is provided "AS IS" with no warranties, and confers no rights

  • Microsoft Dynamics NAV Team Blog

    How to get a Dynamics NAV report with a Web Service

    • 9 Comments

    1. Create a new codeunit. In this scenario we will call this codeunit "CUWebReport" with ID 50000

    2. Navigate to "C/AL Globals" and create a function called "GenerateReport"

    image 

    3. Select "Locals"

    4. Select the "Return Value" tab

    5. Set "Return Type"=Text and "Length"=100

    image

    6. With this completed close "C/AL Locals" window.

    7. Now with "C/AL Globals" windows active again. Select "Variables" tab.

    8. Create a variable called "filename" with "Data Type"=Text and "Length"=100

    image

    9. Now let's add the following code to this codeunit:

    filename := 'C:\inetpub\PdfDocuments\';
    filename += FORMAT(CREATEGUID);
    filename := DELCHR(filename, '=', '{-}');
    filename += '.pdf';
    REPORT.SAVEASPDF(111,filename);
    EXIT(filename);

    image

    10. Save and compile the codeunit.

    11. Now it's time to expose this codeunit as Web Service. Navigate to "Administration/IT Administration/General Setup/Web Services"

    12. Select codeunit 50000 and give this a service name, we use "Get_PDF_Report"

    image

    13. Now it is time to verify that we can see this web service. Open this URL  http://localhost:7047/DynamicsNAV/WS/services.

    You should now see this message in your browser, and your Web Service can now be called :

    image

    If you don't see this message, you might want to check that  the service "Microsoft Dynamics NAV Business Web Services" has been started.

    14. Now it is time to call the Web Service, in this example we use Microsoft Visual Web Developer 2005. And we use the following code to call the Web Service:

    Partial Class _Default
        Inherits System.Web.UI.Page

        Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim service As localhost.Get_PDF_Report = New localhost.Get_PDF_Report()
            service.UseDefaultCredentials = True
            service.Url = "
    http://localhost:7047/DynamicsNAV/WS/CRONUS_International_Ltd/Codeunit/Get_PDF_Report"
            Response.ContentType = "application/pdf"
            Dim pdfFileName As String = service.GenerateReport()
            Response.TransmitFile(pdfFileName)

        End Sub
    End Class

    But how to consume this Web Service is not in scope for this blog, so we suggest you have look our online help how to consume a Web Service from NAV 2009.

    Online help found here: http://msdn.microsoft.com/en-us/library/dd339004.aspx

    15. Now also remember to impersonate your web servers application to an appropriate Dynamics NAV user.

    16. After compiling a running our project we then get this button in our browser.

    image

    17. When activating this button, we get the report specified in Codeunit 50000 displayed as an PDF file in our browser.

    image

    Conclusion, now you can give this URL to people who don't have access to Dynamics NAV, and they can execute the report when they see a need for this.

    Thanks,

    Torben Meyhoff, SDE & Claus Lundstrøm, Program Manager, Microsoft Dynamics NAV

  • Microsoft Dynamics NAV Team Blog

    Microsoft Dynamics NAV and SQL Server Database Compression

    • 9 Comments

    Microsoft SQL Server 2008 introduces two new compression features (available in Enterprise Edition) that are of interest to users with large databases. Data compression applies to individual tables and indexes within a database and comes in two options: ROW and PAGE. Backup compression is a new option when backing up a database to external storage. Both compression techniques can be used to reduce the amount of online storage required for databases. Data compression can be configured for an entire heap (table with no clustered index), clustered index, non-clustered index, or indexed view. For partitioned tables and indexes, the compression option can be configured separately (and differently) for each partition. Compression can also reduce disk utilization and sql memory utilization as dead is stores on disk in a compressed state and also reads in the SQL cache in a compressed state. Compression can add 10 - 30% percent increased CPU utilization depending on what tables and indexes are compressed and what level of compression is used.

    For Dynamics NAV we recommend only compressing tables and indexes that have a read to write ration of 80%/20% (This is a conservative threshold) or higher as compressing tables with a higher write ratio can actually decrease performance. We also recommend using ROW compression if the space saving between ROW and PAGE level compression is less that 10%; if the difference is over 10% then we recommend PAGE compression. This is because if the space savings from PAGE compression is close to or similar to ROW compression then it is not recommended to incur the additional overhead associated with PAGE compression. An example of NAV tables that would benefit greatly from compression are the large "Entry" tables such as G/L Entry, Value Entry, and Item Ledger Entry. An example for NAV tables that would not benefit from compression and where performance may actually decrease due to compression are "Line" tables where the data is temporary in nature such as Sales Line, Purchase Line, and Warehouse Activity Line. SQL Server compression is completely transparent to the Dynamics NAV application.

    Compression is done on Table or Index basis. On tables with clustered indexes the clustered index is the table so compressing the clustered index is equal to compressing the table. Running the ALTER TABLE.. WITH COMPRESSION is only necessary on HEAPS for all tables with Clustered Indexes you can use the ALTER INDEX.. WITH COMPRESSION.

    How to determine the read/write ratio of an index? Luckily SQL keeps track of this for us and all we need to do is extract this data. SQL Server stores this information in the sys.dm_db_index_operational_stats DMV. Remember DMV's are "recycled" each time the SQL Server service is restarted so if SQL has only been up and running for a day or a week this information will be of minimal use. Ideally you would want the server to be up any running for several weeks and through a month end close to get a true idea of actual data access patterns of your database.

    The following query will tell you how long the SQL Server instance has been up and running:

    select 'Sql Server Service has been running for about '

           + cast((datediff(hh, create_date, getdate()))/24 as varchar(3)) + ' days and '

           + cast((datediff(hh, create_date, getdate())) % 24 as varchar(2)) + ' hours'

           from sys.databases where name = 'tempdb'

    The following query will give the approximate read write balance of all the used indexes in the database.

    SELECT o.id, 

                            o.name,

                            x.name,

                            i.index_id,

                            x.type_desc,

                            s.rowcnt,

                            i.leaf_update_count * 100.0 /

                                  (i.range_scan_count + i.leaf_insert_count

                                      + i.leaf_delete_count + i.leaf_update_count

                                      + i.leaf_page_merge_count + i.singleton_lookup_count

                                  ) as Writes,

                            i.range_scan_count * 100.0 /

                                  (i.range_scan_count + i.leaf_insert_count

                                      + i.leaf_delete_count + i.leaf_update_count

                                      + i.leaf_page_merge_count + i.singleton_lookup_count

                                  ) as Reads

                  FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) i

                  JOIN sys.sysobjects o ON o.id = i.object_id

                  JOIN sys.indexes x ON x.object_id = i.object_id AND x.index_id =

    i.index_id

                  JOIN sys.sysindexes s ON s.id = x.object_id and s.indid = x.index_id

                  WHERE (i.range_scan_count + i.leaf_insert_count

                            + i.leaf_delete_count + leaf_update_count

                            + i.leaf_page_merge_count + i.singleton_lookup_count) <> 0

                  AND objectproperty(i.object_id,'IsUserTable') = 1

                 

    You can also run a simulation with the sp_estimate_data_compression_savings stored procedure in SQL with will run a sample of the data through the chose compression level and give you the estimate of the space saved by enabling compression.

    I strongly recommend reading the following MSDN article before engaging in database compression activities.

    http://msdn.microsoft.com/en-us/library/cc280449.aspx

    Note: You can use the following query to check to see if there are currently any compressed indexes in the database.

    SELECT o.name Table_Name, p.index_id as Index_ID, i.name as Index_Name,

          CASE

          WHEN p.data_compression = 1 THEN 'ROW Compression'

          WHEN p.data_compression = 2 THEN 'PAGE Compression'

          ELSE 'ERROR'

          END Compression_Type

    FROM sys.partitions p

    JOIN sys.objects o ON p.object_id = o.object_id

    JOIN sys.sysindexes i ON o.object_id = i.id AND p.index_id = i.indid

    AND p.data_compression in (1,2)

    ORDER BY o.name, p.data_compression, p.index_id

     

    Michael De Voe

    Senior Premier Field Engineer

    Microsoft Dynamics

    Microsoft Certified Master - SQL Server 2008

  • Microsoft Dynamics NAV Team Blog

    Transfooter and Transheader functionality in RDLC(SSRS) reports - revisited

    • 9 Comments

    In one of our previous blog post we discussed the possibility to do Transfooter and Transheader functionality in RDLC(SSRS) reports and describes a viable solution for this in RDLC.

    In this blog post we would like to suggest an alternative, a bit more economical and easier to implement solution for the same problem.

    For the demo we use the same table and the same report and will strive to achieve the same results as in the mentioned in our previous blog post.

    1. Create new report blank report with table 18

    clip_image002

    2. Create DataItem ”Customer”

    3. Go to Section Designer and add the following fields:

    • No.
    • Name
    • Debit Amount

    4. Save the report as ID 50000 – Transfooter / Transheader

    5. Now go to Visual Studio (View / Layout)

    6. Create table and add the fields No, Name and Debit Amount

    7. Give this table the name "MainTable"

    8. Now we have added the basic for this report. But I would also like to have a Grand total of the Debit Amount so I add this as well. I add this in the Footer of the table

    ="GrandTotal: " & sum(Fields!Customer__Debit_Amount_.Value)

    image

    9. Now if my report is printed I get a list of my all my customer with Debit Amount displayed and with GrandTotal in the end of the report:

    10. Now I create a small block of VBS code in order to perform some calculations and store intermediate data

    Open “Report->Report Properties” dialog and select “Code” tab, enter the following VBS code:

    11. Define a hashtable for storing running accumulated sums for each page of the report

    Shared RunningTotals As New System.Collections.Hashtable

    12. Define two public functions, which populate and query the hashtable from above

    Public Function GetRunningTotal(ByVal CurrentPageNumber)

    Return IIF(CurrentPageNumber > 0, RunningTotals(CurrentPageNumber), 0)

    End Function

     

    Public Function SetRunningTotal(ByVal CurrentPageTotal, ByVal CurrentPageNumber)

    RunningTotals(CurrentPageNumber) = CurrentPageTotal + GetRunningTotal(CurrentPageNumber - 1)

    Return RunningTotals(CurrentPageNumber)

    End Function 

    image

    13. Ok, it’s now time to add a Transfooter and Transheader.

    Enable Page Header and Page Footer in the report (click “Report->Page Header” and “Report->Page Footer”).

    14. In the Page Footer I place a text box with the following expression:

    ="Transfooter subtotal = " & Code.SetRunningTotal( Sum(ReportItems!Customer__Debit_Amount_.Value), Globals!PageNumber)

    image

    This code actually performs the following actions:

    - calculate the sum of all “Debit Amount” values on the current page (sic)

    - adds this value to the running total, which has been already calculated for the previous page

    - returns this value as the actual running total for the current page

    15. In the Page header I place a text box with the following expression:

    ="Transheader subtotal = " & Code.GetRunningTotal(Globals!PageNumber-1)

    This code fetches the running total, calculated up to the previous page

    image

    16. And then I set distinctive BackgroundColor and font Color just so this Transfooter and Transheader stand out in my report

    image

    17. Now I’m almost done but I would like to not see the Transheader on the first page and not to see the Transfooter on the last page.

    So I set the following expressions for the “Visibilty->Hidden” properties of the page header:

    =IIF(Globals!PageNumber > 1, False, True)

    And for the page footer:

    =IIF(Globals!PageNumber < Globals!TotalPages, False, True)

    18. Now I’m done, I save, import into NAV and compile. After some fit and finish on the report it now looks like this when I print

    Now I’m done, I save, import into NAV and compile. After some fit and finish on the report it now looks like this when I print:

    clip_image027

    clip_image029

    Question: Would this also work in the example of having a list of sales order lines per sales header and the sales order lines goes to multiple pages?

    Answer: The report above is a bit simplified in order to illustrate the point. It can be easily extended to support your scenario. I.e. the key for the hash should include page number AND header no to accomplish this.

    You can download the report object here, thanks to Nickolay Belofastow.

    /Claus Lundstrøm

  • Microsoft Dynamics NAV Team Blog

    About Object Metadata, and why I can't see object changes in RTC

    • 9 Comments

     

    A few times we have seen case where changes to NAV objects are not seen in RTC until the NAV Server is restarted. This post explains a likely solution to such problems. And as we are on the topic anyway, also explains a bit about the process that turns C/AL code into Metadata (c#).

     

    How object changes get to RTC


    When you save an object in Object Designer, then table 2000000071 "Object Metadata" is updated as well. An easy way to see this part of the process in action is:
    Run table 2000000071 and delete Page 1 (or any object). Then compile Page 1 from Object Designer. Then check that it has been re-created in table 2000000071.

    If you look at this table in SQL Server Management Studio, you can see that it has SQL triggers which update the table "Object Tracking". It is the "Object Tracking" table that NAV Server uses to see when an object has changed, so that it knows to send updated Metadata to RTC.

    There are two ways that the NAV Server can get updated on activity in the "Object Tracking" table:
      SQL Server Broker
      Polling

     

    SQL Broker

    If SQL Server Broker is enabled, then NAV Server will rely on that to notify it when an object has changed. You can see whether the broker is enabled from SQL Server Management Studio under Database Properties, then under Options look for "Broker Enabled".

    If the broker is enabled, then when NAV Server starts up and on first activity (When first RTC connects), it will create a queue and a service under SQL Service broker. You can see this in SQL Server Management Studio under the NAV Database if you expand Service Broker. Here, under Queues and under Services you will see new objects with names like SqlQueryNotificationService-27b7fb21-74a7-4a63-876a-c96b8eecd583. These are created by NAV Server and removed again when NAV Server stops. Their job is to listen to the "Object Tracking" table and notify NAV Server when there are any changes there.

     

    Polling

    If the broker is not enabled, then NAV Server will use polling, i.e. check for changes every now and then. You can see this in two ways. First in the Application Log the NAV Server will log this event shortly after startup:

    Service: MicrosoftDynamicsNavServer
    SQL Query Notifications are unavailable on SQL Server '.' in Database 'NAVDatabase'. The Object Change Listener has switched to polling.

    Secondly, when NAV Server is in polling mode and you start SQL Profiler you will see this query being run regularly by .New sqlClient Data Provider:

    exec sp_execute 1,@lastKnownTimeStamp=463223

    This is a pre-prepared query which looks like this:

    SELECT [Object Timestamp], [Object Type], [Object ID], [Object Key] FROM [dbo].[Object Tracking] WHERE [Object Timestamp] > @lastKnownTimeStamp',@lastKnownTimeStamp=463225

    In polling mode, this is how NAV Server checks for object changes since last time it checked, so it knows whether to send updated object definitions (metadata) to RTC.

     

    Troubleshooting

    So that's the background. How do we handle the case where object changes are not seen in RTC until we restart NAV Server? If this problem happens, then switch method from SQL Broker to Polling or visa versa. You switch by enabling / disabling the broker like this:

    ALTER DATABASE [MyNAVDatabase]
    --SET ENABLE_BROKER
      SET DISABLE_BROKER
    WITH ROLLBACK IMMEDIATE

    The "WITH ROLLBACK IMMEDIATE"-part of this query is to avoid what happened for me that the query just hang, or would only run if the database was put in single user mode.

    In the cases we have seen, the broker has been enabled but had some kind of problem. A slightly cryptic message was recorded in the SQL Server Log every time an object was changed in Object Designer. This message was not logged in the Application log. So make sure to check in SQL Server Management Studio under Management -> SQL Server Logs -> Current. If this gives enough information to solve the problem then good. If not, then there is the option of switching to polling (disable the broker) until the root of the problem can be resolved.

    Enabling the broker is the preferred option since it saves NAV Server for checking ever so often for object changes. But if the broker doesn't work then at least there is the option to disable it until any problems can be resolved.

     

    Lars Lohndorf-Larsen

    Dynamics NAV Support EMEA

     

  • Microsoft Dynamics NAV Team Blog

    Microsoft Dynamics NAV 2013 R2 available in the Windows Azure Portal under MSDN subscription

    • 9 Comments

    Ever wondered what it is like to work with Microsoft Dynamics NAV on Windows Azure - but found it too cumbersome even with our nice Windows Azure provisioning tools?  Then this might be something for you. 

    Today we have made Microsoft Dynamics NAV 2013 R2 easily available on Windows Azure with just a few clicks. The Microsoft Dynamics NAV 2013 R2 image is an exclusive offer for MSDN subscribers and includes a fully functional, ready to use Microsoft Dynamics NAV 2013 R2 installation including Microsoft SQL Server. The MSDN subscription gives you the well-known discounted prices and allows you to use it for development, test, and demo purposes, but not for production purposes.

    How to get it? Make sure you have an MSDN subscription and the subscription has Windows Azure benefits activated. Then follow these steps:

     

    1. Sign in to the Windows Azure Portal using your MSDN subscription.
    2. Choose the + sign at the bottom of the Portal page.
    3. Choose Compute, point to Virtual Machine, and then choose From Gallery.
    4. Select the MSDN filtering checkbox as shown in the following screenshot. This will filter the list to only show the available MSDN images.

    1. Choose the Microsoft Dynamics NAV 2013 R2 image.
    2. Choose the arrow in the lower-right corner.
    3. Fill in the relevant fields about the virtual machine as described in the following list:
      1. Specify the name of the virtual machine.
      2. Specify the size of the virtual machine, such as Medium.
      3. Specify the user name and password for connecting to the virtual machine.
      4. Specify the region  where you want to host your virtual machine, such as West Europe.
      5. Verify the remaining parameters. In most cases, the default values are sufficient.
    4. Choose the Finish icon to start the provisioning process.
    5. Once the image is provisioned after a few minutes, you can use Remote Desktop to connect to it. In the Azure Portal Virtual Machine page, choose the newly created image, and then choose Connect. Use the credentials that you specified step 7.

    When you are connected to the VM, you are welcomed to the Microsoft Dynamics NAV 2013 R2 virtual machine on Windows Azure:

    No further configuration is required and you can immediately start using the clients and the development environment.

    Best regards

    Kamil Koclega & Morten Jensen from the Dynamics NAV team

  • Microsoft Dynamics NAV Team Blog

    Update Rollup 4 for Microsoft Dynamics NAV 2013 R2 has been released

    • 9 Comments

    Update rollup 4 includes all application and platform hotfixes and regulatory features that have been released for Microsoft Dynamics NAV 2013 R2 and includes hotfixes that apply to all countries and hotfixes specific to the following local versions:

    • AU – Australia
    • DE - Germany
    • DK - Denmark
    • FR - France
    • IT - Italy
    • NA – North America
    • NL – Netherlands
    • NZ – New Zealand
    • SE - Sweden
    • UK - United Kingdom

     

    New in update rollup 4 

    Beginning with update rollup 4, the update rollups for Microsoft Dynamics NAV 2013 R2 now also includes hotfixes for the upgrade toolkit. 

     

    Where to find update rollup 4

    You can download update rollup 4 from KB 2930617 - Update Rollup 4 for Microsoft Dynamics NAV 2013 R2 (Build 36078).

    The hotfixes that have been released since update rollup 3 are listed in KB 2930617. For a full list of all hotfixes included in the update rollup, see the following CustomerSource and PartnerSource pages: 

    CustomerSource:

    PartnerSource

     

    More Information

    For more information about update rollups for Microsoft Dynamics NAV 2013 R2, see Announcement of update rollups for Microsoft Dynamics NAV 2013 R2.

  • Microsoft Dynamics NAV Team Blog

    Memory usage in Microsoft Dynamics NAV 2013 print preview

    • 9 Comments

    Periodically we receive requests where customers asking us about RTC print preview consumes all available memory and computers hangs.

    Repro scenarios are more/less similar: run report, click preview, go per pages up/down, close report. Run another report, click preview, go per pages up down and… RTC hangs.
    If we look to memory usage, NAV is using all available memory.

    If we analyze what has happened, we see: with every move per pages memory usage increase and increase and increase until all memory is used.
    It looks like typical memory leak because in NAV 2009 with the same repro everything is OK.

    However it is not so simple. What NAV does in this scenario is: it loads Microsoft Report Viewer 2010 with 2 files: report definition (RDL file where is described report structure) and record set. And that is all, next actions like preview/print/export are managed by report viewer. The same we have in NAV 2009 just it loads Report Viewer 2008 and .NET Framework 3.5 as NAV 2013 uses Report Viewer 2010 and .NET Framework 4.5. And here is reason pointing us to memory usage behavior: Report Viewer 2010 and .NET 4.5 uses another way to manage memory (named more advanced and more secure). However this ‘another’ way gets us mentioned questions from customers and really we can do nothing from NAV platform side – system works as it is designed to work.

    But things are not so bad. In .NET memory is managed very smart and not used memory is released by function named ‘garbage collector’. This function periodically review memory blocks and release not used. And actually it works: if we monitor memory during report previewing and after it close we see that in preview and going per pages memory usage increase, after preview close memory usage is still the same, but after 5-10 min used memory decrease to initial numbers. For example in my tests started RTC using 98.5 MB, after few previews and scrolls and etc. usage increases to 215 MB and doesn’t decrease if I close preview. But after few minutes garbage collector returns usage to 100.4 MB.
    We can force (in NAV platform) garbage collector to work faster and memory release will be faster, but this will increase processor usage and in many cases it is worse than memory usage.

    Few trick could help users do not meet memory problems:

    1. When we open report preview we see report in “funny” interactive mode. This is preview mode where we can change report view in some way described in report code. For example in report ‘Customer – Top 10 List’ sorting can be changed. But exactly this mode is memory monster. If we click ‘Print layout’ button in menu we come to ‘page view’ where we can change nothing in report and see exactly what will be printed. This mode doesn’t use memory so much, so we can use this view easier. Few additional points here: a) all report initially open in interactive mode so users need manually change mode; b) Mode is not available if report is not allowed to preview like Sales Invoice or Order Confirmation.
    2. Report run SAVEASPDF works better if we call it directly without request form. However then users need to do more actions to open report. But maybe this can be reasonable in some cases.
    3. And finally if there are requirements to run big reports with many pages (100 and more) then better is to run them on server side, just because server is 64 bits and usually has more available RAM. This can be done by creating codeunit which runs report SAVEASPDF. Then add codeunit to job queue. Job queue executes codeunit and create pdf file.

     

    New Information since Update Rollup 5:

     

     

     

    These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.

     

    Gedas Busniauskas
    Microsoft Lithuania
    Microsoft Customer Service and Support (CSS) EMEA

     

  • Microsoft Dynamics NAV Team Blog

    How to Deploy a Microsoft Dynamics NAV 2013 R2 Help Server

    • 8 Comments

    As most of you have already found out, we, the NAV R&D team, have developed a new Help Server that shows Help on a website. Help is available for the Windows client as in earlier versions. With the Help Server, the same Help is available from the Web client. Choose a field, and context-sensitive Help opens.

    The Help Server is a website that displays HTML files. Yes, you read correctly: HTML files. So you don’t have to decompile the Microsoft CHM files anymore. But the really good news is that the Help that you created for your current NAV solution will still work with the new Help Server. You just need to build it again with the new Help Toolkit, and then copy the resulting HTML files to the relevant folder on the Help Server website. Then, you can start connecting your users to the Help Server.

    Here are the main steps for deploying your Help to your customers.

    1. Install the Microsoft Dynamics NAV 2013 R2 Help Server. For more information, see Microsoft Dynamics NAV Help Server in the MSDN Library.
      • When you choose the Help Server option in Microsoft Dynamics NAV Setup, a Help Server website is set up for you with folders containing the HTML Help that Microsoft provides.
    2. Install the Microsoft Dynamics NAV 2013 Help Toolkit that is available for download from PartnerSource.
    3. Rebuild your existing Help files with the NAV 203 version of HelpBuilder, but change some of the configuration settings. For more information, see Upgrading Your Existing Help Content in the MSDN Library.

    4. Add the HTML files that HelpBuilder creates to the relevant language-specific folders on your Help Server website.

    5. Grant your users access to the website.

    6. Configure the Microsoft Dynamics NAV 2013 R2 clients to look up Help on your Help Server.

      • This configuration is part of Microsoft Dynamics NAV 2013 R2 Setup. Alternatively, you can configure the Web client server components and the Windows clients manually.

    7. Start a Microsoft Dynamics NAV 2013 R2 client and access Help.

    You can set up a Help Server that all your users can access, or you can set up multiple Help Servers. You can modify the Microsoft-provided Help content without decompiling anything, and you can add and remove Help content whenever you want to. You can add links to existing Word documents or PDF documents to the navigation pane of the Help Server website, so that you users have a single point of entry for Help.

    With the Microsoft Dynamics NAV 2013 R2 Help Server, you can provide the Help content that is relevant for your users, and you can update Help without modifying each client computer.

    We will improve the Help Server in future releases of Microsoft Dynamics NAV, so we hope you will like it as much as we do.

     

    Best regards,

    The Dynamics NAV team

  • Microsoft Dynamics NAV Team Blog

    Update Rollup 6 for Microsoft Dynamics NAV 2013 has been released

    • 8 Comments

    Update rollup 6 for Microsoft Dynamics NAV 2013 (Build 35345) has been released.

    Update rollup 6 includes all application and platform hotfixes and regulatory features that have been released for Microsoft Dynamics NAV 2013 and includes hotfixes that apply to all countries and hotfixes specific to the following local versions:

    • AU – Australia
    • DE - Germany
    • DK - Denmark
    • FR - France
    • IT - Italy
    • NA – North America
    • NL – Netherlands
    • NZ – New Zealand
    • UK - United Kingdom

    Where to find update rollup 6

    You can download update rollup 6 from KB 2881294 - Update Rollup 6 for Microsoft Dynamics NAV 2013 (Build 35345).

    The hotfixes that have been released since update rollup 5 are listed in KB 2881294. For a full list of all hotfixes included in the update rollup, see the following CustomerSource and PartnerSource pages:

    CustomerSource:

    PartnerSource:

    More Information

    For more information about update rollups for Microsoft Dynamics NAV 2013, see Announcement of new hotfix process for Microsoft Dynamics NAV 2013.

  • Microsoft Dynamics NAV Team Blog

    Update Rollup 11 for Microsoft Dynamics NAV 2013 has been released

    • 8 Comments

    Update rollup 11 for Microsoft Dynamics NAV 2013 (Build 36076) has been released.

    Update rollup 11 includes all application and platform hotfixes and regulatory features that have been released for Microsoft Dynamics NAV 2013 and includes hotfixes that apply to all countries and hotfixes specific to the following local versions:

    • AU - Australia
    • DE - Germany
    • DK - Denmark
    • FR - France
    • IT   - Italy
    • NA - North America
    • NL - Netherlands
    • NZ - New Zealand
    • SE - Sweden
    • UK - United Kingdom

    Where to find update rollup 11

    You can download update rollup 11 from KB 2930616 - Update Rollup 11 for Microsoft Dynamics NAV 2013 (Build 36076).

    The hotfixes that have been released since update rollup 10 are listed in KB 2930616. For a full list of all hotfixes included in the update rollup, see the following CustomerSource and PartnerSource pages:

    CustomerSource:

    PartnerSource:

    More Information

    For more information about update rollups for Microsoft Dynamics NAV 2013, see Announcement of new hotfix process for Microsoft Dynamics NAV 2013.

  • Microsoft Dynamics NAV Team Blog

    Reading and Writing Unicode Files using C/AL

    • 8 Comments

    Hello,

    We have had some partner suggestion for adding Unicode capabilities to the existing Microsoft Dynamics NAV File functions. What we recommend is to use .NET Interop to achieve this functionality.

    For example, you can use an instance of the System.IO.StreamWriter class to write to an OutStream or the System.IO.StreamReader class to read from an InStream. You control the encoding by using the System.Text.Encoding class where you select between Default, Unicode or UTF8 encoding.

    • Please note that XMLports in Microsoft Dynamics NAV 2013 directly supports importing and exporting flat text files in MS-DOS, UTF-8, UTF-16 encodings by setting the new TextEncoding property.

    Writing Unicode text files

    Let’s start with a small example on writing some text to a file in Unicode format.

    Declare the following variables:

    Name DataType Subtype
    outFile File  
    outStream OutStream  
    streamWriter DotNet System.IO.StreamWriter.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' 
    encoding DotNet System.Text.Encoding.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' 

    Then write this code, using a suitable path for the outFile:

      outFile.CREATE('c:\temp\OutFile.txt');

      outFile.CREATEOUTSTREAM(outStream);

      streamWriter := streamWriter.StreamWriter(outStream, encoding.Unicode);

     

      streamWriter.WriteLine('Hello World');

     

      streamWriter.Close();


      outFile.CLOSE();
    • You can use 'Hello World' as above or a text string with some more special characters as you like. I added some Danish characters..: 'Hello ÆØÅ World' to more easily see what’s going on.

     Run the code and verify the file is in Unicode.

    • One way to verify a file is in Unicode is to open it with Notepad and select File/Save As…, and then inspect the Encoding field.

    Try change the above example to use encoding.Default and verify the file is in ANSI (codepage) format (for example using Notepad as above).

    Please note that if you use C/AL to write directly to the outStream, like this:

    outStream.WRITETEXT('Hello World');

    this is still handled using MS-DOS encoding and is compatible with previous versions of Microsoft Dynamics NAV.

    • You can open a file in MS-DOS encoding by starting Wordpad and in the Open file dialog select “Text Documents – MS-DOS Format (*.txt)”.

    In Microsoft Dynamics NAV 2013, all normal text handling are done in Unicode, so the data that are entered on the pages – like customer names one on the Customer Card – can utilize the Unicode character set, the data can be stored in the database and used in C/AL code.

    Let’s see how you can extend the above example with data from the Customer table:

    Add the customer variable:

    Name DataType Subtype
     customer Record   Customer

    And write this code – you can set a filter if needed:

      outFile.CREATE('c:\temp\Customers.txt');

      outFile.CREATEOUTSTREAM(outStream);

      streamWriter := streamWriter.StreamWriter(outStream, encoding.Unicode);

     

      customer.FINDSET();

      REPEAT

        streamWriter.WriteLine(customer.Name);

      UNTIL customer.NEXT = 0;

     

      streamWriter.Close();

      outFile.CLOSE();

     If you open the Customers.txt file with a Unicode file viewer it should contain all the Unicode details you have used for your customer names.

    Reading Unicode text files

    Similar to the above you can read Unicode text files by using the System.IO.StreamReader class to read from an InStream as you can see in the small example below:

    Declare the following variables:

    Name DataType Subtype
    inFile File  
    inStream InStream  
    streamReader DotNet System.IO.StreamReader.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
    encoding DotNet System.Text.Encoding.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
    txt Text  

    Then write this code, using a suitable path for the inFile:

      inFile.OPEN('c:\temp\Infile.txt');

      inFile.CREATEINSTREAM(inStream);

       

      streamReader := streamReader.StreamReader(inStream,encoding.Unicode);

      txt := streamReader.ReadToEnd();

      message(txt);

    Create the Infile.txt as a Unicode file and add some Unicode characters to it. You should see them in the message box when you run the C/AL code.

     

     

    I hope these simple examples can get you started with Unicode file handling in C/AL. 

     

    Thanks and best regards!

    Hans Kierulff

  • Microsoft Dynamics NAV Team Blog

    New Excel-Based Reporting Tool for Microsoft Dynamics NAV

    • 8 Comments

    It’s no secret that Microsoft Dynamics NAV customers and partners have needed an ad hoc reporting solution that’s easy to use. Well, now we have one!

    We’ve been working in cooperation with Jet Reports, Inc. on the development of an Excel-based reporting solution. The result is Jet Reports Express for Microsoft Dynamics NAV – a simple but effective business reporting tool that gives customers an easy and simple way to create high impact reports and helps us to enhance our BI & Reporting value proposition.

    Within a familiar Microsoft Excel environment, users will be able to access Microsoft Dynamics NAV data and utilize all of the Excel capabilities, such as Power Pivot, formatting, charting and Pivot Tables, to create powerful, insightful and well-formatted reports. There are multiple report templates available out of the box.

    Plus, users will be able to answer and analyze ad hoc business queries with real time data from Microsoft Dynamics NAV. It’s possible to access and combine data from NAV – including tables, fields, flow fields and dimensions, and you can slice and dice data and do consolidation. You can also drill down into any value in a report to see the underlying data with just one click.

    Jet Reports Express for Microsoft Dynamics NAV really is simple to work with and very easy to demo. It’s the perfect solution for the majority of the Microsoft Dynamics NAV customers who need a basic ad hoc reporting solution.

    Jet Reports Express for Microsoft Dynamics NAV will be available to Microsoft Dynamics NAV customers as new functionality at no additional costs, provided they are on an active Business Ready Enhancement Plan.

    The product can be downloaded via a link from PartnerSource and CustomerSource that will be available sometime in Q3 CY2011. Look out for more information on final release date.

    Stay tuned for more information about all the cool things customers can do and partners can demo with this smart reporting tool.

  • Microsoft Dynamics NAV Team Blog

    Let NAV Speak! (with .NET Interop and NAV 2009 R2)

    • 8 Comments

    In this blog is described a very simple usage of the .NET interoperability feature with Microsoft Dynamics NAV 2009 R2 and can be considered an extension of my previous blog: http://blogs.msdn.com/b/nav/archive/2010/07/09/let-nav-speak-with-a-simple-and-useful-client-add-in.aspx.

    It is intended just to familiarize you with this brand new feature proposed with the NAV 2009 R2 release.

    If you want to know more about .NET interoperability for NAV 2009 R2, please refer to MSDN link:

    Extending Microsoft Dynamics NAV Using Microsoft .NET Framework Interoperability

    http://msdn.microsoft.com/en-us/library/gg502499.aspx

    The .NET interoperability code snippet in this blog is based on the System.Speech namespace.

    http://msdn.microsoft.com/en-us/library/ms554861(v=VS.90).aspx

    My ingredients:

    • NAV 2009 R2
    • Windows 7 Enterprise

    Create your "Speak it!" action and let NAV speak Customer Names :

    1. Open Classic Client

    2. Go to the Object Designer

    3. Select Page object (alt+g)

    4. Select Page 22 "Customer List"

    5. Design Page 22 "Customer List" (alt+d)

    6. Select View > Page Actions (al+v, o)

    7. Create a new Action (F3) in the ActionItems container (see below)

    8. Change the properties of the Action as below:

    Caption - Speak it !

    Image - ViewComments

    Promoted - Yes

    PromotedCategory - Process

    PromotedIsBig - Yes

    9. Edit the code in the Speak it! Action (F9)

    10. Add those LOCAL variables

    dnSpeech

    DataType: DotNet

    Subtype: System.Speech, Version=3.0.0.0, Culture=neutral,PublicKeyToken=31bf3856ad364e35.System.Speech.Synthesis.SpeechSynthesizer

    RUNONCLIENT: Yes
    Synth

    DataType: DotNet

    Subtype: System.Speech, Version=3.0.0.0, Culture=neutral,PublicKeyToken=31bf3856ad364e35.System.Speech.Synthesis.SpeechSynthesizer

    RUNONCLIENT: Yes

    dnVoiceGender

    DataType: DotNet

    Subtype: System.Speech, Version=3.0.0.0, Culture=neutral,PublicKeyToken=31bf3856ad364e35.System.Speech.Synthesis.VoiceGender

    RUNONCLIENT: Yes

    I

    DataType: Integer

     

    NOTE: RUNONCLIENT is a property of each DotNet variable (shift+f4)

    11. Add this code snippet in the OnAction trigger

    ...

    // 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.)

    // Use the constructor to create a new Synthesizer

    Synth := dnSpeech.SpeechSynthesizer();

    // http://msdn.microsoft.com/en-us/library/system.speech.synthesis.voicegender(VS.90).aspx

    // This is an enumeration assignment:

    // 0=NotSet,1=Male,2=Female,3=Netural

    dnVoiceGender := 2; //Female

    // NOTE: it will take the voice depending on what is

    //  installed locally (e.g. Windows 7 has Microsoft Anne voice)

    Synth.SelectVoiceByHints(dnVoiceGender);

    // Please, speak slowly (range [-10:10])

    Synth.Rate := -3;

    Synth.SetOutputToDefaultAudioDevice();

    Synth.Speak(FORMAT(Name));

    // Row below is not needed. Synth is a local variable therefore it will be

    // automatically disposed

    // Synth.Dispose;

    ...

    12. Save and compile (ctrl+s) page 22 "Customer List"

    Now... you are ready to let NAV speech the Customer Name from the customer list by simply click on the "Speak it!" action.

    These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.

    Best Regards,

    Duilio Tacconi (dtacconi)

    Microsoft Dynamics Italy

    Microsoft Customer Service and Support (CSS) EMEA

Page 4 of 46 (682 items) «23456»