Stored Procedure for crystal reports

[skiabox]

Can you help me with this problem?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97205

Thank you very much.

 

[philhege]

Can you post the schema of the base tables, and the query syntax used to produce the views?

Phil H.
-----------
A virtual machine ate my accounting data. I transferred it to a physical box, then I beat it to smithereens with a sledgehammer. I feel better.

 

[skiabox]

ErmisUser.stkef3room

SELECT bedalog, tmpRm, SUM(bedaposo) AS rbedaposo
FROM ErmisUser.QBEPROK
WHERE (bedaflarr = '1') AND (LEFT(bedakind, 1) <> 'P')
GROUP BY bedalog, tmpRm

ErmisUser.stkef5room

SELECT bedalog, tmpRm, SUM(bedaposo) AS rbedaposo1
FROM ErmisUser.QBEPROK
WHERE (bedaflarr <> '1') AND (LEFT(bedakind, 1) <> 'P')
GROUP BY bedalog, tmpRm

ErmisUser.stkef3

SELECT bedalog, SUM(bedaposo) AS sbedaposo
FROM ErmisUser.QBEPROK
WHERE (bedaflarr = '1') AND (LEFT(bedakind, 1) <> 'P')
GROUP BY bedalog

ErmisUser.stkef5

SELECT bedalog, SUM(bedaposo) AS sbedaposo1
FROM ErmisUser.QBEPROK
WHERE (bedaflarr <> '1') AND (LEFT(bedakind, 1) <> 'P')
GROUP BY bedalog

ErmisUser.stkef7

SELECT bedalog, SUM(bedaposo) AS sbedaposo2
FROM ErmisUser.QBEPROK
WHERE (LEFT(bedakind, 1) = 'P')
GROUP BY bedalog

 

[philhege]

I would recommend using a CASE statement to classify each record, so you can work with one recordset in your Crystal design. This would make it much easier to group and sum the results.

Phil H.
-----------
A virtual machine ate my accounting data. I transferred it to a physical box, then I beat it to smithereens with a sledgehammer. I feel better.

 

[skiabox]

Can you give me an example?Thnx for answering!

 

[philhege]

OK:

--ErmisUser.stkef3room

SELECT     bedalog, tmpRm, SUM(bedaposo) AS rbedaposo,
           'stkef3room' as record_type
FROM         ErmisUser.QBEPROK
WHERE     (bedaflarr = '1') AND (LEFT(bedakind, 1) <> 'P')
GROUP BY bedalog, tmpRm

UNION
--ErmisUser.stkef5room

SELECT     bedalog, tmpRm, SUM(bedaposo) AS rbedaposo,
           'stkef5room' as record_type
FROM         ErmisUser.QBEPROK
WHERE     (bedaflarr <> '1') AND (LEFT(bedakind, 1) <> 'P')
GROUP BY bedalog, tmpRm

ORDER BY record_type --or whatever you want to order the results by

Then simply use the record_type field for grouping in the Crystal designer.

Phil H.
-----------
A virtual machine ate my accounting data. I transferred it to a physical box, then I beat it to smithereens with a sledgehammer. I feel better.