SQL Server Performance

Need to do Max like Crystal Reports

Discussion in 'SQL Server Reporting Services' started by bubberz, Dec 20, 2005.

  1. bubberz New Member

    My table structure is a one(tblA) to many(tblB). TblB had my "workers" listed and two columns I use are "Title" and "Person". What I was trying to do in SRS was use IIF( ) and say if "Title" was "....", then give me the "Person" else "". I needed to have this show in the group footer of the field / PK in tblA. There are three expression I need to use in this footer.

    In Crystal reports, I would just use the max function for the group footer since in the detail band....if the function was false, then "blank" would be returned, else a name / "Person" value was returned. I'm just not sure how to do this in SRS.

    My current resolution is a subreport which looks for three certain "Title" values, and the parameter is the grouping of the table object. This is fine, but it's a little bit of a resource ding since I have the subreport.
  2. mkmathi New Member

    Hi,
    Use Runnig value function to get sum:
    place a textbox in table groupfooter use this expression syntax : RunningValue(Fields!Fieldname.Value, Sum, Nothing)
    there u can check with IIF function
    like iif(RunningValue(Fields!Fieldname.Value, Sum, Nothing) = 0," ",name / "Person")
    I hope this will crack it.
    Mathi





    Thanks & Regards
    Mathivanan K
    Great work may have to pass through these stages - ridicule, opposition, and then acceptance. Each man who thinks ahead of his time will probably be greatly misunderstood.- vivekananda

  3. bubberz New Member

    Thanks mkmathi!

    I figured it out by the following expression on the Visibility tab of my 2nd level group "ThreeItems" (table1_Group1 is my top level grouping, and "ThreeItems" is my second level grouping). The field txtPerson is in the "ThreeItems" band.

    Expression in Visibilty section:

    =IIF(
    rownumber("table1_Group1") > 1 and ReportItems!txtPerson.Value = "No Mgr / RTBF / Prgrm Mgr",True,False)

    **********

    What I wanted to do is search for Titles of only three values. If I got those values, then I would print them, else do "ZZZ" for the grouping...so anything other than those three would be last in the second level grouping.

    My second level grouping expression was the following:

    =IIF
    (
    Fields!Title.Value = "WP Manager" or Fields!Title.Value = "RTBF " or Fields!Title.Value = "Program Manager", Fields!Person.Value,"ZZZ"
    )

    **********

    The value I had for ReportItems!txtPerson was the following expression:

    =IIF
    (
    Fields!Title.Value = "WP Manager" or Fields!Title.Value = "RTBF " or Fields!Title.Value = "Program Manager", Fields!Person.Value,"No WP Mgr / RTBF / Prgrm Mgr"
    )

    **********

    Now that I know "ZZZ" would be last in the grouping, and if the count of this 2nd level grouping was greater than 1 (meaning I did in fact have Title values w/in the three desired first) I would hide this group band.

Share This Page