PDA

View Full Version : How to assign the result of a MDX query to a integer variable



Gayani1
05-30-2007, 05:09 AM
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.

ingo.klose
05-30-2007, 06:10 AM
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:


<?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>

bugg_tb
06-18-2007, 09:22 AM
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 { }

cfsanmiguel
04-15-2008, 01:50 PM
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

sadakar
08-06-2013, 04:19 AM
MDX query + date range parameter ??
Can any body post a working example with these two ?


Thanks,
Sadakar