fields summation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

fields summation

Author

Topic

Dominic29
Starting Member

9 Posts

Posted - 2004-11-10 : 14:31:37

Hi,

I'm developping a VB application using Data Environment and DataReport. I've created a Stored Proc in SQL Server to gather all the informations I need to print on the report and I mapped the SP with the DataReport (the DataSource property). But I'm looking to do a summation of some fields returned by the SP, to print the subtotal in the report.

here is the query:

SELECT tcqDatetime, tcqFinitionFM, tcqCriticalSplit, tcqCriticalGashed,
tcqCriticalCrumbled, tcqVisualHunchback, tcqVisualFrizzy,
tcqGeomThick, tcqGeomThin, tcqGeomVeiled,
tcqGeomElbow, tcqGeomLong, tcqGeomCourt,
tcqGeomWide, tcqGeomNarrow, tcqGeomCanot,
tcqGeomNeedle, tcqGeomNode, tcqPrintLight,
tcqPrintDark, tcqPrintWrong, tcqDefectsInEnds,
tcqTensionOfRibbons, tcqSpattersOfGlue,
FROM dbo.TB_pcqPalletControlQuality
WHERE (dbo.TB_palPallet.palCode = @Pallet)

I need the Query to return a summation of the tcqCriticalXXXX fields, the tcqVisualXXXX, the tcqGeomXXXX and the tcqPrintXXXX with the fields themself. Is there a way to do it?

thanks a lot for your time and help

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-10 : 15:16:11

Hi --

data reports really are pretty rough around the edges... I won't say they are horrible but ... they ... are ... let's just say they are a little difficult to work with and only good for the most basic of reporting requirements.

But they should allow you to calculate the sum of any column right on the report. Just add a text box into the section of the report in which you need to subtotal (i.e., in a group footer or the report footer) and in the text box set the FunctionType property to "rptFuncSum". Then set the DataMember and all that to the column you wish to sum up.

- Jeff

Dominic29
Starting Member

9 Posts

Posted - 2004-11-10 : 15:24:52

Hi,
Thanks for your reply. You're right, DataReport is pretty tricky...

Why I wanted to put the summation in the SQL query is because I need to have the subtotal of the fields printed in the same row. Let's say my Stored Proc returns 10 rows, each rows must have 4 subtotal and then, a total of these subtotal (where the rptFuncSum will be usefull). The subtotal must be printed in the Detail section of the DataReport, I can't put a Function in this section. Yhea, dataReport is crap, but it's the only thing I have right now! :-)

thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-10 : 15:34:51

you can CROSS JOIN your data with 1 single recordset containing all of the totals you need:


SELECT A.*, B.*
FROM
  (your original query here ) A
CROSS JOIN
  (SELECT 
     SUM(tcqCriticalXXXX) as Total_tcqCriticalXXXX, 
     SUM(tcqVisualXXXX) as Total_tcqVisualXXXX, 
     SUM(tcqGeomXXXX) as Total_tcqGeomXXXX, 
     SUM(tcqPrintXXXX) as  Total_tcqPrintXXXX
   FROM 
         dbo.TB_pcqPalletControlQuality
   WHERE 
         dbo.TB_palPallet.palCode = @Pallet
   ) B

replace the SUM expressions with the actual columns you wish to add up.

- Jeff

Dominic29
Starting Member

9 Posts

Posted - 2004-11-10 : 16:14:05

Does this query will let me get the informations like, example, this:

Split Gashed Crumbled TOT_CRITICAL Thick Thin ... TOT_GEOMETRIC
1 2 1 4 2 1 ... (total of 'Geom' fields).

thanks for your time

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-10 : 16:16:28

i don't know what you are asking, but why don't you try it?

if you want to provide some sample data and the results you are looking for, that would be even better. I'm just taking wild guesses here as to what you need.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources