Business Objects Reporting

1
Business Objects Reporting
Perceptivity, Inc. presents

• Tips and Tricks for generating SAPPHIRE reports
  with Business Objects

2
This is a technical presentation with formulas
and detailed instructions.It may not be
appropriate for all audiences.
WARNING

• If you feel that this presentation is better
  viewed by someone else at your organization it
  can be downloaded at http//www.perceptivity.us/Do
  wnloads.html

3
Agenda

• How to create charts of sapphire data
• with lines for specification limits
• with the upper and lower control limits
  calculated
• with control limit lines displayed.
• How to format calculated results based on the
  format of associated parameters.
• How to use the new Sapphire views for reporting.
• Some options when working with images.

4
Creating charts of Sapphire data
Objective Display results in a chart with lines
for specification limits, and calculated control
chart limits. For this example, only results that
have a specification limit will be included.
Similar principles can be used for data without
spec limits (to display calculated control chart
limits) but the tables and links will be
different.
5
Creating charts of Sapphire data

• Example chart with specification lines and
  calculated control chart limits

6
Assumptions for this Example

• All items with same Parameter Type for a
  Parameter List should be combined in calculating
  control limits.
• The parameter list id and variant are important
  for calculations but not the parameter list
  version number.
• Only three Limit Types used
• InSpec Within Alert and Regulatory Limits
• Alert Within Regulatory Limits but outside Alert
  Limits
• RegulatorySpec Does not meet regulatory
  specification limits.
• Note If these assumptions are not correct you
  can use the same principles but with different
  groupings for your calculations. For example,
  the calculations may be based on the parameter id
  instead of the parameter type, or the parameter
  list id may be the only important part of the
  parameter list keys instead of the id and variant.

7
Steps in Designer to Display Spec Limits
Link these tables.
Create the object SDIDataItemSpecKeys with this
Select Statement SDIDATAITEM.SDCID
SDIDATAITEM.KEYID1 SDIDATAITEM.KEYID2
SDIDATAITEM.KEYID3 SDIDATAITEMSPEC.SPECID
SDIDATAITEMSPEC.SPECVERSIONID
to_char(SDIDATAITEMSPEC.USERSEQUENCE)
SDIDATAITEM.PARAMLISTID SDIDATAITEM.PARAMLISTVE
RSIONID SDIDATAITEM.VARIANTID
to_char(SDIDATAITEM.DATASET)
SDIDATAITEM.PARAMID SDIDATAITEM.PARAMTYPE
to_char(SDIDATAITEM.REPLICATEID)
SPECLIMITTYPE.LIMITTYPEID to_char(SPECPARAMLIMI
TS.LIMITTYPESEQUENCE)
8
Steps in Designer Spec Limits
Note All of these objects are defined to be
details of the SDIDataItemSpecKeys dimension
object defined on the previous slide.
9
Steps in Designer Control Limits
10
Steps in Reporter

• Create a new report
• For the conditions include Method-Test and Param
  Type (or the values your calculations require for
  uniqueness such as Paramid).
• Also include in your conditions other values
  important for your limitations such as product,
  dates, etc.
• Select the following objects to be included on
  the report

11
Steps in Reporter For Spec Limits

• Calculate the following variables in Reporter

12
How to Create Variables in Reporter
Select Add from the Variables window. Make sure
you select the Definition tab so you can name
your variable.
13
Steps in Reporter For Control Limits

• Calculate the following variables in Reporter

14
Steps in Reporter
If you used the wizard to create the report you
will have a table. Right-click and select Turn
to Chart. Select the chart format shown.
You will then have a chart that looks nothing
like what you want. Right-click on the chart and
select Format Chart.
15
Steps in Reporter
On the General tab select to show the Legend.
Then select the Pivot tab. Place the variables
on the X, Y, and Z Axis as shown below.
X-Axis Sample related fields such as sample id,
batch, dates, etc. Z-Axis Dataset, Replicate,
Paramid (Note These can be hidden.) Y-Axis
Include the variables as shown in the order
shown. This order will result in results
appearing on top of limits and will also result
in Regulatory Limits appearing over top of Alert
Limit lines if there is no Alert Limit.
16
Steps in Reporter
You will then have something like
this. Double-click on the tick marks at the
bottom on the Axis Label Format deselect Tick
Mark.
Right-Click on chart and select Format Plot Area.
Select the Fill and Background Color as shown
above.
17
Steps in Reporter
You will then have something like
this. Double-click on the symbol shown in the
legend key to change the format.
Change the format of each symbol in the
legend. You will finally end up with a chart
similar to this one. Note This example did not
have Alert limits. This one shows the same chart
with different data that includes Alert limits.
18
Agenda

• How to create charts of sapphire data
• with lines for specification limits
• with the upper and lower control limits
  calculated
• with control limit lines displayed.
• How to format calculated results based on the
  format of associated parameters.
• How to use the new Sapphire views for reporting.
• Some options when working with images.

19
Format Calculated Objects on Reports

• Issue
• In Sapphire you go through the process of
  defining the format for each of your tests such
  as 0.00 or 0.000, etc.
• You would like for your Business Objects reports
  to use the same format and furthermore you might
  like averages and standard deviations to use
  display to one additional decimal place.
• You dont want to manually format each report
  based on the data retrieved as you would like one
  report that will work with any subset of data.
• Key components of solution
• First, you need to create an object in designer
  that contains the format.
• Second, in reporter you need to create alerters
  to format the results.

20
Create the format object in Designer
Create a detail object called Format associated
with the SDIDataItemSpecKeys object created
previously. Note If you dont care about the
results having a specification limit you can use
a different dimension object that has all the
relevant keys from sdidataitem.
Use the sdidataitem.displayformat for your
object. The type will be the default type of
Character.
21
Create Alerters in Reporter
In Reporter select a result you want to format
and then select Format ? Alerters. Select
Add. On the Definition Tab for the first Alerter
enter FormatResult for the Name.
On the Conditions tab you will define the formats
as shown above. For the Result Cell Content
on the drop-down select Format On the Number
tab for the FormatResult alerter set the number
of decimals for each row of the Alerter to match
the value shown. For example for the first row
where Format 0.00 the Cell Content format
is set to 0.00 on the Number tab. Note You will
need to set the Font Style to Regular and the
Color to Automatic.
22
Create a Formatted Calculations Alerter

• Next, follow the same steps as previously
  described to create a second Alerter called
  FormatCalculations.
• The only difference is the following
• On the Number tab for the FormatResult alerter
  set the number of decimals for each row of the
  Alerter to one greater value shown.
• For example for the first row where Format
  0.00 the Cell Content format is set to 0.000
  on the Number tab.
• Once these are defined you can create a report
  with results and calculations such as the control
  limits, min, max, average, etc. that use one of
  these two alerters to use the correct number of
  digits. All that you need to do is select the
  alerter for that value on the report.

23
Agenda

• How to create charts of sapphire data
• with lines for specification limits
• with the upper and lower control limits
  calculated
• with control limit lines displayed.
• How to format calculated results based on the
  format of associated parameters.
• How to use the new Sapphire views for reporting.
• Some options when working with images.

24
Sapphire Views

• Issue
• Before using business objects to create reports
  the business objects universe must contain
  appropriate tables with proper links.
• It is often difficult for you as a user to know
  how to define these links in designer.
• Resolution
• Views flatten the data from multiple tables into
  a single data cube, this makes it much easier to
  prepare reports with business objects.
• LabVantage prepared some views for use with
  business objects or other reporting tools.

25
Sapphire Views

• Views included with Sapphire R5
• v_samplepackagehistory
• v_samplepackageitems
• v_sampletest
• v_sampletestresult
• v_specdetails
• v_stabilitystudy
• Note These views wont work if you dont use all
  the tables included in the view.

26
Sapphire Views

• View Name V_SAMPLEPACKAGEHISTORY
• Tables joined in this view
• s_eventlog
• The only table is s_eventlog but it links
  s_eventlog to itself so that it can show the
  changes in the sample package history.

27
Sapphire Views

• View Name
• V_SAMPLEPACKAGEITEMS
• Tables joined in this view
• s_package
• storageunit
• trackitem
• s_box

28
Sapphire Views

• View Name
• V_SAMPLETEST
• Tables joined in this view
• sdiworkitem
• sdiworkitemitem
• sdidata
• workitem
• paramlist
• s_sample

Note If you dont use workitems this view will
not work for your data.
29
Sapphire Views

• View Name
• v_sampletestresult
• Tables joined in this view
• v_sampletest
• sdidataitem
• paramlistitem
• param
• Basically, this view takes the view v_sampletest
  and adds the results stored in sdidataitem.

Note If you dont use workitems this view will
not work for your data.
30
Sapphire Views

• View Name V_SPECDETAILS
• Tables joined in this view
• sdidataitemspec
• sdispec
• specparamlimits
• speclimittype

31
Sapphire Views

• View Name V_STABILITYSTUDY
• Tables joined in this view
• study
• study_scheduleplan
• scheduleplan
• scheduleplanitem
• scheduletimerule
• schedulecondition
• scheduleevent
• schedulecondition_trackitem

32
How to use a View in Sapphire

• Add the view into a universe in designer.
• Try to create the report you need with the view.
• If you get most of what you want but you need to
  pull some other information you can create joins
  in designer between other tables and the view,
  adding the objects you need.
• Example You may want to link to sample to pull
  your user specific sample values.

33
Agenda

• How to create charts of sapphire data
• with lines for specification limits
• with the upper and lower control limits
  calculated
• with control limit lines displayed.
• How to format calculated results based on the
  format of associated parameters.
• How to use the new Sapphire views for reporting.
• Some options when working with images.

34
Options for Drilling Images

• Advantages and Disadvantages of Three Alternatives

35
1- Create in Reporter view as PDF

• Advantages
• Ability to save as PDF and still view the images.
• Good control over page breaking and page numbers.

• Disadvantages
• Image format BMP or TIFF.
• Rather than drilling the report would be
  refreshed with different input values.
• In all formats images will not appear in Excel.

36
2. Create in Reporter view w/ Thick Client

• Advantages
• Drilling available on report.
• Ability to save as PDF and still view the images.
• Good control over page breaking and page numbers.

• Disadvantages
• Could have licensing issues.
• Image format BMP or TIFF.
• In all formats images will not appear in Excel.

37
Option 3 Create in WEBI

• Disadvantages
• Images will not appear on PDF saved copies.
• Page breaking and formatting not as flexible.
• In all formats images will not appear in Excel.

• Advantages
• Drilling available on report.
• Image format JPG or GIF.
• The small hyperlinked report can open to a larger
  copy of the image when clicked.

In Designer the following objects were created to
display the attached images DatasetFilepath
replace(DatasetAttachments.FILENAME,'c\temp\','//
servername/attachments/') DatasetAttachedImage3
'lta href"file//' _at_Select(Attachments
Filepath Information\DatasetFilepath) '"
target"_blank"gtltIMG SRC"file//'
_at_Select(Attachments Filepath Information\DatasetFi
lepath) '" width"175" alt"'
_at_Select(Attachments Filepath Information\DatasetFi
lepath) ' "gt'
38
Contact Information

• V. Nichole Rowland
• http//www.perceptivity.us
• vnrowland_at_gmail.com or vnrowland_at_perceptivity.us
• 1 [phone] ext. 1
• Remember You can download this presentation at
  http//www.perceptivity.us/Downloads.html
• Last years presentation is there as well
• 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