--- title: "Creating a View" canonical: "https://wiki.yellowfinbi.com/space/USER73Plus/1376584/Creating%20a%20View" format: markdown --- Overview The 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 Report Building. 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 include: Relationship Entity Diagram  - selecting the tables you need from your database and defining how data in these tables are joined View Field Selection  - defining which fields you wish to make available from these tables and providing metadata for them. See  Views  for more information.   Create   1. Start the View creation process by using one of these methods:       You will now see the  New View  lightbox. Left Side Navigation Top Right (with Left Side Navigation enabled) Toolbar Admin Console     2. From here you will need to either;   We're going to select Ski Team   from the source type list. 3. You will now see the basic parameters required for your view. We are going to look at a more complex view, so select the  Multiple Tables  option. 4. Click on the  Edit View option in order to access the main view builder. 5. The view builder page will now open. You will see a list of tables in the Database Tables panel on the left hand side and the Table Options on the right hand side of the canvas. 6. Update the view Name  and description in the View Options menu as shown here. Call this view Tutorial View . Enter the View Description : This will be used to learn about Yellowfin .   Entity Relationship The Entity Relationship is one of the key components of the view builder. This allows you to define all the key relationships between your selected database tables.   1. From the table list on the left of the screen, drag the following tables onto your canvas: AthleteFact , Person , and Camp You should now have three tables on your canvas as displayed on the right. 2. On the AthleteFact table click the  join link. This will open the join pop-up. Create a join between the AthleteFact Table and the Person Table. Join From: AthleteFact Join Type: Inner Join Join To: Person Join logic: PersonID Equal to PersonID 3. Click the Add button to add to the join list. You should now see the join logic as depicted on the right. 4. Click the Save & Close button to save your join.   The join will now be displayed as a line between your AthleteFact and Person tables. Hovering over the join icon will display the join logic in a tooltip.   5.  Create another Inner Join from AthleteFact to Camp where CampID = CampID You can move your tables around the canvas to make the diagram easier to read if needed. Click the Save & Close button to save your join. The join will now be displayed as a line between your AthleteFact and Person tables. Hovering over the join icon will display the join logic in a tooltip. See Model for more information.   Selecting Fields Select fields that you wish to make available to your end users for reporting. Only columns selected from each table in your Unattached list will be available for reporting. 1. Click on the expand icon next to a table name to expand it. 2. Click the Properties link on the AthleteFact table. The table properties will now be displayed in the View Options panel. 3. Click the Columns section link to open the options. A set of columns from the AthleteFact table will be displayed. Select the AgeAtCamp , AgeGroupAtCamp , Cost , Demographic , InvoiceEstimate , InvoiceDate , and PersonID columns. 4. Once you have selected these, click on the Properties link again to update your diagram. 5. The columns you selected should now appear in bold on your table (as pictured). 6. Repeat the last step for each table. Camp: CampDemographic , CampDescription , CampRegion , and ISOCODE Person: DateOfBirth , Gender , Region , and ISOCODE 7. Click on Prepare  in the navigation bar to continue to the data preview page. See  Table Properties  for more information.   Field Categories & Meta Data Initially, fields selected from the previous step will all be in the  Unattached  panel in folders that represent the tables that they originated from. These fields have not had meta data associated with them and  cannot  be used by your report writers. You must assign fields to folders in the  Available Fields  panel. The reason you do this is to organise you fields in a way that is logical for the Report Writer, giving you the chance to group them differently than the table structure in the database.   1. First of all, make sure you have the categories you want to use to divide your fields. Click on the Add/Edit Folders  link from the Create menu.   2. Add the  Athlete ,  Athlete Location ,  Athlete Payment , and  Camp folders .  3.  Click Submit to add the folders. 3. Click the Add Fields option in the Create menu in order to add more fields to your folders.   4. From the  AthleteFact  table either select and drag the fields individually or use shift to click on each of the  AgeAtCamp ,  AgeGroupAtCamp , and  Demographic  fields and drag these into the  Athlete  folder. On completion you will note that the column names are now in bold indicating that they have been added to the view. 5. Now follow the same steps as above and put all the fields in their correct folders.     Cost , InvoicedEstimate , and InvoicedDate fields > Athlete  Payment folder.   Region and ISOCODE  fields > Athlete Location  folder.   Person fields > Athlete folder. 6. To update the field name to provide a more user friendly name – click the ISOCODE field heading. Change the business name of the field to Athlete Country . 7. This field will use a Reference Code to convert ISO Country Codes to their respective names. To set this up, click on the drop down menu on the field and choose the Edit Format option.       8.  Open the Format section of the menu. 9. Set the Format option to   Reference Code . In this case you will use an existing type that we have set up for a sample. See  Reference Code Use  for more information. 10. Select  Country   from the drop down menu. 11. Click on the close   button to commit your changes.   12. The Athlete Country column will now be updated.  See  Prepare  for more information.   Field Format The format options will provide the default for how the field will be used on the report. A user will still be able to change a format for a specific report. 1. Click on the Field Settings menu to access formatting options for all your fields. 2. Locate the Cost field in the Athlete Payment folder and click on it. 3. Expand the  Format section. 4. Add a prefix of $ and set Decimal Places to 0 . 5. Apply a colour for chart display if required. 6. Close the menu to apply your changes. See Field Settings for more information.   Calculated Fields In addition to fields from your database you can create calculated fields, pre-defined filters, and date hierarchy fields. Calculated Metric This type of calculated field allows you to build a calculation that will return a numeric value as the result. In this example we will aim to calculate profit by subtracting cost from invoice figures. 1. Click on the Create button and select the Calculated Field option.   2. You will now see the Calculated Field window. Set the Calculated Field Name to Profit . 3. Set the Field Folder  to Athlete Payment. 4. Leave the Formula Type as Simple . 5. From the Select Field drop down search for  INVOICEDAMOUTNT and click it to build it into the calculation. 6. Now click the - (minus) button directly below the Select Field drop down. 7. Select the COST field to finish this simple calculation. 8. Click the Validate button in order to let Yellowfin validate your calculation. You should see a SQL is valid message displayed above the builder if successful. 9. Click Save  to save the field and make it available for use in reports. 11. You will now see the Profit calculated field in the Athlete Payment category and it will have a green icon instead of the usual metric icon to show that it's a formula. See  Calculated Fields  for more information.   Date Hierarchy Fields Date Hierarchy calculated fields allow you to build levels of a hierarchy based on a single date field in your database. This can then be used to define Drill Down hierarchies, or for other purposes in reports.   1. First you will need to ensure you have a date field to use with the hierarchy templates. We've already got the InvoiceDate field in the Date Fields folder, so this has been taken care of. You are now going to use the Date Function builder to create the other levels of your hierarchy, adding them to the same folder as your date field - this is important for when you build the hierarchy later. Start with the Month Start Date . The reason we're using the Month Start Date is so that the field is still a date format, even though we can change the display to be just the Month component. This means we can use it for Time Series charts and other date related functionality. 2. Click on the Create button and select the Date Function option. 3. Select the INVOICEDDATE field from the Date Fields folder to base the function on.   4. Set the Date Function field to be Month Start Date . 5. Set the format to be Month Name . This will mean that the name of the date's month will be displayed in reports and charts, but underneath it will still be a date value. 6. Click Save to complete the function..   7. Repeat the same process, this time creating a  Year field based on  Year Start Date  of  Invoiced Date .   8. You will now have three levels on which to create a date hierarchy (see the next section).   Drill Down Hierarchy The hierarchy allows report users to drill down a dimensional hierarchy by limiting the result set as they select one level to the next. For example drill from Year (2014) to Month (August) etc.   When creating the hierarchy, you need to start from the top level and work your way down. In this example we are creating a Year > Month > Date hierarchy, so we will start with the Year field at the top. 1. Click on the menu on your Year field and select the Drill To option, then click on the field you want to drill down to ( Month Start Date ) 2. You will now notice that there is a link between the Year and Month fields. This lets you know there is a hierarchy link defined between the two fields. 3. Next repeat the process by clicking on the Month  field drop down menu, navigate to Drill To , and specifying the Invoiced Date field. 4. You will now see there is a 3 level hierarchy defined. You wont have to define Drill Down options on the bottom level (Invoiced Date). 5. Once you rename the fields, you'll have a clean hierarchy, ready for use in a report.   See  Drill Down Hierarchies  for more information.   View Summary & Saving 1. From the any step of the builder you can click on the View menu and save your view. 2. Select the Save option. 3. Update the view name and description if required. Specify a content folder and sub folder to store the View in. 4. Click Save and Publish to complete.   Further Information For more information around the creation of Views in Yellowfin see the  Views  section of the wiki.