locked
Aggregate functions cannot be used in other aggregate functions RRS feed

  • Question

  • I have a report I am converting from another reporting program which has:

    PT_CODE counted in group footer(0) - count(PT_CODE).
    PT_CODE summed in group footer(1) - sum(count(distinct PT_CODE)).
    PT_CODE and other fields are in group header(2), but the group is invisible.
    The details band has nothing in it, visible or invisible.

    The idea is simple and the report has only 4 columns: Count the distinct PT_CODES by type and location. So - there will be a page for each type, a line for each location, and a count of the DISTINCT PT_CODES on that line. Of course the PT_CODES are not visible because they are counted and summed.

    I need to convert it to SSRS, but when I use the expression =sum(count(distinct fields!PT_CODE.Value)), I get an error that says "Aggregate functions cannot be used in other aggregate functions". What would be the best way to convert this?

    This may sound complicated, but I don't think it is. I don't care about "converting" from this other solution. You can ignore that part, but I just need to know how to create a report that does what I said in the paragraph that describes the whole idea. Thanks for any input.
    Thursday, September 3, 2009 5:33 PM

Answers

  • If I understand you correctly, just choose the correct field for the cell of the table at the correct group level.  It should add something like: =sum(distinct fields!PT_CODE.Value).  This is the closest you can get.  If this can't be made to give you what you want, then I strongly suspect that your dataset is not set up properly.
    Thanks,
    Corey Furman @ Facebook
    ______________________________________________________
    Please mark posts as answer or helpful when they are.
    • Marked as answer by Jerry Nee Friday, September 11, 2009 6:32 AM
    Thursday, September 3, 2009 8:12 PM