Solved

Formula for Summary Across Hierarchy

Posted on 2004-08-16
9
1,086 Views
Last Modified: 2008-02-01
I am working on a report that uses group by hierarchy feature. I can get summaries for two columns at different levels by ticking 'summary across hierarchy' box. But I can't do calculations over the summarized fields.(summary across hierarchy) using formula.

In fact, if I don't tick 'summary across hierarchy' box, the summary value will not show up in preview as well.  Also I notice, when I was editing
the formula, I click on the summary field available( this was generated by inserting summary and tick 'summary across hierarchy') , it shows
the following:
  Sum ({Tablename.ColumnA},{Talbename.Employee_ID})
If I just use this as content of the formula, it will also not show anything when previewing it.

This doesn't happen to normal grouping. I guess Crystal is doing something special to  'summary across hierarchy'

 I would like to know how to use formula or function to get these values so that I could do further calculations.

Thanks in advance for your help.
0
Comment
Question by:nancen
  • 4
  • 3
9 Comments
 
LVL 10

Expert Comment

by:ebolek
Comment Utility
what kind of calculations do you want  to do. If you explain your report and what you need to accomplish, maybe I can help you .

Regards
Emre
0
 

Author Comment

by:nancen
Comment Utility
Thanks Emre, my report  is like this:

              office  ID         num of shift            task scheduled      %  shift per task
                                    formula1                formula2                formula3

Here I use hierarchygroup ,  group by office_ID, parent_id is parent_office_id,  hopefully will get the look as follows:
              Corp               98
 
0
 

Author Comment

by:nancen
Comment Utility
Sorry Emre,  please forget preious comment,  I just press the space bar, it appeared like this :(

my report  is like this:

              office  ID         num of shift            task scheduled      %  shift per task
                                    formula1                formula2                formula3

Here I use hierarchygroup ,  group by office_ID, parent_id is parent_office_id,  hopefully will get the look as follows:
              Corp               98                          100                       98%
                     Dep1        50                          50                         100%
                          off1    30                           40                         75%
                          off2    20                           10                         200%
                     Dep2        48                           50                        96%
                           off3    48                           50                        96%
Notice, an office could have possible sub_office. Database table only provide office level Data, not Department and Corporate level. These
have to be calculated.

I can get the Department level and corporate level summary for  shift and task. as following:

    office  ID         num of shift            task scheduled      %  shift per task
                          shift sum                    task    sum                    

Here sum means summary across Hierarchy.

But I got problem to calculate this field  "%  shift per task"  using a formula like:
Sum({shift}, {office_id}) % Sum({task},{office_id})

It doesn't complain, but just doesn't show up in the preview.

I know I could calculate these values in sql using sql command  "start with, connect by" kind of thing. I think that is not efficient, and can't coupe with complex situtation. Anyway, I just want to do a simple calculation which two numbers already showed up in the report.

Thanks again for your help and best regards.

Nan
0
 
LVL 10

Expert Comment

by:ebolek
Comment Utility
Do the same thing but where you inserted
Sum({shift}, {office_id}) % Sum({task},{office_id})
insert  a new section, and put this  formula there. I think crystal has a problem witht he evaluation time and cant display your formula. Your summary calculations have to be done for your formula to be evaluated.
You can also use

Evaluate after(summary) function. That will make sure that your formula gets evaluated afer the summary function takes place
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:nancen
Comment Utility
Thanks a lot ebolek.  

I have tried the solution you proposed, unforunately both are not work.

I think the main problem is how to  use a formula to do summary across hierarchy. Simple illustration is how do I say:
Evaluate after(summary across hierarchy)  ?

If I just put Sum({shift}, {office_id}) in the report, it will not show up. Although this is generated by "Insert--> Summary", then choose
field to summary, as well as the group. Then I  tick the 'summary across hierarchy' box.

Note, If I not using formula, it will show the summary across hierarchy correctly if I tick the  'summary across hierarchy' box'. However,
if I don't tick the magic box, it will also not show up.

That's why I assume crystal doing special thing with 'summary across hierarchy'. Because there is no difference in terms of formula. But
the result is different.

I probably have to use function. That is write a special summary function. However, I even don't have a single clue to write a function to
do same thing as crystal  summary function does, for I don't know which construct holds database values, as well as group information,
etc.

Thanks and Best Regards.

Nan
0
 
LVL 10

Expert Comment

by:ebolek
Comment Utility
where are you going to write that function
0
 

Author Comment

by:nancen
Comment Utility
Hi ebolek,

I couldn't remember exactly where, for I didn't have crystal at home. But I am sure
you can add a user defined function in the Crystal Report, and I did tried once. Once the function is written, I could then use the function in the formula.

I already got an idea to solve the problem. Unfortunately I couldn't test it out now,  Basically it will be like this:

NumberVar sum;
IF OnFirstRecord then
    sum :=0
ELSE IF Previous({Office_type}) = {Office_type} then
    sum := sum + {shift}

I have to initialize  sum in the report header. I also have to add a column in the
table to specify office type.

Thanks  again for all the help.

Nan
 
 
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
Comment Utility
PAQed, with points refunded (100)

Computer101
E-E Admin
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This video discusses moving either the default database or any database to a new volume.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now