US and Worldwide: +1 (866) 660-7555
Results 1 to 2 of 2

Thread: Is this report possible?

  1. #1

    Default Is this report possible?

    Hi,

    I'm a new Pentaho user and have to integrate it into my company's infrastructure. My current goal is to replicate our current, simple, reporting that consists of basic queries and calculations. After playing around with Action Sequence I can do basic things such as populate a drop down list with a result set from our db and use the selection as input for another query... however certain things i haven't cracked yet because there's no documentation or examples that would show me how to do basic things. here's what i need to do and if somebody can point me to a right wiki, demo, or explain what needs to be done it would be highly appreciated!

    1) need to prompt user to select a clientID ( no problem with that step)
    2) based on the clientID, query DB and fetch all serviceIDs for that client. (no problem here either)
    3) loop over all serviceIDs, and query for each serviceID another table to get service data. (problem here!)
    3a)for every service, perform statistical calculations(means, totals, etc) that would go into report.(don't know how to do this...)

    i attempt to accomplish step 3 by using a Loop Action on the result-set of step 2. however, the loop seems to only return the last row of the result-set... i get that when i create a brand new Action Sequence with only steps 1,2 and 3. step 3 has a query: select COUNT(*), serviceID from services where serviceID='{serviceID}' group by serviceID. The output is one row containing the last serviceID of the result-set from step 2 and its counter result. Am i doing something wrong with the Loop action? I tried to get around this problem with a nested query which works directly against our MySQL db, but takes forever through Pentaho.

    step 3 i just haven't figured out yet. how can i pass the results from 3 to 3a and perform calculations for each service?

    Thank you!

  2. #2

    Default immediate problem

    how can i loop through a result-set from one query? Action Loop on the result-set only gives me the last row of the result-set. Here's my action xml without db connection details.

    <?xml version="1.0" encoding="UTF-8"?>
    <action-sequence>
    <title>NestedQuery</title>
    <version>1</version>
    <logging-level>ERROR</logging-level>
    <documentation>
    <author>Dmitry</author>
    <description>Empty blank action sequence document</description>
    <help/>
    <result-type>result-set</result-type>
    <icon/>
    </documentation>

    <inputs/>

    <outputs>
    <result type="result-set">
    <destinations>
    <session>query_result</session>
    </destinations>
    </result>
    </outputs>

    <resources/>

    <actions>
    <action-definition>
    <component-name>SQLLookupRule</component-name>
    <action-type>Relational</action-type>
    <action-inputs/>
    <action-outputs>
    <query-result type="result-set" mapping="query_result1"/>
    <service type="string"/>
    </action-outputs>
    <component-definition>
    <live><![CDATA[true]]></live>

    <query><![CDATA[select serviceID as service from services where clientID = 17 order by supercedes desc]]></query>
    </component-definition>
    </action-definition>

    <actions loop-on="query_result1">

    <action-definition>
    <component-name>UtilityComponent</component-name>
    <action-type>Service</action-type>
    <action-inputs>
    <service type="string"/>
    </action-inputs>
    <component-definition>
    <print>
    <arg>service</arg>
    <delimiter>", "</delimiter>
    </print>
    </component-definition>
    </action-definition>


    <action-definition>
    <component-name>SQLLookupRule</component-name>
    <action-type>Relational2</action-type>
    <action-inputs>
    <service type="string"/>
    </action-inputs>
    <action-outputs>
    <query-result type="result-set" mapping="result"/>
    </action-outputs>
    <component-definition>
    <query><![CDATA[select COUNT(*), serviceID from rdw where serviceID={PREPARE:service}]]></query>
    <live><![CDATA[true]]></live>

    </component-definition>
    </action-definition>


    </actions>

    </actions>
    </action-sequence>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •