Skip to Content
Best Practices & Standards for Formatted/Analytical Reporting
Applies to:

SAP BI , Bex Queries, Excel Workbook, Crystal Reports

Summary:

This Article describes some of the best practices for Formatted Reports/Analytical Reports (Workbook/Bex Query/Crystal Report)

Author : Kamlakant Mishra

Company : Capgemini US LLC

Updated on : July 31′ 2013

Kamlakant is responsible for delivering a SAP Reporting Solution for one of the Media & Entertainment Client of Capgemini. He works as a Manager in SAP BI & EPM space within the BIM SL of Capgemini. Kamlakant is also SAP Certified BPC 10.0 Managing consultant and holds BTech (Gold Medalist) in Electronics Engineering & MBA in Supply Chain Management.

Sr. No Requirement Description/Clarification Applies to…
1 Place
  most characteristics

    in “Free Characteristics”

    and not in “Rows”

Since
  we are adjusting the

    local views of these queries,

    reducing the number of

    characteristics in the rows will make inserting the queries a bit faster

Workbooks
2 Creating
  date offsets like

    YTD, last 90 days, same day

    last year, last 6 months,

    Currency conversion,

    custom/exit variables etc…

Date
  offsets/logic should be

    done in BEx only because it

    is usually easier in BEx due to

    offset capability

BEx
  Queries/Crystal/

    Workbooks

3 Numeric
  attributes that are

    character strings like model

    number for example will be text

    by default but other additive

    numeric attributes need to be

    numeric.

Numeric
  attributes should

    be formula variable/key

    figures otherwise they are

    text by default. Example:

    Shelf Life in days, Number

    of Scopes Involved, etc…

BEx Queries/Backend
4 If
  a user will need to search for

    values using a matches pattern

    function in Webi. Make the

    numeric values as text.

    This is the opposite of the item

    above.

Leave
  the numeric attribute as

    a text or character type. Do

    not make it numeric. Example:

    Deal #, Material #, Accounting

    Document Number, etc..

    <<performance may be

    impacted based on this option

    or the one above>>

BEx Queries/Backend
5 Optimize
  how key figures are

    pulled in to BO/Crystal

RSRT
  setting for all universe

    queries, go to properties

    and select “Use Selection of

    Structure Elements”

BEx
  Queries/Crystal/

    Workbooks

6 Initial
  report, should show

    structure with no data

Purge
  the data and also

    choose to purge the last values

    selected before the final save of

    the report

BEx
  Queries/Crystal/

    Workbooks

7 Currency
  format defaults (if

    not specified in requirements):

    Commas, negative signs,

    decimals?

Ensure
  that all values show

    on the report with the correct

    formatting of the numbers or

    dates. Make sure currency

    sign, commas, decimals are

    all appropriately applied. Fill

    Empty values with zero, NA,

    or another appropriate value

    instead of leaving blank.

BEx
  Queries/Crystal/

    Workbooks

8 Branding Make
  sure the Client logo

    or other project logo, color

    scheme, borders, alignment all

    have the same aesthetic look

    and feel for any business area.

    Example : Web reports all look

    and function the same.

BEx
  Queries/Crystal/

    Workbooks

9 To
  ensure uniform look of

    reports when they are initially

    opened and also to ensure user

    is looking at current data.

When
  saving reports to a folder,

    it should be saved with all of the

    data and last selected prompt

    values purged. The report

    should also be saved so that it

    opens on the first report tab

BEx
  Queries/Crystal/

    Workbooks

10 “Allow
  External Access to

    this Query” box should be

    checked on the ‘Extended’

    tab of the Query Properties.

Business
  Objects/Crystal
11 Any
  variables needed in BEx

    to provide key and text for the

    BO prompt should be multiple

    single value, optional variables.

We
  should avoid using

    Selection Option Variables

    and use Multiple Single Value

    and Range Variables where

    possible. The exception to this

    would be in cases where we

    absolutely need selection option

    variables or we need to use

    authorization based variables.

BEx Query
12 Query
  Read Mode – Use the

    right read mode for Queries

    · Read all data (all

    data is read from a

    database and stored in

    user memory space) –

    Used in special cases

    when a majority of the

    users need a given query

    to slice and dice against all

    dimensions

    · Read data during

    navigation (data is read

    from a database only on demand during navigation)

    – Recommended,

    minimizes impact on

    application server

    · Read data during

    navigation and when

    expanding the hierarchy –

    Used for queries involving

    large hierarchies

Use
  “read data during

    navigation” as the default

    and adjust to other options

    if necessary. Use “read all

    data” for non-slice and dice

    queries such as queries

    used for Crystal or Business

    Objects.

BEx
  Query/Crystal/

    Business Objects

13 Minimize
  conditions-andexceptions

    reporting

If
  used, the amount of data

    to be processed should be

    minimized with filters. The

    biggest volume reducers,

    should listed first/on top of the

    section

BEx Queries
14 Reduce Sorting in
  Queries
When
  a majority of the users

    need a given query to slice and

    dice against all dimensions.

    Reducing the ‘text’ in query will

    also speed up the processing.

BEx Queries
15 Minimize
  Restricted &

    Calculated key figures

As
  conditioning is done for each

    of them during query execution.

    This is very time consuming

    and a high number of then can

    seriously hurt query. Instead,

    define calculated & RKFs as

    much as possible on the Info

    provider level instead of locally

    within the query.

BEx Queries
16 Result
  rows should be always

    suppressed

BEx Queries/
  Workbooks
17 Uncheck
  “Hide Repeated

    Key Values” in the query

    properties

Workbooks
18 If
  possible, suppress zeros on

    key figures

Workbooks
19 Generally,
  it is preferable to

    Display As Key (or Key and

    Text) and not solely Text for

    characteristics

Workbooks
20 If
  exclusions exist, make sure

    they exist in the global filter

    area. Try to remove exclusions

    by subtracting out inclusions.

Exclusions
  are not as efficient

    as inclusions. Example:

    Company Code >= 2601 *and*

    Company Code <=2599 is

    better than Company Code <>

    2600

BEx Queries
21 Within
  structures and filters,

    make sure the filter order exists

    with the highest level filter first.

The
  highest order of restriction

    should come first, this reduces

    records to sort through for the

    remaining filters. Fields with

    the least granularity should be

    restricted first, then the next

    granular field.

BEx Queries
22 Validate
  code efficiency for all

    exit variables used in a report.

BEx Queries
23 Move
  Time restrictions to a

    global filter whenever possible.

BEx Queries
24 When
  queries are written

    on multiproviders, restrict to

    InfoProvider in global filter

    whenever possible.

MultiProvider
  queries require

    additional database table joins

    to read data compared to

    those queries against standard

    InfoCubes (InfoProviders), and

    you should therefore hardcode

    the infoprovider in the global

    filter whenever possible to

    eliminate this problem.

BEx Queries
25 Turn
  off formatting to minimize

    Frontend time whenever

    possible.

BEx Queries
26 Check for nested
  hierarchies.
These are very slow
  performing
BEx Queries
27 If
  “Display as hierarchy” is

    being used, look for other

    options to remove it to

    increase performance.

    Use Constant Selection instead

    of SUMCT and SUMGT within

    formulas.

    BEx Queries/Workbooks

    Do review of

BEx Queries/Workbooks
28 Do
  review of order of

    restrictions in formulas. Do as

    many restrictions as you can

    before calculations.

Try
  to avoid calculations before

    restrictions.

BEx Queries
29 Check
  Sequential vs Parallel

    read on Multiproviders.

Parallel
  read is much faster

    if multiple infoproviders are

    involved.

BEx Queries
30 Turn
  off warning messages on

    queries in RSRT.

BEx Queries
31 Check
  aggregation and

    exception aggregation on

    calculated key figures.

Before
  aggregation is generally

    slower and should not be used

    unless explicitly needed.

BEx
  Queries/Crystal/

    Business Objects

32 Avoid
  Cell Editor use if at all

    possible.

BEx Queries
33 Queries
  should be regenerated

    in production using RSRT

    after changes to statistics,

    consistency changes, or

    aggregates.

34 Leverage
  characteristics or

    navigational attributes rather

    than hierarchies.

Using
  a hierarchy requires

    reading temporary hierarchy

    tables and creates additional

    overhead compared to

    characteristics and navigational

    attributes. Therefore,

    characteristics or navigational

    attributes result in significantly

    better query performance than

    hierarchies, especially as the

    size of the hierarchy (e.g., the

    number of nodes and levels)

    and the complexity of the

    selection criteria increase.

BEx Queries
35 If
  hierarchies are used,

    minimize the number of nodes

    to include in the query results

Including
  all nodes in the

    query results (even the

    ones that are not needed

    or blank) slows down the

    query processing. The “not

    assigned” nodes in the

    hierarchy should be filtered

    out, and you should use

    a variable to reduce the

    number of hierarchy nodes

    selected.

BEx Queries
To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply