NAV 2009 - How to generate charts / KPIs

NAV 2009 - How to generate charts / KPIs

  • Comments 5

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.

  }

}

 

Leave a Comment
  • Please add 4 and 4 and type the answer here:
  • Post
  • Regarding Navision 2009 R2; Is it still not possible - in a simple manner - to have data from two different source tables represented in the same chart/graph in the Role Center's Home page ?

      e.g. actual vs. budget (posted invoices vs. budget sale) / (-or table 17 data vs. table 96 data) ?

  • Sorry, but no. No changes in this area in R2. The only way would be to combine two tables into one table and then use that for a chart. Or take a look at a completely different method of making charts here:

    blogs.msdn.com/.../more-charts.aspx

    Lars

  • Hi,

    I have a small comment to make. the XML your tool generates is only partly right. You miss to set the Attribute ID on the Field Tag. Like this the Measure SUM never works. Besides that there is an XSD on the installation Disc. There are some other functions available like AVG, MIN and MAX. Just check it out.

  • Hi Kristof,

    Thanks for these details. I found the available tags by just looking at existing charts, I never thought of checking this XSD file for options!

    Now there is a graphical tool out of the box in NAV2013 so I don't plan to maintain this one but this is good input for anyone still using this tool, thanks!

    Lars

  • Hi Lars

    Is it possible that you could supply me with a few hints?

    - How to set the Attribute ID (when is use Meassure SUM)

    - How to take advantage of AVG, MIN and AMX in the XSD-file?

    /Mikkel

Page 1 of 1 (5 items)