Accounting report design

[WannaFly]

Hello,
I'm looking for advice, hope this is the right place. I have this report which is a cash drawer auditing form. I'm having a hard time with it - i've been reading and my book doesnt help any - so i've come here (hope thats ok).
Oh, i'm using CR9(that came with Visual Studio.net 2003) with MSSQL (soon to buy CR10 though)
Scenerio:
DB Table - Drawers
Contains: date,drawernum,stationid,cash,credit,check
one stationid can have three different drawernums

the report should look like this:

Date: 6/2/2004
Station: Window1
Drawer 1 2 3
Cash $123 " "
Credit $100 " "
Check ... " "

I've played with grouping and cross tabs and have yet to get it. And this is only half of the report. Thanks for any help - it is much appreciated.

 

[lbass]

The easiest approach would be to change the layout to:

Date: 6/2/2004
Station: Window1
Cash Credit Check
Drawer 1 $123 " "
Drawer 2 100 " "
Drawer 3 ... " "

...since your database seems to be laid out this way. You would just need to group on date, then window, and then place your drawer number, cash, credit, and check fields in the detail section. You could drag the detail labels into the window group header, if you wish.

If you must use the layout you showed in your post, you could insert a crosstab, but I think you would achieve a better layout (without a lot of extra space), if you did a manual crosstab, as follows:

Group on date and then on window, and then create the following formulas:

if {drawers.drawernumber} = 1 then {drawers.cash}

if {drawers.drawernumber} = 1 then {drawers.credit}

if {drawers.drawernumber} = 1 then {drawers.check}

Create these same three formulas for drawers 2 and 3 and then insert a summary (sum) on each formula and suppress the details section. In the windows group footer, arrange the summaries according to your planned design, adding text boxes for the row and column labels.

-LB