Like what you see? Have a play with our trial version.


A view is a metadata layer used by Yellowfin 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

See Views Overview for more information.

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 Create button   in the top right corner of your screen
  2. Scroll down to the View menu item and click on it

    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, Ski Team, in these steps
  3. Click on Ski Team from the list of data sources
    The next step will appear, where you can choose the components of your new view

  4. Click on the radio button for Multiple Tables
    A warning message will appear

  5. Click on the Create View button to launch the View Builder window
  6. Double-click on the text New View in the top left corner
  7. Type Tutorial View  and then press your tab button to rename the new view

    Yellowfin 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 on the Publish button 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 Yellowfin
  10. Click on the first dropdown that displays - - Select - - and select Tutorial from the list
  11. Click on the next dropdown that displays - - Select - - and select Training from the list

  12. If you typically use optional tags and wish to add some now, click on + Add tags and add your tags
  13. Click on the Save As Draft button to save your view in draft format
    Yellowfin will close the draft and take you back to the Browse page, where you can see your new draft view

Creating entity relationships

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 ATHLETEFACT and drag. and drop it onto your canvas
  3. Repeat step 1 to add the CAMP and PERSON tables to your canvas
  4. Click on the Create New Join  tool on the ATHLETEFACT 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: Person
  6. In the Join Details section, click on Detect Joins to prompt Yellowfin to automatically detect the join
  7. Click on the Save & Close button
    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 ATHLETEFACT and CAMP where CampId = CampID
  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

See Model for more information.

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 for the AHTLETEFACT table to expand the list of table columns
  3. Click on the Edit Table Properties tool to see the table's properties displayed in the panel on the right of the screen
  4. Click on the expand tool for the Columns section to see the list of columns

  6. Click on any Edit Table Properties tool 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 CAMP table:
  8. Repeat steps 1 to 5 to select the following fields for the PERSON table:


    Your tables and columns are now ready to be prepared.

See Table Properties for more information.

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. Click on the Prepare tab

    Your view will change to a tabular layout, showing you the contents of each column
  3. Click on the Create tool in the bottom left corner, then click on Add/Edit Folders from the list

    The Field Folders dialog box will appear, where you can create new folders, delete existing folders and and add fields to existing folders
  4. Hover over the Dimensions folder
    The burger bun on the left lets you reorder the folder by dragging and dropping, and the red X on the right lets you delete the folder
  5. Click on the Select dropdown and click on Athlete
    The column will now appear in the list of field folders
  6. Repeat step 5 to add Athlete LocationAthlete Payment, and Camp
  7. Click on Add Field Folder to display a new empty field and type Date Fields

  8. Click on the Add button to add the new field folder to your field folder list
  9. Click on the Submit button to 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
  10. 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
  11. Click on the ATHLETEFACT table to expand it
  12. Drag and drop the following fields individually to the Athlete folder (or use your usual keyboard/mouse combination multi-select them): AGEATCAMPAGEGROUPATCAMP and DEMOGRAPHIC 
  13. Click on the Athlete field folder to expand it
    The three fields you just added will be listed
  14. Repeat the last few steps to add COST, INVOICEDESTIMATE and INVOICEDDATE to the Athlete Payment folder
  15. Repeat the last few steps to open the PERSON field folder and add REGION and ISOCODE to the Athlete Location folder
  16. Repeat the last few steps to to add DATEOFBIRTH and GENDER to the Athlete folder

    The fields added to field folders in Tutorial View will now be accessible in the Report Builder
  17. 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. Use your mouse to scroll to the right of the displayed fields and locate the Athlete Location section
  3. Click on the field name ISOCODE to select it
  4. Type Athlete Country and then press your tab key to confirm the field label update
  5. Click on the triangle next to the field label to display a dropdown 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
  6. Click on the Details section to expand it, and read through the options available
  7. Click on the Format section to display the available formatting options
  8. Click on the Format dropdown and take a look at what options are available 
  9. Click on Reference Code from the list
    A new option will become available for you to select what type of reference code should be applied
  10. Click on the Reference Type dropdown to display a list of available reference codes associated with the Ski Team view, and select Country
  11. Click on the X in the top right corner of the dialog box to close the box (your changes are dynamically saved)
    The Reference Code field will now display country names instead of country codes
  12. Locate the column within Athlete Payment for INVOICEDDATE and click on it to change its name to Invoiced Date
  13. Locate the column within Athlete Payment for INVOICEDESTIMATE and click on it to change its name to Invoice Estimate

See Prepare for more information.

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 colours.

  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 the COST field within the Athlete Payment section and click on the dropdown, then click on 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 at the bottom of the screen, then click on Calculated Field

    The Calculated Field dialog box will appear
  3. In Calculated Field Name, type Profit
  4. Click on the Field Folder dropdown and select Athlete Payment
  5. From the Search dropdown, start typing invoice and click on INVOICEDAMOUNT to select it for use in the calculation
  6. From the collection of calculation tools , click on the minus tool to add a minus sign to your calculation
  7. From the Search dropdown, locate and click on the COST field
  8. Click on the Validate button to check that your calculation has no errors
    If there are no validation issues, a successful validation message will be displayed
  9. Click on the Save button to save this new calculated field
  10. Scroll to the left of your field table to check you can see the new field at the end of the Athlete Payment section

See Calculated Fields for more information.

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). Yellowfin's tutorial database, Ski Team, already has a date hierarchy set up for INVOICEDDATE, so we'll use a duplicate of this field that we created in one of the previous steps and we'll create a new set of date fields for a hierarchy.

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

FieldThis component dictates which field from your view should be used for your new date hierarchy. It must use date format to be available for selection from the dropdown.Invoiced Date
Date FunctionThis component lets you choose the granularity of your date field, from day name through to year end date.Month Start Date
FormatThis component lets you choose how to represent this date information, such as a short day name or a business week number, on charts and in tables and any other content within Yellowfin.Month Name
Field FolderThis component provides the list of available view fields so you can store your new date field in an appropriate location
  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 dropdown for Field and click on Invoiced Date
  4. Click on the dropdown for Date Function and select Month Start Date
    You will notice that each time you select something from the dropdown, a sample date is displayed on the right
  5. Click on the dropdown for Format and select Month Name
  6. Click on the dropdown 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 Invoiced Date from the Athlete Payment field folder to the Date Fields field folder to complete the hierarchy

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)

    Your drill down hierarchy is now ready to be used in reports.

See Drill Down Hierarchies for more information.

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 View Info tool to display the View Summary dialog box
  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 on the Publish button
    Yellowfin will now let you publish this view, although if you wish to save it as a draft, that's also possible
  7. To save the draft, click on the dropdown tot he right of the Save and Publish button
  8. To publish your new view, click on the Save and Publish button
    Yellowfin 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)

    Your view may now be used to create new reports and charts.

Further information

For more information around the creation of Views in Yellowfin see the Views Overview section of the wiki.