Creating a view of database tables and fields

You can use a view in Remedy Smart Reporting to hide the complexity of database structures from report writers. Using view, you can define which database columns are available for report building. If fields come from multiple tables, joins are required (the business logic that links rows in a table together).

The two major steps in creating a view include:

1. Creating a relationship entity diagram—Selecting the tables that you need from database and defining how data in these tables are joined.

2. Selecting view fields—Defining which fields you want to make available from these tables and providing metadata for them.

When you use newly created semantic views (not the out-of-the-box semantic views) in a virtual table, the Available Fields list in the virtual table might not display the fields from the newly created semantic view. To view the semantic view fields in a virtual table, the Remedy Smart Reporting super admin user gives permission to the Skip Schema Check field. 

For more information about virtual tables, see Virtual tables.

To provide Skip Schema Check permission to the Public Content Writer & Collaborator - Advanced role

1. Log in to Remedy Smart Reporting as a super admin user.
2. Select Administration > Admin Console.
3. Click the Roles panel.
4. Click the list next to the Public Content Writer & Collaborator - Advanced role, and select Edit.
5. In the Functions section, click the Data Sources & Views subsection to expand it.
6. Select the Skip Schema Check field.
7. Click Save.

To create a view from a single table

1. From the Create menu, select View.
   
2. On the Create New View window, select your data source.
   The basic parameters required for your view are displayed.
3. Select Single Table.
4. Select the table for creating view.
5. Enter a view name and description.
6. Click Create Analysis.
   The view is saved, and the report builder is opened.
   When you save a view, you can make it active. Report writers can use only active views to create reports

To create a view from multiple tables

1. From the Create menu, select View.
2. On the Create New View window, select your data source.
   The basic parameters required for your view are displayed.
3. Select Multiple Tables.
4. Click Create View.
   The view builder page appears. A list of tables in the Database Tables panel is on the left side, and the Options are on the right side of the canvas.
5. Drag the tables to the canvas, and create entity relationships as described in the following steps.
6. Set the table options for each table.
   1. Click the table properties icon on the table on the canvas.
      
   2. Open the panels under Table Options on the right side of the canvas, and make the required changes.
   3. To select the fields that you want to make available to your end users for reporting, open the Columns panel, and select the necessary check boxes.
7. Click the Prepare tab, and format the fields.
   The fields are listed in a table, and when you click the drop-down list, you can select from many options:
   
8. To test your view, click the Test tab.
9. To save and publish your view:
   1. Click the Publish tab.
   2. Enter a name and description.
   3. To store the view, select a category (folder) and subcategory (subfolder).
   4. Click Save and Publish.
      When you save a view, you can make it active. Report writers can use only active views to create reports.

To create virtual table views

1. Create a new view by using the + symbol on the top right side. 
   For more information, see Creating a view of database tables and fields.
2. From the list of tables on the left side, drag the Virtual Table to the Entity Relationship window.
3. Add the AR JDBC SQL for the table.
4. Define an alias name for each column in the SQL as follows: 
   SELECT DISTINCT `HPD:Help Desk`.`Closed Date` as Closed, 
`HPD:Help Desk`.`Incident Number` as Incident, 
`HPD:Help Desk`.`Status` as status 
FROM `AR System Schema`.`HPD:Help Desk`


5. Click Validate SQL and then Save.
6. Publish the view.

To define the relationships among database tables

The entity relationship is one of the key components of the view builder. With this relationship, you can define all the key relationships between the selected database tables.

1. While creating a view from multiple tables, drag the required tables onto your canvas.
2. To join a table, click the join icon on the table on the canvas.
   
   The New Join dialog box appears.
   
3. Select values for the Join Type, Cardinality, and Join To fields.
4. Configure Join Details.
5. Click Save & Close.
   The join is displayed as a line between the tables. To display the join logic, click the join icon on the line.

To select fields

You can select the fields that you want to make available to your end users for reporting. Only columns selected from each table will be available for reporting.

1. Click the expand icon next to a table name.
2. Click the Edit Table Properties icon on the required table.
   
   
   The Table Options panel is displayed on the right side.
3. From the Table Options panel, click the Columns section link to open the options. 
   A set of columns from the table will be displayed.
   
4. Select the required columns and click on the Edit Table Properties again to update the diagram.
   The columns that you selected now appear in bold on the table.
   
5. Repeat the last step for each table.
6. To continue to the data preview page, in the navigation bar, click Prepare.
   For more information, see Yellowfin documentation at  Table Properties .

Adding Field Folders and Fields

Initially, fields that you select are available in the Unattached panel in the table folders. These fields do not have meta data associated with them and report writers cannot used these fields . You must assign fields to folders in the Available Fields panel. This helps you to organize your fields in a way that is logical for the Report Writer. You can group them differently than the table structure in the database.

Before you begin, make sure you have the categories you want to use to divide your fields. You can add or edit a folder either from Admin Console or from a view builder.

To add a folder through Admin Console

1. On the right panel of the Admin Console, click Field Folders.
   
2. Click Add.
3. Enter the folder name and click Save.

To add a folder through a view builder

1. On the view builder, click Prepare.
2. In the left navigation pane, click the plus (+) icon at the bottom of the pane and click Add/Edit Folders.
   
3. Select folder from the drop down list or click Add Field Folder to create a new folder.
   
   
4. Enter the folder name and click Add.
5. Click Submit.

The new folder appears on the left pane.

To add fields to the folders

1. In the left navigation pane, click the plus (+) icon at the bottom of the pane and click Add Fields.
2. From the Table Fields pane, either select and drag the fields individually or use the Shift option to click on each of the required fields to drag these into the required folder.
   
   
   The column names now appear in bold indicating that they have been added to the view.
   Repeat this step to put all the fields in the respective folders.
3. To update the field name, click the field heading.
4. Click the list on the required field and select the Edit Format option.
5. Open the Format section of the menu.
6. Set the Format option to Reference Code. 
   For more information, see  Reference Codes .
7. Click Close to commit your changes and update the column.

Virtual tables

You can use virtual tables to insert an SQL statement into a view that brings back a set of derived fields and is used as a logical table in the view builder.

Virtual tables have the following advantages:

• Reduce the amount of data returned in a query
  In a virtual table, you can include calculations and functions that are supported by AR JDBC. These operations are performed before the result set is returned to a report, which saves time and reduces the report complexity.
• Reduce the maintenance of database summary tables
  In some cases, virtual tables can replace the aggregate tables stored in a database. These aggregate tables are costly to maintain. Virtual tables can return the same data and provide real time data analysis.

Example of SQL query for virtual tables

Multiple tables such as Incident, Change, and so on contain the relationship data for a Remedy ticket. Instead of using multiple tables in a view and joining these tables, you can use an SQL query as shown in the following example directly in a virtual table and build reports on it:

SELECT DISTINCT
`HPD:Associations`.`Request ID01`, 
`HPD:Help Desk`.`Incident
Number`, 
`HPD:Help
Desk`.`Description`, 
`HPD:Help Desk`.`Company`, 
`HPD:Help Desk`.`Assigned
Group`, 
`HPD:Help Desk`.`Assignee`,
 `HPD:Help Desk`.`Priority`,
 `HPD:Help Desk`.`Status` 
FROM `AR System Schema`.`HPD:Help Desk` LEFT 
OUTER JOIN `AR System Schema`.`HPD:Associations` ON ( `HPD:Help Desk`.`Incident
Number` = `HPD:Associations`.`Request ID02` ) 
WHERE ( `HPD:Associations`.`Association
Type01` IN ('Caused by') AND `HPD:Associations`.`Request Type01` IN
('Infrastructure Change') )

For more information on creating and editing virtual tables, see the Yellowfin documentation at  Virtual tables .

Calculated Fields

You can use calculated field to build a calculation that will return a numeric value as the result. For more information, see Performing calculations on fields in a report.

Date Hierarchy Fields

You can use Date Hierarchy calculated fields 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. Ensure that you have a date field to use with the hierarchy templates. In our example, we will consider the InvoiceDate field in the field folder.

1. 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 to build the hierarchy later.
2. 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.
3. Click on the plus (+) icon at the bottom of the pane and click Date Function.
4. Select the INVOICEDDATE field from the Date Fields folder to base the function on.
5. Set the Date Function field to be Month Start Date.
6. Set the format to be Month Name. This means that the month will be displayed in reports and charts, but underneath it will still be a date value.
7. Click Save.
8. Repeat the same process, this time creating a Year field based on Year Start Date of Invoiced Date.

You will now have three levels on which to create a date hierarchy (see the next section).

For more information, see the Yellowfin documentation at Date Functions .

Drill Down Hierarchy

You can use this feature 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).
   A link between the Year and Month fields is displayed. This lets you know there is a hierarchy link defined between the two fields.
2. Repeat the process by clicking on the Month field drop down menu, navigate to Drill To, and specifying the Invoiced Date field.
   A 3 level hierarchy is defined. You don't have to define Drill Down options on the bottom level (Invoiced Date). Once you rename the fields, you will have a clean hierarchy, ready for use in a report. For more information, see  Drill Down Hierarchies  .

Where to go from here

Editing, deleting, or rolling back a view