You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 30 Next »

It is recommended to get familiar with crossValue, memberValue and filter function before proceeding with this tutorial.

CrossValue function is used to access Indicators from other Data Sets. Since BellaDati 2.9.2, it is also possible to access Attributes by using function such as lastValue() of firstValue().

It receives two parameters:

  1. Cube name
  2. Indicator name

Following example returns Students count from Results data set.

crossValue('CUBE_RESULTS.RESULTS','L_STUDENT_COUNT')

Access cross reference function from Formulas help to predefine Cube name.

Cross Reference with and without Drill-down

When cross-referencing values, BellaDati does take in consideration applied drill-downs. That means both data sets need to have the same attribute (same code) and same members.

To display value without drill-down, use the cross value in combination with the function withoutDrillDown. That will display same value for each member.

withoutDrillDown() { 
crossValue('CROSSVALUETEST2.CROSSVALUETEST2','M_GDP')}

The same also applies to date and time drill-downs and function withoutDateTime.

withoutDateTime() { 
crossValue('CROSSVALUETEST2.CROSSVALUETEST2','M_GDP')}

In some cases the members might be the same but the attribute code might be different. In that case you need to explicitelly tell BellaDati, which attribute from the referenced data set should be used.

You can achieve this by specifying memberIdentifier as second parameter of crossValue functions. You need to construct you own string with memberValue.

crossValue('CROSSVALUETEST2.CROSSVALUETEST2','[L_COUNTRY_REF="'+memberValue()+'"]','M_GDP')

In this example L_COUNTRY_REF is the code of attribute in referenced data set and memberValue is value of current member (name of country).

This will also automatically handle drill-down paths.

Cross Reference with Filter

BellaDati also does not take in consideration filters applied throught view settings. Therefore, you need to explicitelly tell BellaDati in formula definition which filters and how do you want to use them.

filter("L_PASS_RESULT in ('"+ memberValue()+"') AND L_PASS_SUBJECT in ('Math')"){  
	crossValue('CUBE_RESULTS.RESULTS','L_STUDENT_COUNT')
}

filter("L_DATE is not NULL"){
	crossValue('DATASET','[L_NAME="'+memberValue('L_USERNAME')+'"]','L_USERNAME_DCOUNT')
}

 

Filter can be defined also directly inside the crossValue function:

crossValue('CROSS','[L_ATT1="aaa"]','M_CROSS')

Multiple conditions can be combined by using the plus sign:

crossValue('CROSS','[L_ATT1="aaa"]'+'[L_ATT2="bbb"]','M_CROSS')

Please note that the value of the filter has to be String, even when it is a numerical value.

Example: crossValue('dataset','[L_MONTH="'+month(minus(date(contextDay()),'months',1)).toString()+'"]','M_INDICATOR')

 

Cross Reference with Date

When filtering date, date attribute must be in format 'yyyy-MM-dd'.

filter("L_DATE in ('2015-01-01')"){  
	crossValue('CUBE_RESULTS.RESULTS','M_MEASURE')
}

filtered date can also defined inside the crossValue function:

crossValue('CUBE_RESULTS.RESULTS','[L_DATE={2015-01-01}]','M_MEASURE')

You can find more about filters and their combinations in Filtering in Formulas .

Cross-Referencing attributes

Since BellaDati 2.9.2, it is also possible to access Attributes by using operators such as LASTVALUE or FIRSTVALUE.

crossValue('COUNTIES','L_REGION_LASTVALUE')

Next Steps

  • No labels