Has anyone had to do a report by rolling up list items before? I need some advice on the following scenario:
Environment: - One top level MOSS site lets call this the Management site. - Underneath it there exist multiple Vendor sites. - In each Vendor site there is a Contract list with lots of fields. Requirement: - At the top level management site i want to show a report listing all the contracts in all the vendor sites (not so hard) and then i want to show summaries of these contracts by adding all the costs together by status or manager (tricky bit). Sample Data: A simplified view of (all) the contract data might be: Commencement Date Expiry Date Contract Name Vendor Manager Status Value 1/09/2007 1/10/2007 Website WebDesign Pty. John Smith Active $20,000 10/09/2007 5/09/2008 Webserver HP Tony White Pending $10,000 1/01/2007 1/09/2007 Net Bandwith Telstra Bob Dylan Terminated $100,000 1/01/2007 9/09/2009 Power Energy Aus. John Smith Active $200,000 1/09/2007 1/01/2008 Net Bandwith Optus Tony White Active $50,000 2/09/2007 2/02/2008 MOSS Server Powertel Tony White Active $5,000 now this can easily be retrieved by using a CAML query and SPSiteDataQuery however what we need is some grouping and summing of the columns so they look more like: Month Manager Contracts Value Sep-07 John Smith 1 $20,000 Sep-07 Tony White 2 $55,000 OR Month Status Contracts Value Sep-07 Active 3 $275,000 Sep-07 Pending 1 $10,000 Sep-07 Closed 0 $0 Sep-07 Terminated 1 $2,000 Now if i was to again use CAML queries and a SPSiteDataQuery object i would get the above Contracts datatable with all the items and would then have to programattically do the summing and grouping, which may be possible using: http://support.microsoft.com/kb/326145 but not very nice & a lot more complex than i would like.. surely there is a better/easier way? Cheers, Mundeep ------------------------------------------------------------------- OzMOSS.com - to unsubscribe from this list, send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by www.readify.net
