Last Updated: September 10th, 2016
Sub reports were one of the most confusing things to me when I started out with myEvolv. They represent one of the best ways by which you can pull data that is already in the system onto your event forms but they are also one of the parts of myEvolv that are frustratingly limited. Knowing how they work and their limitations can save you some headaches when you go about configuring forms.
This post will be an attempt to layout everything I know about sub reports in myEvolv. As with all MEGAPOSTS, I will update this post with new information as I come across is. If you have any additions, corrections or elucidations, please send them along!
| Table of Contents |
|---|
| Use & Purpose |
| Limitations & Security Considerations |
| Sub Report Maintenance |
| Creating a New user Defined Sub Report |
| Customizing a Sub Report |
Use & Purpose
Sub reports are used to pull data that is already in myEvolv onto forms. In the form designer, you can add a UDF Report to any form as a method of displaying relevant information from other areas of the system. Some basic examples of when you might use a sub report are to list a person’s active diagnoses, allergies or demographic information that is fairly static. You might also have sub reports that are more dynamic and pull in service notes for a date range based on fields on the form.
Limitations & Security Considerations
All sub reports in myEvolv are based on views that were created by Netsmart, therefore you cannot pull data from user defined fields into a subreport because your user-defined fields are not known by Netsmart and were not included in the select statement that generates the view. In other words, none of the views available for sub reports include any columns from an _x table, where your user defined columns live.
This turns out to be a very annoying limitation but one that you can sometimes get around if you don’t mind re-purposing Event Log fields. My post on How To: Filter Activity Type and Encounter With Picklists gets into this a little bit and I will post more about where I have used this in upcoming posts.
Another thing to keep in mind with subreports is that there is no security applied to the data that pulls in on the sub report besides the customization that you put into the report. It is therefore possible for users to see data for clients that they are not assigned to through sub reports. Be careful that you are only pulling in data that the end-user should be seeing when you use a sub report.
Sub Report Maintenance
There are two Formsets related to Sub Reports in the Setup module: Sub Reports – System and Sub Reports – User
Sub Report – System > System Sub Reports
This area will allow you to see all of the system sub reports that exist. These are the sub reports that are used on default system forms throughout the system. You cannot delete or create new system sub reports. You also cannot modify any of these sub reports and because of this, you also cannot see what additional Report Fields are available without creating a User Sub Report based on the same Data Source.
The Select Sub Reports picklist will show you the Report Name, Data Source Name, Remarks to explain the purpose of the sub report and a list of the Join Columns
Sub Report – User > User Sub Reports
This area is where you can view and edit user defined sub reports. The Select Sub Report picklist for this formset member will only show the Report Name and the Report Code, both of which are set by the user.
Creating a New User Defined Sub Report
When you create a user defined sub report, as when you create new user defined database fields, it is recommended that you use a prefix like ‘udr’ to distinguish it from system sub reports. The reason for this is avoiding name collisions in the event that Netsmart adds a new system sub report with the same name.
The trick with creating a new user defined sub report is finding a view that contains all of the columns that you would need for your sub report, again keeping in mind that you will not have access to anything in a user defined column. Since you cannot see all of the report fields available for each data source in the System Sub Reports area, you will either need to create a user defined sub report for each data source to see all of the columns available or if you can link to your database with Crystal Reports, you can check out all of the views to see what columns are included in each.
Customizing a Sub Report
User Report Name and User Report Code are editable so you can rename and recode your report as you like.
Remarks are useful for keeping notes about what the sub report is designed to do so I strongly using it.
SQL Code
The SQL Code field allows you to add some customization to the SQL that is used to pull data into the subreport. From what I can tell, the SQL that you enter into this field executes as though it is the search condition of a WHERE clause in the SQL statement. This allows you to filter the results in a multitude of ways.
For example, a sub report related to services for a client would pull in all services for the client regardless of program so you may want to filter for a specific program. You can do that by using program_name = 'My Program' in the SQL Code box.
You might want to be more specific and select only services of a certain type so you could use event_name = 'My Service'.
You can create filters on any of the Report Fields available in the view and you can add complexity using AND and OR operators.
You can also use SQL Server functions. For example, if you want to get all of the services that have occurred within the last 3 months of today, you could use actual_date > DATEADD(MONTH, -3, GETDATE())
Report Fields
This sub form allows you to customize the look of the sub report on the form and also allows you to do some more filtering of the data based on the parent form fields.
Column Name
The Column Name is used to select which columns from the data source will be used in the subreport. When you click the build button for this field, you will be able to see all of the columns that have been included in this view by Netsmart. These are all of the columns that you can include in the report. The picklist popup will show you the column name and the data type for the column. Many of the columns that are included in the views are uniqueidentifier data type. These are the GUIDs that are used for primary keys in myEvolv’s tables. These are not useful to be displayed but come into play if you want to filter data through a join.
Data Type
The Data Type allows you to display the data from the column in a different format. For the most part, you will want to display the data in your sub report using the same data type that the data is stored in. One place where this might be different is displaying a DateTime as Date Only if you do not wish to display the time.
Caption
The Caption allows you to determine the column header on the displayed sub report.
Field Order
The Field Order allows you to determine the order that the columns render in the sub report table.
Sorted Column
If this box is checked, the subreport will be sorted by this column in ascending order. If more than one column has this box checked in the subreport, the columns will be ordered in priority based on their Field Order.
Order is descending?
If this box is checked, the sort will be in descending order rather than ascending order for this column.
Is Visible?
If this checkbox is checked, the column will display in the sub report on the form.
Join Field
When this box is checked, the sub report will filter based on the value of this field on the parent form.
For example, if people_id is included in the sub report and used as a Join Field and this sub report is used on a form that has a people_id field on it, the sub report will only select rows from the view where the people_id‘s match, effectively filtering the sub report to display only data for the client that the form is for. people_id is a commonly used EventLog field on activities for people and is often configured to default to the value for the client whose record is selected.
Overwrite Form Field to Join
This field allows you to designate a different form field from the parent form to join on. If you simply check the Join Field checkbox, the sub report will look for a parent form field that matches the sub report form field. Instead, you can designate a different field here. As an example of where this might come into play, say you have a monthly summary event where the summary’s actual_date will be the month after that being summarized. The actual_date on the parent form will be June when you are summarizing May’s services. In order to filter the sub report to May’s notes, you will not be able to simply use actual_date as a Join Field since that would pull in June’s notes. Instead, you would add another date field to the summary (for example udf_summarized_date and then use it to overwrite the form field to join with the sub report’s actual_date.
Operator
The Operator allows you to choose a different operator to use with your join. By default, the operator is equal. You can use this to filter a sub report with a join on a date field using greater than or less than to get entries that occurred before or after a date field on the parent form.
Hi Dean, It’s Vanessa from Morrison. I’m using a view where staff_id and people_id are included. The people_id belongs to the client. The sub report I’m trying to use is published to a formset that looks at the staff’s people_id. Is there any way to overwrite the join field to get the staff’s people_id and join on that?
I tried staff.people_id and wt.people_id
I’ve tried a few where clauses as well but no luck.
Hi Vanessa!
Which data source/view are you using? And what are you trying to do exactly? It sounds like you are trying to use a client events view to pull in events for a staff or something like that?
Hi Dean,
Yes, it’s the tests_event_view and currently joins on people_id (which is the client people_id).
And yea, my ultimate goal is to have a Formset member that contains sub reports of all events not completed by a staff, publish it in the Agency > Staff & Security > Staff Information area and grant supervisors access to it.
Any time a staff member leaves, they have to pull reports to see what isn’t complete and I’d like to give them (and us ad administrators) an at a glance view of incompletes.
I think there might be a better way. Let me mess around with those views. Are you considering not completed to be not signed?
Yes, or is_locked = 0, just depends on what is in the view… haven’t gotten that far yet.
Ok, here’s the post that explains my solution. Let me know if anything is unclear or you can’t get it working.
Ok, I have a solution that should work for you. I will try to post it tonight.
Hi Dean. Do you have code for a sub report from physical_characteristics_view that would help me only display entries from 30 days prior to and including the date the parent form is created? My report currently uses actual_date > GetDate() -30 but that is displayed 30 days of entries prior to todays date. So when I view an entry from September 2017 I see February 2018 entries. Thank You!
I think I have a solution for that. Let me make sure it will work on the subreport you want to use.
Time got away from me today so I was only able to post the method I would use for your problem but this should do it for you. I have one or two other ways to go about “anchoring” sub reports to the parent event which I will post later. Stay tuned and let me know if you get stuck on this one.
How To: Anchor Sub Report Data to the Parent Event
Hi Dean – It looks you’re already helping someone else this morning, but I’m thinking my question will be a quick answer. You didn’t list this as an explicit limitation, but from what you wrote it sounds like we also can’t create subreports based on specific answers from tests & assessments that we’ve created (since Netsmart didn’t create them), is that correct? Basically, we have a test & assessment (that I created) that gets filled out every 3 months & the program management is asking if each question can list the previous test & assessment’s answer(s).
Thanks,
Natasha
I have not done anything with the test sub reports. That said, it does look like that might be possible to do using the test_details_answers_expanded_view sub reports. The Netsmart sub reports look like they have been based on this view and then somehow tailored to specific types of test questions – multiselects, single answers, etc.
My guess is you would need to join both the people_id and the test_details_id to the question so it filters properly. I’ll see if I can find time to play with these and see if I can’t get it working how you are looking to do it.
That’s great! That actually worked when I gave the test I wanted to pull from an event code & gave the question I wanted to pull in a question code. Then I joined on just the people_id & then I input “event_code = ‘Myeventcode’ and question_code = ‘Myquestioncode’ into the SQL Code box.
Thanks for your help Dean, that was perfect!
Natasha
Hi Dean,
Hoping you can help with a sub report code I am trying to execute for my current treatment plan goals and objectives. this sql works and shows me active goals and objectives but I need to add some program security. Can I write something that shows the programs that the staff is enrolled in or just their own goals and objectives? Right now it shows all programs regardless of their security.
goal_status != ‘Discontinued’ and objective_status != ‘Discontinued’
I think something could be possible here but I need some more information.
Is this subreport on a service form where the Program Providing Service will default in on the form? If so, you could probably limit the subreport to only show the goals from the program providing service and then the security is already handled at the event level.
Hi Dean this blog is very helpful for a person like me with limited java and SQL skills So thanks for sharing !
I do have a question abut sub reports- is there anyway to Bracken date ranges? I have been told by our Netsmart report consultant that this is not possible with sub reports like it is with would a data insight report or SSR report where full SQL can be used. Unfortunately our DHS want this information embedded on the weekly review Event
The form where the sub reports are attached to is a weekly review of services event that is a multiday event using Actual date as 1st date of the week and End date as last date of the week . Each sub report on the review form would ideally pull in the single event notes(several types) or group notes with an Actual date duration but no end date(single day event) >= the review form actual date and <=review form End date. Basically just pulling one week of information.
I have a workaround solution right now basically we pull an SQL report and then just add the duration data into a UDF numeric field on the weekly review but it is a cumbersome process.
Hi Tracy,
The main barrier with “anchoring” subreports to notes, as I call it, is finding a view that has the fields you want to show on the report and enough date columns to play with but there is often a way to get around those barriers with some SQL as well.
When I have created monthly summaries in the past and needed to display the notes for the time span of the summary on that event, I used the event_view, which has some custom columns in it like calendar_week_start that you can use as your join column instead of actual_date. Just add it to the subreport, check Join Field, enter ‘actual_date’ as the Overwrite Form Field to Join and then select the Equal operator. Clear out those last few fields for the actual_date column if they are filled in.
That view also has a month_start for monthly time frames, etc. That will anchor it so that the subreport doesn’t change it’s date range relative to the [today’s date]. Then you just have to see if the view has all of the other data columns you need to display on the summary. You’ll never see a user_defined field in a subreport and sometimes it’s that one or two columns that are missing from the an otherwise perfect subreport view.