How to create a calculated field if the copay type ="Percent"
-
Here are screen shots of my report. I need to divide the Required field by the Count field in order to get the Percent of copays that were collected. How can I do this based on the report? Something like this........
(When CopayType=Count) / (When CopayType=Req). This will get me the correct value for Percent, rather than sum of the Percent field, which it's doing now.
-
To get the percent calculation, you need to divide the correct report items value for the fields that you want to calculate for example.
For the sub total line, it would be something like, within the text box expression for the percentage sub total.
=ReportItems!Required.value/ReportItems!Count.Value
Replace Required and Count with the name of the text box for the relevant subtotals.
I hope that makes sense.
-
It's more like =(ReportItems!CopayType.Value="1 Req")/(ReportItems!CopayType.Value="2 Count"). But, I need the CopayValue that goes with the CopayTypes of "1 Req" and "2 Count". I'm using the numbers so, the columns will sort correctly.
-
Could you not use inline if function?
Something like
=iif(ReportItems!CopayType.Value="1 Req", ReportItems!CopayValue.Value,0 or something else)iif(/ReportItems!CopayType = "2 Count", ReportItems!CopayValue.Value,0 or something else)
I am not sure what you would put in the else part without knowing your data. -
Sorry just managed to enlarge your screenshot.
I don't know if I fully understand, however shouldn't it just be dividing the column 1 Req over 2 Count to give you the percentage.
So if this is the case it will be the name of the textbox that relates to 1 Req and the name of the textbox that relates to 2 Count.For the details line it would be
=ReportItems!1 Req.Value/ReportItems!2 Count.Value
And for the subtotal it would be something like.
=ReportItems!1 Req SubTotal.Value/ReportItems!2 CountSubTotal.Value - replace with the name of the textbox that relates to each of the sub total. -
The name of the textbox is CopayType and it's a column group so, when it runs you get these columns, "1 Req", "2 Count", "3 Dollars", and "4 Percent". There is no distinct text box with the values. What I need is how to identify the CopayValue based on the CopayType. I think we're almost there.
-
How is CopayType being populated?
Is it possible to split this into the separate fields that the column group is dynamically creating? Then you could use a Tablix and then the percentage calculation would be straightforward?
Or Inline If could work for you? -
Sounds like I'll have to change the stored procedure. Thanx for your help.
-
I am sure there would be a way to achieve what you are asking, however sometimes it might be easier to change your source and then it will make the report building more straightforward.
Personally I would try and use a Tablix with the calculated fields separated, which will then make the formulas easier.
Good luck.
-
Agreed
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply