Summing a formula

[AnnieRider]

Hi

My report details multiple histories for individual clients. I have to pick out certain histories so each history has the following formula:

=IIf(Fields!DateStart.Value< #7/1/2008# And (Fields!HistoryType_Ref.Value = "G"
or Fields!HistoryType_Ref.Value = "R"), 1,0)

The histories are grouped by the Client they belong to and I have to denote which Clients have any of the certain histories. I've done this by inserting a total for each client, so:

=SUM(IIf(Fields!DateStart.Value< #7/1/2008# And (Fields!HistoryType_Ref.Value = "G"
or Fields!HistoryType_Ref.Value = "R"), 1,0))

and then using an expression to show clients where the above is more than zero, like so:

=iif(SUM(IIf(Fields!DateStart.Value< #7/1/2008# And (Fields!HistoryType_Ref.Value = "G"
or Fields!HistoryType_Ref.Value = "R"), 1,0)) >=1,1,0)

To make life even more difficult I need to show the sum of the Clients who have at least one of the certain histories as a percentage of the total Clients in the report!

The problem being that I can't even find a way to do the sum, before even thinking about the percentage!

I'm wondering if I've got too many formulas because what I came up with is a Sum of the iif(SUM(IIf and I imagine that's probably expecting too much!

Many thanks
Annie

 

[xlbo]

Try making these Dataset fields (right click on a dataset member in teh fields list and choose "Add")

You should then be able to reference these fields in your SUM rather than trying lots of nested IIF statements

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnnieRider]

I can do this for the first statement and have added a dataset field called Intervention with that statement in. For the second I've got

=SUM(Fields!Intervention.Value)

and have put that as a seperate dataset field but the report wont run and gives the error

The definition of the report '/Percentage of YP Seen Yr11 2008' is invalid. An internal error occured on the report server.

Is this because the second expression is for the group and not an individual field?

It errors after creating the field and before inserting it in to the report.

Regards
Annie

 

[xlbo]

I was thinking more along the lines of making these:
=IIf(Fields!DateStart.Value< #7/1/2008# And (Fields!HistoryType_Ref.Value = "G"
or Fields!HistoryType_Ref.Value = "R"), 1,0)

dataset fields - your summing may well have to be done as an expression but it will be much easier to get the logic right if you are using a field name rather than having to input the formula multiple times...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnnieRider]

Hi again

yes that's what I did for the first expression so I've got

=IIf(Fields!DateStart.Value< #7/1/2008# And (Fields!HistoryType_Ref.Value = "G"
or Fields!HistoryType_Ref.Value = "R"), 1,0)

as a dataset field called Intervention and have got the sum

=SUM(Fields!Intervention.Value)

but I now need to count all those for whom the above SUM is not 0 and it's this that it won't let me do. I seem to have just too many sums.

Regards
Annie

 

[AnnieRider]

Is there a way of specifying the Group in the following

=iif(SUM(Fields!Intervention.Value) >=1,1,0)?

I was wondering if I could put this as a dataset field also

 

[acessn]

Try this:


=IIf(Fields!DateStart.Value< #7/1/2008# And (Fields!HistoryType_Ref.Value = "G"
or Fields!HistoryType_Ref.Value = "R"), 1.00,0.00)

I have often had troubles with formulas and summing if I don't use a decimal. You can probably try


=IIf(Fields!DateStart.Value< #7/1/2008# And (Fields!HistoryType_Ref.Value = "G"
or Fields!HistoryType_Ref.Value = "R"), cdec(1),cdec(0))


as well


Regards,
Bjorn

 

[AnnieRider]

Hi Bjorn

Thanks, but my issue is more that I cannot do a Sum of a Sum.

I can create a SUM of the above expression (with or without decimal) fine. But I need to then add up all those for who that SUM is 1 or greater (or <>0).

And it won't let me, I can do it in Crystal so surely it's not impossible.

Thanks everyone for all the input, I know it's probably getting rather confuddled now! I'm going to keep searching, anyhow.

 

[AnnieRider]

Oh my giddy aunt we've done it!!

I've done a SUM referencing the Textbox that holds the true or false expression. I've had to put it in the header (but at this point in time I figure that the layout is the least of my issues I will jig it about later!)

Thanks Geoff and Bjorn.

 

[AnnieRider]

Ah, won't slight problem I'm now having with this is that I cannot work out the Sum that I've now got in the header as a percentage of another amount also held in the header.

So I've got my sum of those whose value in an expression textbox is True, which is the one I was having issues with above:

=SUM(ReportItems!textbox19.Value)

and I've got a count of all records (Clients) in the report:

=CountDistinct(ReportItems!Client_Ref_1.Value)
both are in the header and I want to show in another textbox the result of the SUM as a % of the COUNT.


So in a textbox also in the header I've put:

=(ReportItems!textbox7.Value/ReportItems!textbox23.Value)*100

And I'm told that "An expression in a page header can refer to only one report item"

So I've tried:

=(ReportItems!textbox7.Value/CountDistinct(Fields!Client_Ref.Value))*100

But cannot refer to Fields in page headers.

I'm starting to feel really restricted now :-(
Does anyone know how I might be able to do this?

Thanks Annie