Create any graph in NAV using Excel

denpar

Simple example showing 3D clustered bar showing customer sales (LCY) last three years:

ExcelApp	Automation	'Microsoft Excel 15.0 Object Library'.Application	
ExcelBook	Automation	'Microsoft Excel 15.0 Object Library'.Workbook	
ExcelSheet	Automation	'Microsoft Excel 15.0 Object Library'.Worksheet	
ExcelRange	Automation	'Microsoft Excel 15.0 Object Library'.Range	
ExcelChart	Automation	'Microsoft Excel 15.0 Object Library'.Chart	
GraphFile	File		
MemStream	InStream		
OStream	OutStream		
Customer	Record	Customer	

//Add field GraphCustomer in Customer table
//Data type BLOB Subtype BitMap
//You can use this field in the customer page or a RDLC report

CREATE(ExcelApp, FALSE, TRUE);

ExcelBook := ExcelApp.Workbooks.Add(-4167);
ExcelSheet := ExcelApp.ActiveSheet;
ExcelSheet.Name := 'Sales customer';

Customer.SETFILTER("Date Filter",'%1..%2',010112D,311212D);
Customer.CALCFIELDS("Sales (LCY)");
ExcelSheet.Range('A1').Value := '2012';
ExcelSheet.Range('A2').Value := Customer."Sales (LCY)";

Customer.SETFILTER("Date Filter",'%1..%2',010113D,311213D);
Customer.CALCFIELDS("Sales (LCY)");
ExcelSheet.Range('B1').Value := '2013';
ExcelSheet.Range('B2').Value := Customer."Sales (LCY)";

Customer.SETFILTER("Date Filter",'%1..%2',010114D,311214D);
Customer.CALCFIELDS("Sales (LCY)");
ExcelSheet.Range('C1').Value := '2014';
ExcelSheet.Range('C2').Value := Customer."Sales (LCY)";

ExcelRange := ExcelSheet.Range('A1:C2');
ExcelChart := ExcelBook.Charts.Add;
ExcelChart.Name := 'Customergraph';

//ChartType 60 = 3D CLUSTERED BAR
ExcelChart.ChartWizard(ExcelRange,60,70,1,1,0,0,'Sales (LCY) Customer Pardaan Inc.');
ExcelChart.Export(TEMPORARYPATH+'graph.png');

GraphFile.OPEN(TEMPORARYPATH+'graph.png');
GraphFile.CREATEINSTREAM(MemStream);
CALCFIELDS(Graph);
Graph.CREATEOUTSTREAM(OStream);
COPYSTREAM(OStream,MemStream);
MODIFY;
GraphFile.CLOSE;

Link Parameters ChartWizard Method : https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.chart.chartwizard.aspx
Link ChartType enumeration : http://it.toolbox.com/wiki/index.php/EXCEL_Chart_Type_Enumeration

asmilie2b

Very nice idea! Thanks for sharing.
We have NAV 2013, and the graphs in NAV were a nice addition, but the lack of filtering and control that you have is limiting. With Excel in this way there is much more control possible. Thanks again.

I wonder if in RTC it is possible to increase the size of the BLOB field in the page at all.
I have it working in the customer card, but the BLOB field is small, so you need to click onto the image to have it run in a picture viewer to make it out.

denpar

Unfortunately (as far as I know) you can't change the size of a BLOB field on a page

I use this solution mainly in management reports!

stony

Hallo,

Unfortunately, I get the following error.

And on the Servie Tier Server there isn´t any File with the name graph.png

thanks
stony

denpar

You have to move the file from the client to a server directory...

You can use this function:

Function CopyFileToRTCfromClient(parTxtFilename : Text[1024]):

Variables
Name DataType Subtype Length
TxtFileToDownload Text 250
FilGraphFile File
InsInstream InStream
TxtMagicpath Text 250
AutFileSystemObject Automation 'Microsoft Scripting Runtime'.FileSystemObject
TxtDestinationFileName Text 250
IntI Integer
TxtFileToUpload Text 250
TxtFileToUpload1 Text 250
TxtFoldername Text 250
OutOutStream OutStream

FilGraphFile.CREATETEMPFILE;
FilGraphFile.CREATEINSTREAM(InsInstream);
DOWNLOADFROMSTREAM(InsInstream,'','<TEMP>', '',TxtMagicpath);
FilGraphFile.CLOSE;

FOR IntI := STRLEN(TxtMagicpath) DOWNTO 1 DO BEGIN
  IF TxtMagicpath[IntI] = '\' THEN BEGIN
    TxtMagicpath := COPYSTR(TxtMagicpath,1,IntI);
    IntI := 1;
  END;
END;

TxtFileToUpload := parTxtFilename;
TxtFoldername :='c:\temp'; //client directory

IF ISCLEAR(AutFileSystemObject) THEN
  CREATE(AutFileSystemObject,TRUE,TRUE);

AutFileSystemObject.CopyFile(TxtFoldername + '\' + TxtFileToUpload, TxtMagicpath + TxtFileToUpload);

TxtFileToUpload1:=TxtFoldername + '\' + TxtFileToUpload;

UPLOADINTOSTREAM('','<TEMP>','',TxtFileToUpload1,InsInstream);

FilGraphFile.WRITEMODE(TRUE);
FilGraphFile.CREATE((TEMPORARYPATH + TxtFileToUpload));
FilGraphFile.CREATEOUTSTREAM(OutOutStream);
COPYSTREAM(OutOutStream,InsInstream);
FilGraphFile.CLOSE;

stony

Where should I call this function. And must be installed Excel on the Thier Server.

denpar

@Stony:

I modified the objects for client/server, you can download the objects here....

http://www.pardaan.com/download133

stony

Thank you, it works!

Kalel

denpar wrote: »

Simple example showing 3D clustered bar showing customer sales (LCY) last three years:

ExcelApp	Automation	'Microsoft Excel 15.0 Object Library'.Application	
ExcelBook	Automation	'Microsoft Excel 15.0 Object Library'.Workbook	
ExcelSheet	Automation	'Microsoft Excel 15.0 Object Library'.Worksheet	
ExcelRange	Automation	'Microsoft Excel 15.0 Object Library'.Range	
ExcelChart	Automation	'Microsoft Excel 15.0 Object Library'.Chart	
GraphFile	File		
MemStream	InStream		
OStream	OutStream		
Customer	Record	Customer	

//Add field GraphCustomer in Customer table
//Data type BLOB Subtype BitMap
//You can use this field in the customer page or a RDLC report

CREATE(ExcelApp, FALSE, TRUE);

ExcelBook := ExcelApp.Workbooks.Add(-4167);
ExcelSheet := ExcelApp.ActiveSheet;
ExcelSheet.Name := 'Sales customer';

Customer.SETFILTER("Date Filter",'%1..%2',010112D,311212D);
Customer.CALCFIELDS("Sales (LCY)");
ExcelSheet.Range('A1').Value := '2012';
ExcelSheet.Range('A2').Value := Customer."Sales (LCY)";

Customer.SETFILTER("Date Filter",'%1..%2',010113D,311213D);
Customer.CALCFIELDS("Sales (LCY)");
ExcelSheet.Range('B1').Value := '2013';
ExcelSheet.Range('B2').Value := Customer."Sales (LCY)";

Customer.SETFILTER("Date Filter",'%1..%2',010114D,311214D);
Customer.CALCFIELDS("Sales (LCY)");
ExcelSheet.Range('C1').Value := '2014';
ExcelSheet.Range('C2').Value := Customer."Sales (LCY)";

ExcelRange := ExcelSheet.Range('A1:C2');
ExcelChart := ExcelBook.Charts.Add;
ExcelChart.Name := 'Customergraph';

//ChartType 60 = 3D CLUSTERED BAR
ExcelChart.ChartWizard(ExcelRange,60,70,1,1,0,0,'Sales (LCY) Customer Pardaan Inc.');
ExcelChart.Export(TEMPORARYPATH+'graph.png');

GraphFile.OPEN(TEMPORARYPATH+'graph.png');
GraphFile.CREATEINSTREAM(MemStream);
CALCFIELDS(Graph);
Graph.CREATEOUTSTREAM(OStream);
COPYSTREAM(OStream,MemStream);
MODIFY;
GraphFile.CLOSE;

Link Parameters ChartWizard Method : https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.chart.chartwizard.aspx
Link ChartType enumeration : http://it.toolbox.com/wiki/index.php/EXCEL_Chart_Type_Enumeration

como puedo crear varios gráficos en una misma hoja?