asked on August 25, 2015
How do I use calculated values? I couldn't get anything to work, so I created a very simple example of what I would like to do:
if([Svc Cd].[Svc Cd] == 124, 1, 0)
Since I couldn't get that to work, I tried this to see the value of Svc Cd:
if([Svc Cd].[Svc Cd] == 124, 999, [Svc Cd].[Svc Cd])
Svc Cd is always 0. I've checked the "Calculate individual values" check box. Why is Svc Cd always a zero value, even though the actual value of 124 is displayed on the row?
This is my report.xml file:
<defaultSlice>
<axes>
<axis name="rows">
<hierarchy sort="asc">
<dimensionName>[Svc Dt]</dimensionName>
<hierarchyName>[Svc Dt].[Svc Dt]</hierarchyName>
</hierarchy>
<hierarchy sort="asc">
<dimensionName>[Service]</dimensionName>
<hierarchyName>[Service].[Service]</hierarchyName>
</hierarchy>
<hierarchy sort="asc">
<dimensionName>[Svc Cd]</dimensionName>
<hierarchyName>[Svc Cd].[Svc Cd]</hierarchyName>
</hierarchy>
<expanded>
<tuple>1</tuple>
<tuple>1,3</tuple>
</expanded>
</axis>
<axis name="columns">
<hierarchy sort="asc">
<dimensionName>[Pgm Desc]</dimensionName>
<hierarchyName>[Pgm Desc].[Pgm Desc]</hierarchyName>
</hierarchy>
<hierarchy>
<dimensionName>[Measures]</dimensionName>
<hierarchyName>[Measures]</hierarchyName>
</hierarchy>
</axis>
<axis name="pages"/>
</axes>
<measures>
<measure aggregation="distinctcount" active="true">[Measures].[Pat ID]</measure>
<measure calculated="true" individual="true" uniqueName="[Measures].[1ofiui5c]" grandTotalCaption="Total Bed Days" caption="WPH Total Bed Days" active="true"><![CDATA[if([Svc Cd].[Svc Cd] == 0, 9, [Svc Cd].[Svc Cd])]]></measure>
<measure aggregation="count" active="false" availableAggregations="count,distinctcount">[Measures].[Svc Dt]</measure>
<measure aggregation="count" active="false" availableAggregations="count,distinctcount">[Measures].[Svc Cd]</measure>
<measure aggregation="count" active="false" availableAggregations="count,distinctcount">[Measures].[Service]</measure>
<measure aggregation="sum" active="false">[Measures].[Pgm Cd]</measure>
<measure aggregation="count" active="false" availableAggregations="count,distinctcount">[Measures].[Pgm Desc]</measure>
<measure aggregation="count" active="false" availableAggregations="count,distinctcount">[Measures].[Chart #]</measure>
<measure aggregation="count" active="false" availableAggregations="count,distinctcount">[Measures].[Sex]</measure>
<measure aggregation="count" active="false" availableAggregations="count,distinctcount">[Measures].[DOB]</measure>
<measure aggregation="count" active="false" availableAggregations="count,distinctcount">[Measures].[Pop]</measure>
<measure aggregation="count" active="false" availableAggregations="count,distinctcount">[Measures].[T19]</measure>
</measures>
</defaultSlice>
<view>
<column><![CDATA[[Measures].[Pat ID],98]]></column>
<column><![CDATA[[Pgm Desc].[Pgm Desc].[Haddon House Residential],[Measures].[Pat ID],89]]></column>
<column><![CDATA[[Pgm Desc].[Pgm Desc].[Hillside - Substance Abuse Residential],[Measures].[Pat ID],107]]></column>
<column><![CDATA[[Pgm Desc].[Pgm Desc].[PP - Windhaven Psychiatric Hospital],[Measures].[Pat ID],116]]></column>
<column><![CDATA[[Pgm Desc].[Pgm Desc].[Windhaven Psychiatric Hospital],[Measures].[Pat ID],109]]></column>
<column><![CDATA[[Pgm Desc].[Pgm Desc].[Haddon House Residential],[Measures].[1ofiui5c],72]]></column>
<column><![CDATA[[Pgm Desc].[Pgm Desc].[Hillside - Substance Abuse Residential],[Measures].[1ofiui5c],76]]></column>
<column><![CDATA[[Pgm Desc].[Pgm Desc].[PP - Windhaven Psychiatric Hospital],[Measures].[1ofiui5c],72]]></column>
<column><![CDATA[[Pgm Desc].[Pgm Desc].[Windhaven Psychiatric Hospital],[Measures].[1ofiui5c],75]]></column>
</view>
⋅ August 25, 2015
Hello,
Thanks for the question.
It seems that there is a mistake in the formula, please try the following:
if (sum("[Svc Cd].[Svc Cd]") == 124, 1, 0)
Also, we recommend using drag'n'drop in the formula creator to avoid such typos.
Please contact me if you need any further assistance.
Regards,
Ian
⋅ August 25, 2015
Ian,
Thank you for responding. I need to state clearly what I am trying to do. Your example is checking to see if the sum of the Svc Cd is 124. I want to do this (I left out the sum just to make my question as simple as possible):
sum( if(“[Svc Cd].[Svc Cd]“ == 124, 1, 0) )
In other words, if the Svc Cd is "124", I want to add 1 to the displayed sum, otherwise add 0.
Is this possible?
⋅ August 25, 2015
Yes, it's possible.
In that case, please try the following with the "Calculate individual values" checked (http://take.ms/hY0mL):
if( sum("[Measures].[Svc Cd]") == 124, 125, sum("[Measures].[Svc Cd]") )
⋅ August 25, 2015
Ian,
I checked the "Calculate individual values" check box. That didn't work. In fact, the following formula does not display any value at all (It's "blank", not even a zero):
sum(“[Measures].[Svc Cd]”)
⋅ August 26, 2015
Hi,
Thanks for the details.
I've created a simple fiddle with live sample - https://jsfiddle.net/irynakulchytska/139w9sjc/
Please check line 31 with formula.
Hope this helps.
Regards,
Ian
⋅ December 19, 2016
Hello John and all Flexmonster users,
Starting from version 2.3, the structure of Report Object was changed. Find more details in the documentation: http://www.flexmonster.com/api/report-object/. Please check the updated sample: https://jsfiddle.net/flexmonster/bgmwtd1x/.
Regards,
Tanya
