Microsoft Dynamics NAV

Team Blog

August, 2008

  • Microsoft Dynamics NAV Team Blog

    NAV 2009 - How to generate charts / KPIs

    • 5 Comments

    Microsoft Dynamics NAV 2009 has a new graphical object type called Charts or KPIs (Key Performance indicators). It is a way to create simple graphical charts, to give a visual overview of key figures. This post describes what you can and cannot do with charts, and it contains some sample code to how you might create a "Chart Generator Tool" to make it easier to generate new charts.

    What you can and can't do with charts:

    • A chart must be based on 1 table, and 1 table only. You can't combine data from multiple tables.
    • You have two basic chart types: Points or Columns.
    • You can have any colour as long as you want green.
    • Charts can use one of two operators: Sum or Count.
    • You can only apply static filters in charts, i.e. you must type in (hardcode) the filter when you generate the chart.

    The layout of a chart:

    A chart has a simple layout. You choose the table to base it on, then a field from that table to show along the X-Axis, and - if needed - a filter on this table. Then you select what data from the table to show on the Y-Axis (also called Measure). A measure can be based on Sum or Count. You can display multiple measures in the same chart.

    This is what your charts could look like:

    Charts

    Showing a chart:

    Charts can only be displayed in the new NAV 2009 client. To display a chart, follow these steps:

    • Start the new client, then click on the "Customize" button in the top right corner (just left of the ?-button), and select "Customize this page".
    • From there you can add a new Chart part. So add one of them, move it left / right / up and down to where on the page you want it.
    • Click the "Customize Part"-button to select which chart to display. This button looks up into the Chart-table in NAV (table 2000000078) and lets you select a chart.

    Removing a chart:

    You can personalize the page again to remove a chart, or click "Restore Defaults" to remove any personalization. You can also remove any personalization (including any charts that a user has added) with the classic client, from table 2000000075 "User Metadata". This is the table that stores the personalization for each page for each user. Deleting a record from this table removes any personalization made to that page by that user.

    Chart objects:

    Charts are stored in system table 2000000078 "Chart". You can view them with form 9182 Charts, or from Administration -> Application Setup -> Role Tailored Client -> Charts.

    Charts are defined by xml documents which are stored as BLOB fields. You can export a chart to an xml document, then modify the xml document, and import it back as a new chart.

    This is an example of a chart definition:

    ChartDef

    The rules for the xml documents are:


    • You must specify exactly one Table ID.
    • You can only have one X-Axis.
    • You can only have one Filters-element, but it can contain multiple Filter-elements.
    • You can only have ony Y-Axis, but it can contain multiple measures.
    • Measure operator must be either Count or Sum.

    Ideas for a Chart Generator Tool (CGT):

    Modifying xml documents can be tedious. But it can be made easier with tools. Below, I have put some sample NAV code, how such a tool could be made.

    Note !!!! ===
    The code below is only supplied as an example of how such a tool could be made. It is completely un-supported and to be used at your own risk and responsibility.

    ====

    The code below is provided in Text format. When importing text objects into NAV directly, you will not get any warnings whether to overwrite existing objects. So each object has been deliberately changed by adding [RemoveMe] in front of each Object ID. This would have to be removed, after reviewing each Object type and number, to check that it would not overwrite any existing objects!

    This is how the tool would work:

    the tool contains the following objects:

    Type    ID    Name
    1    72000    Chart Generator
    1    72001    Chart Generator Filter
    1    72003    Chart Generator YAxis
    2    72000    Chart Generator List
    2    72001    Chart Filters
    2    72003    YAxis List
    2    72004    Chart Generator Card
    5    72000    Chart Generator Mgt

    Charts can be generated from form 72004 "Chart Generator Card":

    Hit F3 to create a new chart. Enter ID and a Name. As a minimum you must specify Title, "Table ID", "X-Axis Field Name", and one or more Y-Axis fields. Once you have specified the chart definition, then click on Chart -> "Generate Chart". This will automatically either update an existing chart, or generate a new chart in table 2000000078, ready to be used from the new client.

    The following example shows how to create a chart to show Inventory for certain items:

    1. Create a new chart (F3) from the Chart Generator card. As ID, specify INVENTORY.
    2. As name, also enter Inventory.
    3. Set Table ID = 27 (Item).
    4. Leave Type as Column, and select what to display along the X-Axis - let's choose Description.
    5. Click on Chart -> Y-Axis (or drill down on the "YAxis Fields"-field). This is where we choose the measure, in our case Inventory. So look up in the field "Y-Axis Measure Field ID" and select Inventory.
    6. Leave the Caption, and the "Measure operator" to their default values, and go back to the Card Generator form.
    7. Finally, to avoid showing 100s of items on the chart, click on chart -> Filters.
    8. In the Filters-list, look up in the field "Filter Field ID" and select "No.", then enter a filter value of 70000|70001|70002|70003

    Now the definition of the chart is done. To make it available in the new client, click chart -> Generate Chart (F11). If you want to change the chart definition, then just make the necessary changes from the "Chart Generator Card", then hit F11 again to update the existing chart.

    Now the chart will be available for the new client as described above. If you update the chart, then the new client will use the new chart definition next time you restart it.

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

    Lars Lohndorf-Larsen (Lohndorf)
    Microsoft Dynamics UK
    Microsoft Customer Service and Support (CSS) EMEA

    === sample code only ===

    === 

    Updated 19/10 2008

    Updated version of the tool on this post:

    3D charts. Chart Generator tool II 

    This version of the tool also has Z-axis for 3D charts

    ===

     

    OBJECT Table [RemoveMe]72000 Chart Generator

    {

      OBJECT-PROPERTIES

      {

        Date=19/08/08;

        Time=09:13:04;

        Modified=Yes;

        Version List=CGT;

      }

      PROPERTIES

      {

        OnInsert=BEGIN

                   IF Chart.GET(Company,ID) THEN

                     ERROR('Chart already exists in table 2000000078. Run form 9182 and delete it, or use a different ID.');

                 END;

     

        LookupFormID=Form72000;

      }

      FIELDS

      {

        { 1   ;   ;Company             ;Text30         }

        { 2   ;   ;ID                  ;Code20        ;NotBlank=Yes }

        { 10  ;   ;Name                ;Text50        ;OnValidate=BEGIN

                                                                    Title := Name;

                                                                  END;

                                                                   }

        { 11  ;   ;Title               ;Text50         }

        { 15  ;   ;Type                ;Option        ;OptionString=Column,Point }

        { 16  ;   ;Table ID            ;Integer       ;OnValidate=BEGIN

                                                                    MODIFY;

                                                                  END;

                                                                   }

        { 17  ;   ;Table Name          ;Text30        ;FieldClass=FlowField;

                                                       CalcFormula=Lookup(Object.Name WHERE (Type=CONST(Table),

                                                                                             ID=FIELD(Table ID)));

                                                       Editable=No }

        { 20  ;   ;XAxis Field ID      ;Integer        }

        { 21  ;   ;XAxis Field Name    ;Text80        ;OnValidate=BEGIN

                                                                    IF "XAxis Field Name" <> '' THEN BEGIN

                                                                      FieldRec.SETRANGE(TableNo,"Table ID");

                                                                      FieldRec.SETRANGE(FieldName,"XAxis Field Name");

                                                                      FieldRec.FINDFIRST;

                                                                      "XAxis Field ID" := FieldRec."No.";

                                                                      "XAxis Field Name" := FieldRec.FieldName;

                                                                      "XAxis Field Caption" := FieldRec."Field Caption";

                                                                      "XAxis title" := FieldRec."Field Caption";

                                                                    END ELSE BEGIN

                                                                      "XAxis Field ID" := 0;

                                                                      "XAxis Field Name" := '';

                                                                      "XAxis Field Caption" := '';

                                                                      "XAxis title" := '';

                                                                    END;

                                                                  END;

                                                                   }

        { 22  ;   ;XAxis Field Caption ;Text80         }

        { 23  ;   ;XAxis title         ;Text80         }

        { 24  ;   ;Show Title          ;Boolean       ;InitValue=Yes }

        { 30  ;   ;YAxis fields        ;Integer       ;FieldClass=FlowField;

                                                       CalcFormula=Count("Chart Generator YAxis" WHERE (Company=FIELD(Company),

                                                                                                        ID=FIELD(ID)));

                                                       Editable=No }

      }

      KEYS

      {

        {    ;Company,ID                              ;Clustered=Yes }

      }

      FIELDGROUPS

      {

      }

      CODE

      {

        VAR

          Chart@1000 : Record 2000000078;

          FieldRec@1102601000 : Record 2000000041;

     

        BEGIN

        END.

      }

    }

     

    OBJECT Table [RemoveMe]72001 Chart Generator Filter

    {

      OBJECT-PROPERTIES

      {

        Date=19/08/08;

        Time=16:44:37;

        Modified=Yes;

        Version List=CGT;

      }

      PROPERTIES

      {

        OnInsert=BEGIN

                   TESTFIELD("Filter Field ID");

                 END;

     

      }

      FIELDS

      {

        { 1   ;   ;Company             ;Text30         }

        { 2   ;   ;ID                  ;Code20         }

        { 3   ;   ;Line No.            ;Integer        }

        { 10  ;   ;Filter Field ID     ;Integer       ;OnValidate=BEGIN

                                                                    IF "Filter Field ID" <> 0 THEN BEGIN

                                                                       ChartGen.GET(Company,ID);

                                                                       ChartGen.TESTFIELD("Table ID");

                                                                       FieldRec.GET(ChartGen."Table ID","Filter Field ID");

                                                                       "Filter Field Name" := FieldRec.FieldName;

                                                                    END ELSE

                                                                       "Filter Field Name" := '';

                                                                  END;

                                                                   }

        { 11  ;   ;Filter Field Name   ;Text30         }

        { 15  ;   ;Filter Value        ;Text30         }

      }

      KEYS

      {

        {    ;Company,ID,Line No.                     ;Clustered=Yes }

      }

      FIELDGROUPS

      {

      }

      CODE

      {

        VAR

          ChartGen@1102601001 : Record 72000;

          FieldRec@1102601000 : Record 2000000041;

     

        BEGIN

        END.

      }

    }

     

    OBJECT Table [RemoveMe]72003 Chart Generator YAxis

    {

      OBJECT-PROPERTIES

      {

        Date=19/08/08;

        Time=07:26:24;

        Modified=Yes;

        Version List=CGT;

      }

      PROPERTIES

      {

        OnInsert=BEGIN

                   TESTFIELD("YAxis Measure Field ID");

                 END;

     

        LookupFormID=Form72003;

        DrillDownFormID=Form72003;

      }

      FIELDS

      {

        { 1   ;   ;Company             ;Text30         }

        { 2   ;   ;ID                  ;Code20         }

        { 3   ;   ;Line No.            ;Integer        }

        { 10  ;   ;YAxis Measure Field ID;Integer      }

        { 11  ;   ;YAxis Measure Field Caption;Text30  }

        { 12  ;   ;Mearure Operator    ;Option        ;OptionString=Sum,Count }

        { 20  ;   ;Show Title          ;Boolean        }

      }

      KEYS

      {

        {    ;Company,ID,Line No.                     ;Clustered=Yes }

      }

      FIELDGROUPS

      {

      }

      CODE

      {

     

        BEGIN

        END.

      }

    }

     

    OBJECT Form [RemoveMe]72000 Chart Generator List

    {

      OBJECT-PROPERTIES

      {

        Date=12/08/08;

        Time=06:50:19;

        Modified=Yes;

        Version List=CGT;

      }

      PROPERTIES

      {

        Width=16500;

        Height=6710;

        TableBoxID=1;

        SourceTable=Table72000;

      }

      CONTROLS

      {

        { 1   ;TableBox     ;220  ;220  ;16060;5500 ;HorzGlue=Both;

                                                     VertGlue=Both }

        { 2   ;TextBox      ;0    ;0    ;4400 ;0    ;HorzGlue=Both;

                                                     Visible=No;

                                                     ParentControl=1;

                                                     InColumn=Yes;

                                                     SourceExpr=Company }

        { 3   ;Label        ;0    ;0    ;0    ;0    ;ParentControl=2;

                                                     InColumnHeading=Yes }

        { 4   ;TextBox      ;0    ;0    ;1700 ;0    ;ParentControl=1;

                                                     InColumn=Yes;

                                                     SourceExpr=ID }

        { 5   ;Label        ;0    ;0    ;0    ;0    ;ParentControl=4;

                                                     InColumnHeading=Yes }

        { 6   ;TextBox      ;0    ;0    ;4400 ;0    ;ParentControl=1;

                                                     InColumn=Yes;

                                                     SourceExpr=Name }

        { 7   ;Label        ;0    ;0    ;0    ;0    ;ParentControl=6;

                                                     InColumnHeading=Yes }

        { 8   ;TextBox      ;0    ;0    ;4400 ;0    ;ParentControl=1;

                                                     InColumn=Yes;

                                                     SourceExpr=Title }

        { 9   ;Label        ;0    ;0    ;0    ;0    ;ParentControl=8;

                                                     InColumnHeading=Yes }

        { 10  ;TextBox      ;0    ;0    ;550  ;0    ;ParentControl=1;

                                                     InColumn=Yes;

                                                     SourceExpr=Type }

        { 11  ;Label        ;0    ;0    ;0    ;0    ;ParentControl=10;

                                                     InColumnHeading=Yes }

        { 12  ;TextBox      ;0    ;0    ;1700 ;0    ;ParentControl=1;

                                                     InColumn=Yes;

                                                     SourceExpr="Table ID" }

        { 13  ;Label        ;0    ;0    ;0    ;0    ;ParentControl=12;

                                                     InColumnHeading=Yes }

        { 14  ;CommandButton;4400 ;5940 ;2200 ;550  ;HorzGlue=Right;

                                                     VertGlue=Bottom;

                                                     Default=Yes;

                                                     PushAction=LookupOK;

                                                     InvalidActionAppearance=Hide }

        { 15  ;CommandButton;6820 ;5940 ;2200 ;550  ;HorzGlue=Right;

                                                     VertGlue=Bottom;

                                                     Cancel=Yes;

                                                     PushAction=LookupCancel;

                                                     InvalidActionAppearance=Hide }

        { 16  ;CommandButton;14080;5940 ;2200 ;550  ;HorzGlue=Right;

                                                     VertGlue=Bottom;

                                                     PushAction=FormHelp }

        { 17  ;MenuButton   ;9240 ;5940 ;2200 ;550  ;HorzGlue=Right;

                                                     VertGlue=Bottom;

                                                     CaptionML=ENU=Chart;

                                                     Menu=MENUITEMS

                                                     {

                                                       { ID=18;

                                                         PushAction=RunObject;

                                                         CaptionML=ENU=Filters;

                                                         RunObject=Form 72001;

                                                         RunFormLink=Company=FIELD(Company),

                                                                     ID=FIELD(ID) }

                                                       { ID=20;

                                                         PushAction=RunObject;

                                                         CaptionML=ENU=XAxis;

                                                         RunObject=Form 72002;

                                                         RunFormLink=Field1=FIELD(Company),

                                                                     Field2=FIELD(ID) }

                                                       { ID=21;

                                                         PushAction=RunObject;

                                                         CaptionML=ENU=YAxis;

                                                         RunObject=Form 72003;

                                                         RunFormLink=Company=FIELD(Company),

                                                                     ID=FIELD(ID) }

                                                     }

                                                      }

        { 19  ;MenuButton   ;11660;5940 ;2200 ;550  ;HorzGlue=Right;

                                                     VertGlue=Bottom;

                                                     CaptionML=ENU=F&unctions;

                                                     Menu=MENUITEMS

                                                     {

                                                       { ID=22;

                                                         PushAction=RunObject;

                                                         ShortCutKey=F11;

                                                         CaptionML=ENU=Generate Chart;

                                                         RunObject=Codeunit 72000 }

                                                     }

                                                      }

      }

      CODE

      {

        VAR

          Chart@1000 : Record 2000000078;

     

        BEGIN

        END.

      }

    }

     

    OBJECT Form [RemoveMe]72001 Chart Filters

    {

      OBJECT-PROPERTIES

      {

        Date=19/08/08;

        Time=09:21:22;

        Modified=Yes;

        Version List=CGT;

      }

      PROPERTIES

      {

        Width=12150;

        Height=6710;

        TableBoxID=1;

        SourceTable=Table72001;

        AutoSplitKey=Yes;

        DelayedInsert=Yes;

      }

      CONTROLS

      {

        { 1   ;TableBox     ;220  ;220  ;11710;5500 ;HorzGlue=Both;

                                                     VertGlue=Both }

        { 2   ;TextBox      ;0    ;0    ;1700 ;0    ;ParentControl=1;

                                                     InColumn=Yes;

                                                     SourceExpr="Filter Field ID";

                                                     OnLookup=BEGIN

                                                                ChartGen.GET(Company,ID);

                                                                ChartGen.TESTFIELD("Table ID");

                                                                FieldRec.SETRANGE(TableNo,ChartGen."Table ID");

     

                                                                IF FORM.RUNMODAL(FORM::"Field List",FieldRec) = ACTION::LookupOK THEN

                                                                  VALIDATE("Filter Field ID",FieldRec."No.");

                                                              END;

                                                               }

        { 3   ;Label        ;0    ;0    ;0    ;0    ;ParentControl=2;

                                                     InColumnHeading=Yes }

        { 4   ;TextBox      ;0    ;0    ;4400 ;0    ;HorzGlue=Both;

                                                     ParentControl=1;

                                                     InColumn=Yes;

                                                     SourceExpr="Filter Field Name" }

        { 5   ;Label        ;0    ;0    ;0    ;0    ;ParentControl=4;

                                                     InColumnHeading=Yes }

        { 6   ;TextBox      ;0    ;0    ;4400 ;0    ;ParentControl=1;

                                                     InColumn=Yes;

                                                     SourceExpr="Filter Value" }

        { 7   ;Label        ;0    ;0    ;0    ;0    ;ParentControl=6;

                                                     InColumnHeading=Yes }

        { 8   ;CommandButton;4890 ;5940 ;2200 ;550  ;HorzGlue=Right;

                                                     VertGlue=Bottom;

                                                     Default=Yes;

                                                     PushAction=LookupOK;

                                                     InvalidActionAppearance=Hide }

        { 9   ;CommandButton;7310 ;5940 ;2200 ;550  ;HorzGlue=Right;

                                                     VertGlue=Bottom;

                                                     Cancel=Yes;

                                                     PushAction=LookupCancel;

                                                     InvalidActionAppearance=Hide }

        { 10  ;CommandButton;9730 ;5940 ;2200 ;550  ;HorzGlue=Right;

                                                     VertGlue=Bottom;

                                                     PushAction=FormHelp }

      }

      CODE

      {

        VAR

          ChartGen@1000 : Record 72000;

          FieldRec@1001 : Record 2000000041;

     

        BEGIN

        END.

      }

    }

     

    OBJECT Form [RemoveMe]72003 YAxis List

    {

      OBJECT-PROPERTIES

      {

        Date=19/08/08;

        Time=07:31:05;

        Modified=Yes;

        Version List=CGT;

      }

      PROPERTIES

      {

        Width=10000;

        Height=6710;

        TableBoxID=1;

        SourceTable=Table72003;

        AutoSplitKey=Yes;

        DelayedInsert=Yes;

      }

      CONTROLS

      {

        { 1   ;TableBox     ;220  ;220  ;9560 ;5500 ;HorzGlue=Both;

                                                     VertGlue=Both }

        { 2   ;TextBox      ;0    ;0    ;1700 ;0    ;ParentControl=1;

                                                     InColumn=Yes;

                                                     SourceExpr="YAxis Measure Field ID";

                                                     OnLookup=BEGIN

                                                                ChartGen.GET(Company,ID);

                                                                ChartGen.TESTFIELD("Table ID");

                                                                FieldRec.SETRANGE(TableNo,ChartGen."Table ID");

     

                                                                IF FORM.RUNMODAL(FORM::"Field List",FieldRec) = ACTION::LookupOK THEN BEGIN

                                                                  "YAxis Measure Field ID" := FieldRec."No.";

                                                                  "YAxis Measure Field Caption" := FieldRec."Field Caption";

                                                                END;

                                                              END;

                                                               }

        { 3   ;Label        ;0    ;0    ;0    ;0    ;ParentControl=2;

                                                     InColumnHeading=Yes }

        { 4   ;TextBox      ;0    ;0    ;4400 ;0    ;HorzGlue=Both;

                                                     ParentControl=1;

                                                     InColumn=Yes;

                                                     SourceExpr="YAxis Measure Field Caption" }

        { 5   ;Label        ;0    ;0    ;0    ;0    ;ParentControl=4;

                                                     InColumnHeading=Yes }

        { 6   ;TextBox      ;0    ;0    ;550  ;0    ;ParentControl=1;

                                                     InColumn=Yes;

                                                     SourceExpr="Mearure Operator" }

        { 7   ;Label        ;0    ;0    ;0    ;0    ;ParentControl=6;

                                                     InColumnHeading=Yes }

        { 8   ;CheckBox     ;0    ;0    ;1700 ;0    ;ParentControl=1;

                                                     InColumn=Yes;

                                                     ShowCaption=No;

                                                     SourceExpr="Show Title" }

        { 9   ;Label        ;0    ;0    ;0    ;0    ;ParentControl=8;

                                                     InColumnHeading=Yes }

        { 10  ;CommandButton;2740 ;5940 ;2200 ;550  ;HorzGlue=Right;

                                                     VertGlue=Bottom;

                                                     Default=Yes;

                                                     PushAction=LookupOK;

                                                     InvalidActionAppearance=Hide }

        { 11  ;CommandButton;5160 ;5940 ;2200 ;550  ;HorzGlue=Right;

                                                     VertGlue=Bottom;

                                                     Cancel=Yes;

                                                     PushAction=LookupCancel;

                                                     InvalidActionAppearance=Hide }

        { 12  ;CommandButton;7580 ;5940 ;2200 ;550  ;HorzGlue=Right;

                                                     VertGlue=Bottom;

                                                     PushAction=FormHelp }

      }

      CODE

      {

        VAR

          ChartGen@1000 : Record 72000;

          FieldRec@1001 : Record 2000000041;

     

        BEGIN

        END.

      }

    }

     

    OBJECT Form [RemoveMe]72004 Chart Generator Card

    {

      OBJECT-PROPERTIES

      {

        Date=19/08/08;

        Time=08:17:28;

        Modified=Yes;

        Version List=CGT;

      }

      PROPERTIES

      {

        Width=16170;

        Height=6490;

        SourceTable=Table72000;

      }

      CONTROLS

      {

        { 1   ;TabControl   ;220  ;220  ;15730;5280 ;HorzGlue=Both;

                                                     VertGlue=Both;

                                                     PageNamesML=ENU=General }

        { 4   ;TextBox      ;3850 ;990  ;2750 ;440  ;ParentControl=1;

                                                     InPage=0;

                                                     SourceExpr=ID }

        { 5   ;Label        ;440  ;990  ;3300 ;440  ;ParentControl=4 }

        { 6   ;TextBox      ;3850 ;1650 ;5500 ;440  ;ParentControl=1;

                                                     InPage=0;

                                                     NextControl=12;

                                                     SourceExpr=Name }

        { 7   ;Label        ;440  ;1650 ;3300 ;440  ;ParentControl=6 }

        { 8   ;TextBox      ;3850 ;2200 ;5500 ;440  ;ParentControl=1;

                                                     InPage=0;

                                                     SourceExpr=Title }

        { 9   ;Label        ;440  ;2200 ;3300 ;440  ;ParentControl=8 }

        { 10  ;TextBox      ;3850 ;3520 ;2750 ;440  ;ParentControl=1;

                                                     InPage=0;

                                                     SourceExpr=Type }

        { 11  ;Label        ;440  ;3520 ;3300 ;440  ;ParentControl=10 }

        { 12  ;TextBox      ;3850 ;2860 ;1700 ;440  ;ParentControl=1;

                                                     InPage=0;

                                                     SourceExpr="Table ID";

                                                     OnLookup=BEGIN

                                                                Object.SETRANGE(Type,Object.Type::Table);

                                                                IF FORM.RUNMODAL(FORM::Objects,Object) = ACTION::LookupOK THEN

                                                                  "Table ID" := Object.ID;

                                                              END;

     

                                                     OnAfterValidate=BEGIN

                                                                       CALCFIELDS("Table Name");

                                                                     END;

                                                                      }

        { 13  ;Label        ;440  ;2860 ;3300 ;440  ;ParentControl=12 }

        { 21  ;TextBox      ;12980;990  ;2750 ;440  ;ParentControl=1;

                                                     InPage=0;

                                                     NextControl=1102601000;

                                                     SourceExpr="XAxis Field Name";

                                                     OnLookup=BEGIN

                                                                TESTFIELD("Table ID");

                                                                FieldRec.SETRANGE(TableNo,"Table ID");

     

                                                                IF FORM.RUNMODAL(FORM::"Field List",FieldRec) = ACTION::LookupOK THEN

                                                                  VALIDATE("XAxis Field Name",FieldRec.FieldName);

                                                              END;

                                                               }

        { 22  ;Label        ;9570 ;990  ;3300 ;440  ;ParentControl=21 }

        { 20  ;TextBox      ;12980;1650 ;2750 ;440  ;ParentControl=1;

                                                     InPage=0;

                                                     SourceExpr="XAxis Field Caption" }

        { 23  ;Label        ;9570 ;1650 ;3300 ;440  ;ParentControl=20 }

        { 24  ;CheckBox     ;12980;2750 ;440  ;440  ;ParentControl=1;

                                                     InPage=0;

                                                     ShowCaption=No;

                                                     SourceExpr="Show Title" }

        { 25  ;Label        ;9570 ;2750 ;3300 ;440  ;ParentControl=24 }

        { 27  ;TextBox      ;12980;2200 ;2750 ;440  ;ParentControl=1;

                                                     InPage=0;

                                                     SourceExpr="XAxis title" }

        { 28  ;Label        ;9570 ;2200 ;3300 ;440  ;ParentControl=27 }

        { 1102601000;TextBox;12980;3410 ;1700 ;440  ;ParentControl=1;

                                                     InPage=0;

                                                     SourceExpr="YAxis fields" }

        { 1102601001;Label  ;9570 ;3410 ;3300 ;440  ;ParentControl=1102601000 }

        { 1102601002;TextBox;5720 ;2860 ;3630 ;440  ;ParentControl=1;

                                                     InPage=0;

                                                     SourceExpr="Table Name" }

        { 14  ;CommandButton;13750;5720 ;2200 ;550  ;HorzGlue=Right;

                                                     VertGlue=Bottom;

                                                     PushAction=FormHelp }

        { 15  ;MenuButton   ;11330;5720 ;2200 ;550  ;HorzGlue=Right;

                                                     VertGlue=Bottom;

                                                     CaptionML=ENU=&Chart;

                                                     Menu=MENUITEMS

                                                     {

                                                       { ID=16;

                                                         PushAction=LookupTable;

                                                         ShortCutKey=F5;

                                                         CaptionML=ENU=L&ist }

                                                       { ID=26;

                                                         PushAction=RunObject;

                                                         CaptionML=ENU=Filters;

                                                         RunObject=Form 72001;

                                                         RunFormLink=Company=FIELD(Company),

                                                                     ID=FIELD(ID) }

                                                       { ID=17;

                                                         PushAction=RunObject;

                                                         CaptionML=ENU=YAxis;

                                                         RunObject=Form 72003;

                                                         RunFormLink=Company=FIELD(Company),

                                                                     ID=FIELD(ID) }

                                                       { ID=18;

                                                         MenuItemType=Separator }

                                                       { ID=19;

                                                         PushAction=RunObject;

                                                         ShortCutKey=F11;

                                                         CaptionML=ENU=Generate Chart;

                                                         RunObject=Codeunit 72000 }

                                                     }

                                                      }

      }

      CODE

      {

        VAR

          FieldRec@1001 : Record 2000000041;

          Object@1002 : Record 2000000001;

          XAxisField@1000 : Text[30];

     

        BEGIN

        END.

      }

    }

     

    OBJECT Codeunit [RemoveMe]72000 Chart Generator Mgt

    {

      OBJECT-PROPERTIES

      {

        Date=19/08/08;

        Time=16:47:56;

        Modified=Yes;

        Version List=CGT;

      }

      PROPERTIES

      {

        TableNo=72000;

        OnRun=BEGIN

                CreateXML(Rec);

     

                MESSAGE('Chart %1 was created / updated.',Chart.ID);

              END;

     

      }

      CODE

      {

        VAR

          Chart@1015 : Record 2000000078;

          ChartGen@1003 : Record 72000;

          ChartFilters@1008 : Record 72001;

          ChartYAxis@1012 : Record 72003;

          "3TierMgt"@1013 : Codeunit 419;

          XMLDoc@1000 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 4.0:{F6D90F11-9C73-11D3-B32E-00C04F990BB4}:'Microsoft XML, v4.0'.DOMDocument";

          DomNode@1001 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 4.0:{2933BF80-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v4.0'.IXMLDOMNode";

          DomNode2@1006 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 4.0:{2933BF80-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v4.0'.IXMLDOMNode";

          DomTextNode@1002 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 4.0:{2933BF87-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v4.0'.IXMLDOMText";

          DomAttribute@1004 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 4.0:{2933BF85-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v4.0'.IXMLDOMAttribute";

          DomNodeList@1009 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 4.0:{2933BF82-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v4.0'.IXMLDOMNodeList";

          DomProcessInstruction@1005 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 4.0:{2933BF89-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v4.0'.IXMLDOMProcessingInstruction";

          NameSpace@1007 : Text[80];

          i@1010 : Integer;

          TempFileName@1014 : Text[250];

          Debug@1016 : Boolean;

          DebugFileName@1102601000 : Text[250];

          IStream@1102601001 : InStream;

     

        PROCEDURE CreateXML@1(ChartGenerator@1000 : Record 72000);

        BEGIN

          //=== Enable debug and filename to save the xml document to disk

          Debug := FALSE;

          DebugFileName := '';

          //===

     

          CREATE(XMLDoc);

          XMLDoc.async(FALSE);

     

          // Initialize document and set namespaces

          NameSpace := 'urn:schemas-microsoft-com:dynamics:NAV:MetaObjects';

     

          DomNode := XMLDoc.createNode(1,'ChartDefinition',NameSpace);

          DomAttribute := XMLDoc.createAttribute('xmlns:xsd');

          DomAttribute.value := 'http://www.w3.org/2001/XMLSchema';

          DomNode.attributes.setNamedItem(DomAttribute);

          DomAttribute := XMLDoc.createAttribute('xmlns:xsi');

          DomAttribute.value := 'http://www.w3.org/2001/XMLSchema-instance';

          DomNode.attributes.setNamedItem(DomAttribute);

     

          // add chart type

          DomAttribute := XMLDoc.createAttribute('Type');

          CASE ChartGenerator.Type OF

            ChartGenerator.Type::Column:

              DomAttribute.value := 'Column';

            ChartGenerator.Type::Point:

              DomAttribute.value := 'Point';

          END;

          DomNode.attributes.setNamedItem(DomAttribute);

          XMLDoc.appendChild(DomNode);

     

          AddNode('ChartDefinition','Title','');

          AddNode('ChartDefinition/Title','Text',ChartGenerator.Title);

          AddAttribute('ChartDefinition/Title/Text','ID','ENU');

          AddNode('ChartDefinition','Table','');

          AddAttribute('ChartDefinition/Table','ID',FORMAT(ChartGenerator."Table ID"));

          AddNode('ChartDefinition/Table','Filters','');

     

          // Filters

          ChartFilters.SETRANGE(Company,ChartGenerator.Company);

          ChartFilters.SETRANGE(ID,ChartGenerator.ID);

          IF ChartFilters.FINDSET THEN

            REPEAT

              AddNode('ChartDefinition/Table/Filters','Filter','');

              AddNode('ChartDefinition/Table/Filters/Filter','Field','');

              AddAttribute('ChartDefinition/Table/Filters/Filter/Field','Name',ChartFilters."Filter Field Name");

              AddNode('ChartDefinition/Table/Filters/Filter','Value',ChartFilters."Filter Value");

            UNTIL ChartFilters.NEXT = 0;

     

          // XAxis

          ChartGenerator.TESTFIELD("XAxis Field Name");

     

          AddNode('ChartDefinition','XAxis','');

          AddAttribute('ChartDefinition/XAxis','ShowTitle',BooleanFormat(ChartGenerator."Show Title")); ///

          AddNode('ChartDefinition/XAxis','Title','');

          AddNode('ChartDefinition/XAxis/Title','Text',ChartGenerator."XAxis title");

          AddAttribute('ChartDefinition/XAxis/Title/Text','ID','ENU');

          AddNode('ChartDefinition/XAxis','Field','');

          AddAttribute('ChartDefinition/XAxis/Field','Name',ChartGenerator."XAxis Field Name");

     

          // YAxis

          ChartYAxis.SETRANGE(Company,ChartGenerator.Company);

          ChartYAxis.SETRANGE(ID,ChartGenerator.ID);

          IF ChartYAxis.FINDSET THEN BEGIN

            AddNode('ChartDefinition','YAxis','');

            AddAttribute('ChartDefinition/YAxis','ShowTitle',BooleanFormat(ChartYAxis."Show Title"));

            AddNode('ChartDefinition/YAxis','Measures','');

     

            REPEAT

              AddNode('ChartDefinition/YAxis/Measures','Measure','');

              AddAttribute('ChartDefinition/YAxis/Measures/Measure','Operator',FORMAT(ChartYAxis."Mearure Operator"));

              AddNode('ChartDefinition/YAxis/Measures/Measure','Field','');

              AddAttribute('ChartDefinition/YAxis/Measures/Measure/Field','Name',FORMAT(ChartYAxis."YAxis Measure Field Caption"));

            UNTIL ChartYAxis.NEXT = 0;

          END;

     

          TempFileName := TEMPORARYPATH + '\' + FORMAT(CREATEGUID) + '.xml';

     

          XMLDoc.save(TempFileName);

     

          IF NOT Chart.GET(ChartGenerator.Company,ChartGenerator.ID) THEN BEGIN

            Chart.Company := ChartGenerator.Company;

            Chart.ID := ChartGenerator.ID;

            Chart.Name := ChartGenerator.Name;

            Chart.INSERT;

          END;

          Chart.BLOB.IMPORT(TempFileName);

          Chart.MODIFY;

     

          IF Debug THEN

            XMLDoc.save(DebugFileName);

     

          // Clean up

          IF EXISTS(TempFileName) THEN

            ERASE(TempFileName);

          CLEAR(DomNode);

          CLEAR(XMLDoc);

        END;

     

        PROCEDURE AddNode@2(AddToNode@1000 : Text[120];NodeName@1001 : Text[120];NodeValue@1002 : Text[120]);

        BEGIN

          DomNodeList := XMLDoc.selectNodes(AddToNode);

          DomNode := DomNodeList.item(DomNodeList.length - 1);

     

          DomNode2 := XMLDoc.createNode(1,NodeName,NameSpace);

          DomTextNode := XMLDoc.createTextNode(NodeValue);

          DomNode2.appendChild(DomTextNode);

          DomNode.appendChild(DomNode2);

        END;

     

        PROCEDURE AddAttribute@3(AddToNode@1000 : Text[120];AttributeName@1001 : Text[120];AttributeValue@1002 : Text[120]);

        BEGIN

          DomNodeList := XMLDoc.selectNodes(AddToNode);

          DomNode := DomNodeList.item(DomNodeList.length - 1);

     

          DomAttribute := XMLDoc.createAttribute(AttributeName);

          DomAttribute.value := AttributeValue;

          DomNode.attributes.setNamedItem(DomAttribute);

        END;

     

        PROCEDURE BooleanFormat@4(Boo@1000 : Boolean) : Text[30];

        BEGIN

          IF Boo THEN

            EXIT('true');

     

          EXIT('false');

        END;

     

        EVENT XMLDoc@1000::ondataavailable@198();

        BEGIN

        END;

     

        EVENT XMLDoc@1000::onreadystatechange@-609();

        BEGIN

        END;

     

        BEGIN

        END.

      }

    }

     

  • Microsoft Dynamics NAV Team Blog

    NAV 2009 - Sample C# project to consume a NAV web service

    • 2 Comments

    This post is a step-by-step guide, how to make a very simple C# project (3 lines of code) to integrate to NAV 2009 via a web service.

    == Blog updated on 21/01 2009:

    Line added in the sample code below, to avoid error “Path property must be set before calling the Send method” when making an update via the web service

    ==

     

    For simplicity, make a new codeunit in NAV with one function, for example like this:

     

    OBJECT Codeunit 78000 Test Web Service
    {
      OBJECT-PROPERTIES
      {
        Date=15/08/08;
        Time=07:40:24;
        Modified=Yes;
        Version List=;
      }
      PROPERTIES
      {
        OnRun=BEGIN
              END;

      }
      CODE
      {

        PROCEDURE AddX@1102601000(Value@1102601000 : Text[30]) : Text[30];
        BEGIN
          EXIT(Value + 'x');
        END;

        BEGIN
        END.
      }
    }

     

    The function just adds 'x' to whatever Text-parameter you give it, and returns the new value.

     

    Publishing this codeunit as a web service is a simple as just specifying it in form 810 "Web Services", giving it a name - for example "TestWebService" and tick "Publish".

    Before you continue, check that your new web service is available by going to this link in your internet browser (modify it to match your system):

    http://[MachineName]:[Port]/[InstanceName]/ws/[CompanyName]/Services, for example 

    http://MyMachine:7047/DynamicsNAV/ws/CRONUS_International_Ltd./Services

    If you can't see your new web service here, then first check if the "Microsoft Dynamics NAV Business Web Services"-service is running. Then (re)start it and check in the application log which port it is listening to. For more details about publishing a Web Service from NAV, go to this post: "NAV 2009 - How to publish a web service". 

     

    When you can see that your web service is available, then you are finished with NAV. The rest of the work happens in Visual Studio (VS). You can use either VS2005 or 2008. Follow these steps:

    1)  After opening VS, select File -> New -> Project. Select a Visual C# project, and  under templates, select a "Windows Forms Application" (in VS2005 it is just called "Windows Application").

    2)  This step depends a bit on whether you use VS2005 or 2008. In VS2005, just rightclick on "References" in the Solution Explorer, and select "Add Web Reference". In VS2008, to get to the same place, rightclick on "References", then select "Add Service Reference", then click the Advanced button, and then click the "Add Web Reference" button.

    3)  In URL, enter the link to your web services - the same link as above, and click "Go". This will list available web services. Find your TestWebService, and click on "View Service". The Web reference name defaults to the machine name. Change that to "WS", and then click the "Add Reference" button.

    4)  Now to the C# code. Open the Toolbox and add a  button and two TextBoxes to your form, then doubleclick on the button to get to the code. This is the code that you need:

    WS.TestWebService NAVWebService = new WS.TestWebService();

    NAVWebService.UseDefaultCredentials = true;

    // Line below added 21/1 2009:

    NAVWebService.Url = "http://[MachineName]:7047/DynamicsNAV/WS/CRONUS_International_Ltd./Codeunit/TestWebService";

    textBox2.Text = NAVWebService.AddX(textBox1.Text);

     

    This is what the first line refers to:

    WS: The name you gave to your reference in step 3.

    TestWebService: This is the name of the you typed into form 810 in NAV.

    NAVWebService: This is the name that you assign. You could call it anything.

    5)  Now run the project (F5), enter something into TextBox1, and hit the button.

     

     

    Lars Lohndorf-Larsen (Lohndorf )

    Microsoft Dynamics UK


    Microsoft Customer Service and Support (CSS) EMEA

  • Microsoft Dynamics NAV Team Blog

    NAV 2009 - How to publish a web service

    • 2 Comments

    This posts only describes how you can publish a web service in Microsoft Dynamics NAV (which is easy enough), and how you can see what you published. It does not describe how you can actually use this web service. Future posts will describe this.

    Please note that the features described here are only available in NAV 2009, which is not released yet. For more details about NAV 2009, follow this link:

    NAV 2009 Technical Preview

     

    The short story:

    In NAV 2009, run form 810 "Web Services". Select either a page or a codeunit, then click "Publish". The page or codeunit selected is now published as a web service.

    And, a few more details: 

    First of all, make sure to start the service "Microsoft Dynamics NAV Business Web Services". This is the service that handles web service requests. It runs from the same folder as the service "Microsoft Dynamics NAV Server", which handles requests from the new client.

    Then check the application log, to
      1) Make sure that the service started OK
      2) See what port it is listening to
    Currently, the service will listen to port 7047. But this has changed and may change again, so just check the application log for an entry like this:

    Service MicrosoftDynamicsNavWS is listening to requests at http://[MachineName]:7047/[InstanceName].

    Open your Internet browser and go to this site:
    http://[MachineName]:7047/InstanceName/WS/CRONUS_International_Ltd./Services
    The site name is built by taking the link from the application log (see above), and then adding /WS/[CompanyName]/Services
    Note that in the comapny name, space is replaced with _ (underscore). In my case - running on a machine called TEST - the link is:

    http://TEST:7047/InstanceName/ws/CRONUS_International_Ltd./Services

    Your internet browser should now show a page like this:

     
    - <discovery xmlns="http://schemas.xmlsoap.org/disco/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <contractRef ref=http://TEST:7047/InstanceName/ws/CRONUS_International_Ltd/SystemService xmlns="http://schemas.xmlsoap.org/disco/scl/" />
      </discovery>
     

    It lists just one service called SystemService. The next step is to add a service from NAV:

      1) Start a classic client, open Object Designer, and run form 810 "Web Services".
      2) Select Object Type = Page, Object ID = 21, Service Name = Customer Card, and tick "Published".

    Go back to your internet browser and refresh, and you should see your new service.


    To see details of this service, copy the link into the address bar of your browser - in my case:
    http://TEST:7047/InstanceName/ws/CRONUS_International_Ltd/Page/Customer_Card

    This shows you all interfaces to this service. You will notice that this web service exposes the fields from the customer card page. In addition to this, it exposes the following system functions:

    Read
    ReadMultiple
    Create
    CreateMultiple
    Update
    UpdateMultiple
    Delete


    These are the functions that are available on any web service which is based on a page.


    As indicated by the available object types in form 810 "Web Services", you can base a web service on either a page or on a codeunit. So just repeat the steps above, but select "Codeunit" instead of "Page" in "Object Type" in form 810 to publish a codeunit.

     

    A web service is a method of integration based on a common framework. So a developer who knows about web services can now use your NAV webservices to develop integration without knowing anything about NAV. When using a NAV web service, it will run the same triggers as if the page or codeunit was used from a NAV client. More posts and examples will follow on this blog, about how to consume the web services that you have now published.

     

    Lars Lohndorf-Larsen (Lohndorf)
    Microsoft Dynamics UK


    Microsoft Customer Service and Support (CSS) EMEA

  • Microsoft Dynamics NAV Team Blog

    NAV 2009 - New one-stop Launch Portal on PartnerSource

    • 0 Comments

    One stop Microsoft Dynamics NAV 2009 launch portal for partners can be found at this link on PartnerSource (login required):

    Launch Portal for Microsoft Dynamics NAV 2009

    Follow the portal for:

    • Key Announcements
    • What's New
    • Readiness and Training
    • Sales and Marketing Materials
    • Events
    • Coming Soon

     

    Lars Lohndorf-Larsen

    Microsoft Dynamics UK

Page 1 of 1 (4 items)