Main Report-Subreport displaying zero values 2

[shaleen7]

I have a main report and a subreport. The main report contains the list of employees along with the sites they belong to and the subreport contains the # of applications submitted by each employee for the month. The main report is linked to the subreport via the employee index. Note not every employee submits an application for every month.

Now my main report contains the list of employees grouped by site with the number of apps submitted for the month.

Yun 5
Kely
Peters 7
Reilly 1


However I want to display a zero next to any employee that has not submitted an application.

Yun 5
Kely 0
Peters 7
Reilly 1

Any suggestions? Thanks.

 

[Madawc]

Assuming Crystal 8.5, and assuming that the subreport looks for records and may find nothing, or nothing suitable:

Also assuming that the figure you show is the portion of the subreport that displays.

Create an extra section in the subreport, which shown 0. Give it a suppression formula (right-click, Format Section)

Suppress when Count=0, or when detail records are found within the subreport, e.g. not isnull ({Your.Acc})

This will only show when nothing else has been found, and should do the trick.

You might also be able to do it by a formula within the subreport, which checks for nulls and otherwise shows a count. It depends on the data you use.

Madawc Williams
East Anglia, Great Britain

 

[Naith]

Or go to File/Report Options and activate the "Convert Null Field Value to Default" or use a display formula like:

If IsNull({Field})
Then 0
Else {Field}

Naith

 

[shaleen7]

I'm using Crystal 9.
My main report contains the site and employee names:
New York
Yun
Kely

California
Peters
Reilly

My subreport contains the # of application submitted by each employee. Note the employees that haven't submitted any apps don't display. For example Kelly is in the employee list on the main report but he is not in the subreport because he hasn't submitted any apps.
Yun 5
Peters 7
Reilly 1

I then link the employee name index between my 2 reports.

Results after linking on my main report:
Yun 5
Kely
Peters 7
Reilly 1


Desired Results for main report:
Yun 5
Kely 0
Peters 7
Reilly 1


Note I count the number of apps by using the application number which is a series of number and letters.

I tried both suggestions and it didn't work.

 

[Naith]

What is the field behind the 5,7 and 1? Is it a main report formula, or is the name the main report and the number I'm seeing actually me looking at the subreport?

Essentially, whatever it is behind the number should have the formula I gave you applied to it. Describe how you attempted to use it when you say it didn't work.

Naith

 

[lbass]

When I mocked up a report like yours with nulls for the subreport value, the subreport summary (a distinctcount of a field) still displayed "0". Have you checked to make sure you do not have the subreport field formatted to "Suppress if Zero"?

-LB

 

[shaleen7]

lbass you were right. I had the subreport formatted to "suppress zero". Naith's formula worked perfectly.

Many thanks!)

 

[shaleen7]

I have another question. In that same report I'm trying to count the total number of application submitted by each site and then the total overall.
Desired Results:
New York
Yun 5
Kely 0
Total 5

California
Peters 7
Reilly 1
total 8


Total overall 13



In my main report I've grouped the employees by their site and my subreport brings over the number of applications. How do I get the total of each site and then the total overall and bring it over to the main report?


Do I need to create separate report for totals too?

 

[lbass]

I'm not sure why you needed a subreport to count applications initially, but because you have chosen to use a subreport, you now need to use variables to make further calculations in the main report.

In the subreport (which I assume is in the Group #2 (Employee) header), you will need to create a formula that makes your application count a shared variable, as in:

whileprintingrecords;
shared numbervar applic := distinctcount({table.applic}, {table.employee})

Place this on the subreport instead of the summary currently displayed. Then in the main report, create the following formulas:

//{@resetsubtot} to be placed in the Group #1 (site) header:
whileprintingrecords;
numbervar subtot := 0;

//{@totals} to be placed in the Group #2 (employee) footer:
whileprintingrecords;
shared numbervar applic;
numbervar subtot := subtot + applic;
numbervar grtot := grtot + applic;

//{@displsubtot} to be placed in the Group #1 (site) footer:
whileprintingrecords;
numbervar subtot;

//{@displgrtot}:
whileprintingrecords;
numbervar grtot;

-LB

 

[shaleen7]

I need to maintain a static list of employee names at all times whether or not they submit an application. That's why I need to have the zero value to come across when the employee hasn't submitted any application. I thought the only way to accomplish this was to do a mainreport/subreport.

The main report contains all the names and the subreport contains the # of applications for the employees.
Report Results
Hunt 0
Kelly 1
Love 5
Mix 3

Is there another way? Thanks!

 

[lbass]

The employee field and the application field are from different tables, correct? You would just use a left join from the employee table to the application table, and then for the application count create a formula like:

if isnull({application.applicationID}) then 0 else 1

You could then insert a summary (SUM, not count) on this formula to get subtotals and the grand total.

-LB

 

[shaleen7]

I tried your suggestion but it didn't work. It changes the number of applications to 1 for all employees that have submitted an app. So if Hunt has 5 apps the formula you suggested displays it as 1 not 5. And I don't get the entire list of employees just the ones that have submitted an app.
I have 3 tables.

1. Employee.table
2. App.table
3. Location.table

I have a left join between the employee table and the app.table. I have a center join between the location table and the the employee table.
My current results are correct except I don't have the list of all the employee names. It just displays the the employees that have submitted an application.

Current results:
New York
Hunt 5
Mix 8
Waters 2


California
Tims 2
Roberts 1
Rich 4

Florida
Smith 2
Lyons 1

Desired Results:
New York
Kates 0
Hunt 5
Mix 8
Waters 2

California
Tims 2
Roberts 1
Rich 4
Nelson 0

Florida
Smith 2
Lyons 1
Richards 0

Any suggestions? Thanks

 

[lbass]

Do you have a database field that returns a number for the number of applications, e.g., 5, or do you only get results like "5" by counting application IDs?

-LB

 

[shaleen7]

I get results like "5" by counting application IDs.

 

[lbass]

Then if my formula was returning only "1"s, it was because you put it in the wrong section--this is a detail level formula to be placed in the detail section (even if this section is suppressed). To get the group level results and grand total results, you have to right click on the formula and choose "insert summary" and choose Sum. Check insert summaries for all groups and for the grand total.

You should have a left join FROM Employee TO Application. When you click on the arrow joining the tables it should read "left join." If not, with the arrow selected, click on "Link Options" and change the join from equal to left join.

If all employees have a corresponding record in the location table, then an equal join between employee and location is okay.

To ensure that all employees are returned, you must not have any selection criteria on the application table. If you do, please provide your record selection formula.

-LB

 

[shaleen7]

I'm trying both your suggestions. The one with the sharing variable formula and your latest suggestion.
Sharing variable formula.
It seems to work but my values are too high.
Both my reports are grouped by site then by employee.
I placed the formulas in the sections as suggested above but my values are too high.





New York

Total
Hunt 5 0
Kates 5
Mix 8 10
Waters 2 18
subtotal 18

The subtotal is 18. It should be 15

It looks like it's doing a running total.
Desired Results:
New York
Hunt 5
Kates 0
Mix 8
Waters 2
Total 15

California
Tims 2
Roberts 1
Rich 4
Nelson 0
Total 7

Group total 22




Also how do I get the zeros to display in the new formula.

Where did I go wrong?

 

[lbass]

Using the shared variable approach, in the main report add a reset formula {@resetsharedvar} in the Group #2 footer:

whileprintingrecords;
shared numbervar applic := 0;

Can you verify that your subreport is in the group #2 header and that the accumulation formula {@totals} is in the group #2 footer?

-LB

 

[shaleen7]

Eureka!! *dances around* It's counting properly now.


Yes my subreport is in group header #2 and the {@totals) formula is in the group # 2 (employee)footer.
Current Results:
New York

@Total
Hunt 5 5
Kates 5
Mix 8 13
Waters 2 15
@subtotal 15

How do I place a zero where there is no values. For example the employee Kates did not submitted an app therefore there should be a zero.
Desired Results:
@Total
Hunt 5 5
Kates 0 5
Mix 8 13
Waters 2 15
@subtotal 15

 

[shaleen7]

Latter suggestion. Yes I do have a selection criteria.

not ({employee.name} in ["ADAM", "AKRAM", "ALEXANDER"]) and
not ({location.site} in ["france", "germany"]) and app.date in 1/1/2004 to 12/31/2004

 

[lbass]

I thought we already solved the zero problem--right click on the shared variable formula and make sure you don't have the number format set to "suppress if zero."

If this is still a problem, then go back into the subreport and change the shared variable formula to:

whileprintingrecords;
shared numbervar applic := sum({@applic}, {table.employee});

//where {@applic} equals:

if isnull({table.applicationID}) then 0 else 1

The reason my second suggestion wasn't working properly is because you have a date select on the application table. To use that approach you would remove the date select, and then change the count formula to:

if isnull({application.applicationID}) or
year({application.date}) <> 2004 then 0 else 1

-LB