Calculated Values

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:
<axis name=”rows”>
<hierarchy sort=”asc”>
<dimensionName>[Svc Dt]</dimensionName>
<hierarchyName>[Svc Dt].[Svc Dt]</hierarchyName>
<hierarchy sort=”asc”>
<hierarchy sort=”asc”>
<dimensionName>[Svc Cd]</dimensionName>
<hierarchyName>[Svc Cd].[Svc Cd]</hierarchyName>
<axis name=”columns”>
<hierarchy sort=”asc”>
<dimensionName>[Pgm Desc]</dimensionName>
<hierarchyName>[Pgm Desc].[Pgm Desc]</hierarchyName>
<axis name=”pages”/>
<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>
<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>

5 answers

Ian Sadovy Staff August 25, 2015

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.

j.warner August 25, 2015

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?

Ian Sadovy Staff August 25, 2015

Yes, it’s possible.
In that case, please try the following with the “Calculate individual values” checked (
if( sum(“[Measures].[Svc Cd]”) == 124, 125, sum(“[Measures].[Svc Cd]”) ) 

j.warner August 25, 2015

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]”)

Ian Sadovy Staff August 26, 2015

Thanks for the details.
I’ve created a simple fiddle with live sample –
Please check line 30 with formula.
Hope this helps. 

Please login or Register to Submit Answer