DO I USE SUBREPORT OR SHARED VARIABLE? 4

[ppark001]

I am using Crystal 8.5

My report uses one table;

The fields in questions are Employee,Project,Hours worked, Date.

The report's primary Group is Employee.

It will look like this;

NAME JOB# Hours Worked %hours Total Job Hours

John Smith 11111 2 2% 100
John Smith 23333 3 3% 100
John Smith 44444 1 100% 1
---------------------------------------------------
Total Hours Worked 6

Mary Smith 22222 5 10% 50
Mary Smith 11111 6 6% 100
----------------------------------------------------
Total Hours Worked 11


The question I have, is how do I get the total job hours on the right hand column, before the report has calculated.

Currently, I am using a subreport, but it is soooo slow.

Thanks,

Paula Park

 

[IdoMillet]

Assuming you simply need to sum the hours worked for each job, you can simply create the following formula and use it in place of the subreport:

Sum({Hours_Worked}, {Job#})

Cheers,
- Ido ixm7@psu.edu

 

[ppark001]

Maybe I am missing something. But I can't use a summary total because Group #1 is Employee name, and Group #2 is the job, and since the same job number is populating the report for multiple employees and can I put the grand total for a job at the top of the report.

Thanks,

Paula Park

 

[chelseatech]

So you are trying to summarise all Job 11111's data for both employees. Can't be done in the structure you've got...

But there is always a way.

Can you create a Job total in a SQL view? SQL performance makes this the best method.

If you can't do that you will need a subreport to calculate the total on each line. I hope there are not too many transactions. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[ppark001]

The database I use is not SQL. So, I am assuming I cannot use the SQL solution. So I did solve this with a subreport. uggg. And yes, there are alot of records.

Thanks you very much for responding.

Paula Park

 

[IdoMillet]

If you can't use an SQL approach, and if the performance is too much of a pain, I would revisit the question of what you are trying to achieve with this report.

Assuming the user/manager is receptive to a design change
(about 25% of the cases... ) ) it would be better to split this report into two unlinked subreports (or two separate reports):

Report #1: Group by Job within Employee (as above) and show number & % hours worked on each job.
Purpose: see how each employee allocated their time.

Report #2: Group by Employee within Job and show number
and % hours worked by each employee within each job.
Purpose: see who is leading the work effort within each job.

The previous design mixes these objectives in a manner
that sacrifices objective #2.

The proposed design would also run much faster because it requires no subreports.

Note: don't ask them if they'd be willing to use a new design. Just generate the new report and show it to them.
That should increases your chances from 25% to 45%... )

Cheers,
- Ido
ixm7@psu.edu

 

[ppark001]

That is a very good idea. However, my boss wants these reports distributed by me, so I will just design with a subreport.

Thanks so much for your design solution. You obviously have a lot of experience with the 'people part' of report design.

Paula Park

 

[jkb154]

Perhaps in your sub report you can create an array variable, and populate it with each of the sum values. Place this sub report in your report header and suppress. This sub report will then run first.

Next create a formula in your main report to lookup and display the value in the array variable, based on the job number.

Calculating the % hours would then be simple, by dividing the hours worked by your new formula. I'm not sure this will be faster than creating two sub-reports. But should allow for the format you have presented, and should certainly be faster than running a sub-report for every record.

Best of Luck
Josh

 

[ppark001]

Sounds like a great idea, but I havn't a clue how to do it. I need a good Crystal book that contains lots of examples of advanced Crystal tips/tricks. Do you or anyone else have any recommendations?

Paula Park

 

[Ngolem]

the best book for my money is "The Complete Reference Crystal Reports (insert version here) by George Peck published by Osborne

lots of good stuff and written in english...not Crystalese

Jim

 

[kenhamady]

Peck is the best out there, but even Peck doesn't go into array variables. I haven't seen a good document on them so I have had to hash them out for myself. Probably because they aren't used by many CR developers. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Ngolem]

Ken

Peck is the best and yes some aspects like Arrays are not described very well. But overall I like the book for its readability and logical format.

I don't think there is a definative book on CR. But then if there was we wouldn't be having fun in these forums
Would we? I have picked up and past on many of the tricks that are not documented anywhere. That is one of the reasons I like this forum...it gets you out of the box when seeing how others have appraoched a particular CR issue.

 

[IdoMillet]

Paula,

I believe arrays in Crystal have a limit of 1000 elements,
so if the number of jobs in your report must be less that that. If it is, the array approach for your case would work.

If interested, Crystal Decisions has a zip file containing a pdf document and some report samples dealing with array techniques. If you can't find it on their site, e-mail me and I'll bounce the file back to you.

I still believe that the design of this report is flawed,
but it should be fun to apply arrays to this situation.

Cheers,
- Ido ixm7@psu.edu

 

[kenhamady]

CD doesn't allow key word searches for downloads (only in the KB), so we would need to know the name of the file. Can you post the name of the file? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[IdoMillet]

OK, I went back to the site and here's the link:

http://support.crystaldecisions.com...c6=@META_SSFileName&ct=livecommunity-ftpfiles

The file name is CR_Arrays.Zip

Cheers,
- Ido ixm7@psu.edu

 

[kenhamady]

Thanks. I wonder if this was around when v8 was released. If so, it would have saved me some time. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[ppark001]

Thanks Ken and Ido,

I downloaded the array pdf file and am printing it out. The learning curve is never ending and very steep in this business.

Ido, your post about my report design was extremely helpful. I had forgotton the obvious, which is not to use formulas in data selection. I was using a date range for parameter passing and two or three IN functions for data selection. So, even though I am using a subreport for about a 100,000 records, it is not too bad.

Thanks all!