Suppress group header

[vngpal]

Report version: 8.5
Database: SQL server
Hello,
I have a report with the following groups and I have data as shown below.
Group1 Site
Group2 Room
Group3 Service
Group4 Surgeon
Group5 Patient

Below is the data I am getting from the database. The values are displayed in Group5 footer as per each patient.

Group1 header Test
Group2 header Rm1
Group3 header Orth
Group4 header xxyy
Group5 header suppressed.
Detail section is suppressed.
Group7 footer
Date PT.Name Delayed Minutes
10/1/06 AAA 0
10/1/06 BBB 15
10/1/06 CCC 0


Group2 header Rm2
Group3 header ENT
Group4 header mrsr
Date PT.Name Delayed Minutes
10/1/06 abc 0

I am suppressing the group footer 7 (where I am displaying the results if the delayed minutes is "0"). Now I want to suppress all the headers if there is no data is dipalyed. As in the above example For the "Rm1" eventhouth there are three cases I am suppressing the "AAA" and "CCC" but displaying "BBB". So in that case all the group headers should show up. But for the second room Rm2 there will not be any data displayed since I am conditionally suppressing the data. In this case I want to suppress all the group headers.
Also I need to get the count of patients where the delayed minutes are not "0" in the header section. The running total will not work since the dealyed minutes are calculated in the third pass. I mean to get the delayed minutes two other formulas has to be evaluated before. Also I have a calculated field in the Group 2 level(like minimum(time,group2). So this formula is not showingup while creating a runnimg total.

I s there a way to suppress the headers info? Also is there a way to calculate the count at the header section? I can calculate the count at the footer section and it works fine. But as per the business need I have to do it at the header level.

Appreciate your help.

Thanks.

 

[lbass]

Please show the content of the formulas you are using for {@delayed minutes}. Also, what are the group fields for group 6 and 7?

-LB

 

[vngpal]

I am sorry. There is no group 6 or 7. The results are shown in group footer 5 only.
Formula.
@{@delayed minutes}
if Maximum ({@Room_Time}, (group5namefield)) > {fieldname} then elapsedtime({fieldname},Maximum ({@Room_Time}, {group5namefield}))
else 0
Thanks

 

[lbass]

Please show the content of the nested formula also {@Room_time} and any other formulas nested within that.

Is elapsed time a user-defined function? I'm guessing it's the same as datediff.

Please also provide a sample of {fieldname}. What does this number signify?

-LB

 

[vngpal]

@RoomTime is the Maximum(roomtime, ptname(group5field))
and the {fieldname} is the database field. Yes you are correct. Elapsed time is like dateDiff and calculates the time differece between two times.

Any other info needed?

Thanks

 

[lbass]

Why do you need to use a maximum on the roomtime? Please show some sample data and also explain what fieldname is/does. It's a number that means what? You will have to do the calculations differently in order to suppress any headers, I think, so I need a little more information.

-LB

 

[vngpal]

For each patient name(group5) there are multiple times. Since I need the maximum time I have to use the maximum on room time formula for each patient(group5). The delayed minutes are the difference between two times and the result is a number.

Here is the logic.
Each room will have multiple scheduled times.
I want to get the minimum scheduled time for each room.
For each rooms there are multiple patient names and each patient has their own scheduled time. Besides the Scheduled time each patient has other times also like Room time.
Now I want to get the patient whose scheduled time is equal to the minimum of the scheduled time for that room.
The room time for each patient name may be before or after or on the scheduled time.
Now my calculation is if the roomtime for each patien name(group5) is greater than the schedule time and also if the minimum of room(group2) schedule time is equal to the schedule time of the patient name (group5)then it will calculet the time difference else it will be "0". If the roomtime is less than the scheduled time it will show "0" only.
So the room may have multiple ptnames and only one will be with delayed time and rest of them "0". As per the suppress condition the report will show the delayed time is not equal to "0".

This is how th logic works.
Thanks

 

[lbass]

So what you are referring to as {fieldname} is really the minimum of scheduled time at the group 2 level?

The only way you will be able to suppress group headers in 8.5 based on the criteria you mention is to create SQL expressions that return the maximum(roomtime, ptname(group5field)) and the minimum schedule time per group 2. Create the first one like {%maxroom}:

(
select max(A.`roomtime`) from table A
where A.`patient` = table.`patient` and
A.`site` = table.`site` and
A.`surgeon` = table.`surgeon` and
A.`service` = table.`service` and
A.`room` = table.`room`
)

You would then be able to use this in your formula for {@delayed minutes} instead of the maximum summary. Similarly replace the minimum of scheduled time. Then you can create a formula to use for suppression like:

maximum({@delayed minutes},{table.group5}) = 0

The only other approach would be to save the report as a subreport placed somewhere above the group headers you want suppressed and then pass the values of your formulas to the main report as shared variables.

-LB

 

[vngpal]

Thanks. Let me try.