Dundas complex calculations

#1

Hi Team,

We need to work on 2 complex calculations

  1. Avg Value=total(amount)/count(seller)
    where count (seller) is created based on seller name,id (count(seller) for seller name,id)

2)We have 2 flags coming from data table like is_seller or is_distributor both these flag has ‘Yes’ , ‘No’ value.
at dashboard level we need a single radion button to select either seller or distributor.When we select seller is_seller flag should be set as ‘Y’ and when we select Distributor,is_distributor flag should be set as ‘Y’

Can you please help us how can we achieve these scenarios/calculations in Dundas?

1 Like
#2

Hi Mitsu,

In order to do calculation in Dundas BI, you can use our inbuilt formulas. You can check out the list of formulas here:
https://www.dundas.com/support/learning/documentation/analyze-data/formulas/list-of-formulas?v=7.0

We also support all the javascript function in case the desired formula is not covered.

For the radio button to change the flag to yes, you would have to create radio button component from the toolbar and add values to it manually. Later, on the selection changed event of the radio button, write a script which checks the value and set the view parameter accordingly which further changes the visualization. Here is the sample script doing the same:

var myVP = this.parentView.control.getViewParameterByName(“viewParameter1”);

if(radioButtonList1.items[0].isChecked == true){

// Remove all values and tokens from the parameter value
myVP.parameterValue.clearTokens();
myVP.parameterValue.clearValues();

var myMemberValue =new dundas.data.MemberValue({
“hierarchyUniqueName”:“OnlineOrderFlag”,
“levelUniqueName”:“OnlineOrderFlag”,
“memberKind”:“Regular”,
“uniqueName”:“True.OnlineOrderFlag”
});

myVP.parameterValue.token =null;
myVP.parameterValue.values.length =0;
myVP.parameterValue.values.push(myMemberValue);

myVP.invalidateParameterValueLastModifiedTime();
myVP.refreshAllAdapters();
}
else{

// Remove all values and tokens from the parameter value
myVP.parameterValue.clearTokens();
myVP.parameterValue.clearValues();

var myMemberValue =new dundas.data.MemberValue({
“hierarchyUniqueName”:“OnlineOrderFlag”,
“levelUniqueName”:“OnlineOrderFlag”,
“memberKind”:“Regular”,
“uniqueName”:“False.OnlineOrderFlag”
});

myVP.parameterValue.token =null;
myVP.parameterValue.values.length =0;
myVP.parameterValue.values.push(myMemberValue);

myVP.invalidateParameterValueLastModifiedTime();
myVP.refreshAllAdapters();
}

NOTE: You would have to change the script as per your use case.

Here is the related articles which will help you to implement the solution mentioned:
https://www.dundas.com/support/developer/script-library/filter-and-parameter/set-collection-member-parameter

Regards,
Abhay Rai | BI Implementation Associate

#3

Thanks a lot Abhay for help.

For the first question about the calculations do you mean that we have to create the formula at metric set level instead creating at cube level? will it affect the performance?

#4

As a note, you don’t need to use script for this.

Instead if you pre-create a View Parameter and attach it to the parameter you want, you can then add a ‘Filter Action’ to the ‘Selection Changed’ actions, and when you select the view parameter you created it will give you the opportunity to select a constant value (and create another VP to hold this constant).

You also have to set the ‘Bound Visual’ to the correct radio button item you want to have that value set (and add multiple filter actions to the ‘Selection Changed’ - 1 for each radio button item).

You can read more about this here:
https://www.dundas.com/support/learning/documentation/create-view-content/interactions/using-interactions

1 Like
#5

Thanks Terrence for help.

1 Like
#6

Hi Terrence ,

I was trying what you suggested.

I could not understand few lines:
nstead if you pre-create a View Parameter and attach it to the parameter you want, you can then add a ‘Filter Action’ to the ‘Selection Changed’ actions, and when you select the view parameter you created it will give you the opportunity to select a constant value .I could not understood why 3 paramters are required.

i tried implementing the same as below:
image

When it is billed -billed filter is Y but when i am clicking Recognized than also Billed is Y. What we want it , when we click billed; recognized should reselt to ‘all’. Here i am not able to set 2 filters for single parameter ‘Billed’ or ‘Recognized’ (‘Y’ or ‘ALL’) in ‘Selection Changed’ event.

Billed and Recognized value filters are coming from db but radio button is created with static value.

#7

I’m not sure what three view parameters you’re referring to, but when setting up a filter action on your radio button list and choosing a constant value such as “Y”, this is a shortcut for creating an additional view parameter for the purpose of storing this specific value. The filter action’s parameter mapping is set up to pass this value from one view parameter to the other.

You can add as many filter actions as you want to the radio button list. By setting Bound Visual to a particular radio button you can set up two filter actions per radio button, one for each filter. For example: Recognized = Y & Billed = All for one button, and Recognized = All & Billed = Y for the other.

#8

Hi,

I was able to achieve the solution suggested above for setting the viewparameter values based on radio button selection. I have to implement clear filter functionality as well.I am able to reset view parameters to all but i am not able to reset the radio button. can you please help here?

#9

Great. If it’s a radio button list with items you created manually, I don’t think there is anything that would clear the checked states automatically and doing so would probably involve script. For example:

radioButtonList1.items.forEach(function (item) {
  item.isChecked = false;
});