New to CR

[saphir54321]

Hi Everybody,

I'm new with Crystal Report and I have a few questions.

I have a table who has an attribute which is a foreign key of the table itself.
Code:

Company {id, compName}
Department {id, companyId, departmentId, depName}
- we can then have sub departments
- departmentId will be null if it's the top level

Report {id, companyId, report}
Work {id, reportId, departmentId, work}

I need to display something like this:
Code:

Departments Name Nb Work
Dep 1 5 //Sum of Sub Dep 11 and SubDep 12
Sub Dep 11 3 // Sum of Sub Sub Dep 111 and Sub Sub Dep 112
Sub Sub Dep 111 1
Sub Sub Dep 112 2
Sub Dep 12 2 // Same process than Sub Dep 11
Sub Sub Dep 121 1
Sub Sub Dep 122 1
Sub Sub Dep 123 0 // I would like to display 0 for the departments without works
Dep 2 4 // Same process than Dep1
Sub Dep 21 1
Sub Dep 22 0
Sub Dep 23 3

What I have so far:

Parameter entered by user:
Sort data by Company.Id AND Report.Id

I have a group on the Department.id
To calculate nb work: count ({Work.departmentId}, Department.id)

How can I:

1) Indent depending on the department level
2) Have zero for the departments without works
3) The sum of the sub departments for departments with sub departments

Thanks a lot

saphir

 

[chelseatech]

You can place a table in a report more than once. Each time you do that you are asked for the alias name for the new instance of the table.

But your requirements might work better using the hierarchical grouping option. There is some cool stuff you can do with self referencing tables with that feature.

There are several articles about both approaches in past issues of Crystal Clear. Download the back issues from our web site.

Editor and Publisher of Crystal Clear

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

 

[saphir54321]

Thanks for your reply chelseatech.

I've been looking to the hierarchical grouping option.
It works quite well, except when you indent, the background color and all data next to it are indented as well. :-S

Apart from that my problem is still the same.
I lose the departments where I have sub departments when I sort the data with my reportId parameter from the Work table because those departments are not linked with any data in the Work table. That's why I would like to have the sum of the sub departments for those one.
In my example above, as you can see, I need to calculate the sum for Dep 1 (which is the sum of Sub Dep 11 and Sub Dep 12), Sub Dep 11 (which is the sum of Sub Sub Dep 111 and Sub Sub Dep 112), Sub Dep 12 (same process than Sub Dep 11) and Dep 2 (same process than Dep 1)

Any ideas?

 

[saphir54321]

Hi again,

In my example above, I need to calculate the sum for Dep 1 (which is the sum of Sub Dep 11 and Sub Dep 12), Sub Dep 11 (which is the sum of Sub Sub Dep 111 and Sub Sub Dep 112), Sub Dep 12 (same process than Sub Dep 11) and Dep 2 (same process than Dep 1).
I have used the hierarchical grouping.

Any ideas???

 

[Madawc]

Running totals allow you to sum values using a rule, such as just accumulating for a particular set of values. The use of Crystal's automated totals is outlined at FAQ767-6524.

Note that Crystal has limits on how you can use totals that are summaries of more than one detail line.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[saphir54321]

Thanks for your reply.

I tried running totals. They work quite well but unfortunately I need to display them in the header and not in the footer.

Any ideas???

 

[chelseatech]

Summary fields have the option to "Sum across Hierarchy". Right click on the summary to Edit it and tick that option. Crystal say you can't then use that summary in other calculations, but wer have found two ways to do this - covered in previous issues of our newsletter.

Editor and Publisher of Crystal Clear

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

 

[saphir54321]

Thanks chelseatech. It worked perfectly. :-D