Jump to content
Changes to the Jaspersoft community edition download ×

help with variable please!!


Recommended Posts

Hi to all!!


I'm very confused with this!!! look... i've this data:


qty unitcost costlast difunitcost %varUC

period coslastperiod lastper

Line1 7678 527.4 2273.3 -1745.9 -331

Prod1 7561 506.11 0 506.11 100

Prod2 0 0 0 0 0

Prod3 0 0 0 0 0

Prod4 3 523.33 0 523.33 100

Prod5 63 2273.3 2273.3 0 0

Prod6 51 1527.76 0 1527.76 100


dif unit cost last period it's unitcost - costlastcost


and %varUClasper it's a formula as (difunitcost * 100)/unitcost


if you see in Line1 the value of %varUClastper its -331 what if whe think its ok. But this % isn't representative of %varUC Products under Line1... i need a variable what help in navigation and search where %var... it's more big and detect cost variations...


i really anyone understand my problem and can help me to create a real usefull variable.



Link to comment
Share on other sites

  • Replies 5
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Ok... this is


<?xml version="1.0"?>

<Schema name="Puig">

<Dimension name="Producto">

<Hierarchy hasAll="true" primaryKey="PRODUCTO_KEY">

<Table name="Dim_Producto_Existencias"/>

<Level column="NombreDivision" name="Nombre Division"/>

<Level column="NombreCasa" name="Nombre Casa"/>

<Level column="NombreMarca" name="Nombre Marca"/>

<Level column="CodigoReferencia" name="Codigo Referencia">

<Property name="NombreReferencia" column="NombreReferencia"/>




<Dimension name="TipoMovimiento">

<Hierarchy hasAll="true" primaryKey="TIPO_MOVIMIENTO_KEY">

<Table name="Dim_Tipo_Movimiento"/>

<Level column="TipoMovimiento" name="TipoMovimiento"/>

<Level column="SubTipoMovimiento" name="SubTipoMovimiento"/>



<Dimension name="FechaDocumento" type="TimeDimension">

<Hierarchy hasAll="true" primaryKey="CIERRE_KEY">

<Table name="dim_cierre_ex"/>

<Level name="Ano" column="Ano" type="Numeric" uniqueMembers="true" levelType="TimeYears"/>

<Level name="Mes" column="CodigoMes" uniqueMembers="false" type="Numeric" levelType="TimeMonths"/>



<Cube name="Existencias">

<Table name="Tabla_Fact_DM_EXISTENCIAS"/>

<DimensionUsage foreignKey="TIPO_MOVIMIENTO_KEY" name="TipoMovimiento" source="TipoMovimiento"/>

<DimensionUsage foreignKey="PRODUCTO_KEY" name="Producto" source="Producto"/>

<DimensionUsage foreignKey="CIERRE_KEY" name="FechaDocumento" source="FechaDocumento" />

<Measure aggregator="sum" column="cantidad" formatString="Standard" name="Qty"/>

<Measure aggregator="sum" column="valor" formatString="#,###.00" name="ValorTrx"/>

<Measure aggregator="count" column="cantidad" name="QMovs"/>

<Measure aggregator="sum" column="costopromedio" formatString="#,###.00" name="CuTrx"/>

<CalculatedMember name="CuActual" dimension="Measures"




<CalculatedMember name="CuLP" dimension="Measures" formatString="Currency"

formula="([Measures].[CuActual],[FechaDocumento].PrevMember)" >

<CalculatedMemberProperty name="SOLVE_ORDER" value="1"/>



<CalculatedMember name="VarCuLP" dimension="Measures"



<CalculatedMemberProperty name="SOLVE_ORDER" value="1"/>



<CalculatedMember name="VarCierreLY" dimension="Measures"

formula="([FechaDocumento].CurrentMember,[Measures].[CuActual]) - (ClosingPeriod(Mes,[FechaDocumento].CurrentMember.Parent.PrevMember), [Measures].[CuActual])">

<CalculatedMemberProperty name="SOLVE_ORDER" value="3"/>





Link to comment
Share on other sites

this is:


with member [Measures].[Var%CuLP] as 'Iif([Producto].CurrentMember.Level.Name = "Codigo Referencia ",(([Measures].[VarCuLP] * 100.0) / ([Producto].CurrentMember, [Measures].[CuLP])),(([Measures].[VarCuLP] * 100.0) / ([Producto].CurrentMember, [Measures].[CuLP])))',

FORMAT_STRING = IIf(([Measures].[Var%CuLP] < 15.0), IIf(([Measures].[Var%CuLP] < 10.0), IIf(([Measures].[Var%CuLP] < 0.0), "|#|style=green","|#|style=yellow"),"|#|style=yellow"), "|#|style=red")

select {[Measures].[Qty], [Measures].[CuActual], [Measures].[CuLP], [Measures].[VarCuLP], [Measures].[Var%CuLP], [Measures].[VarCierreLY] } ON COLUMNS,

{[Producto].[All Productos], [Producto].[All Productos].[FPC ], [Producto].[All Productos].[PBB ], [Producto].[All Productos].[Producto T], [Producto].[All Productos].[servicio ]} ON ROWS

from [Existencias]

where [FechaDocumento].[All FechaDocumentos].[2006].[2]


Thanks for any help, i'm really confused

Link to comment
Share on other sites

This seems weird to me.


<CalculatedMember name="CuActual" dimension="Measures"
formula="([Producto].CurrentMember,[Measures].[ValorTrx])/([Producto].CurrentMember,[Measures].[Qty] )"/>





<CalculatedMember name="CuActual" dimension="Measures"


formula="iif([Measures].[Qty] > 0, [Measures].[ValorTrx]/[Measures].[Qty], 0)"/>





Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...