Multicolumn report - can you do right hand grand total column?

[tammiew]

I'm currently working on a dry cleaning software app. The programmer writing the app is using Visual Studio.NET with VB.NET as the front end and SQL as the back end. I'm doing the reports using Crystal Reports .NET I have a report that I've laid out as a multicolumn report. I chose this over cross tab because I have to display different details for each day of a user inputted time span. The report is to show the days from left to right with the details going down the columns. The details are either data pulled directly from a field or a sum of the fields. I've got all that to work but my problem is I'm supposed to do a Total column on the right hand of the report. I've searched books, knowledge bases, looked at the technical briefs of multicolumn vs. cross tab and I guess I'm just brain dead. Here's how the report looks when displayed (for say, a 3 day span - the user can input anywhere from one day up to seven days): Note: I have shortened the list of details going down the page just for simplicity sake.

Mon Tues Wed
Discounted Tickets -37.69 0.00 -15.42
Ticket Upcharges 26.33 4.00 3.50
Ticket Alt Chgs 18.25 3.50 5.50
.
.
.

{Daily.Date} is what I've created the column on. Is is even possible to do a right hand Total column? I want one that would show:

Total
Discounted Tickets -53.11
Ticket Upcharges 33.83
Tick Alt Chgs 27.25

I don't know if I've gone brain dead after doing 150 some odd reports for this app or what. Believe it or not, I've been able to do all of the other reports using cross tabs, subreports or straight reports so this is the first one where I just can't seem to figure out the obvious.

Any suggestions?

Thanks,
Tammie

 

[synapsevampire]

I think that you have options here, with a crosstab being the simplest.

But I'm at a loss for what "I chose this over cross tab because I have to display different details for each day of a user inputted time span" means.

You can base the crosstab on formulas, which can be altered based on parameters, or you can filter the rows in the report based on parameters, so I don't understand.

Please post table layouts(s), and any special formulas/functions or information which will help this fuzzy ole mind.

-k

 

[lbass]

You should be able to do one of the following. If your display is at the group level, so that the numbers you show in your example are actually summary values, then you could create a formula {@rowtotal} which is the addition of the formulas for each column. I am assuming that the column values at the detail level are formulas such as {@Mon}:

if dayofweek({table.date}) = 2 then {table.amt}

To get a total, create a formula {@rowtotal} like this:

{@Mon}+{@Tues}+{@Weds}

Then you can insert a summary on {@rowtotal} to get the total for the group footer.

Alternatively, you could also create a formula at the group footer level like the following {@grouptotal}, assuming that "Discounted Tickets", "Ticket Upcharges", and "Tick Alt Chgs" are instances of a field like {table.chgtype} that you have grouped on:

sum({@Mon},{table.chgtype})+sum({@Tues},{table.chgtype})+sum({@Weds},{table.chgtype})

I'm making a lot of assumptions here, but maybe you can adapt one of these ideas to make your report work.

-LB

 

[tammiew]

Okay, I'm not doing a good job explaining things. The details section of the report is just fields being pulled from a database, not summaries. The group is a daily.date field. So, in the database, the fields are daily.date, daily.discounted, daily.upcharges, daily.altchgs, etc. I need a report that can print by day for a week's span in a column format; i.e,
Mon Tues Wed Thur ...

with the detail fields all in columns under their appropriate date. Getting a sum at the bottom of each day is not a problem. Getting the week's sum or the 4 day time span or whatever on the right hand side (mon-thurs total for daily.upcharges, etc) is a problem with a multicolumn report. The crosstabs I've done in the past all display summarizations of the fields and in this case my columns are not summary fields, they're just the field being pulled from the database.

Here's a partial of what I want from my daily database and sort of how I'd like it to display:
Mon Tues Wed Total
daily.upcharges 3.50 2.50 1.00 7.00
daily. discounts -1.00 -.50 -4.00 -5.50
daily.altchgs 6.00 5.00 0.00 11.00
daily.tax 1.00 1.00 1.00 3.00
Total for Day (sum) 9.50 8.00 -2.00 15.50

Will a crosstab allow me to display my daily details without showing the summary until the Total column and the Total row? My multi column report does this right now:

Mon Tues Wed
daily.upcharges 3.50 2.50 1.00
daily.discounts -1.00 -.50 -4.00
daily.altchgs 6.00 5.00 0.00
daily.tax 1.00 1.00 1.00
Total for Day (sum) 9.50 8.00 -2.00

I've got it grouped on daily.date, with the daily.* fields in the detail section, it's formatted as a multi column report going across then down. And, I now think it's the wrong solution as I can't get a right hand column total. If cross tab is the way to go, that's good, I just don't know how to format one like the above.

Tammie

 

[tammiew]

I've reread one of the replies and I'm going to go play with putting a summary in the group footer and/or header and using the underlay sections to see if I can come up with something. That gives me something to chew on. I just went back and looked at crosstabs again and that's not an option for me as I need information to display in the details section, not just summarized information.

Tammie

 

[synapsevampire]

I'd think that the Cross-tab would have column date, row whatever field this is (daily.upcharges), and the amount in the summarized fields, there should be only one, so the value should be correct.

-k

 

[tammiew]

You've both given me some options to try and that's the biggest help I needed. This project was supposed to be about 50 reports and the client has kept coming back with the "forgot about this one" so it's become the never ending project (I'm at 164 reports and counting) but it's all supposed to be completed within the same timeframe. I think I'm just getting a little burnt out and finding myself unable to think at times. I've been doing far more complicated reports than this one, lots have had to use stored procedures, mixtures of several "creative" formulas and running totals, etc. Here I get down to this last one and it's like I've gone brain dead!

Thank you both for the suggestions. I'm finishing something else for a meeting later today (yes, we're actually having client meetings on Sunday afternoons at this point!). I'm going to start playing with both of your ideas tomorrow. If you think of anything else, I'm very appreciative of all suggestions and input. Once my deadline is met, I want to help contribute more to this board - I really love how helpful everyone's been.

Tammie

 

[lbass]

I just tried the following and it seems to work for your requirements:

Insert a crosstab and enter {daily.date} as the column. Insert {daily.upchgs},{daily.discounts},{daily.altchgs}, and {daily.tax} as the summarized fields. Then click on "New Formula" and enter a formula {@dailytotal}:

{daily.upchgs}+{daily.discounts}+{daily.altchgs}+{daily.tax}

Add this as the last summarized field. Do not enter any row fields. Your results will now look like this:

6/26 6/27 6/28 Total
Total 3.50 2.50 1.00 7.00
-1.00 -.50 -4.00 -5.50
6.00 5.00 0.00 11.00
1.00 1.00 1.00 3.00
9.50 8.00 -2.00 15.50

"Total" will appear in a single box on the left. Select the box and enter the names of your row fields, with a return after each one. If your {daily.date} returns a date as in my example, and not a weekday name, you should create a formula {@weekday}:

weekdayname(dayofweek({daily.date}))

Use this as your date group instead of {daily.date}. This will print in alphabetical order, so then select {@weekday}, choose "group options," and select "specifed order", to get the weekdaynames in correct order.

Although there is an option within group options to customize the group name, the column heading doesn't seem to change datatype when you write a formula changing the date to a string, so the formula, although accepted, does not display correctly--at least in v.8.0.

-LB