Creating Bar Chart in Navision

Bar Chart represent an exciting, new way for users to see statistical information in a graphical way in Navision. By being able to examine the relationships of statistics in a visual way, users can more quickly and easily grasp the meaning of and implications of the data on display. From a development point of view, the bar charts are a new way of using visual tools in forms by connecting then with data.

The bar charts in Navision are based on three generic bar chart subforms:

1. Form 630 Bar Chart 12x1 Subform (Show 12 columns of 1 value)
   
2. Form 631 Bar Chart 6x2 Subform (Show 6 columns of 2 compared values)
   
3. Form 632 Bar Chart 4x3 Subform (Show 4 columns of 3 compared values)

I would like to develop a bar chart to display salesperson performance in Quarter. Therefore, I decide to use Bar Chart 4x3 subform . 4 columns for showing months in Quarter.3 values for showing Sales Amount, Cost Amount and Gross Profit Amount. I use the code from the existing bar chart windows as a basis then I create a design for the functions I need in the main form and pass data to the subform using these functions. I refer to Opportunities Bar Chart design to look at codes more details.

How to create Treeview layout in Navision

We have known that treeview layout can be found in Chart of Account overview form. I try to learn the logic and how it works to create treeview layout. Therefore, I create a table and a form to test it.

The important thing that in table must has : No, Name, Type(Heading, Begin-Total, Posting, End-Total, Total), Indentation, and Totaling field.


For Creating treeview form from the table, we just follow the logic from Chart of Account Overview form. I think it's quite simple to do that. First, we create 7 functions :

1. InitTempTable (send parameter(Yes/No) to CopyCountryToTemp function)
   
2. ExpandAll (to expand all)
   
3. CopyCountryToTemp (Insert table data to temporary table)
   
4. GetEndTotal (get Totaling data from "End-Total" to "Begin-Tota"
   
5. HasChildren (Check whether has children or not)
   
6. IsExpanded (Check whether has already expanded or not)
   
7. ToggleExpandCollapse (toggle for expand and collapse)

Second, add codes in OnFindRecord, OnNextRecord, and OnAfterGetRecord event


Result:

How to create chart in Excel without using ChartWizard Object

I try to create chart in Excel from Navision without using ChartWizard object because I want to control chart object in Excel from Navision (Size of chart, series collection,etc). Here the sample codes :


//Excel Objet
//Walkthrough

Excel : 'Microsoft Excel 12.0 Object Library'.Application
Book : 'Microsoft Excel 12.0 Object Library'.Workbook
Range: 'Microsoft Excel 12.0 Object Library'.Range
Sheet : 'Microsoft Excel 12.0 Object Library'.Worksheet
Chart : 'Microsoft Excel 12.0 Object Library'.Chart
ChartObject : 'Microsoft Excel 12.0 Object Library'.ChartObject
ChartObjects : 'Microsoft Excel 12.0 Object Library'.ChartObjects
Series : 'Microsoft Excel 12.0 Object Library'.Series
SeriesCollection : 'Microsoft Excel 12.0 Object Library'.SeriesCollection
Axis : 'Microsoft Excel 12.0 Object Library'.Axis

IF NOT CREATE(Excel) THEN
CREATE(Excel);
Excel.Visible(FALSE);

Book := Excel.Workbooks.Add(-4167);
Sheet := Excel.ActiveSheet;
Sheet.Name := 'SHEET NAME';

//Sample Data
Sheet.Range('A2').Value := 'FY2008';
Sheet.Range('A3').Value := 'FY2009';
Sheet.Range('B1').Value := 'Sales Sept';
Sheet.Range('C1').Value := 'YTD Sept';
Sheet.Range('D1').Value := 'YTD Budget';
Sheet.Range('E1').Value := 'Collection';
Sheet.Range('F1').Value := 'COGS';
Sheet.Range('G1').Value := 'Gross Profit';

Sheet.Range('B2').Value := 119903.32;
Sheet.Range('C2').Value := 1284487.9;
Sheet.Range('D2').Value := 0;
Sheet.Range('E2').Value := 1281430.5;
Sheet.Range('F2').Value := 600345.81;
Sheet.Range('G2').Value := 684142.05;

Sheet.Range('B3').Value := 88616.06;
Sheet.Range('C3').Value := 875341.22;
Sheet.Range('D3').Value := 1094876;
Sheet.Range('E3').Value := 878037.17;
Sheet.Range('F3').Value := 456889.95;
Sheet.Range('G3').Value := 418451.27;

Range:=Sheet.Range('A1:G3');
Range.Columns.AutoFit;

//Create Chart Object
ChartObjects := Sheet.ChartObjects;
ChartObject := ChartObjects.Add(Excel.CentimetersToPoints(1),
Excel.CentimetersToPoints(2),
Excel.CentimetersToPoints(18),
Excel.CentimetersToPoints(10));

Chart := ChartObject.Chart;
Chart.SetSourceData(Range,2);
Chart.HasTitle := TRUE;
Chart.ChartTitle.Text := 'Sales Performances';

//Set Title on X-Axis
Axis := Chart.Axes(1);
Axis.HasTitle(TRUE);
Axis.AxisTitle.Text := 'Year to Date';

//Set Title on Y-Axis
Axis := Chart.Axes(2);
Axis.HasTitle(TRUE);
Axis.AxisTitle.Text := '$S';

//Set Series
Series := Chart.SeriesCollection(1);
Series.ChartType := 51;
Series := Chart.SeriesCollection(2);
Series.ChartType := 51;
Series := Chart.SeriesCollection(3);
Series.ChartType := 51;
Series := Chart.SeriesCollection(4);
Series.ChartType := 65;
Series := Chart.SeriesCollection(5);
Series.ChartType := 65;
Series := Chart.SeriesCollection(6);
Series.ChartType := 65;

Excel.Visible(TRUE);
Excel.UserControl(TRUE);
CLEAR(Excel);

The Result:

How to change Caption field on Runtime in Navision?

If we want to change Caption on field, normally we change Codeunit 1 to do that. But there is a trick how to change caption on field without modify codeunit 1.

On CaptionClass property put :
1,5,, + txtYourCaption (or you can put a function to set the caption)


First parameter : 1 for Dimension, 2 for VAT

Second parameter :
1 --> Global Dimension (Code Caption)
2 --> Shortcut Dimension (Code Caption)
3 --> Global Dimension (Filter Caption)
4 --> Shortcut Dimension (Filter Caption)
5 --> Dimension Code (Code Caption)
6 --> Dimension Code (Filter Caption)

Third parameter : Dimension code you want. If this dimension is blank, it will show fourth parameter on the caption.

Fourth parameter : The value you want to show as caption

Formula :
Optional parameter 1 (3rd parameter) + dimension caption + Optional parameter 2 (4th parameter)

Note : Dont forget to put Currform.Updatecontrols to reload captions

Sample Connection between Navision and Microsoft Access

Last Year, 27 June 2008, I tried to create sample connection between Navision and Microsoft Access. This was a basic sample using ADO. You can watch my video for more detail :-)

List of Bitmap Value in Navision

Sometimes I find it difficult to know bitmap value in Navision. I have to try one by one to find bitmap image I need by setting bitmap value properties. Therefore, I try to create list of Navision's bitmap in a form. There is a difference bitmap value between Navision version 4 and Navision 5. Maximum bitmap value in Navision 4 is 44 and 53 in Navision 5. For my next post, I will try to create it for Navision 2009.