This documentation supports the 18.05 version of Remedy IT Service Management Suite.

To view the latest version, select the version from the Product version menu.

Creating a view

The View is a metadata layer used by BMC Remedy Smart Reporting 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:

  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 want to make available from these tables and providing metadata for them.

This section explains the following topics:

For BMC Remedy Smart Reporting version 9.1.03 and later

The following sections are applicable for BMC Remedy Smart Reporting version 9.1.03 and later.

Before creating a semantic view

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 may not display the fields from the newly created semantic view. To be able to view the semantic view fields in a virtual table, you need to obtain the Skip Schema Check permission from BMC Remedy Smart Reporting super admin (siadmin) before creating a semantic view. As users with Public Content Writer & Collaborator - Advanced role can create semantic views, siadmin must provide the Skip Schema Check permission to the Public Content Writer & Collaborator - Advanced role.

If you are using a custom role which allows you to create semantic views, you must request your siadmin to provide the Skip Schema Check permission to the custom role before creating semantic views.

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

  1. Log on to BMC Remedy Smart Reporting as a super admin (siadmin).
  2. Navigate to Admin Console > Roles.
  3. Click the Roles panel to expand it.
  4. Click the drop-down list next to the Public Content Writer & Collaborator - Advanced role. 
  5. To update the permissions, click Edit.
  6. In the Functions section, click the Data Sources & Views sub section to expand it.
  7. Select the Skip Schema Check field to attach the permission to the Public Content Writer & Collaborator - Advanced role.
  8. Click Save.

To create a view

  1. Use one of the following methods:
    • Left Side Nav—Open the left side navigation panel, click Create and select the View option.
    • Top Right Button—Click Create and select the View option.
    • Toolbar—Click on the Create link in the toolbar and select the View option.
    • Admin Console—Navigate to the Admin Console, open the Views section, click Add.
  2. On the New View lightbox, either select your data source or create a new source.
    The basic parameters required for your view are displayed.
  3. Click Edit View to access the main view builder.
    The view builder page is displayed along with 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.
  4. Update the view name and description in the View Options menu.
  5. Enter the View Description and click Create Analysis.
    The view is now created.

Entity Relationship

The Entity Relationship is one of the key components of the view builder, which allows you to define all the key relationships between the selected database tables.

  1. From the table list on the left of the screen, drag the required tables onto your canvas.
  2. On the AthleteFact table, click the join link to open a join pop-up window to create a join between the tables and enter the following information:
    • Join From
    • Join Type
    • Join To
    • Join logic
  3. Click Add to add to the join list.
    The join logic is displayed on the right.
  4. Click Save & Close to save your join.
    The join is now displayed as a line between the tables.You can hover over the join icon to display the join logic in a tooltip format.
    For more information, see Model.

Selecting Fields

You can select the fields that you want 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 required table to display the table properties in the View Options panel.
  3. Click the Columns section link to open the options.
    A set of columns from the table will be displayed.
  4. Select the required columns.
  5. Click on the Properties link again to update the diagram.
    The columns that you selected now appear in bold on the table.
  6. Repeat the last step for each table.
  7. Click Prepare in the navigation bar to continue to the data preview page.
    For more information, see Table Properties.

Field Categories & Metadata

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. This helps you to organise your 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.

Before you begin, make sure you have the categories you want to use to divide your fields.

  1. Click on the Add/Edit Folders link from the Create menu.
  2. Add the required folders.
  3. Click Submit to add the folders.
  4. Click the Add Fields option in the Create menu to add more fields to your folders.
  5. From the table, 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.
  6. Follow the above steps to put all the fields in their correct folders.
  7. Click the field heading to update the field name.
  8. Click on the drop-down list on the required field and select the Edit Format option.
  9. Open the Format section of the menu.
  10. Set the Format option to Reference Code.
    For more information, see Reference Code Use.
  11. Select the required value from the drop-down list.
  12. Click Close to commit your changes and update the column.

Field Format

The field format option decides how the field will be used on the report. Follow the steps given below to change a format for a specific field on a report.

  1. Click on the Field Settings menu to access formatting options for all your fields.
  2. Locate and click on the required field in the respective folder.
  3. Expand the Format section. 
  4. Add the required values for the Prefix and Decimal Places option.
  5. If required, apply a colour for chart display.
  6. Close the menu to apply your changes.
    For more information, see Field Settings.

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 BMC Remedy Smart Reporting 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.

For BMC Smart Reporting versions earlier than 9.1.03

The following sections are applicable for the BMC Remedy Smart Reporting versions earlier than 9.1.03.

Recommendations

Following are the recommendations for creating views:
  • Create multiple small views specific to use cases instead of one large view that covers all use cases.
  • Plan for view objects like forms and fields that will be included in the new view.
  • To make a view more useful, give business names to the view objects.

Following are the recommendations for modifying an existing view:

  • BMC strongly recommends that you do not delete the current objects, joins, forms, fields, or tables from the out-of-the-box views. BMC will continue to update out-of-the-box views with every release.
  • If you choose to modify out-of-the-box views, create and save a copy of the out-of-the-box view, and then make changes to the copy.
    For example, create a copy of the Incident Management view, call it Incident Management - CUSTOM, and make changes to the custom view. 
  • Although you can create multiple custom views for each out-of-the-box view, BMC recommends that you combine your changes into as few customized views as possible.
  • When you create new objects in a view, always use unique names; for example, Object_X or X_Object.

Also, see Implications of customized views on reports and dashboards and Implications of customized views on an upgrade.

This section will walk through the initial steps of creating the view, including selecting the data source connection to base it on and the builder type to use.

To create a view

Entity relationship

Entity relationship is one of the key components of the view builder that allows you to define all key relationships between your selected database tables.

To add an entity relationship

 Click to Expand Instructions

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 Save & Close 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

See Drag and drop view builder 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.

 Click to Expand Instructions

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. Replicate the last step for each table.

Camp: CampDemographic, CampDescription, CampRegion, and ISOCODE

Person: DateOfBirth, Gender, Region, and ISOCODE

7. Click on Step 2 in the navigation bar to continue to the View Fields page.

See Table properties for more information.

Field categories and 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 categories (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.

 

 Click to Expand Instructions

1. First of all, make sure you have the categories you want to use to divide your fields. Click on the Edit Categories link in the Available Fields section.

2. Add the Athlete, Camp, Payment, and Time categories and click the Save & Close button.

 

3. Click the expand arrow next to the folder names to see the columns you have selected from each table.

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 categories. Cost, InvoicedAmount, and InvoicedDate fields > Payment category. InvoiceDate field > Time category. Camp fields > Camp category. Person fields > Athlete category.
6. To update the field name to provide a more user friendly name – double click the ISOCODE field. Change the business name of the field to Athlete Country.

7. This field will use an Org Ref Code to convert ISO Country Codes to their respective names. To set this up, click on the Format tab.

8. Set the Format option to Org Ref Code. In this case you will use an existing type that we have set up for a sample. See Organisation Reference Codes for more information.

9. Select Country from the drop down menu.

10. Click Save to commit your changes.

11. The ISOCODE column will now be updated in the Athlete folder. This method is used to provide business terms for all your attributes.

 See View fields 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.

 Click to Expand Instructions

1. Double click on the Cost field to open it in the edit window.

2. Click on the Format tab.

3. Add a prefix of $ and set Decimal Places to 0.

4. Select SUM as the default aggregation.

5. Apply a colour for chart display if required.

6. Click Save to commit your changes.

 

 See Field format 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.

 Click to Expand Instructions

1. In the Unattached panel open the Calculated Fields folder and then the Formula Builder folder.

2. Drag the Metric field into the Athlete Payment folder.

  

3. You will now see the Definition tab. Set the Business Name to Profit and enter the calculation logic Invoiced - Cost into the Description - this is useful for users that want to know the formula used in a calculation.

4. Click the Formula tab to define the calculation.

5. From the Select Field drop down choose INVOICEDAMOUNT and click + Add button next to 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 and click the + Add button to finish this simple calculation.

8. Click the Click here to test this formula link in order to let BMC Remedy Smart Reporting validate your calculation. You should see a Formula Successful message displayed above the builder if successful.

9. Click Activate 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 Semantic view 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.

 Click to Expand Instructions

1. First you will need to ensure you have a date field to use with the hierarchy templates. We've already added the InvoiceDate field into the Time folder, so this has been taken care of.

2. Drag each of the levels one by one into the same folder as your date field - this is important for when you build the hierarchy later. Start with the Year Start Date. The reason we're using the Year Start Date is so that the field is still a date format, even though we can change the display to be just the Year component. This means we can use it for Time Series charts and other date related functionality.

 

3. Rename the field to Year and the Description to Invoiced Year. This lets the user know that the field is based on the Invoiced Date, avoiding incorrect use.

4. Set the field type to Dimension on the Definition tab - the field has to be a dimension in order to use it in a Drill Down hierarchy as metrics are not deemed compatible.

5. Set the field to base the Year on to INVOICEDATE, found at the very bottom of the Definition tab.

6. Activate the field.

7. Repeat the same process, this time creating a Month field based on Month Start Date of Invoiced Date.

 

8. Double click your INVOICEDATE field in order to rename it to Invoiced Date and change the field type to Dimension in preparation for building the hierarchy.

9. Activate the field.

 

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.

 Click to Expand Instructions

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. Double click the Year and navigate to the Hierarchy tab.

2. Enable Drill Down and select the Month field as the Drill To option. Activate the field.

 

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 double clicking on the Month field, navigating to the Hierarchy tab, enabling Drill Down, and specifying the Invoiced Date field as the Drill To option.

4. Activate the field.

  

5. 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).

 

See Drill Down hierarchies for more information.

View summary and saving

 Click to Expand Instructions

1. From the View Fields page click the last step to continue to the Summary page.

 

2. You can explore the tabs to see example data and the SQL that BMC Remedy Smart Reporting generates.

3. Click the Activate button to save you view and activate it for use.

4. You will now see on the general tab that your Tutorial view is Active. It is now ready to report off.

5. Click Close to close the view and return to you view list.

 

 See Semantic view summary for more information.

Related topic

For more information around the creation of Views in BMC Remedy Smart Reporting see the BMC Remedy Smart Reporting Semantic Views section of the wiki.

Was this page helpful? Yes No Submitting... Thank you

Comments