Create any graph in NAV using Excel

denpardenpar Member Posts: 80
edited 2015-02-09 in NAV Tips & Tricks
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
If your work becomes a passion...
www.pardaan.com

Comments

  • asmilie2basmilie2b Member Posts: 122
    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.

    Bruce Anderson
  • denpardenpar Member Posts: 80
    Unfortunately (as far as I know) you can't change the size of a BLOB field on a page :cry:

    I use this solution mainly in management reports!
    If your work becomes a passion...
    www.pardaan.com
  • stonystony Member Posts: 122
    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
  • denpardenpar Member Posts: 80
    edited 2015-02-09
    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;
    
    If your work becomes a passion...
    www.pardaan.com
  • stonystony Member Posts: 122
    Where should I call this function. And must be installed Excel on the Thier Server.
  • denpardenpar Member Posts: 80
    @Stony:

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

    http://www.pardaan.com/download133
    If your work becomes a passion...
    www.pardaan.com
  • stonystony Member Posts: 122
    Thank you, it works!
  • KalelKalel Member Posts: 4
    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?
Sign In or Register to comment.