SQL Server Performance

Grouping data derived from custom code

Discussion in 'SQL Server 2005 Reporting Services' started by Frosty, Aug 16, 2007.

  1. Frosty New Member

    Sorry if this one is a bit long winded. But this is a rather tricky one to explain, but has anyone experienced this in SSRS Reporting?
    There could be up to 20 rows of data per client. During the processing of each row within a client, the data is manipulated and stored in an array held in ‘Custom Code’. Upon change of client, the data in the array is referenced to display/printed as a Group Total Line. The problem is, after the first change of client, the Group Total line is blank, but upon change of following clients(s), the details from the array are for the previous client. The following example shows the details rows if visibility set to true.
    BobABC
    BobG I
    Bob NO
    Group Total
    JimHKE
    JimRSF
    Group TotalBobAGB NCIO
    Tom BF
    TomVH
    TomEGS
    Group TotalJimHRKSEF
    There is no total line for Tom
    If the group total contains an aggregate, this is displayed correctly. So maybe it’s not possible to display data from an array via a Group Total??? But I don’t see how else to do it.
    To force a total out for Tom, I added a union statement into to the query, similar to creating high values (Zz) row. This produces a semi acceptable result.
    BobABC
    BobG I
    Bob NO
    Group Total
    JimHKE
    JimRSF
    Group TotalBobAGB NCIO
    Tom BF
    TomVH
    TomEGS
    Group TotalJimHRKSEF
    Zz
    Group TotalTom VEBHGF S
    By suppressing the detail line, this is what I now see – not too bad!!
    Group Total
    Group TotalBobAGB NCIO
    Group TotalJimHRKSEF
    Group TotalTom VEBHGF S
    However, upon rerunning the report, the ‘high values’ data displays first aaaarghhh!!
    .
    Group TotalZz
    Group TotalBobAGB NCIO
    Group TotalJimHRKSEF
    Group TotalTom VEBHGF S
    Any help would be appreciated - Thankyou
  2. MichaelB Member

    To simplify, just provide the raw data (which you did - I think) and what you want it to look like. Most data can be prep'd on the server before it goes to RS. I dont think we need to start working it in arrays and such.
  3. martins New Member

    I agree with Mike. However, it seems to me like there is a problem in your code that populates the array (from what you have explained).
    It looks like the array is only populated from the second position, and then when it is displayed (starting at the first item) there are no values. This will also explain why there is no data for the last group, as the array probably ran out of space (because of the first position being empty). There is definitely some counter issue.
    Arrays usually start at position 0, which is the first item in the array. You will probably find that the code only starts populating the array from position 1, or there is some other counter issue that mismatches the first data and array item when attempting to populate the first item in the array.
    Hope this helps. I would still try and do it by prepping the data before it goes to RS though...as Mike suggested.
  4. Frosty New Member

    Hi Martins
    Thank you for your replying to my SSRS problem. The example I provided was a very simplified. Unfortunately, for the real report, the data coming in and how it is represented bears no resemblence. I'm aware of arrays starting at position 0. I have trouble getting my head around what's going on behind the scenes for this one and trying to explain it even more difficult. I'm thinking that grouping should only be used for aggregates (totalling etc) as I've produced many SSRS reports and have been very pleased with the results (although I've been pulling my hair out on a few occasions).
    For this particular problem, to put it in a nutshell, what I want to happen is, on the change of a group display the values held in the array. What is happening is, the first time round, none of the items are displayed (blank line), but on change of second and subsequent groups, all the items in the array are displayed, but for the previous (-1) group ( the results look ok, but are for the wrong group) consequently, the data in the array for the last group is lost in the void.
    I'm going to re-think this one through again and do as Mike suggested, try and get the data prepped before it goes through to RS.
    Again, many thanks for taking the time addressing this issue.
    Regards

Share This Page