Mohana’s Dynamics Blog

I am back to add the charts in below Classic report (Monthly Purchase Invoices)

This can be done using the excel automation as below..

Add the below variables in report

* “Tempblob” is a temporary record.

Add the below code in Date - OnAfterGetRecord() to store the “PeriodText” value in “MonthNames” Array variable with dimensions 12 (for 1 year)

Date - OnPreDataItem()
SETFILTER(”Period Start”,’%1..’,StartDate);
SETFILTER(”Period End”,’..%1′,EndDate);

i := 0; // New Code

Date - OnAfterGetRecord()
PeriodText := “Period Name” + ‘-’ + FORMAT(DATE2DMY(”Period Start”,3));

i := i + 1; // New Code
MonthNames[i] := PeriodText; // New Code

Add the below code in Purch. Inv. Header, Footer (2) - OnPreSection() to store the Monthly Amount total in “TotalAmtMonthly” array variable with dimensions 12

TotalAmtMonthly[i] := “Amount to Vendor”; // New Code

Add a picture box in Date Footer and add the below code in Date, Footer (4) - OnPreSection() which creates a chart in excel and exports the picture to a temporary path and imports to tempblob record.

Set SourceExpr = tempblob.Blob for above added picture box.

// New Code Begin

tempblob.DELETEALL;

CREATE(XlApp);

XlApp.Visible(FALSE);

XlWorkBk := XlApp.Workbooks.Add;

XlWorkSht := XlWorkBk.Worksheets.Add;

XlWorkSht.Range(’A1′).Value := ”;

XlWorkSht.Range(’B1′).Value := ‘Invoice Amount’;

XlWorkSht.Range(’A2′).Value := MonthNames[1];

XlWorkSht.Range(’B2′).Value := TotalAmtMonthly[1];

XlWorkSht.Range(’A3′).Value := MonthNames[2];

XlWorkSht.Range(’B3′).Value := TotalAmtMonthly[2];

XlWorkSht.Range(’A4′).Value := MonthNames[3];

XlWorkSht.Range(’B4′).Value := TotalAmtMonthly[3];

XlWorkSht.Range(’A5′).Value := MonthNames[4];

XlWorkSht.Range(’B5′).Value := TotalAmtMonthly[4];

XlWorkSht.Range(’A6′).Value := MonthNames[5];

XlWorkSht.Range(’B6′).Value := TotalAmtMonthly[5];

XlWorkSht.Range(’A7′).Value := MonthNames[6];

XlWorkSht.Range(’B7′).Value := TotalAmtMonthly[6];

XlWorkSht.Range(’A8′).Value := MonthNames[7];

XlWorkSht.Range(’B8′).Value := TotalAmtMonthly[7];

XlWorkSht.Range(’A9′).Value := MonthNames[8];

XlWorkSht.Range(’B9′).Value := TotalAmtMonthly[8];

XlWorkSht.Range(’A10′).Value := MonthNames[9];

XlWorkSht.Range(’B10′).Value := TotalAmtMonthly[9];

XlWorkSht.Range(’A11′).Value := MonthNames[10];

XlWorkSht.Range(’B11′).Value := TotalAmtMonthly[10];

XlWorkSht.Range(’A12′).Value := MonthNames[11];

XlWorkSht.Range(’B12′).Value := TotalAmtMonthly[11];

XlWorkSht.Range(’A13′).Value := MonthNames[12];

XlWorkSht.Range(’B13′).Value := TotalAmtMonthly[12];

 XlCharts := XlWorkSht.ChartObjects;

MyChart := XlCharts.Add(10,80,460,350);

ChartPage := MyChart.Chart;

ChartRange := XlWorkSht.Range(’A1′,’B13′);

ChartPage.SetSourceData(ChartRange);

ChartPage.ChartType := -4100;

ChartPage.Export(TEMPORARYPATH+’\excel_chart_export.bmp’,'BMP’);

tempblob.”Primay Key” += 1;

tempblob.Blob.IMPORT(TEMPORARYPATH+’\excel_chart_export.bmp’);

tempblob.INSERT;

tempblob.GET(tempblob.”Primay Key”);

tempblob.CALCFIELDS(Blob);

XlWorkBk.Close(FALSE);

XlApp.Quit;

CLEAR(XlApp);

// New Code End

Now, run the report for 1 year (01/01/10 to 31/12/10)

The output will be (Chart in Classic Report)

This entry was posted on Sunday, February 13th, 2011 at 7:34 am and is filed under Classic Charts. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.