Product Support Portal

Overview

A view is a metadata layer used by KPI Studio to hide the complexity of database structures from report writers. The view is used to define which columns in your database you wish to make available for building reports. These fields may come from multiple tables and therefore will require joins to be defined (the business logic that links rows in a table together).

The two major steps in creating a view are:

1. Relationship entity diagram — selecting the tables you need from your database and defining how data in these tables are joined.
2. View field selection — defining which fields you wish to make available from these tables and providing metadata for them.

Creating a view

You can create a view based on an existing data source or from a new data source that you have access to connect to. Make sure you have the required details of any new data sources before you begin.

Views can consist of one or more tables, and advanced users have the option of using Freehand SQL. In the steps below, we will create a new view with multiple tables, which automatically opens the View Builder.

1. Click on the plus sign that appears at the top right corner of your screen
2. Select View to create a new view.
   The Create New View dialog box will appear, where you can choose an existing source for your new view, or create a new one.
   We'll use an existing sample data source, cqs-validation-pg-10-5-clinical, in these steps.
3. Click on cqs-validation-pg-10-5-clinical from the list of data sources.
   The next step will appear, where you can choose the components of your new view.
4. In Select your view type, select Multiple Tables.
   A warning message will appear.
   
   
5. Click Create View  to launch the View Builder window.
   
6. Double-click on the text New View that appears in the top left corner.
7. Type Tutorial View and then press Enter to rename the new view.
   KPI Studio automatically saves your updates, but if you can also manually save your view as a draft at any time, allowing you to save your new view to a particular folder.
8. Click Publish to display the Save View dialog box.
   
9. Triple-click on the phrase New View and type This view will be used to learn about KPI Studio
   
10. Select your folder from the first dropdown. Here, Executive Dashboard is selected.
11. Select the content folder from the next dropdown. Here, Overview is selected.
    
12. Click Save As Draft to save your view in draft format.
    KPI Studio will close the draft and take you back to the Browse page, where you can see your new draft view.
    
    

Building entity relationships is one of the key purposes of the View Builder. This allows you to define all the key relationships between your selected database tables.

1. Double-click on the draft view you created in the previous steps  to edit it in the View Builder
2. From the table list on the left of the screen, find  the table dimstudy and drag. and drop it onto your canvas.
3. Repeat step 1 to add the dimsite and ae tables to your canvas.
4. Click on the Create New Join tool, which appears next to the cross icon, on the dimstudy table.
   The New Join dialog box will be displayed.
5. Use the dropdowns to create the following join:
   Join Type: Inner Join
   Cardinality: One to One
   Join To: AE
6. Under Join Details, click Detect Joins to prompt KPI Studio to automatically detect the join.
7. Click Save & Close.
   The join will now be displayed as a line between the two tables, and hovering over the join icon will display the logic in a tooltip.
8. Repeat steps 3 to 6 to create a similar inner join between dimstudy and dimsite where studyid = studyid.
9. Drag and drop one of the three tables to a new location on the canvas: you can move these around as needed to make any diagrams easier to read as you add and join more tables.

Selecting fields

From the View Builder you can select which fields to make available to end users for reporting. Only columns selected from each table in your Unattached list will be available for reporting.

1. Ensure your new view is loaded in the View Builder and that you've created any entity relationships (if not, follow the previous steps).
2. Click on the expand tool, which appears next to the table name, for the dimstudy table to expand the list of table columns.
3. Click on the Edit Table Properties icon, which appears just below the table name, to see the table's properties displayed in the panel on the right of the screen.
4. Click on the expand icon, that appears next to Columns: dimstudy on the right pane, to see the list of columns.
5. Check the checkboxes for current_count_sites_activated, current_enrollment_count, first_site_activation_planned_date, latest_planned_count_sites_activated, latest_planned_date_for_last_site_activated, site_activation_completion_date, study_subjects, studycro, and studyid.
6. Click on any Edit Table Properties icon once more — to refresh the display of the columns on the canvas.
   The columns you selected will now appear in bold.
7. Repeat steps 1 to 5 to select the following fields for the dimsite table: i) studyid, ii) sitename, iii) siteid, iv) sitecreationdate, v) sitecountry, vi) enrolled_count, vii) enrollment_rate, viii) ae_rate, ix) ae_count.
8. Repeat steps 1 to 5 to select the following fields for the AE table: i) studyid, ii) siteid, iii) sitecountry, iv) usubjid, v) aeverbatim, vi) aetox, vii) aeterm, viiii) aeser, ix) aestdtc.

Setting field folders & meta data

Initially in the preparation phase, fields selected from the previous steps (the modeling phase) will be in the Unattached panel in folders that represent the tables that they originated from. Assigning fields to folders lets you organize your fields in a way that is logical for the report writer, giving you the chance to group them differently to how the columns were grouped in the table structure in the data source.

Assigning a field to a folder associates meta data with that field so that report writers will be able to view and use the field when they build reports.  Without that meta data, a field won't be available in the Report Builder. 

You can assign fields to folders in the Available Fields panel. In the steps below, we'll show you how to add folders and then assign fields to them.

1. Ensure you have completed the modeling phase for your new view  (if not, follow the previous steps).
2. Go to Prepare tab.
3. Your view will change to a tabular layout, showing you the contents of each column.
4. Click on the Create icon in the bottom left corner, then select Add/Edit Folders from the list.
   
5. The Field Folders dialog box will appear, where you can create new folders, delete existing folders, and add fields to existing folders.
   
6. Hover over the Dimensions folder.
   The ellipses that appears next to the folder name, lets you reorder the folder by dragging and dropping, and the red X on the right lets you delete the folder.
   
7. Click Add Field Folder and enter Sites.
   The column will now appear in the list of field folders.
8. Repeat step 5 to add Adverse Events, Study, and Disposition.
9. Click Add Field Folder to display a new empty field and type Date Fields.
10. Click Add to add the new field folder to your field folder list.
11. Click Submit o add the columns as field folders.
    The view will now show the added field folders in the field/folder list in the panel on the left.
    Now that the folders exist, we can add some fields to them.
12. Click on the Create tool, then click on Add Fields.
    A new panel will appear further left that lists the related table names, which can be expanded to display the table fields.
13. Click on the DIMSITE table to expand it.
14. Drag and drop the following fields individually to the Sites folder (or use your usual keyboard/mouse combination multi-select them): siteid, studyid, enrollment_rate, and enrollment_count.
15. Click on the Sites field folder to expand it.
    The three fields you just added will be listed.
16. Repeat the last few steps to add siteid, aeser, and usubjid to the Adverse Events folder.
17. Repeat the last few steps to open the Studies field folder and add study_subjects and current_count_sites_activated.
18. Repeat the last few steps to to add current_enrollment_count to the Disposition folder.
    The fields added to field folders in Tutorial View will now be accessible in the Report Builder.
19. Click on the white arrow for Table Fields heading to hide the column.

Changing field content display

The View Builder lets you choose how field content should be displayed. For example, you can change those uppercase field names to be more user friendly, and make values display in a more meaningful way. In the steps below, we'll make some basic and useful changes, including updating some reference codes.

1. Ensure you have set up your field folders and meta data for your new view  (if not, follow the previous steps).
2. Click on the field name studyid to select it.
3. Type Study Identifier and then press your tab key to confirm the field label update.
4. Click on the triangle next to the field label to display a drop down list and click on Edit Format.
   The Field Settings dialog box will be displayed, where you can find all the information associated with a field and update it to suit your needs.
5. Click on the Details section to expand it, and read through the options available.
6. Click on the Format section to display the available formatting options.
7. Click on the Format drop down and take a look at what options are available.

Formatting fields

Once you're happy with the basic display of your field values, you can style them further with formatting options, such as currency symbols and colors.

1. Ensure you're in the Prepare tab of your view and that you're using the tutorial view we set up earlier on this page (if not, follow the previous steps).
2. Locate a monetary field and select Edit Format.
   
3. Add a prefix of $ and set Decimal Places to 0.
   
   
4. Further down the list, locate Color and click on the circle and pick red from the options: this will set the default color for the field when used in charts (which can be overridden in each report).
5. Click on the Details section and update the Display Name field to display Cost.
6. Scroll back up to the top of the dialog box and click on the X in the top right corner to close the box (your changes are saved dynamically).
   The Cost field values will be updated to display your formatting changes.
   

Setting up calculated fields

Calculated fields let you create new fields based on existing fields. You can use simple calculations right through to freehand SQL for more complex needs. These can be useful for displaying information that's likely to be used regularly in reports, saving the report builder from having to recreate the field at the report level each time. Calculated fields can also be useful when used in conjunction with Guided NLQ,  providing pre-calculated fields for your users to include in their queries. In the steps below, we'll create a simple calculation that produces a metric field as output. We'll aim to calculate profit by subtracting the cost from the invoice figure.

1. Ensure you're in the Prepare tab of your view and that you're using the tutorial view we set up earlier on this page (if not, follow the previous steps).
2. Click on the Create tool, that appears at the bottom of the screen, then click on Calculated Field
   
   The Calculated Field dialog box will appear.
   
3. In Calculated Field Name, type AE Count.
4. Click on the Field Folder drop down and select Adverse Events.
5. 
6. From the Search drop down, start typing invoice and click on INVOICEDAMOUNT to select it for use in the calculation.
7. Select ae_count and click Validate. If there is no error, then is shows as SQL is valid and then no change is required.
8. Click Save to save this new calculated field.Scroll to the left of your field table to check you can see the new field at the end of the Metrics section.

Building date hierarchy fields

Date hierarchy fields are essentially calculated fields that let you build levels of a hierarchy based on a single date field in your data source. Date hierarchies enrich analysis through more flexible drill down options, a better Guided NLQ end-user experience, and for use with time series charts. Date hierarchies are created by using granularity to dictate day/week/month/quarter/year etc. Read more about date granularity if you'd like further information before performing these steps.

To build a date hierarchy, you must start with a field that already uses a date format, rather than a dimension (such as a string of characters), or a metric (such as a number). 

Building a date hierarchy requires four components, as outlined in the table below.

1. Ensure you're in the Prepare tab of your view and that you're using the tutorial view we set up earlier on this page (if not, follow the previous steps).
2. Click on the Create tool,at the bottom of the screen, then click on Date Function.
    
3. From the Create Date Function dialog box, click on the drop down for Field and click on first_site_activation_planned_date.
4. Click on the drop down for Date Function and select Month Start Date.
   You will notice that each time you select something from the drop down, a sample date is displayed on the right.
   
5. Click on the drop down for Format and select Month Name.
6. Click on the drop down for Field Folder and select Date Fields.
   
7. Click on the Save button to save your new date field.
8. Repeat steps 2 to 7 to add a new date field with the following details:
   Field: Invoiced Date
   Date Function: Year Start Date
   Format: Year
   Field Folder: Date Fields
   Your Date Fields field folder now has two new fields in it, and to keep our date field together, we can move the original date field here too.
9. Use your mouse to drag and drop fields from one folder to another.

Building a drill down hierarchy

The drill down hierarchy lets report users drill down within a dimensional hierarchy by limiting the result set as they select one level to the next. For example, drill from Year (2022) to Month (August), and so on.

When creating a drill down hierarchy, you need to start from the top level and work your way down. In this example, we will create a Year > Month > Date hierarchy, so we will start with the Year field at the top.

1. Ensure you're in the Prepare tab of your view and that you're using the tutorial view we set up earlier on this page (if not, follow the previous steps).
2. In the field folder section, hover to the right of the Year Start Date field to display three blue dots.
   
3. Click on the dots to display a menu and select Drill To to display a sub-menu.
   
   As we're drilling from year to month, we need to connect our year field to our month field.
4. Click on Month Start Date in the sub-menu.
   The two date fields will now show that they're joined in a hierarchy with a grey line.
   
5. Hover to the right of the Month Start Date field and click on the dot to display a menu.
6. Click on Drill To, and then on the new sub-menu item, also called Drill To.
   
   The new sub-menu will appear as soon as you have associated any two date fields, as we have already done.
7. Click on Invoiced Date to link our monthly field to our original date field.
   All three date fields will now be linked from their top level (first in the list) to their lowest, most granular level (last in the list).
   

Reviewing and publishing your view 

At any time when editing your view, you can check the summary information directly from the top toolbar. It's a good idea to check this before you publish a view. In addition, you can access other details, such a security and performance details from this toolbar, and these areas are covered elsewhere in this section of the wiki. We'll just show you the basics of where to find them here.

1. Locate the toolbar at the top of your View Builder screen.
2. Hover over each of the options to see what is available.
3. Click on the three dots (ellipses) that appear next to the view name and select View Summary.
4. Click on each of the tabs to see what information is available, including the general information about the view,  which fields it contains, its raw SQL, and any reports using it, which can be useful if you need to edit an existing view and wish to check any reliant content.
5. Click on the X in the corner of the dialog box to close it.
6. Click Publish.
   KPI Studio will now let you publish this view, although if you wish to save it as a draft, that's also possible.
7. 
8. To save the draft, click on the drop down tot he right of the Save and Publish button and select Save as Draft.
   
9. To publish your new view, click Save and Publish.
   KPI Studio will save and close the view, and return you to the Browse page, where your view icon will turn from grey (draft) to green (published).
10. Your view may now be used to create new reports and charts.