If you are anything like me, you work with Data Insight by roughing out your SQL query, dropping it into a virtual view, and then starting a report to see if your query is pulling the data you were expecting. If you need to make changes to your virtual view, you return to the virtual view management area of Data Insight and make the changes and hen back to your report to check the results.
Once you have spent many hours working with Data Insight, you will see some pretty strange behavior but one of the most frustrating occurs when you spend a long time troubleshooting a query issue and no changes you make to the virtual view or report seem to make a difference. The report runs in the preview window exactly as it had before.
Most of this is speculation but this is what my gut tells me is happening here. With other reporting tools like Crystal Reports or SSRS, you write your query and when it’s time to run the report, the query executes on the database. With Data Insight, you are creating a virtual view that runs when you save the virtual view definition and creates a data object that you then use to write a report.
This might explain why just saving a definition on some more complex SQL can take a while and also why you are limited to using filters on the data you retrieve AFTER retrieval rather than including parameters in your query statement.
When you are in the Report Designer, changing the columns used in the report has the effect of refreshing the virtual view that is being used. So after you update your virtual view, reload your saved report, add a column to the report and then remove it before running the report to see your changes reflected in the data that pulls in. Keep in mind that this process will need to be done to all reports that use the virtual view if you want them to update.
In a previous post I used Crystal Reports to find the GUID associated with a program in my tables. Since Crystal Reports is not an option for everyone, I thought I would share two methods that everyone has for discovering the GUID of a picklist item in myEvolv.
Method 1: Form Field Manipulation
If you are trying to determine the GUID value for a picklist field, you can make a quick change in the for designer to expose the GUID.
In this example, I am going to try to figure out the GUID for my Preschool – Classroom program. On the form below, I have the
program_providing_service field which uses the “Program Listing – All” lookup table. I have selected ‘Preschool – Classroom’ on the form and will save the form with this value.
Next step is to go to the form designer and edit this form. The
program_providing_service field on the form is a Foreign Key field and it is associated with a lookup table. This accounts for how it displays on the form as a picklist field where instead of seeing the GUID of the program providing service, you see its description value from the lookup table.
If we change the Display Type Code of the field to Regular String and clear the value for the lookup table, the Program field will display on the form as a text field and expect the user to type in a GUID. However, on forms where the value already has been entered and saved, the value will display as the GUID of the item selected. So we will change the Display Type Code to Regular String and then Save the form.
Upon re-opening the saved event from before, we see the GUID of ‘Preschool – Classroom’ program displayed in the Program column. Once you have your GUID, you can revert the changes made to the form.
Method 2: Data Insight Report
This method requires a little knowledge about the tables in myEvolv but works when you can’t edit the form to use method 1.
Navigate to Reports >Data Insight Report Writer >Custom Reporting >Custom Reporting
Click Configuration then Click “Create a new virtual view”
Give your virtual view a name and friendly name and then use a simple SQL statement to get everything from the table that contains GUID. In my example, I am looking for everything from the
program_info table because that is where all of the programs are kept.
user_defined_lut contains all of the user defined lookup table values combined together.
So I will use
SELECT * FROM program_info
Save the view and then click “Report Management” and click “Add”. Select “Tabular Report With Header”
Select your virtual view as the data source and click “OK”.
In the Report designer, add the Primary Key Column (in this case
program_info_id) – it is usually the one with the same name as the table and then _id at the end.
Also add the
description column – in some cases this column might have a different name – for my example, the column I want is
At the bottom of the screen, you will see a preview of your report with the GUID and the Description so that you can tell which GUID belongs to which item.