Data from 2 sources.

[Betz]

My current report is working fine, with one list and one table. One TotalCount dataset and one detail dataset selecting only where DaysBetweenContactAndEntry >= @Threshold.

What I am trying to do... add two fields that need the total count of notes for each caseworker.

Caseworker's Name
Total Notes for time period = x
Notes over threshold = 3 (this was easy with dataset select statement)
Percent of notes overdue = y

I need a way to get the x and y.

Tried several methods to get at this data, none successful. Any help would be appreciated.

 

[xlbo]

If the report is for 1 case wroker at a time then

=Sum(Fields!NotesOverThreshold.value, "DataSet1Name")/Sum(Fields!TotalNotes.Value, "Dataset2Name")

Should work

If it is for multiple case workers, you may be able to reference the reportitmes collection to refer to a textbox or alternatively, you would need to return the Total Notes value as part of your 2nd query

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

 

[Betz]

Thanks for the reply Geoff,

It is for multiple caseworkers. So the table groups by caseworker.

Interestingly, I do have a report item with total working outside the table, showing grand total of notes.

And, among several other ideas, I did try using a report item outside the table to calculate, but if that method is how I should go, then I don't understand how to 'link' it to the caseworker group. So it is calculating on the grand total instead of the caseworker's total. Could it be something about 'lists' that I am not understanding?

I've tried so many things that I'm starting to think the solution is either right in front of my nose, OR it is not possible.

 

[xlbo]

You won;t be able to link it to the case worker group if they are from different data sets

your best bet will be to return the total no. of notes in your 2nd query along with those over threshold

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