SQL Server Performance Forum – Threads Archive
Need to do Max like Crystal ReportsMy 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.
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
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
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.