Stored Procedure for crystal reports
-
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
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
I use 3 tables and then some views that come from these tables.
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 :
-
These views that I posted are just little samples of the normal views which are a lot larger.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply