Hitachi Vantara Pentaho Community Forums
Results 1 to 21 of 21

Thread: xaction for generating reports and save them as pdf

  1. #1
    Join Date
    Apr 2006
    Posts
    9

    Question xaction for generating reports and save them as pdf

    Hi there,

    i'am currently trying to build an xaction which should generate several PDF-Files as output from a report which is looped by an SQL. The PDF Files shoul have unique names by using a variable from the loop.

    My problem is that i dont know how to gernerate pdf-Files from the report while looping, with different filenames.

    The flow should look like this:

    1. Get a result-set from database with userid and username
    2. start looping over the resultset from Step 1
    2.1. Get resultset from database with where criteria (user_id= user_id from Step1.user_id which is available from the loop in Step2)

    Now with step 4 i get problems (
    2.2. Start a report bases on dataresult from 2.1
    2.2/2.3 Report output as pdf to directory xyz/report_{user_id}_date.pdf


    I hope someone understand what i mean.
    I want the output of a report to a pdf. The filename of the pdf should include a dynamic value from the loop.

    Maybe this ist quite easy,
    but i dont find the solution.


    Thank you for help

    Greetings

    Alexander Bruch
    www.bitroom.de

  2. #2
    dmoran Guest

    Default

    Alexander,

    The attached Action Sequence demonstrates how to do what you want. It is a little tricky to explain why it works since some of the XML is not even documented. Another issue is that the Design Studio will prevent you from specifying the output from the jFreeReports component for this use case. You can still use the Design Studio but you will need to edit the XML directly using the XML Source tab.

    The highlights:

    There is an undocumented (and in the Design Studio, unimplemented) output destination called "file" that takes a filepath. The path can have parameter names in it.
    For example:
    Code:
      <outputs> 
        <report_out type="content"> 
          <destinations> 
            <file>/xyz/report_{user_id}_date.pdf</file> 
          </destinations> 
        </report_out> 
      </outputs>
    An <action-definition> that references this type of output needs to supply a value for user_id by including an <action-input> like <user_id type="string">.

    The <action-output> parameter for the report component CANNOT be named report-output (strange I know) and must match the output name - in this example - report_out

    This explanation will make more sense when you look at the attached example. It is based on BurstActionSequence.xaction located in the samples/bursting folder distributed with the pentaho demo (pre-configured-install). Simply drop FileBurst.xaction in that folder (it relies on the report XML in that folder already) and edit the path in the <destination> node to match the directory to save the reports in. When you run the Action Sequence, it should create 4 PDF's, one for each region in that location.

    Thanks for pointing out an area of the platform that needs to be improved. Let me know how it works for you.

    Doug
    Attached Files Attached Files
    Last edited by dmoran; 12-15-2006 at 07:01 PM.

  3. #3
    Join Date
    Apr 2006
    Posts
    9

    Default

    Doug,

    thank you for this detailed post.
    It works very fine for me.

    Here ist my Action Sequence
    Code:
    <?xml version="1.0" encoding="UTF-8"?>
    <action-sequence> 
      <title>UserPdfReport</title>
      <version>1</version>
      <logging-level>DEBUG</logging-level>
      <documentation> 
        <author>alb</author>  
        <description>Generates Reports for System Users</description>  
        <help/>  
        <result-type>report</result-type>  
        <icon/> 
      </documentation>
    
      <inputs> 
        <output-type type="string"> 
          <default-value>pdf</default-value>  
          <sources> 
            <request>type</request> 
          </sources> 
        </output-type> 
      </inputs>
    
      <outputs> 
        <report_out type="content"> 
          <destinations> 
            <file>/reports/{login}/BasisReport.{output-type}</file> 
          </destinations> 
        </report_out> 
      </outputs>
    
      <resources> 
        <report-definition> 
          <solution-file> 
            <location>basisreport.xml</location>  
            <mime-type>text/xml</mime-type> 
          </solution-file> 
        </report-definition> 
      </resources>
      
      <actions> 
        <action-definition> 
          <component-name>SQLLookupRule</component-name>
          <action-type>getUsers</action-type>
          <action-outputs> 
            <query-result type="result-set" mapping="query_result_users"/>  
            <USER_ID type="integer"/>  
            <LOGIN type="string"/>  
          </action-outputs>
          <component-definition> 
            <jndi>mysql_database</jndi>  
            <live>true</live>  
            <query><![CDATA[select user_id,fname login from users where downloads=1]]></query> 
          </component-definition> 
        </action-definition>
      
        <actions loop-on="query_result_users"> 
          <action-definition> 
            <component-name>SQLLookupRule</component-name>
            <action-type>ReportData</action-type>
            <action-inputs> 
              <user_id type="string"/>  
            </action-inputs>
            <action-outputs> 
              <query-result type="result-set" mapping="query_result_reportdata"/> 
            </action-outputs>
            <component-definition> 
              <jndi>mysql_db</jndi>  
              <live>true</live>  
              <query><![CDATA[select data1,data2,data3 from reporttable where user_id={user_id}]]></query> 
            </component-definition> 
          </action-definition>
      
          <action-definition> 
            <component-name>JFreeReportComponent</component-name>
            <action-type>Pentaho Report</action-type>
            <action-inputs> 
              <data type="result-set" mapping="query_result_reportdata"/>  
              <user_id type="integer"/>  
              <login type="integer"/>  
              <output-type type="string"/> 
            </action-inputs>
            <action-resources> 
              <report-definition type="resource"/> 
            </action-resources>
            <action-outputs> 
              <report_out type="content"/> 
            </action-outputs>
            <component-definition> 
              <output-type>pdf</output-type> 
            </component-definition> 
          </action-definition>
        </actions>
       </actions> 
    </action-sequence>
    But i still have one more problem.
    I want to include the current date into the Reports Destination Filename.
    So the file outputs line should look like this:
    Code:
    <file>/reports/{login}/BasisReport_{reportdate}.{output-type}</file>
    To realize this i added a field to the SQLLookUpRule query_result_user.
    So the SQL looks like this:
    Code:
    <query><![CDATA[select now() reportdate, user_id,fname login from users where reporting=1]]></query>
    and added this field as string to the action-outputs of the SQLLookupRule.
    Also i added this field as string to the action-inputs of the JFreeReportComponent.

    But now when i run the xaction the filename becomes like a temporary filename
    RHKIJB~7.pdf

    What i'am doing wrong ?
    Is it possible to define a SQLLookupRule field as string even if it is a datetime? Or i have to use another Datatype?

    Ps: if i delete {reportdate} part from the filename than i ge a file name Report1_.pdf


    Greetings

    Thank you for Help


    Alexander Bruch
    www.bitroom.de

  4. #4

    Default Need to make PDF's also

    I am also trying to generate and save my reports as PDF's. When I use your (slightly modfied) xaction posted (I precreated the tables and report to match), I get an error message that "Adobe Reader could not open 'AcrD4.tmp' because it is either not a a supported file type or because the file has been damaged. What am I doing wrong? I can't see to output a file in any fashion. My xaction is:
    <?xml version="1.0" encoding="UTF-8"?>
    <action-sequence xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <name>basisreport.xaction</name>
    <title>%title</title>
    <version>1</version>
    <documentation>
    <author>Report Design Wizard</author>
    <description>%description</description>
    <icon>PentahoReporting.png</icon>
    <help>This action-sequence was generated by the Report Design Wizard. To edit this action-sequence use the Pentaho Design Studio.</help>
    <result-type>report</result-type>
    </documentation>

    <inputs>
    <output-type type="string">
    <default-value><![CDATA[pdf]]></default-value>
    <sources>
    <request>type</request>
    </sources>
    </output-type>
    </inputs>

    <outputs>
    <report_out type="content">
    <destinations>
    <file>./report_{user_id}_date.pdf</file>
    </destinations>
    </report_out>
    </outputs>

    <resources>
    <!-- use this section to identify any files that the component needs to execute the report -->
    <report-definition>
    <solution-file>
    <location>basisreport.xml</location>
    <mime-type>text/plain</mime-type>
    </solution-file>
    </report-definition>
    </resources>

    <actions>
    <action-definition>
    <component-name>SQLLookupRule</component-name>
    <action-type>rule</action-type>
    <action-inputs/>
    <action-outputs>
    <rule-result type="result-set"/>
    </action-outputs>
    <component-definition>
    <jndi>epbi_mysql</jndi>
    <query><![CDATA[select data1,data2,data3 from reporttable where user_id =1]]></query>
    </component-definition>
    </action-definition>

    <action-definition>
    <component-name>JFreeReportComponent</component-name>
    <action-type>report</action-type>
    <action-inputs>
    <data type="result-set" mapping="rule-result"/>
    </action-inputs>
    <action-resources>
    <report-definition type="resource"/>
    </action-resources>
    <action-outputs>
    <report-output type="content" mapping="report_out"/>
    </action-outputs>
    <component-definition>
    <output-type><![CDATA[html]]></output-type>
    </component-definition>
    </action-definition>

    </actions>
    </action-sequence>

  5. #5

    Default Still more efforts

    I now don't crash but I get

    Action Successful
    --------------------------------------------------------------------------------

    report_out=epbi/outputreports/basisreports/basisreport.xaction.html, epbi/outputreports/basisreports/basisreport.xaction/html/d5c1d76a-5101-11dc-aa4d-93272f5cb053, text/html

    using
    <?xml version="1.0" encoding="UTF-8"?>
    <action-sequence xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <name>basisreport.xaction</name>
    <title>%title</title>
    <version>1</version>
    <documentation>
    <author>Report Design Wizard</author>
    <description>%description</description>
    <icon>PentahoReporting.png</icon>
    <help>This action-sequence was generated by the Report Design Wizard. To edit this action-sequence use the Pentaho Design Studio.</help>
    <result-type>report</result-type>
    </documentation>

    <inputs>
    <output-type type="string">
    <default-value><![CDATA[pdf]]></default-value>
    <sources>
    <request>type</request>
    </sources>
    </output-type>
    </inputs>

    <outputs>
    <report_out type="content"/>
    </outputs>

    <resources>
    <!-- use this section to identify any files that the component needs to execute the report -->
    <report-definition>
    <solution-file>
    <location>basisreport.xml</location>
    <mime-type>text/plain</mime-type>
    </solution-file>
    </report-definition>
    </resources>

    <actions>
    <action-definition>
    <component-name>SQLLookupRule</component-name>
    <action-type>rule</action-type>
    <action-inputs/>
    <action-outputs>
    <rule-result type="result-set"/>
    </action-outputs>
    <component-definition>
    <jndi>epbi_mysql</jndi>
    <query><![CDATA[select data1,data2,data3 from reporttable where user_id =1]]></query>
    </component-definition>
    </action-definition>

    <action-definition>
    <component-name>JFreeReportComponent</component-name>
    <action-type>report</action-type>
    <action-inputs>
    <data type="result-set" mapping="rule-result"/>
    </action-inputs>
    <action-resources>
    <report-definition type="resource"/>
    </action-resources>
    <action-outputs>
    <report-output type="content" mapping="report_out"/>
    </action-outputs>
    <component-definition>
    <output-type><![CDATA[html]]></output-type>
    </component-definition>
    </action-definition>

    </actions>
    </action-sequence>

    But I still don't see a file anywhere.

  6. #6
    Join Date
    Jul 2007
    Posts
    2,498

    Default

    I'm digging into the source cause I have exactly the same problem (chartcomponent, on my case). Those output types are really undocumented and I'm having a hard time finding the right behaviour.

    Even if you dont get the file to the browser, you can find it (if all went right) in your solution_dir/sytem/tmp

    Please tell if you get the answer

    Good luck
    Last edited by pmalves; 08-22-2007 at 07:07 PM.
    Pedro Alves
    Meet us on ##pentaho, a FreeNode irc channel

  7. #7
    Join Date
    Jan 2008
    Posts
    2

    Question Still having problems with this

    I think i have all the correct xml elements for this, but I am still running into difficulty when saving my xaction output to a file.

    I don't get an error message when I am running my xaction, but I get this:

    Action Successfulreport_out=customerInsights/scheduler/export_events.xaction.html, customerInsights/scheduler/export_events.xaction/html/014f181c-c87d-11dc-a5d2-1bbea865f5fe, text/html

    Here is my xml file:
    Attached Files Attached Files

  8. #8

    Default

    I have tried to implement this example but all I get is an Action Successful message, I can't find where the file was saved and there are no errors in the log. Any suggestions?

  9. #9

    Default

    This is mine and it worked when I last tried it:

    Code:
    <?xml version="1.0" encoding="UTF-8"?>
    <action-sequence>
      <title>OAPLJNL151(hide)</title>
      <version>1</version>
      <logging-level>TRACE</logging-level>
      <documentation>
        <author>Report Designer</author>
        <description>Created using the Pentaho Report Designer.</description>
        <icon>PentahoReporting.png</icon>
        <help>Choose Report Output Format.</help>
        <result-type>report</result-type>
      </documentation>
    
      <inputs>
        <output-type type="string">
          <sources>
            <request>type</request>
          </sources>
          <default-value><![CDATA[pdf]]></default-value>
        </output-type>
      </inputs>
    
      <outputs>
        <report-bits type="content">
          <destinations>
            <file>/mnt/ndrive/Purchase Ledger/Documents/plv_151_{blahout}.pdf</file>
          </destinations>
        </report-bits>
      </outputs>
    
      <resources>
        <report-definition>
          <solution-file>
            <location>OAPLJNL2-151.xml</location>
            <mime-type>text/xml</mime-type>
          </solution-file>
        </report-definition>
      </resources>
    
      <actions>
        <action-definition>
          <component-name>SQLLookupRule</component-name>
          <action-type>Relational</action-type>
          <action-outputs>
            <query-result type="result-set" mapping="branchcode"/>
            <blahout type="string"/>
            <brchout type="string"/>
          </action-outputs>
          <component-definition>
            <jndi><![CDATA[VectaEncon]]></jndi>
            <live><![CDATA[true]]></live>
            <query><![CDATA[SELECT DISTINCT
      `tbl_pl`.`costcentre` as blahout,
      `BRANCHES`.`BRANCH_ID` as brchout
     FROM `tbl_pl`
     INNER JOIN `BRANCHES` ON (`tbl_pl`.`costcentre` = `BRANCHES`.`Number`)]]></query>
          </component-definition>
        </action-definition>
    
        <actions loop-on="branchcode">
          <action-definition>
            <component-name>JFreeReportComponent</component-name>
            <action-type>report</action-type>
            <action-inputs>
              <blahout type="string"/>
              <output-type type="string"/>
            </action-inputs>
            <action-outputs>
              <report-bits type="content"/>
            </action-outputs>
            <component-definition>
              <live>true</live>
              <jndi><![CDATA[VectaEncon]]></jndi>
              <source>sql</source>
              <query><![CDATA[SELECT
         `BRANCHES`.`BRANCH_NAME`,
         `tbl_pl`.`costcentre` AS Branch,
         Concat(`period`, '/', `yearno`) as PerYear,
         `tbl_pl`.`expensecode` AS tbl_pl_expensecode,
         Concat(`doc-id`, '/', `docnumber`) as Document_ID,
         `tbl_pl`.`docdate` AS DocumentDate,
         `tbl_pl`.`sourceref` AS SrcRef,
         `tbl_pl`.`description` AS De,
         IF(`tbl_pl`.`extra`='INV', 'Total Supp CR Notes for month', 'Total Accounts Adjustments') AS tbl_pl_extra,
         `tbl_pl`.`baseval` AS Val
    
    FROM
         `tbl_pl` INNER JOIN BRANCHES ON tbl_pl.costcentre = BRANCHES.Number
    WHERE
         `tbl_pl`.`expensecode` = '20/151' AND
         `tbl_pl`.`costcentre` = '{blahout}'
    Order By `costcentre`, `tbl_pl`.`extra`]]></query>
            </component-definition>
          </action-definition>
    
        </actions>
    
      </actions>
    </action-sequence>
    This is a signature.... everyone gets it.

    Join the Unofficial Pentaho IRC channel on freenode.
    Server: chat.freenode.net Channel: ##pentaho

    Please try and make an effort and search the wiki and forums before posting!
    Checkout the Saiku, the future of Open Source Interactive OLAP(http://analytical-labs.com)

    http://mattlittle.files.wordpress.co...-bananaman.jpg

  10. #10
    Join Date
    May 2008
    Posts
    1

    Default

    HI

    I have tried to implement this example but all I get is an Action Successful message:

    [pt_121] Action Successfulreport_out=samples/reporting/testeMeu.xaction.pdf, samples/reporting/testeMeu.xaction/pdf/f7ed7905-2738-11dd-9c0a-63aba8b126f1, application/pdf

    what's this?

    sorry my english

    vilmar spies

  11. #11

    Arrow

    Hi.

    I get the same message:
    report_output=/Reports/ReportCPU.xaction.pdf, /Reports/ReportCPU.xaction/pdf/bf011032-4352-11dd-a5c4-81422d1d7351, application/pdf

    I found the report on the server in the folder system\content\Reports\ReportCPU.xaction\pdf with a filename
    d6b99a84-4352-11dd-a5c4-81422d1d7351.pdf.

    But the report file is not shown in the browser.
    I have installed Pentaho BI Platform 1.6.0.GA.863 and tried IE7, IE6 & Firefox 3.0.

    How can I send the report to the browser?

    Kind regards
    myma

  12. #12
    Join Date
    Jul 2007
    Posts
    2,498

    Default

    Destination of the report_output must be 'content'
    Pedro Alves
    Meet us on ##pentaho, a FreeNode irc channel

  13. #13

    Default

    report destination for browser must be content for saving to disk must be file.
    This is a signature.... everyone gets it.

    Join the Unofficial Pentaho IRC channel on freenode.
    Server: chat.freenode.net Channel: ##pentaho

    Please try and make an effort and search the wiki and forums before posting!
    Checkout the Saiku, the future of Open Source Interactive OLAP(http://analytical-labs.com)

    http://mattlittle.files.wordpress.co...-bananaman.jpg

  14. #14

    Arrow

    Hi,

    thx 4 your fast reply.
    Right. The report is send to the browser wit the setting Destianation -> response and Name -> content.
    But I need a report within the browser with a name like plv_151_{parameter}.pdf. So an end user can safe the report on the local disk without editing the filename for the report.
    My default file name is always ViewAction.pdf.

    How can you sent a report to the browser with a pre defined filename?
    Thx 4 reply :-).

    Regards myma

  15. #15

    Default

    What version of the Server are you running as that should now happen automatically
    This is a signature.... everyone gets it.

    Join the Unofficial Pentaho IRC channel on freenode.
    Server: chat.freenode.net Channel: ##pentaho

    Please try and make an effort and search the wiki and forums before posting!
    Checkout the Saiku, the future of Open Source Interactive OLAP(http://analytical-labs.com)

    http://mattlittle.files.wordpress.co...-bananaman.jpg

  16. #16

    Default

    That's was the fastest reply ever. thx.

    I have installed Pentaho BI Platform 1.6.0.GA.863 and I tried IE7, IE6 & Firefox 3.0.

  17. #17
    Join Date
    Apr 2009
    Posts
    337

    Default

    Hi,

    I am trying to save the reports at a specific location and at the same time make it available for the user to view from User console under his workspace(for executed public schedules) is this possible under the same xaction ??

  18. #18
    Join Date
    Apr 2009
    Posts
    337

    Default

    I am viewing report in browser byr setting "response-> content"in output.. but When i try to schedule it, it wouldn't work! it just runs the report.. can we not schedule xactions which run prpt and show output in User console?

  19. #19
    Join Date
    Apr 2009
    Posts
    337

    Default

    Ok few things I found out.. if i add some prompt/filter for output type/ input parameters, then it gets scheduled successfully... but it just runs it if i dont have any prompt and shows the report on screen when i try to schedule it!!!

  20. #20
    Join Date
    Aug 2010
    Posts
    21

    Default

    Hi,

    emm.. my english is so poor, but if I understand the problem, you need to show the report in pdf file (show a window for save the report by the final user) isn't it?

    you say you've added some prompt/filte to choose the output type, and anyway, the report is shown in the screen... So, when you add the report action, in the REPORT FORMAT field you must put the output type parameter... (default is HTML)

    I hope to help you...

    Regards

  21. #21
    Join Date
    Apr 2009
    Posts
    337

    Default

    Thanks glahuka, but i have solved the issue already.. what i wanted to do was to generate a pdf(any format for that matter) file and automatically store it in a given location--- this part is distribution, and also parallely show the report on screen(response--> content)..

    so that, when scheduled, the user can view the execution history of the xaction from his workspace and view them by clicking view from the workspace(meanwhile an archive of pdfs(again, any format for that matter) is available in the filesystem also)

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.