--- title: "Creating a View" canonical: "https://wiki.yellowfinbi.com/space/yfcurrent/2196163/Creating%20a%20View" format: markdown --- > Macro (anchor) > Macro (toc) ## Overview 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](https://yellowfin.atlassian.net/wiki/spaces/yfcurrent/pages/2199460) 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 > Macro (inline-media-image)   in the top right corner of your screen 2. Scroll down to the **View** menu item and click on it > Macro (inline-media-image) The **Create New View **dialog box will appear, where you can choose an existing source for your new view, or create a new one > Macro (inline-media-image) 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 > Macro (inline-media-image) 4. Click on the radio button for **Multiple Tables** A warning message will appear > Macro (inline-media-image) 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 > Macro (inline-media-image) 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 > Macro (inline-media-image) [top](#CreatingaView-top) ## 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** > Macro (inline-media-image) 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](https://yellowfin.atlassian.net/wiki/spaces/yfcurrent/pages/2197391) for more information. [top](#CreatingaView-top) ## 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 5. Check the checkboxes for **AGEATCAMP**, **AGEGROUPATCAMP**, **COST**, **DEMOGRAPHIC**, **INVOICEDAMOUNT, ****INVOICEDATE**, **INVOICEESTIMATE** and **PERSONID** > Macro (inline-media-image) 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 > Macro (inline-media-image) 7. Repeat steps 1 to 5 to select the following fields for the **CAMP** table: **CAMPDEMOGRAPHIC** **CAMPDESCRIPTION** **CAMPREGION** **ISOCODE** 8. Repeat steps 1 to 5 to select the following fields for the **PERSON** table: **DATEOFBIRTH** **GENDER** **ISOCODE** **REGION** Your tables and columns are now ready to be prepared. > See [Table Properties](https://yellowfin.atlassian.net/wiki/spaces/yfcurrent/pages/2200130) for more information. [top](#CreatingaView-top) ## 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 Location**, **Athlete Payment**, and **Camp** > Macro (inline-media-image) 7. Click on **Add Field Folder** to display a new empty field and type **Date Fields** > Macro (inline-media-image) 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): **AGEATCAMP**, **AGEGROUPATCAMP** 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 [top](#CreatingaView-top) ## 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](https://yellowfin.atlassian.net/wiki/spaces/yfcurrent/pages/2201784). 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](https://yellowfin.atlassian.net/wiki/spaces/yfcurrent/pages/2197041) for more information. [top](#CreatingaView-top) ## 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** > Macro (inline-media-image) 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 [top](#CreatingaView-top) ## 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 [Natural Language Query - Guided and AI-Enabled](https://yellowfin.atlassian.net/wiki/spaces/yfcurrent/pages/2197531),  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](https://yellowfin.atlassian.net/wiki/spaces/yfcurrent/pages/2196681) for more information. [top](#CreatingaView-top) ## 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](https://yellowfin.atlassian.net/wiki/spaces/yfcurrent/pages/2196163) end-user experience, and for use with [time series](https://yellowfin.atlassian.net/wiki/spaces/yfcurrent/pages/2205042) charts. Date hierarchies are created by using granularity to dictate day/week/month/quarter/year etc. [Read more about date granularity](https://yellowfin.atlassian.net/wiki/spaces/yfcurrent/pages/2227273) 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. | **Comonent** | **Description** | **Example** | | --- | --- | --- | | Field | This 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 Function | This component lets you choose the granularity of your date field, from day name through to year end date. | Month Start Date | | Format | This 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 Folder | This 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 [top](#CreatingaView-top) ## 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](https://yellowfin.atlassian.net/wiki/spaces/yfcurrent/pages/2200335) for more information. [top](#CreatingaView-top) ## 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. [top](#CreatingaView-top) ## Further information For more information around the creation of Views in Yellowfin see the [Views Overview](https://yellowfin.atlassian.net/wiki/spaces/yfcurrent/pages/2199460) section of the wiki.