OracleBIBlog Search

Friday, March 12, 2010

Tips and hints relating to the development of Financial Reporting Studio reports – Tip 1

In this installment of my blog contributions I would like to focus on some techniques that I have leveraged to get through some of the more challenging aspects of reports development.

While the function builder for member selection often allows you to get the detail selection that is needed I have found instances where I have had to become creative to achieve the exact detail that was necessary within the report being developed.

Four approaches that I leverage quite frequently are:

  1. Selecting Multiple Members Based on Criteria
  2. Defining Member Lists
  3. Referencing separate data sources within the same grid
  4. Using multiple grids and hiding all but one in the final product

Selecting Multiple Members Based on Criteria

You can select members dynamically based on user-defined criteria. You define criteria by creating expressions of members, Boolean operations, and commands. Boolean operators enable you to specify precise member combinations for the report, which is useful for dealing with large volumes of data. Use the AND, OR, UNION, and NOT Boolean operators, combined with expression commands, to refine your member selections.

For demonstration sake, lets assume you need to include all level 0 children of "Software/Other" but none of these members parents within the report. It is also necessary that when a new level 0 member is added that the report dynamically picks this member up.

To select multiple members based on criteria for this need I perform the following:

  1. Double-click a dimension cell for which you want to assign members.

  2. In the Select Members dialog box, right-click inside the selected area, then click Advanced.

  3. Build the expression to satisfy the above need by 1st selecting the descendants of "Software/Other"

  4. Next toggle the Select Member pane to "Lists" and select "Level 0, Product "

  5. Now update the operator to "AND"

  6. Now render the report in Print Preview, the objective has been obtained

  7. Explore other functions:
  • Select NOT to add the Boolean operator NOT to the expression. NOT is the inverse of the selected condition.
  • Type a left parenthesis, (, to add an opening character to the expression.
  • Type a right parenthesis, ), to add a closing character to the expression.
  • Click in the OPERATOR column, then select AND, OR, or UNION.
  • When all conditions must be met, use the AND operator.
  • When one condition of several must be met, use the OR operator.
  • To combine the data, use the UNION operator.

In the next installment we will cover "Defining Member Lists."