Stored Procedure for crystal reports

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 2005 Forums

Transact-SQL (2005)

Stored Procedure for crystal reports

Author

Topic

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-12 : 07:52:27

I have a report written with crystal reports and 4 columns.
The columns are : Arrangements, Extras, Credit, Remaining
This report is about hotels.
At arrangements column I have placed the field stkef3room.rbedaposo.
At extras column I have placed the field stkef5room.rbedaposo1.
These fields come from views that I have created in the database.
In the report I have made 2 groupings.
The outer grouping is on account number and the inner one is per room in the account number.
For example for account number 8932 there is one line for room 502 another one for room 507 and so on.
The 2 mentioned fields are placed in the room grouping area so they are calculated for every room.
In the account grouping area and under Credit column I calculate using a view ,again directly from the database ,the total credit for every account number.
This calculation produces a negative number.
For example the total credit for account number 8909 is -264.
That field is named stkef7.sbedaposo2.

I want to find a way to produce these results (I'll describe what I want with numbers rather than words - I believe this is a better way)

Account Room Arrangements Extras Credit Remaining
8932 502 69 0 69 0
507 50 0 50 0
508 50 0 50 0
509 50 0 50 0
510 50 18,5 68,5 0
511 50 0 50 0
514 50 0 32,5 17,50
515 50 0 0 50
522 50 0 0 50
. . . . .
. . . . .
Totals 1412 18,5 370 1.060,5

If you remember from what I wrote above 370 comes from -370 and it is the stkef7.sbedaposo2 field.

In this account number (8932) Arrangements + Extras in the first row is greater than the absolute number of total credit(370)
When this is not the case as in account number 8910 I want this thing to be displayed :

Account Room Arrangements Extras Credit Remaining
8910 406 264 0 230 34
407 264 0 0 264
408 264 0 0 264
409 264 9 0 273
431 264 15,75 0 279,75
. . . . .
. . . . .
Totals 5568 117,20 230 5.455,20

Thank you in advance.

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-12 : 08:20:14

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-13 : 03:15:18

Any ideas guys how to achieve this in sql?

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-13 : 03:34:58

I need a way to appear the arrangements + extras result at credit column but only when credit total is bigger than that number.
At the remaining column I want the difference of arrangements+extras minus credit column.
When credit total is lower than Arrangements + Extras I want that credit total to appear at the column credit.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-13 : 03:45:32

what are your tables? could you post their structure with some sample data?

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-13 : 04:12:00

I use 3 tables and then some views that come from these tables.
Do u want me to extract the tables and the views to some files?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-13 : 04:19:38

nope. just a few records from each (about 5) with their column names will do.

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-13 : 08:14:29

ok
this one is stkef3room view

bedalog tmpRm rbedaposo
2697 224 336
3121 224 96
3299 224 336
4575 224 336

From this view I take the rbedaposo field in the report in the room grouping area of the report under Arrangements.

this one is stkef3 view

bedalog sbedaposo
426 504
431 125
432 130
433 60

From this view I take sbedaposo field in the report in the account grouping area of the report under Arrangements.

The same thing is happening with Extras column.

From stkef5room view below

bedalog tmpRm rbedaposo1
3324 512 142,10
4805 512 172,50
4296 512 14
8036 512 58,50

I take the rbedaposo1 field and I put it under Extras column in the report in the room grouping area.

Then I take from stkef5 view below

bedalog sbedaposo1
795 26
796 30,90
797 16,80
803 5,40

the sbedaposo1 field and I put it under Extras column in the report in the account grouping area.

The last field that should be used is stkef7.sbedaposo2 from stkef7 view below :

bedalog sbedaposo2
562 -661,50
563 -441
564 -441
569 -47,10

This field is used under the column credit in the account grouping area.

Take a video to see what I mean :

http://rapidshare.com/files/91466167/crystal1.avi

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-14 : 02:42:28

Difficult? :)

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-19 : 03:03:59

Any ideas guys?Should I use CASE ?

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-19 : 06:41:51

I also need a running total in the Credit column to change the way the results are appearing after the Credit for that account is gone.
Any idea if this can be done in transact sql?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-19 : 07:27:10

quote:
Originally posted by skiabox

I also need a running total in the Credit column to change the way the results are appearing after the Credit for that account is gone.
Any idea if this can be done in transact sql?

this can be done in transact Sql. you just need a variable to hold the intermediate values and add it to next one.
something like this,

CREATE Table #Account
(AccountNo int,
RoomNo int,
Credits decimal(10,2),
Remaining decimal(10,2)
)
INSERT INTO #Account (AccountNo,RoomNo,Credits) values (8932,507,230)
INSERT INTO #Account (AccountNo,RoomNo,Credits) values (8932,507,0)
INSERT INTO #Account (AccountNo,RoomNo,Credits) values (8932,507,100)
INSERT INTO #Account (AccountNo,RoomNo,Credits) values (8932,507,50)
INSERT INTO #Account (AccountNo,RoomNo,Credits) values (8932,507,20)
INSERT INTO #Account (AccountNo,RoomNo,Credits) values (8932,507,40)


select * from #Account

DECLARE @Var decimal(10,2)--temp variable to hold intermediate values

UPDATE #Account
SET @Var=Remaining=Credits+ISNULL(@Var,0)
WHERE AccountNo=8932

select * from #Account--check result

output
----------
AccountNo   RoomNo      Credits   Remaining
----------- ----------- ---------- -----------
8932        507         230.00   230.00
8932        507         0.00     230.00
8932        507         100.00   330.00
8932        507         50.00    380.00
8932        507         20.00    400.00
8932        507         40.00    440.00

skiabox
Posting Yak Master

169 Posts

Posted - 2008-02-19 : 09:27:41

Do you believe that I can solve my data presentation problem using transact sql or it is better to go to application layer?(crystal reports)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-19 : 09:30:31

your data presentation can be more easily handled at front end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources