Group footer before details section 1

[crystaldev1]

Hello. I'm using CR 9 and Sql Server 2005. I would like to show Groupfooter and then show the details. The reason is I am using running totals to count the records in the details and would like to show this summary at the top of the report followed by the details. By using the running totals in the group header, it does not count the records. I would like to know if there's a workaround other than using a subreport. Thanks.

 

[franceshd]

Employee 1: All info since rec-10 CostCtr 33 does not have any corresponding rec-1 CC 33

Employee 2: Suppress since all rec-10's have a corresponding rec-1 CC

Employee 3: All info since none of the rec-10 CostCtrs have a corresponding rec-1 CC

THANKS SOOO MUCHHHH, LB !!!!!!!!!!!!!!!!

 

[lbass]

Create a formula {@null} by opening and closing a new formula without entering anything. Then create a formula {@rec1acct} like this:

if {table.rec} = 1 then
{table.GLcc} else
{@null} //this assumes that GLcc is a string

Then use a group selection formula like this:

distinctcount({table.rec},{table.employee}) > 1 and
distinctcount({table.GLcc},{table.employee}) > distinctcount({@rec1acct},{table.employee})

This still assumes you are not using a crosstab.

-LB

 

[franceshd]

So this code is counting the number of Rec-1 pay records and then the number of GLCctr records. But how does comparing their counts let you know if any of the Rec-10 GLCctr records are different from the Rec-1 GLCctr recs ?

THANKS

 

[lbass]

The first line just ensures only those employees with both codes appear.

If Rec=10 has the same GLaccts as Rec=1, then the distinctcount of all GLaccts will be the same as the distinctcount for only Rec=1. If it has different ones, the distinctcount of ALL Glaccts will be greater than the distinctcount for Rec=1.

-LB

 

[franceshd]

ok. Ive done it and it looks good.

What's next please ?

THANKS

 

[lbass]

What do you mean?

-LB

 

[franceshd]

Sorry I had to catch my bus and then got groceries.

SORRY I havent been clear enough.

The report is showing everyone with at least 1 rec-1 and at least 1 rec-10. But the report should only show those whose Rec-10 GLCCtr does not have a match in a Rec-1 GLCctr

OHHHH. Maybe it needs to sort by GLCCtr then Rec-type. THEN
add to Group Selection:

if GLCCtr <> Previous (GLCCtr)
and RecType <> Previous (RecType)

am going to try it

 

[franceshd]

NOPE. cannot use PREVIOUS() function in group select

Guess I'll just hold each rec-1 GLCCtr as a variable and compare each rec-10 to them.

THANKS for all the effort you expended trying to help dumb me. Believe it or not, I have to help my boss with her CR.

THANKS AGAIN!
Have a great evening!

 

[lbass]

Did you try my suggestion? It should work. If it didn't, please explain the results you got that don't meet your expectations.

-LB

 

[franceshd]

OH, after a good night's sleep, now I see what you mean !!!

But just the count itself is not quite the answer.

See the GL CostCenter is NOT a key. It a a portion of the whole GL number- like 0144-7-2210-140-800-0168-000
****
THIS is the CCtr

I did Job1GLCctr := Mid ( GLNo, 21 , 4 )
and Job10GLCctr := Mid ( GLNo, 21 , 4 )
to get them for EACH Job record

THANKS FOR ALL YOUR HELP AND PATIENCE, lb.

If anything else comes to you PLEASE let me know.
THANKS AGAIN!!

 

[franceshd]

Hallelujah !!

I FINALLY GOT IT !

What finally worked:
Stringing the Job1 GLCctrs together
If InStr (Job1GLCctrs, Job10GLCctr) > 0


THANKS AGAIN, LB.
I learned a lot more from your help.

Things are getting easier but it is like pulling teeth.

Since my boss and I had 30 years COBOL experience, the powers that be didnt see fit to have database or SQL training. CRYSTAL FOR DUMMIES isnt much help. And the 800+ pages of the OFFICIAL CRYSTAL REPORTS XI guide (by Business Objects Press) assumes proficiency.
Well, THANKS AGAIN !!!!
Have a GREAT day! I will since I got this done !

 

[lbass]

You could simply have substituted your mid() formula for {table.GLcc} in my formulas.

-LB

 

[franceshd]

OH. Okay. Will try it when I get a few minutes. Gave them that report and have go on to putting out another fire- a report for the IRS on non-teacher staff with a specific type of retirement program.
THANKS AGAIN, LB !!!

Happy October!