Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: How to assign the result of a MDX query to a integer variable

  1. #1
    Join Date
    May 2007
    Posts
    10

    Unhappy How to assign the result of a MDX query to a integer variable

    i am referring to dial.xaction example in Pentaho Dashboard samples. i want to pass a result of MDX query(which is a integer field) to the input variable called value.pls help.

    Below is my query.i want to calculate the varaince of target sales and achieved sales.


    with member [Measures].[Variance Percent] as '([Measures].[TotalAchievedSales] / [Measures].[TotalTargetSales])',

    value=select NON EMPTY {[Measures].[TotalTargetSales],[Measures].[TotalAchievedSales], [Measures].[Variance Percent] } ON COLUMNS,
    NON EMPTY {[Territory.Territory].[All territory]} ON ROWS
    from [RDTestSales3]

    thanks.

  2. #2
    Join Date
    Dec 2005
    Posts
    531

    Default use Javascript

    Hi,

    You can pass the result set from the MDX query into a Java Script action. In there you can access the result set with various methods such as:

    getRowCount()
    getValueAt(x,y)

    This way you can select the right cell in the result set and write it into a new variable.

    All the best,
    Ingo

    Sample ActionSequence:
    Code:
    <?xml version="1.0" encoding="UTF-8"?>
    <action-sequence> 
      <title>test MDX Java Script</title>
      <inputs/>
    
      <outputs> 
        <reportData type="result-set"/>
        <test type="string"/>
      </outputs>
    
      <resources> 
        <catalog> 
          <!-- solution-file>
    				<location>SampleData.xml</location>
    				<mime-type>text/xml</mime-type>
    			</solution-file -->  
          <url> 
            <location>samples/reporting/SampleData.mondrian.xml</location>  
            <mime-type>text/xml</mime-type> 
          </url> 
        </catalog> 
      </resources>
      
      <actions> 
        <action-definition> 
          <component-name>MDXLookupRule</component-name>
          <action-type>OLAP Query For Report Data</action-type>
          <action-resources> 
            <catalog type="resource"/> 
          </action-resources>
          <action-outputs> 
            <query-results type="result-set" mapping="reportData"/> 
          </action-outputs>
          <component-definition> 
            <jndi>SampleData</jndi>  
            <location>mondrian</location>  
            <query><![CDATA[select NON EMPTY {[Measures].[Actual], [Measures].[Budget]} ON COLUMNS,
    NON EMPTY {[Region].[All Regions]} ON ROWS
    from [Quadrant Analysis]]]></query> 
          </component-definition> 
        </action-definition>
      
        <action-definition> 
          <component-name>JavascriptRule</component-name>
          <action-type>JavaScript</action-type>
          <action-inputs> 
            <reportData type="result-set"/> 
          </action-inputs>
          <action-outputs> 
            <test type="integer"/> 
          </action-outputs>
          <component-definition> 
            <script><![CDATA[test = reportData.getValueAt(0,0);]]></script> 
          </component-definition> 
        </action-definition>
     
      </actions> 
    </action-sequence>
    Last edited by ingo.klose; 05-30-2007 at 06:25 AM.

  3. #3

    Default

    In a reversal anyone know how to pass inputs to an mdx query?

    Cheers

    Tom

    Ignore - dumb question didn't think an action script would like the { }
    Last edited by bugg_tb; 06-19-2007 at 06:18 AM.

  4. #4
    Join Date
    Oct 2007
    Posts
    7

    Default

    hi;
    one question. I have the problem to pass de correct param to drill down because, drill url receive time=2008|time. when i only want time=2008 you say that it's posible to solve it with javascript in the xaction can you tell me how? how i can to delete |time for the result of the mdx query?

    thanks

  5. #5
    Join Date
    Feb 2013
    Posts
    530

    Default

    MDX query + date range parameter ??
    Can any body post a working example with these two ?


    Thanks,
    Sadakar

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.