Calculate who is onsite now 1

[odins701]

Hi all! I need some help with my report. In the example screenshot below, there are 2 employees. Cliff has entered the site but has not left. Jesse has entered the site and has left. This means that 2 have been on site for the day, but 1 is on-site now. A distinct count of the employee id gives me the total of who has been on-site for the day. I need to calculate who is still on site. In the Entry/Exit column, 1 = entry and 2 = exit. Note: it is possible that someone may come and go multiple times throughout the day. If Jesse were to come and go twice in a day, he would have 4 records returned. I'm grouping by employee name. Could someone help me with this? I am using crystal reports 2020 patch 1 version 14.3.0.3569.

 

[odins701]

Take a look at this screenshot, maybe it needs joins?

 

[odins701]

I've been directed the the result needs to be in the header :-(

 

[odins701]

Update the sql expression yields 9, but the onsitenow formulat is returning all nulls with the remainder,3

 

[odins701]

Do you think the issue is that the sql expression is happening at the database level nd not the report level? Seems like it should be different for each record in the report to work?

 

[lbass]

Your where clause in the second one makes no sense--empID just equals itself, so has no effect. I can't test the cast() and getdate() functions to see if they are working, but it doesn't matter because your approach to the SQL expression isn't working. As I said earlier, I think it's time to consider using a subreport to get the correct result in the header. You wouldn't need to use a SQL expression at all.

-LB

 

[odins701]

i'm willing to give anything a try. not sure how to do a subreport

 

[lbass]

Delete the SQL expression and then save your report (now the "main" report), and then do a "save as" to create a copy of it which you can rename with an extension of "-sub", for example.

In the main report, insert another report header section, and then in design mode move your current report header so that it is RH_b. Next go to insert->subreport and select "choose existing report" and select your subreport and insert it in RH_a. Do not link the subreport to the main report.

In the subreport, insert a group on the EVENTS.EMPID field. Then change your {@onsitenow} formula to the following and place it in GF_1:

Whileprintingrecords;
Shared Numbervar onsite;
If remainder(sum({READER.TIMEATT},{EVENTS.EMPID}),3)=1 then
onsite := onsite + 1;

In the main report, create a formula {@OnsitenowTotal} and place it in the appropriate spot in RH_b:

Whileprintingrecords;
Shared numbervar onsite;

To make the subreport and the section it is in disappear, do the following. Note that you cannot directly suppress the subreport OR the RH_b section itself.

Within the subreport, in the section expert, suppress all sections of the subreport and format each section to "suppress blank section". In the main report, go to format->format subreport-> check "suppress blank subreport". Then still in the format editor go to the border tab and change the border lines to "None". In the mai report, format RH_a in the section expert to "suppress blank section".

-LB

 

[odins701]

I will give it a shot.

 

[odins701]

I get an error when saving the @onsite formula. says there must be a group that matches this field, and the following is highlighted... sum({READER.TIMEATT},{EVENTS.EMPID}). Do i need a group for reader.timeatt? oops nevermind.

 

[odins701]

Ok, all that is done. When i preview the report i get the error below.

 

[lbass]

What is the content of your current {@Onsitennow} formula? It should have been changed as noted in my last post.

-LB

 

[odins701]

Whileprintingrecords;
Shared Numbervar onsite;
If remainder(sum({READER.TIMEATT},{EVENTS.EMPID}),3)=1 then
onsite := onsite + 1;

in the subreport

 

[lbass]

Do you have a formula of the same name in the main report? If so, remove it.

-LB

 

[odins701]

i removed it from the main report (it was blank) and get same error.

 

[lbass]

Please also check whether any of your formulas in the main report and subreport reference {@onsitenow} (from when we were trying the first method) and remove those formulas.

-LB

 

[odins701]

i'll do that right away

 

[odins701]

ok, that cleared that error up, thx! screenshot below shows the results, but the count onsite now is 0.00 (i'll format that once it's good), should be 1...

 

[lbass]

Please double check the formulas in the subreport and ensure that you used "shared" variables.

-LB

 

[lbass]

Also make sure you did not suppress the subreport object or the RH section it is in.

-LB

 

[odins701]

what do you mean by shared variables?