Create Business Charts from Scratch in NAV 2013 R2

To Create Business Charts in NAV 2013 I will take help of :


> A Query to fetch the Data from Database.
> A Codeunit to fetch the Data from this Query and feed it to the Chart.
> A Page to Display my Chart.






Scenario : My client wants to see the No. of the Sales orders made Month and Status wise.

Step 1. First step will be to create a simple Query as shown below,

Step 2. If I save and Execute my Query, the result must be as shown below,

Here Month shows the integer value for the Month (1 represents Jan & so-on). So, quite easy to understand, if I take a look at any row, say Row no. 4 shows there are 31 Sales Orders with Status "Open" in the moth of "April".

Step 3. Create a Codeunit with 2 functions :

> Function 1 (This function will do the Hard work ;-)
with name : GetSOByStatus

with Parameters :
Name                DataType    Subtype                          Length
BusChartBuf Record         Business Chart Buffer

and with Local Variables : 
Name       DataType    Subtype                         Length
Qry1         Query          Count SO
i                 Integer

(Note : The query declared in the Locals of this Funtion is the query I created in Step 1.)

Now in this function the Code goes as follows :
BusChartBuf.Initialize;
BusChartBuf.AddMeasure('No. of Orders',1,BusChartBuf."Data Type"::Integer,BusChartBuf."Chart Type"::Column); //Define Y-Axis
BusChartBuf.SetXAxis('Month',BusChartBuf."Data Type"::String); //Define X-Axis

i := 0;
Qry1.OPEN;
WHILE Qry1.READ DO BEGIN
  i += 1;
  BusChartBuf.AddColumn('Month: ' + MonthName(Qry1.Month) + ', Status: ' + FORMAT(Qry1.Status)); //Define X-Axis Values
  BusChartBuf.SetValue('No. of Orders',i - 1,Qry1.Count);  //Define Y-Axis Values
END;
Qry1.CLOSE;

> Function 2 (This function is just to return the Name of the Month)
with name : MonthName

with Parameters :
Name        DataType    Subtype                         Length

Month        Integer

with Return Value :

Return Type - Text

Length          - 10

Now in this function the Code goes as follows :

IF Month = 0 THEN

  EXIT('');

EXIT(FORMAT(DMY2DATE(1,Month,2000),0,'<Month Text>'));

Step 4. Create a Page.

Now, As you can see I named my field as Chart but I haven't declared a SourceExpr for it. Actually we do not need to! Just go in the properties of this field and Paste this -

Microsoft.Dynamics.Nav.Client.BusinessChart;PublicKeyToken=31bf3856ad364e35 

in the ControlAddIn property of this field. This is an AddIn developed by Microsoft Itself to aid Business charts development.

Now as soon as you paste this, You will be able to see 3 new Triggers in this Page as shown below,

I am not going to use the first 2 triggers in my current Scenario. I will only use the 3rd Trigger which is AddInReady(), we will get back to this trigger later.

Step 5. Create a Global function in this Page.

Function Name : UpdateChart()

with Local Variables : 

Name                       DataType    Subtype                                   Length

BusChartBuffer       Record        Business Chart Buffer

SOMonthWise        Codeunit      Mntly Status wise SO Chart Mgt

(Note : The Codeunit declared in the Locals of this Function is the Codeunit I created in Step 3.)

Now in this function the Code goes as follows :

SOMonthWise.GetSOByStatus(BusChartBuffer);  //Fetch Data

BusChartBuffer.Update(CurrPage.Chart);              //Update this Data in our Chart

Step 6. The last and final step, Call the function created in Step 5.

Call UpdateChart function in OnAfterGetRecord() and Chart::AddInReady() Triggers of the page created in Step 4. And the final code done on this page may look like this.

Save and exit the page. Execute the page and check the chart must display somewhat like below,

Thanks Guys. 

Post your comments if any improvements are required in this post. Also post your doubts and queries I will be very glad to answer them.

Cheers, 

Ishwar Sharma