Hide Employees with no data from subreports 1

[cpjeffm]

I've got a report where I'm pulling all employees in from a field UserFeatures.EmpName. This field shows every employee that's setup in the company. I'm passing this field to numerous subreports. I need to hide all employees unless they have data in any of the subreports. For example, I have 4 subreports...one for sales, one for credits, one for cash paid outs and one for deposits taken in. If any of these subreports return data for an employee, I need the employee to show on the main report. However, if none of the subreports return data for an employee, I need to hide that employee on the main report.

Hope this makes sense...it did in my head .

 

[LaurieHamlin]

In the main report, in the Section Expert for the section where the employee name is, open the formula editor for the suppress box. In there enter IsNull({subreport1Field}) And IsNull({subreport2Field}) And IsNull({subreport3Field}) etc. for each subreport
(Enter the actual sub report names.)
For this to work there must be a field in each sub that will always have data if there is anything for that employee. For Ex. in the sales sub, a field for total sales. The field would only be blank if the entire sales sub is blank.
I hope that helps.

 

[cpjeffm]

My first subreport is named WCI and the field name for my total is @Total. How would I enter that in the suppress formula to reference the {subreport1Field}?

 

[lbass]

What is the layout of your report? You would need to place the subreport in a section above the one containing your employee info. Let's say you have a group on employee ID. You would place the subs in GH1a, and the main report info in GH1b. You can then format GH1a to underlay following sections in the section expert, so that summaries from the sub can display in the same row as the employee ID. In each sub, write a formula like this and place it the detail section:

Whileprintingrecords;
Shared numbervar cnt1; //change the name in each sub e.g., cnt2, cnt3
If not isnull{table.salesno}) then //use a field that will always be present if there is data
Cnt1 := cnt1 + 1;

Then in the main report section expert, go to the GH1a->suppress->x+2 formula area and enter:

Whileprintingrecords;
Shared numbervar cnt1;
Shared numbervar cnt2;
Shared numbervar cnt3;
Cnt1+cnt2+cnt3=0 //assumes that there are no negative values

Format GH1a and GH1a to suppress if blank. Remove the borders from the subs and format the subs (in the main report subreport tab) to "suppress if empty".

-LB

 

[LaurieHamlin]

Thanks for catching that LB. It's been a while since I inserted a subreport.

 

[cpjeffm]

Ok not sure what I'm doing wrong here but it's suppressing the subreport now whether there's data or not. I moved the subreport to GH1A on the main report and the Employee Name to GH1B.

I've got a formula called SuppressIfBlank in the detail section of my subreport. This formula says:

Whileprintingrecords;
Shared numbervar SUB1;
If not isnull({@Emp_Total}) then
SUB1 := SUB1 + 1;

I then have a formula in the Section Export --> GH1A --> Suppress --> X-2 that says:

Whileprintingrecords;
Shared numbervar SUB1;
SUB1=0

Both GH1A and GH1B are set to suppress blank sections. When I run the report, I see all the employees listed but the subreport is suppressed on every employee...even those where the @Emp_Total field isn't 0.

 

[cpjeffm]

Ok I see the problem but trying to figure out how to fix it. I added a formula to the main report that pulls SUB1 from the subreport. I put the formula in GH1B with the employee name and it's returning 1 for the first employee name, 2 for the second, 3 for the third, etc. even if these employees have no data.

 

[lbass]

First, if you don't want the subs to show, suppress all sections WITHIN the sub, but do not suppress the sub itself. If you remove the border and check "suppress if blank" on the subreport tab of the main report, the sub will disappear from view but the shared variables will still pass. If you suppress the sub itself, it will not pass the variables. So remove the suppression formula for GH1a.

My apologies. You need to add a reset formula for the shared variables:

//{@reset}:
Whileprintingrecords;
Shared numbervar cnt1 := 0;
Shared numbervar cnt2 := 0;
Shared numbervar cnt3 := 0;

Place this in the GF1.

-LB

 

[cpjeffm]

Ok that cleared up the reset on the shared variables. But it's still not doing what I need it. I need the subreports to show so I can't suppress the sections I want to show. I just need to suppress the employee group on the main report if there's no data passed from the subreport via the @Emp_Total field that's in the subreport.

 

[lbass]

Well, you don't have to suppress the subreports. However, if you there is a lot of detail in the subs that need to show, then do as I suggested in order to suppress the employee ID, and then add the subs a second time a GHc,d,e sections, etc.

-LB

 

[cpjeffm]

Ok I've got a mess. Any help would be greatly appreciated.

I still need help suppressing employees if the Emp_Grand_Total field = 0 (I need it to show if it returns anything but a 0).

Also, and this may go away once I have the employees suppressed, after the report hits the first employee with data, that data carries over to all employees afterwards.

I attached a copy of the report. On page 32, you'll see data for employee JEFFMILLER. All employees afterwards show data from employee JEFFMILLER.

http://files.engineering.com/getfil...caa99bdf80ac&file=CashDrawerReport_(TEST).rpt

 

[BettyJ]

I believe pmax9999's reply in the following link will help.

[tt]

http://www.tek-tips.com/viewthread.cfm?qid=1720758

[/tt]

 

[lbass]

You need to reset your shared variables as I mentioned earlier, but place it in the group#2f footer.

As I said earlier, because of the order of processing, you cannot access the shared variables from subreports placed AFTER the section containing the Employee ID. You could either place the subreports in sections above the Group #2 name by inserting additional Group 2 Header sections and moving the new sections by grabbing them in design mode and moving them up. Then you could pass all items using shared variables (which it seems you are mostly doing already) both to use for suppression of the Employee Name group section and to display the relevant fields. If you don't want to do that, then just place copies of the subs in sections above the Group Name and proceed as I mentioned earlier (suppressing the sections within the subs so they don't display, etc) in order to pass the values you need for suppression.

Try making a copy of the report and try one of these methods.

-LB

 

[cpjeffm]

Ok I'm soooo close. The only thing I can't figure out is how to reset the totals sections on the main report for employees. After the report hits the first employee with data, that data carries over to all employees afterwards.

I attached a copy of the updated report. On page 2, you'll see data for employee JEFFMILLER. The employee JEFFMILLER2 is inheriting the totals from the previous employee JEFFMILLER, even though JEFFMILLER2 has no data in any of the subreports.

I added the reset formulas in the subreports and then added a formula in group#2f footer but it didn't reset the totals in the headers for group#2c, group#2d, group#2e, group#2f, or group#2j or in the footers for group#2a, group#2b, group#2c, group#2d, group#2e, group#2f.

 

[lbass]

Place the reset formula in the main report GF2.

-LB

 

[lbass]

I meant to say in an inserted group footer section--GF2g.

-LB

 

[cpjeffm]

Thanks for the help. I finally got the report finished and it runs fine inside Crystal Reports 2011 but when I run it with cView 11, it tanks. Sigh...