none
Lookup function with CountRows

    Question

  • Hi

    I have an issue where I am trying to use the CountRows function with a Lookup function and I don't even know if this is possible.  I am guessing by the error I am receiving it cannot be done but I thought I would ask the question anyway.

    My report breaks down individual aspects of the staff members timesheet in non chargeable, chargeable, illness and so fourth which works fine however now I am trying to add a Bradford Factor column to assist our HR department.

    This is an example of a Lookup I am already using

    =Lookup(Fields!StaffId.Value, Fields!StaffId.Value, Fields!Sum_NonChargeableMinutes.Value, "TAXIllnessYTD") / 60

    I know the following works in its own table along with a row group for StaffId.  This then lists all the staff members ID's and shows how many separate spells of absences they have had for that period.

    =CountRows()

    Staff ID / Spells of Absence

    BJ / 20

    CJJ / 2

    HS / 5

    JH / 0

    The table above is an example of the output.

    I would ideally like to add the Bradford Factor as a column in my existing table and not display the results separately and was hoping to use the Lookup function as follows

    =Lookup(Fields!StaffId.Value, Fields!StaffId.Value, CountRows(), "BF")

    Unfortunately it didnt work and came up with the following error "The Value expression for the textrun 'Textbox122.Paragraphs[0].TextRuns[0]' contains an aggregate function in an argument to a Lookup or LookupSet function. Aggregate functions cannot be used for the destination or result Expression parameter of a lookup function."

    Is there a way around this?

    Cheers


    Cheers Chris

    Friday, May 10, 2013 8:36 AM

Answers