myEvolv sub reports can be very useful despite their limitations. Their most useful out-of-the-box application is in something like a face sheet, where you want to bring in the current information available, perhaps with some filtering.
In the first draft of some of our form designs, we used the current location sub report to display a client address. What we did not anticipate was that the address on previously entered notes would change when the individual’s address changed. We wanted that address to be locked on the form once it was saved. A few years later some of the Audit Snapshot functionality came out but that still didn’t work for us because of how audit snapshots worked. We needed a way to have the data on the sub report be based on the date of that event, not today’s date.
In subsequent years, I have figured out a few ways to accomplish this, depending on what columsn the sub report views provide and what we are trying to accomplish. I will try to lay out a few different methods below as I have time.
Method 1: Using a Join Column as a Parameter in the SQL Code
This method is good for when you want a sub report that will show you events within a time range from the parent event’s date. For example, you are creating a monthly report type event and need to list all of the progress notes from the 30 days prior to the monthly report date so that you can display them for the monthly report writers to summarize without running a separate report or opening them individually.
In this example, I will create a sub report using the physical_characteristics_view. I want to only show the physical characteristics from any and all events that occurred in the 30 days leading up to the event on which the sub report will display.
Configure Join Columns
When I first copy the system sub report to make my user sub report, the only join column is the people_id. If I display the sub report with this as the only join column, the subreport will show all of the physical characteristics in the system for all time for that person. If I save the event today, when I open it in a year, it will have an additional year’s worth of physical characteristics showing. The first thing we need to do is also use actual_date as a join field.
Check the box for Join Field? and select Less Than or Equal To for Operator.
With this step done, the subreport will now only ever show the physical characteristics for this client that have an actual_date prior to the actual date of the event it is being displayed on. Half of the problem is solved but this report does not cut off at 30 days prior– it would show all of the events prior, going back to the first one entered for this client.
Custom SQL Code
Values entered in the SQL Code field act as a WHERE clause in a SQL query. We already took care of filtering future events through a Less Than or Equal To join so now we just need to limit the past events in the WHERE clause.
I found that if you are using a field as a join field in the previous step, you can actually bring it in as a parameter value (though I have not used it enough to be sure this will hold for all cases, it seems to work here consistently). This allows us to use the same date value that we are using to filter on the join in our WHERE clause. We will combine it with the DATEADD function to go back 30 days and filter for those physical characteristics with an actual_date Greater Than or Equal To that date.
The first actual_date is the actual_date of events in the physical characteristics view. The @actual_date is a parameter that will get its value from the actual_date field on the parent form at the time the form is generated.
The DATEADD calculates thirty days prior to that parameter value and we are looking for anything that happened after that 30-days-ago-date and the parent event’s date.
actual_date >= DATEADD(day, -30, @actual_date)