Report with Multiple Subreports

[eric333]

Good morning,

I have developed a report that is comprised of a main report with only one field (event number) located in the page header along with some text boxes for the header. The report is grouped by event number but the group header and footer are suppressed. There is also a page footer which contains three fields (event number, event date and event number combined with the print date and time).

There are seven subreports that all link back to the event number. The subreports could contain data about persons, items or notes related to the event. For each subreport, if there is no person or item or note, that specific subreport is suppressed.

The report runs every hour and looks for all new events entered during the prior hour, using the following selection formula:

({event_entered_datetime} in dateadd("n",-60,datetime(currentdate,time(hour(currentdatetime),(int(minute(currentdatetime)/60)*60),0))) to
dateadd("s",-1,datetime(currentdate,time(hour(currentdatetime),(int(minute(currentdatetime)/60)*60),0))))

This works great if the persons, items or notes are all entered the same hour. However, sometimes, a few days later we discover another person or item is involved in the event and the person or item is then entered.

In those instances, I want to print the spplicable subreports with the main report header and footer. However, my main report is looking only for events entered during the prior hour whereas the event may have happened days ago, but the person was just added to the event during the last hour.

Does anyone have a suggestion as to how I can accomplish my goal?

Thanks!

PS - Sorry if this was not well explained. It makes total sense to me, but I've been working on this report for a month hahaha.

 

[eric333]

I neglected to mention that each subreport is using the same selection criteria for records entered during the prior hour. The database structure supports this in that each table used to generate a subreport has a record_entered_datetime field in it.

 

[lbass]

Using your current selection formula, the new information for events entered days ago cannot appear--since your are only allowing events from the current last hour into the report, so those old event numbers will not appear. Do you have another field that captures when data is entered for the details (the info in the subs)? If so, you could modify your selection formula to allow current events or new details for old events. Something like:
(
({event_entered_datetime} in dateadd("n",-60,datetime(currentdate,time(hour(currentdatetime),(int(minute(currentdatetime)/60)*60),0))) to
dateadd("s",-1,datetime(currentdate,time(hour(currentdatetime),(int(minute(currentdatetime)/60)*60),0)))) or

({event_details_entered_datetime} in dateadd("n",-60,datetime(currentdate,time(hour(currentdatetime),(int(minute(currentdatetime)/60)*60),0))) to
dateadd("s",-1,datetime(currentdate,time(hour(currentdatetime),(int(minute(currentdatetime)/60)*60),0))))
)

-LB

 

[eric333]

Thank you for the suggestion. Each subreport is based upon a different table. PersonType1, PersonTyp2, ItemType1, ItemType2, etc. Each table does have a column that indicates when the record was entered (PersonType1_entered_datetime, PersonType2_entered_datetime, etc.).

Given that information, would your suggestion of a selection formula that looks at current events (entered during the prior hour) or new details for old events (entered during the prior hour) still work? If so, would the formula look something like?

(
({event_entered_datetime} in dateadd("n",-60,datetime(currentdate,time(hour(currentdatetime),(int(minute(currentdatetime)/60)*60),0))) to
dateadd("s",-1,datetime(currentdate,time(hour(currentdatetime),(int(minute(currentdatetime)/60)*60),0)))) or

({PersonType1_entered_datetime} in dateadd("n",-60,datetime(currentdate,time(hour(currentdatetime),(int(minute(currentdatetime)/60)*60),0))) to
dateadd("s",-1,datetime(currentdate,time(hour(currentdatetime),(int(minute(currentdatetime)/60)*60),0)))) or

({PersonType2_entered_datetime} in dateadd("n",-60,datetime(currentdate,time(hour(currentdatetime),(int(minute(currentdatetime)/60)*60),0))) to
dateadd("s",-1,datetime(currentdate,time(hour(currentdatetime),(int(minute(currentdatetime)/60)*60),0)))) or

({ItemType1_entered_datetime} in dateadd("n",-60,datetime(currentdate,time(hour(currentdatetime),(int(minute(currentdatetime)/60)*60),0))) to
dateadd("s",-1,datetime(currentdate,time(hour(currentdatetime),(int(minute(currentdatetime)/60)*60),0))))
)

 

[lbass]

Well, yes, but this means those tables have to be in the main report, and I'm guessing you intended to use subreports to limit the number of records returned in the main report. Another way of handling this might be to add (unlinked) subs again in the report footer where you provide the event number in the subs and show records within the subs that were entered during the last hour.

-LB

 

[eric333]

Actually, I created the subreports because I wanted to be able to suppress the entire section containing the subreport if there were no records for that specific subreport/section. Is there a better way to handle that?

 

[eric333]

I figured out the reason I setup multiple subreports. The Event can have many Persons or Items associated with it. So, those subreports are needed for the various involvements.