Hello Community,
We hear you. It can be down right challenging to build a View when you have no experience with the data, the database's structure and relationship between the tables. This has been the single greatest challenge for VDM users. We've been quietly working on a solution for some time now and it'll be included as a preview feature in the next release later this month. We call it Command Tables.
Here's an example view from medical database. It doesn't look too scary, but what if you don't know what the HCPERSON, HCACCOUNT and HCENCOUNTER tables are, or better yet what is field HCPNFNM?
Now let's take a look at Linking. There are three tables in this View, which is actually quite basic for many databases. There are some Views with over 14 tables. Linking is what tells VDM how data is related from one table to the next. So, what are the right fields to link, in what order, explicit or implicit joins? These are just some of the questions that need to be answered to get proper results out of VDM.
Once you've found your tables and fields, sorted out Linking, and successfully execute a query, your output will look like this. Now that you have some data you can start making sense of the column names, but what about when you export this View to Excel and send it along to your colleague, or try to build a Finished Report of Visualize. It's easy to get confused and a bit lost.
Let's take a look at building a Command Table. You'll right click on Command Tables under your Connection Profile and select Add.
We've added the ability to Import an existing View's structure to automatically build a Command Table. Alternatively you, or an advanced user with experience leveraging SQL, could build the Command Table from scratch by leveraging SQL syntax. The key is you'll only have to do this once for each Command Table, and there's no limit to how many fields you can include. As an example let's say you want to create an "Inventory" Command Table. You'll include pretty much everything anybody will want to know about Inventory.
The Command Table builder allows you to create user friendly field names as well as field descriptions.
When completed and you click OK, you'll see the newly created Command Table under Tables and Fields complete with its easy to read field names and descriptions. You can now leverage the Command Table to build a View just like it's any other Table and Field.
The user friendly field names stay with the View throughout the process. This saves time from needing to constantly rename your columns with each View and your colleagues will be able to more easily follow along with the output.
Command Tables can also be joined to other Command Tables or other SQL tables to make larger and more complete reports. Command Tables are also portable and can be shared with other Users.
Command Tables with aggregated data make it easy bring summary level data into the detail of a View. In the example below we created an aggregated Command Tables that is used to track number of Encounters and the Last Visit Date and Time. By leveraging Command Tables, the user didn't need to add Summaries, Group By or Having statements.
This is an open invitation to the Community to let your voice be heard. We want to know what you think about Command Tables.
Look for a Command Tables preview to be included in an Ontario Reports release later this month.