Sapphire and Business Objects Tips and Tricks V' Nichole Rowland Bret J' Boyer

1
Sapphire and Business Objects Tips and Tricks
V. Nichole RowlandBret J. Boyer
2
Agenda

• Browser Time-Out Warning
• Displaying several fields from related tables
• Tips for using roles to configure security
• Business Objects Tips Tricks
• Flexible Report Headers
• Relative Retention Times formatted like (0.40)
• Concatenate Rows in a Table
• When to make object a dimension, detail, or
  measure
• Report showing Sapphire Report Configuration
  Details
• How to Warn of Partial Results on Report
• How to include SQL statement on Report

Note Only artificial data was used for this
presentation.
3
Browser Time-Out Warning

• Issue Users were unhappy because they had no
  warning before they were logged out of the system
  and lost unsaved data.
• Key User Requirements
• Prevent the user from being logged out based on
  mouse clicks.
• Provide user alert that they may be logged out
  with option to remain connected.
• Allow a timed-out user to log back into the
  system without losing any entered but unsaved
  data.

Note Only artificial data was used for this
presentation.
4
Time-Out Warning Solution
Note Only artificial data was used for this
presentation.
5
Time-Out Warning Solution
Note Only artificial data was used for this
presentation.
6
Time-Out Warning Solution
Note Only artificial data was used for this
presentation.
7
How to create time-out warning

• Set timeout period in Evergreen.
• Requires one custom jsp that will check,
  maintain, and re-establish the database
  connection.
• Javascript file calls the custom JSP.
• Javascript file added in Evergreen layout so that
  Sapphire dynamically adds the timeout warning
  functionality to every page based on the layout.

Note Only artificial data was used for this
presentation.
8
Agenda

• Browser Time-Out Warning
• Displaying several fields from related tables
• Tips for using roles to configure security
• Business Objects Tips Tricks
• Flexible Report Headers
• Relative Retention Times formatted like (0.40)
• Concatenate Rows in a Table
• When to make object a dimension, detail, or
  measure
• Report showing Sapphire Report Configuration
  Details
• How to Warn of Partial Results on Report
• How to include SQL statement on Report

Note Only artificial data was used for this
presentation.
9
Display Several Fields from Related Tables

• Situation Users would like to see multiple
  fields from related tables such as Special
  Laboratory Investigations (SLINs) and Involved
  Samples where there is a one to many
  relationship between Samples and Involved Samples
  and also a one to many relationship between SLINs
  and Involved Sample.

Note Only artificial data was used for this
presentation.
10
How to Display Several Fields from Related Tables

• Develop SQL function to aggregate data and return
  HTML
• Provide Oracle developer with the desired HTML
• Call SQL function from Evergreen, pass necessary
  parameters
• Display results using Pseudo Column this embeds
  the output of the SQL function into the page

Note Only artificial data was used for this
presentation.
11
Evergreen Configuration-Call SQL Function from
Pseudo Column
Note Only artificial data was used for this
presentation.
12
Agenda

• Browser Time-Out Warning
• Displaying several fields from related tables
• Tips for using roles to configure security
• Business Objects Tips Tricks
• Flexible Report Headers
• Relative Retention Times formatted like (0.40)
• Concatenate Rows in a Table
• When to make object a dimension, detail, or
  measure
• Report showing Sapphire Report Configuration
  Details
• How to Warn of Partial Results on Report
• How to include SQL statement on Report

Note Only artificial data was used for this
presentation.
13
Configuring Security

• Situation
• Multiple departments or sites with similar groups
  of users who do same functions but against
  different data.
• Would like to keep maintenance to a minimum but
  would like ability to easily add new departments
  or sites.
• Users from one site should not see data from
  other sites.

Note Only artificial data was used for this
presentation.
14
Configuring Security

• Issue
• If you have a different role for each department
  and each type of user in the department then you
  would need to have role level security on the
  items you wanted to restrict and you would need
  to update the security for all your pages and
  operations every time you added a new department.
• Our system administrators wanted an approach that
  was easier to maintain.

Note Only artificial data was used for this
presentation.
15
A Different Approach to Configuring Security

• Assign a different role for each department and
  assign roles for each type of user and include a
  role that all users will be assigned.
• Example roles Dept A, Dept B, Site 2, LabUser,
  LabMgr, Everyone.
• Use the roles for each type of user to assign
  security to pages and operations. Do not use
  the site roles for the pages or operations.

Note Only artificial data was used for this
presentation.
16
A Different Approach to Configuring Security

• Data pages that need to restrict based on site
  should have the site as part of the data such as
  a field at sample login that identifies the
  department or site it can be indirect such as
  based on user site.
• Modify the Query SDC to Allow role level access
  control.
• In Evergreen remove the basic search from
  Advanced Search element.
• Create Queries that restrict the data based on
  site and assign the role for that
  site/department.
• If everyone should see the data (who can access
  the page) then assign the Everyone role to the
  Query.

Note Only artificial data was used for this
presentation.
17
A Different Approach to Configuring Security

• With this solution all that is needed to add a
  new site/department is to create queries based on
  the new site and role for the site/department
  No need to modify any page or operation security.

Note Only artificial data was used for this
presentation.
18
Agenda

• Browser Time-Out Warning
• Displaying several fields from related tables
• Tips for using roles to configure security
• Business Objects Tips Tricks
• Flexible Report Headers
• Relative Retention Times formatted like (0.40)
• Concatenate Rows in a Table
• When to make object a dimension, detail, or
  measure
• Report showing Sapphire Report Configuration
  Details
• How to Warn of Partial Results on Report
• How to include SQL statement on Report

Note Only artificial data was used for this
presentation.
19
Flexible Report Headers

• Situation Some fields need to appear on a report
  only if they are populated.
• Solution Use flexible report headers to avoid
  empty space between items in the report header.
• Example Second Address Line

Note Only artificial data was used for this
presentation.
20
Flexible Report Headers
Note Only artificial data was used for this
presentation.
21
How to make flexible headers

• If the object is a date use IsDate function.

• Make each item a table.
• Use custom filters.

Note Only artificial data was used for this
presentation.
22
How to make flexible headers
Note Only artificial data was used for this
presentation.

• Make each item a table.
• Use custom filters.
• Put the vertical position of each field linked to
  table above.

23
Agenda

• Browser Time-Out Warning
• Displaying several fields from related tables
• Tips for using roles to configure security
• Business Objects Tips Tricks
• Flexible Report Headers
• Relative Retention Times formatted like (0.40)
• Concatenate Rows in a Table
• When to make object a dimension, detail, or
  measure
• Report showing Sapphire Report Configuration
  Details
• How to Warn of Partial Results on Report
• How to include SQL statement on Report

Note Only artificial data was used for this
presentation.
24
Relative Retention Times (RRT)

• Situation Need to display certain numeric fields
  in following format (.).
• Issue When there were trailing zeroes the report
  dropped digits.
• Additional Information
• Formatting was not a problem when there were no
  parenthesis around the result.
• Not all rows have an RRT.
• Solution
• Divided into three columns.
• Used Alerters to hide values when empty.

Note Only artificial data was used for this
presentation.
25
Agenda

• Browser Time-Out Warning
• Displaying several fields from related tables
• Tips for using roles to configure security
• Business Objects Tips Tricks
• Flexible Report Headers
• Relative Retention Times formatted like (0.40)
• Concatenate Rows in a Table
• When to make object a dimension, detail, or
  measure
• Report showing Sapphire Report Configuration
  Details
• How to Warn of Partial Results on Report
• How to include SQL statement on Report

Note Only artificial data was used for this
presentation.
26
Concatenate Rows in a Table
Note Only artificial data was used for this
presentation.

• Situation Would like to include items from an
  associated table in one row even though the
  values span multiple rows in an associated table.
  
• Examples
• All categories for an item
• All roles for a user
• All the Samples for a Batch

27
Concatenate Steps in Designer
Note Only artificial data was used for this
presentation.

• Take a character field and make it a measure with
  Sum for the function.
• Important Make sure that the item remains a
  character and does not get changed to Numeric on
  the Definition Tab.

28
Concatenate Steps in Designer
Note Only artificial data was used for this
presentation.

• Take a character field and make it a measure with
  Sum for the function.
• Use the Concat function placing a comma and
  space after each item.
• Notice That the type is Character.

29
Concatenate - Steps in Reporter

• Remove the trailing comma in Reporter.
• Create a new variable to trim the trailing comma.
• Use the formula
• Left(ltOBJECTgt ,Length(ltOBJECTgt)-2)

Note Only artificial data was used for this
presentation.
30
Agenda

• Browser Time-Out Warning
• Displaying several fields from related tables
• Tips for using roles to configure security
• Business Objects Tips Tricks
• Flexible Report Headers
• Relative Retention Times formatted like (0.40)
• Concatenate Rows in a Table
• When to make object a dimension, detail, or
  measure
• Report showing Sapphire Report Configuration
  Details
• How to Warn of Partial Results on Report
• How to include SQL statement on Report

Note Only artificial data was used for this
presentation.
31
Dimension, Detail or Measure

• Sapphire is not a data warehouse. The data model
  of Sapphire is closer to a relational (OLTP)
  model.
• For Sapphire universes you will have an easier
  time creating reports if you use mostly detail
  objects.
• Use dimension objects for the key id fields and
  make the other objects details.
• Use measures with caution, not all numeric values
  should be measures.

Note Only artificial data was used for this
presentation.
32
Dimension Objects

• If the table has multiple key values then
  concatenate the key values into one dimension
  object. You can then have all the other values
  as details and you only need to show the details
  on the reports.
• If you are going to link multiple data providers
  on a report be diligent about defining the
  concatenation on dimensions the same.
• I often have fewer dimensions in a universe than
  I have tables.

Note Only artificial data was used for this
presentation.
33
Detail Objects

• Almost all the objects I use with Sapphire are
  detail objects.
• Even the result values such as the displayvalue
  column are details.
• Advantages of using details instead of
  dimensions
• Easy to create test groups where blocks do not
  break across pages between results.
• Easier to join multiple data providers in a
  report.

Note Only artificial data was used for this
presentation.
34
Example Formatting

• Most of these objects are details.
• Multiple data providers were used.
• A test will not break between pages.

Note Only artificial data was used for this
presentation.
35
Measure Objects

• Unless I am concatenating rows from a related
  table I am unlikely to create a measure object
  within Designer ? Can add in Reporter.
• The danger with measure objects is that if you do
  not include all of the key values from a table
  such as if you forget to include Dataset or
  Replicate you may get a sum of the results when
  you think you are only getting the result for one
  test.

Note Only artificial data was used for this
presentation.
36
Agenda

• Browser Time-Out Warning
• Displaying several fields from related tables
• Tips for using roles to configure security
• Business Objects Tips Tricks
• Flexible Report Headers
• Relative Retention Times formatted like (0.40)
• Concatenate Rows in a Table
• When to make object a dimension, detail, or
  measure
• Report showing Sapphire Report Configuration
  Details
• How to Warn of Partial Results on Report
• How to include SQL statement on Report

Note Only artificial data was used for this
presentation.
37
Report Showing Sapphire Report Configuration
Details
Note Only artificial data was used for this
presentation.

• This report does not show the business objects
  configuration details but it does show all the
  Sapphire configuration related to how a report is
  registered as well as security and webpage
  operations.

38
Agenda

• Browser Time-Out Warning
• Displaying several fields from related tables
• Tips for using roles to configure security
• Business Objects Tips Tricks
• Flexible Report Headers
• Relative Retention Times formatted like (0.40)
• Concatenate Rows in a Table
• When to make object a dimension, detail, or
  measure
• Report showing Sapphire Report Configuration
  Details
• How to Warn of Partial Results on Report
• How to include SQL statement on Report

Note Only artificial data was used for this
presentation.
39
Warn of Partial Results on Report

• Create a variable that uses the business objects
  report function DocumentPartiallyRefreshed()
• Create an alerter based on the new variable.
• Place the variable on the report and use the
  alerter so that if the report was partially
  refreshed a warning will print.

Note Only artificial data was used for this
presentation.
40
Agenda

• Browser Time-Out Warning
• Displaying several fields from related tables
• Tips for using roles to configure security
• Business Objects Tips Tricks
• Flexible Report Headers
• Relative Retention Times formatted like (0.40)
• Concatenate Rows in a Table
• When to make object a dimension, detail, or
  measure
• Report showing Sapphire Report Configuration
  Details
• How to Warn of Partial Results on Report
• How to include SQL statement on Report

Note Only artificial data was used for this
presentation.
41
Include SQL Statement on Report

• It may be useful if you have a one-time report to
  show the SQL used to generate the report on the
  report.
• Use the following function where ltkeyid1gt is a
  variable in the data provider DataProviderSQL(Da
  taProvider(ltKeyid1gt))

Note Only artificial data was used for this
presentation.
42
Include SQL Statement on Report

• It may be useful if you have a one-time report to
  show the SQL used to generate the report on the
  report.
• Use the following function where ltkeyid1gt is a
  variable in the data provider DataProviderSQL(Da
  taProvider(ltKeyid1gt))

Note Only artificial data was used for this
presentation.
43
If we had more time

• How to configure RRT values to appear beside
  appropriate results.
• How long text specifications can be displayed for
  tests.
• How to show current number of sapphire
  connections as part of browser header information
  and warn administrators when certain thresholds
  are reached.
• How to automatically scan jaguar log file for
  specific errors and e-mail warnings to
  administrators.
• How to configure tests so that Parameter names
  print for some tests but not others even though
  the results will print.
• How to use Business Objects to create reports for
  annual product reviews.

Note Only artificial data was used for this
presentation.
44
Contact Information

• V. Nichole Rowland
• Business Objects tips tricks
• www.perceptivity.us
• vnrowland_at_gmail.com
• [phone] ext. 1
• Bret J. Boyer
• Sapphire tips tricks
• bret_boyer_at_hotmail.com
• [phone]