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 call Store Procedure from Navision

Today, I try to create simple testing to call store procedure from navision using ADO. In fact, we can do a lot of thing by using ADO and store procedure in SQL. This testing only for sample ( I use Cronus Company for testing). This testing will show salesperson name after we send salesperson code parameter to the store procedure.

1. Create Store Procedure in SQL

CREATE PROCEDURE SP_Test @SName as Varchar(20) OUTPUT
AS
SELECT [CRONUS International Ltd_$Salesperson_Purchaser].Name as Nama from
[CRONUS International Ltd_$Salesperson_Purchaser] where
[CRONUS International Ltd_$Salesperson_Purchaser].Code = @SName

GO

2. Call Store Procedure from Navision

Variables:
ADOConnection : 'Microsoft ActiveX Data Objects 2.8 Library'.Connection
ADOCommand : 'Microsoft ActiveX Data Objects 2.8 Library'.Command
ADOParameter : 'Microsoft ActiveX Data Objects 2.8 Library'.Parameter
ADORecSet : 'Microsoft ActiveX Data Objects 2.8 Library'.Recordset
VarActiveConnection : Variant

IF ISCLEAR(ADOConnection) THEN
CREATE(ADOConnection);

ADOConnection.ConnectionString:=
'Driver={SQL Server};'
+ 'Server='+'cronus'+';'
+ 'Database='+'cronus'+';'
+ 'Uid='+'sa'+';'
+ 'Pwd='+'sa'+';';

ADOConnection.Open;

IF ISCLEAR(ADOCommand) THEN
CREATE(ADOCommand);

VarActiveConnection := ADOConnection;
ADOCommand.ActiveConnection := VarActiveConnection;

// CommandType property
ADOCommand.CommandText := 'SP_Test';
ADOCommand.CommandType := 4;
ADOCommand.CommandTimeout := 0;

//CreateParameter method
ADOParameter:=ADOCommand.CreateParameter('@SalesCode', 200, 1, 30,'AH');
ADOCommand.Parameters.Append(ADOParameter);

ADOCommand.Execute;

IF ISCLEAR(ADORecSet) THEN
CREATE(ADORecSet);

ADORecSet.ActiveConnection := VarActiveConnection;
ADORecSet.Open(ADOCommand);

WHILE NOT ADORecSet.EOF DO BEGIN
MESSAGE(FORMAT(ADORecSet.Fields.Item('Nama').Value));
ADORecSet.MoveNext;
END;

ADOConnection.Close;
CLEAR(ADOConnection);

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

Ghost Friends Soundtrack

Bos bikin kesalahan, Siapa yang tanggung?

Pada satu hari, si majikan bilang ama babi, “ Malam ini kita akan adain pesta, pergi siapkan biscuit untuk acara malam ini”

Si babi dengan serius bikin biscuit, dengan sepenuh hati. Namun ga lama kemudian, si majikan masuk ke dapur dan bertanya ama si Babi, “ Kenapa apinya kecil begitu?? Kecil begitu kapan matangnya??” Si Babi bilang ke majikan, “ Kalo apinya terlalu besar, nanti biskuitnya gosong”. Si majikan bilang ke babi, “ Kamu ini otak babi, kamu ngerti apa? Lakukan saja apa yang aku perintahkan!”





Akhirnya biskuitnya gosong seperti perkiraan si Babi... Lalu si babi bertanya ke majikan, “apa yg harus kita lakukan sekarang?”



Kata majikan, “Kalo gitu, kita ganti menu jadi steak babi saja”

Moral of the story:
“Bos selamanya tidak pernah salah, kalo ada salah pun Anda yang menanggung”

Bos

Adalah seekor babi yang serba bisa di sebuah peternakan



Setiap pagi babi itu mengikuti ayam jantan berkokok untuk membangunkan majikannya.


Dia belajar kaya kucing untuk menangkap tikus yang suka merusak.


Binatang2 yang laen bertanya ama si Babi, kenapa dia begitu "kaypo", ikut campur dalam banyak hal.



Tetapi satu hari, si Majikan datang dan babi tersebut ditangkapnya dan mau dipersiapkan untuk disembelih. Tetapi babi itu rasa2nya tidak percaya. Dan babi itupun akhirnya bertanya kepada majikannya, " Aku begitu banyak kemampuan, dan sudah banyak membantumu dalam banyak hal, kenapa Engkau mau menyembelihku?"

Majikan tersebut cuma menjawab, " Ga ada alasan khusus, aku cuma pengen makan steak babi"




Moral of the Story: Majikan hanya melihat dari apa yang seharusnya anda lakukan, bukan dari apa yang bisa Anda lakukan. (Kalo bos lagi mau “sembelih” orang, kapan aja bisa dan siapa aja bisa jadi “korban”. Jadi cukup lakukan apa saja yang seharusnya Anda lakukan, karena semua itu bisa jadi ga dihargai bos)

In June 2009, I created an application for Logistic company. There were some features of this application, such as maintain customer data, create Subsidiary Certification(SubCert), and send the certification to customer by email.

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.

My First Blog

Today is my first day to create my blog.
I will try to write my stories and share my knowledge by this blog.