Help with Gigantic SQL Grand Total retrieval

• dba321

  Hall of Fame

  Points: 3917

  More actions

  February 27, 2006 at 9:19 am

  #113419

   

  I have to create a calculation in SQL for my report.  The problem for me is that this is huge. This calculation contains other calculations.

  I'll first start by having you look at this document which explains all the calculations needed in my report.  So far, I have all of them done in SSRS 2005, but ProjFee is so complex, SSRS 2005 cannot handle it.  I have to try to create the grand total for column ProjFee in SQL now as a last resort.  I got the fields in the group to work for ProjFee but the problem is GT because I have to take into consideration Commission % for each of the Group fields at the end for the GT.

  Here are the calculations:  http://www.photopizzaz.biz/calcs.xls

  My schemas (simplified for this thread):

  http://www.photopizzaz.biz/schemas.zip

   

  Additional info on NewPDC, OldPDC, NewCC, and OldCC.  Basically you just check each record for the following:

  So what I need to get is the Grand Total for ProjFee.  Right now, if you take a look at my Report in SQL Server 2005 below, you'll see what I'm trying to sum, which is the ProjFee column.  I have to do this GT in SQL now due to limitations and complexity of this stupid report which originally came from a complicated financial excel spreadsheet.

  I am not sure how to go about this as I have never tried to code something so ridiculous in SQL before for a calculation.

  Here is the main stored proc for my entire report which grabs all the information and throws it into a table which my report feeds on:

  http://www.photopizzaz.biz/main_query.zip

  This is the table that the main query dumps into for my SSRS 2005 Report to build on via dataset.  I have other datasets but this is the main one.

  http://www.photopizzaz.biz/dc.zip

  This is the .rdl that is the report that runs off my main_query:

  http://photopizzaz.biz/myreport.zip

  I'm sure you'll have questions...

   

• paramind

  SSCrazy

  Points: 2160

  More actions

  February 27, 2006 at 9:47 am

  #623485

  Of course, there are questions: will you pay me, if I do your work?

  Otherwise your problem should at least be broken down to an answerable question. 

  regards,

  _/_/_/ paramind _/_/_/

   

   

  
  _/_/_/ paramind _/_/_/

• dba321

  Hall of Fame

  Points: 3917

  More actions

  February 27, 2006 at 9:50 am

  #623487

  It's very complex, that's the best I can do to explain, I've taken the time to provide you all the details so what are you needing at this point that you don't see or understand...I was pretty clear.