Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: PDI-Kettle: Excel multiple-sheet reports with charts

  1. #1
    Join Date
    Sep 2013
    Posts
    15

    Question PDI-Kettle: Excel multiple-sheet reports with charts

    Hi there,

    I hope someone can help with this.
    I just recently started using the BI-server and Report designer of Pentaho.
    I think Pentaho is a great BI suite.

    One of the things that I was drawn to in the Pentaho world was when I saw the plugin called the Excel-Writer which
    is now in Kettle.
    I have the need to create reports in Excel with multiple sheets and each sheet contains a chart and its associated datatable below it.
    I tried to do this with the Report Designer, but upon export to Excel, it did do multiple sheets but the Chart were Images and not
    real Excel Chart Objects(this is one of my requirements).

    Then I came across an article that talked about using an Excel Template with PDI do some transformations.
    On this forum the closest thing I could find was this link, but it was not detailed enough to see if it can help.
    http://forums.pentaho.com/showthread...ultiple-sheets

    SO this is the use-case I need to implement:
    -------------------------------------------------------------------------
    In a MySql database, say I have a field for Salesmen and also have some statistics about each Salesman that can be plotted.
    I will need to grab the list of Salesmen from the database and using an Excel Template that has one sheet with one template chart, somewhere below it will be an area that will be populated that the chart will reference.
    So I will need the PDI to loop thru each Salesman, give the sheet the name of the Salesman, populate the sheet in order to populate the chart and then copy the template sheet to do the whole process all over again for the next salesman and the next..
    .. etc.
    -------------------------------------------------------------------------

    SO this is what I need to be done. And since we start with an excel template with a live chart, in the iterations where the copying of the template sheet is done, then all sheets will have live Charts.

    I have not seen any tutorials that go thru this. I have seen one, but it only populates one chart and not a dynamic amount of charts.(Actually just not more than one chart)
    BUT I did see in the recent past some reference that it can be done, but they did not go into much detail.

    Hope someone can help.

    P

  2. #2
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    Hi.
    I have done exactly what you are trying to achieve, therefore it's possible :-)
    You have to learn about Variables in PDI and how to run the same transformation for each row in a data feed.

    In short, you need the list of your salesman - in a datatable or grid or you can parse the data itself.
    Then connect this list to a job and in the settings check "Execute once for every inputrow".
    Within this job you have to use your transformation and variables to create the excel files.

    The key part is to set up your variables so that you create one file with a different name for each iteration of your job.

    I've done this few months ago and I have to dig somewhere to get my example, please let me know if you need more info.
    -- Mick --

  3. #3
    Join Date
    Feb 2011
    Posts
    840

    Default

    I think I understands something different than Mick did. You want various sheets in a single file - so the "key part" Mick points would kill the idea.

    Excel Writer behaves differently than other steps regarding the "Block this step until steps finish" - it will only release the "lock" when the lock on the file being written is done. Therefore, you can use this to write different sheets to the same file, in the same transformation.

    A couple examples I have at hand:
    Name:  2013-09-25_17h39_57.jpg
Views: 372
Size:  38.3 KB

    (edit)Forgot to mention, in both examples, all Excel Writer steps are pointing to the same file, different sheets. The first one has template set, the other ones are configured to "Write to existing file".

    Name:  2013-09-25_17h40_26.png
Views: 338
Size:  20.8 KB
    Join us on IRC! =)

    Twitter / Google+ / Timezone: BRT-BRST
    BI Server & PDI 5.4 / MS SQL 2012 / Learning CDE & CTools
    Windows 8 64-bit / Java 7 (jdk1.8.0_75)

    Quote Originally Posted by gutlez
    PLEASE NOTE: No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.

    I'm no expert.Take my comments at your own risk.

  4. #4
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    Hi Joao.
    Even in your example you have to use variables :-)
    In one of my job I had to combine my example with yours.
    Basically I was creating a new Excel file for each salesman and within that file multiple sheet, each one of them with different list of clients.
    I solved it using a "Execute once for every inputrow" twice, creating a loop within a loop.

    If it's needed I can dig out the job.
    -- Mick --

  5. #5
    Join Date
    Feb 2011
    Posts
    840

    Default

    actually, both those examples only have ${year}${month} as variables - but they do use the same for each run.
    So if, on the second example, I run with y=2013, m=07, I'll get an XLS file with name like 201307.xls containing 3 sheets: Contas, Detalhamento, Detalhe - but it could be a fixed filename...
    Join us on IRC! =)

    Twitter / Google+ / Timezone: BRT-BRST
    BI Server & PDI 5.4 / MS SQL 2012 / Learning CDE & CTools
    Windows 8 64-bit / Java 7 (jdk1.8.0_75)

    Quote Originally Posted by gutlez
    PLEASE NOTE: No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.

    I'm no expert.Take my comments at your own risk.

  6. #6
    Join Date
    Sep 2013
    Posts
    15

    Default

    Hi there,
    Just to clarify my question. The template is just one sheet, with a chart, which will need in an area below it to be populated with data so that the chart will be popluated.
    And Yes, from the database there will be the list of Salesmen that one will use to create new sheets based on the template sheet.
    On the web I saw an article, but now I can't find it, where the person said that they figured out how to copy the template sheet in memory and then place it into the Excel file with the tab having the new name(Salesmen in my case) and hence the Chart in this new sheet will have reference to this new sheet and not reference the original template sheet for its data.

    There is an open source project I was trying use before Pentaho : http://jxls.sourceforge.net/, and when using POI to copy the sheet, it fails to make changes to data references in the chart.
    So that led to me search some more and I found Pentaho and excel writer.

    I hope that clarifies things some more.
    BUT for you guys that are saying you know how to do this, I was wondering if I can get one of you guys as a tutor for learning about using PDI-kettle.

    Regards,

    P

  7. #7
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    Hi Pablo.
    I would love to tutor you, but I'm a bit too busy!
    If you want I can try to find my project and attach it her.
    You will have to change few settings and replace few steps, which you should be able to do after some testing.

    I'll try to attach a sample this weekend.
    -- Mick --

  8. #8
    Join Date
    Sep 2013
    Posts
    15

    Default

    Hi Mick,

    Thanks for trying to help me, really appreciate it. I was not well for a week, so I am back now.
    I look forward to seeing your sample.

    Pablo

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.