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)
On version 425, it does not look like the @actual_date works anymore. I’m not able to grab the parent form actual_date to compare to an end_date.
I heard it was never supposed to work and that the “fix” might be coming for a few years – this may be The End for that method, then. What to do in it’s place, I do not know. It was a handy “bug” while it lasted. We are still on 350 for now, so I haven’t been forced to figure out a new way yet. Something to look forward to, I guess.
Anything that will work in NX? Trying to do a subreport where end_date is greater than or equal to the actual date OR the end_date is NULL.
With the new functionality of custom virtual views that can be turned into subreports, you can more or less handle anything by writing the specific virtual view you need with the logic in the SQL query and any number of columns you might need to use for join columns on the subreport. This article is on the docket to be revamped for NX, but I am trying to figure out some examples common anchoring scenarios that came up in Classic and how I would approach the same problem with the custom views and subreports.
I know one would be to only show items with a date_entered prior to form’s actual_date – so that new items added to the client record don’t show up retroactively. I think another method to cover will be adding multiple columns of the same field so you can JOIN them multiple times to different parent form fields. For example, if you want to show all of the events between two dates on the parent form, you might need the actual_date column twice on the subreport so you can join like: [subreport actual_date] >= [parent form start date] AND [subreport actual_date_2] <= [parent form end_date]
If you have a specific use case, I can use it in my NX post.