Total in Subreport

[marylally]

I've created a main report by userid. My subreport links by userid and also reservationNo.

I have a tourtotal field for each record that matches. I can't seem to get a sum of all the tourtotals on the subreport. Instead it only sums each single record. Any ideas?



MaryBeth

 

[IanWaterman]

Mary

How is your main report gouped.

Where is subreport located.

What is relationship between a tour and a reservation?

Ian

 

[MzKitty]

Did you put in grouping on your subreport? You should have a grouping for reservationno and another for userid. Userid should be the top level, then reservationno.

Cathy

 

[marylally]

My main report is grouped on User from Event table
My sub report is grouped by user also from event table

the main report is looking at when the user checked the guest in from the event table

the sub is looking at when the same user created a tour from the event table

for each time a user checks a guest in i need to see if they created a tour for them
in the tour table the user enters the same res no and that's how I'm linking the subreport -= by that number and then by user

MaryBeth

 

[marylally]

Yes, I have the sub report gruped by userid and then res no.

MaryBeth

 

[IanWaterman]

Please detail structure of report, what groups do you have, where is the subreport evaluated.

Can a tour have multiple reservations, or is it the other way around, a reservation has multiple tours.

What do you expect to happen if there are no tours booked?

Ian

 

[marylally]

Main Rpt Grouped by Event.UserName
Par on Event.DateCreated

Main Report looks like this: Sub Report

UserName ReNo Rental Owner TotCkIn UserName ResNo To
Abrouil 1234 1 1
Abrouil 5788 1 1 Abrouil 5788 1
2
Sub is grouped by Event.UserName
Par on Event.DateCreated

1st Link is on Event.UserName Main report to Event.UserName Sub report

2nd Link is on Event.ResNo to Tour.User6 (which should equal ResNo)

Only the matching E.ResNo to T.User6 populate in the Sub Report

My goal is to count 1 for each matching record in the subreport and then bring the total counts to the main report so I can create a conversion % of Chk Ins to Tours

MaryBeth

 

[IanWaterman]

If you want to get a total for each tour from the Subreports you will need to use a shared Variable

In Group header main report

@reset
whileprintingrecords;

shared numbervar tours:=0;

In subreport where tours are totaled

@evaluate
whileprintingrecords;

shared numbervar tours:=tours + {whatever summary you have}
;

@display
whileprintingrecords;

shared numbervar tours;

using the same format above you can then incorporate the shared var tours into your % calc. eg

@%ckhin
whileprintingrecords;

shared numbervar tours;

tours/{mainreport summary}*100

Hope this is what you are seeking.

Ian

 

[marylally]

Ian, Thanks,
The subreport now totals all the tours, but not by each user.
Ex:
Abrouil had 2 matches
JJones has 3 matches

Total matches 5

Also the reset formula is only returning 0 on my main report? and I put the display in the sub and main and it returns 0 also?
Any other ideas?


MaryBeth

 

[marylally]

Ian,
I got the display number to calculate on main report. But now I've lost the running total on the sub report.

MaryBeth

 

[marylally]

Ian,
I think I said my goal incorrectly.

What I want to do is subtotal tours by user in the sub report
and bring the sub totals to each user in the main report. Pleae help

MaryBeth

 

[IanWaterman]

Split userid group header into two sections

Place @reset in top section of your userID group header on main report

Place subreport in lower section userID group header

Place @evaluate in the userID group footer of the subreport and change to

shared numbervar tours:=count(toursfield, userIDgroupfield);

Place @display and @chkin in main report userID group footer.

Does this give you the results you expect.

Ian

 

[marylally]

Hi Ian,
When I put the sub report in the group header I lose my data.
I have my sub report vertically next to the main report detail.
Main detail subreort

I was able to get the total tours and % into the main report thanks to your help!

Now I can't sum tours by the users, it's a string and it wants a number. Even if I put a summary count on the username field it only counts 1.
any more ideas? I so appreciate your help!
Thanks, Mary

MaryBeth

 

[IanWaterman]

I see now what you are doing. ie display result of SR in main report, this is not easy because of the order in which Crystal displays and executes data

If you want to extract a value for each user id then sub must execute before you use the Var in main report.

In main report split userId group footer and pplace SR in top one and your other data and formulae in bottow one.

In section expert for upper section check under lay following sections.

The @reset is meant to return zero it is resetting var to zero.

Ian

 

[lbass]

If you are trying to sum the shared variables in the main report to get a grand total in the main report, you need to create a variable like this:

whileprintingrecords;
shared numbervar tours;
numbervar addtours := addtours + tours;

Place this in a section below the one where your sub is located. If the sub is in GF_a, place this formula in GF_b. Then in the report footer, use the following to display total tours:

whileprintingrecords;
numbervar addtours;

You can then add calculations to this formula to get the percentage, e.g.,

whileprintingrecords;
numbervar addtours;
addtours%distinctcount({EventReNo})

-LB

 

[marylally]

Thanks Ian,
That works for my total of tours, however, I still cant' get the subtotal of tours per user. If I put the sub in the group footer, I loose my data? This report is killing me! I so appreciate all your help?

Thanks, Mary

MaryBeth

 

[lbass]

Who are you talking to? You should not be linking on the ResNo if you only want the total number of tours per user name.

-LB

 

[marylally]

I apologize LB! I was confering with Ian previously and didn't notice your name. I'm sorry.
Okay, My first link is on ResNo and 2nd link is on UserName.
okay, so I deleted the UserName link, what do you suggest on how to get tour per user?
Thanks, mary

MaryBeth

 

[marylally]

LB,
Sorry, when I delete the link using resno, I get all resno for each user and it throws my data off?

MaryBeth

 

[marylally]

Also, I need the second link (Username) as I only want tours where the same user Checks the guest in and also creates the tour.

MaryBeth