OBIEE - Hiding an column Dynamically

Hello Mohan, 

There is also a way to pseudo-hide a column using a dummy dimension. In most of our models, we include a dimension called "Empty Column". It only has one column, with the name "." (that is a single period). The dimension has the fact table(s) of the model as its logical table source(s), and it is mapped to '.' (a literal string containing a single period). 

We typically use this column when creating Table View reports that have multiple column selectors - a sort of "mini-ad-hoc" report for users. If the report has four column selectors, we make sure that the "." column is included as one of the choices in the selector. This essentially allows the user to "turn off" a column in the report and just aggregate by whatever else is chosen. 

For your requirement, you could use a CASE statement in your report to examine the presentation variable and either show the "real" column or the Empty Column. It doesn't actually "hide" the column, but the only thing displayed is a single period. You could map the column to a single space and then you wouldn't even see the period - just a completely blank column in your report. 

Jeff's solution is more elegant, but this is at least an option.


====================
This may help: 
You can use condition sections to display 1 of 2 reports. The conditional 
section (poorly named Guided navigation section) will filter on the value 
selected and show a specific report A or hide it. Report B will show for a 
different condition.

OBIEE - Changing Columns headers dynamically - Article 2

http://total-bi.com/2011/03/obiee-dynamic-column-headings

OBIEE Dynamic Column Headings

Posted on March 1, 2011 by Paul McGarrick
Filed Under 10.x Answers, Answers, OBIEE 10, OBIEE 11g

A common requirement I come across is to create a report with dynamic column titles. For example a column should be labelled ‘Mar 11′ rather than Revenue and this should change each month with the data.

Easy you would think? Well sadly not as easy at it perhaps should be from an enterprise BI product.

So what are the options?

1. Venkat came up with a clumsy workaround a couple of years back but that involves casting all your columns to char. It never really felt like a proper way of doing it.

2. Use a session variable to dynamically set the column name in the presentation layer of the RPD. E.g.

3. Use a presentation variable as the column name.

This is probably the better of the techniques but it is still far from ideal. Simply edit the column properties and put the name as @{variable_name}{default_value}.

But we still have the problem of how to make this dynamic. Well, we can set a presentation variable using a dashboard prompt so we need to create a prompt and force it to a default value. Note that the prompt will need to be added to the dashboard but can be hidden by setting the CSS style to display:none on the dashboard section.

Let’s hope Oracle make this functionality a bit easier to achieve in a future release!

Bye for now.

OBIEE - Changing Column heading Dynamically - Article 1

1. http://oraclebizint.wordpress.com/2008/01/25/oracle-bi-ee-101332-dynamic-column-headers-using-presentation-variables-sets-and-conditional-formatting/

Oracle BI EE 10.1.3.3/2 – Dynamic Column Headers using Presentation Variables, Sets and Conditional Formatting

Posted by Venkatakrishnan J on January 25, 2008

Yesterday we saw how to dynamically vary the columns in a report based on the column that we choose in a dashboard prompt. But if you had noticed, the column header in that report would have had a constant value. I got this question yesterday via email, whether there is any way we can make this column header dynamic. Well, today we shall see how to go about achieving dynamic column headers. Actually if you go to table or column headings in a table view you would notice that it accepts only static text. One cannot enter presentation variables and make it to convert them to their actual values. So, in order to achieve the dynamic column headers, we start with the same report from where we left off yesterday here. The idea is very straight forward. It consists of the following steps

1.   Hide the actual Column and Table Headers.
2.   Cast all the columns to CHAR.
3.   Decide on the name for your static column headers.
4.   Create conditional formatting on the all the columns and make them to display in a specific color whenever data matches the Column Header Names.
5.   Add a new criteria and combine it with the older one using UNION ALL. The columns in this criteria would contain the Actual Column Header values that you want (dynamic).
6.   Make these values to display at the top of the report.

I know it might be a bit confusing why we are doing certain operations above. We shall go through each of the above in detail. Lets take the same dynamic report that we created yesterday.

1.   Go to the table view properties and hide the actual column and table headers. We are doing this because our plan is to use a data element from the table itself(which we shall generate later using UNION ALL) as the column header instead of the actual column headers.

      

2.   Cast all the numeric columns to char since we would be appending non-numeric column headers which would come from another data set. Now decide on a column header name for all the columns that you have in your report. For example, in our report, one column header name would be dynamic (which would come from the presentation variable @{Geography} and the other Column header would SALES

      

3.   Once you have the column header names decided, now add conditional formatting to all the columns in the report to something like this i.e. whenever SALES1.SALES = ‘SALES’ (the static column header name that we decided earlier) then add a background color of light green. In my case. i have added the below conditional formatting to both the columns.

      

4.   Now that we have added the conditional formatting, next is we need to add the criteria that will add our necessary column header values to the report data. So, use UNION all to combine the new criteria with the older one.

      

Add some dummy columns and in the formula enter the column header values. In my case ‘{@Geography}’ and ‘SALES’. Ensure that you include atleast one column in the formula as shown below.

      

      

5.   The last step is to make the new data that we added to display at the top of the report. So, now add 1 new column to both the criteria and enter 2 in formula tab for the one containing the data and 1 in the formula tab for the one containing the column header values. Sort the results on this column.

      

      

6.   Now save this report. And go to the dashboard. You would have dynamic column headers.

      

      

I know the above procedure is too tedious for achieving a very small functionality. But again, if your users absolutely need it and you dont have any other options just use this approach. Just remember this works only for table views.

Queries for FIC

1. Getting BU

select distinct bu.bu_id, bu.bu_name from fun_all_business_units_v bu, fun_business_functions_b bf, fun_bu_usages_v buu where bu.bu_id = buu.business_unit_id and bf.business_function_code = 'INCENTIVE_COMPENSATION_BF' and bf.business_function_id = buu.module_id

====================================

2. BU Master Tables

Manage parameter data goes here.
CN_REPOSITORIES_ALL_TL
CN_REPOSITORIES_ALL_b

================================

3. Earning Type
CN_CREDIT_TYPES_ALL_tl
CN_CREDIT_TYPES_ALL_b

=============
4. RULES

CN_RS_RULES_ALL_TL

select A.name, b.* from CN_RS_RULES_ALL_TL A,

              CN_RS_RULES_ALL_B B

WHERE a.rule_id = b.rule_id


select b.name, a.* from CN_RS_RULE_ASSIGNMENTS_ALL a,
              CN_RS_RULES_ALL_tl  b
where a.rule_id = b.rule_id

=======

select b.name, a.rule_id, a.rule_attribute_id , c.* from CN_RS_RULE_ATTRIBUTES_ALL a,

              CN_RS_RULES_ALL_tl  b,

CN_RS_RULE_ATTR_VALUES_ALL c

where a.rule_id = b.rule_id

and 

a.rule_attribute_id = c.rule_attribute_id
============

Query to find credit rules hirerachy
-------------------------

select a.name, b.rule_id, b.enabled_flag, b.parent_rule_id,
b.rank,b.num_winners, b.absolute_rank, b.relative_rank,
b.attr_relation_product
  from CN_RS_RULES_ALL_TL a ,
                   CN_RS_RULES_ALL_B b
where a.rule_id = b.rule_id
connect by prior b.rule_id = b.parent_rule_id
start with b.rule_id = 300000004113199

==========================================================

Rule hierarchy with assignments


select a.name, b.rule_id, b.enabled_flag, b.parent_rule_id,
b.rank,b.num_winners, b.absolute_rank, b.relative_rank,
b.attr_relation_product , c.assigned_object_type,
c.org_id, c.role_id, c.start_date, c.end_date, c.split_pct, c.revenue_type,
 c.rollup_flag, c.group_member_credit_flag,c.summary_flag
  from CN_RS_RULES_ALL_TL a ,
                  (select * from  CN_RS_RULES_ALL_B b  connect by prior b.rule_id = b.parent_rule_id
start with b.rule_id = 300000004113199) b,
CN_RS_RULE_ASSIGNMENTS_ALL c
where a.rule_id = b.rule_id
and a.rule_id = c.rule_id

==============


Rule attribute joins

select b1.rule_attr_value_id,b1.rule_attribute_id, b1.comparison_operator,b1.low_value_char, b1.high_value_char, b1.low_value_number,
b1.high_value_number,
 a1.rule_id, a1.rule_attribute_id, a1.attr_usage_id, a1.org_id
 from CN_RS_RULE_ATTRIBUTES_ALL a1,
              CN_RS_RULE_ATTR_VALUES_ALL b1
 where rule_id in (300000004113199,
300000004115269,
300000004113254,
300000004113255,
300000004113257,
300000004113268
)
and a1.rule_attribute_id = b1.rule_attribute_id
==============

Debug table joins

select l.message_text
 from cn_process_audits_all a, cn_process_audit_lines_all l
where a.request_id = :process_id
  and a.process_audit_id = l.process_audit_id
order by l.process_audit_line_id desc


select l.message_text , l.process_audit_line_id
 from cn_process_audits_all a, cn_process_audit_lines_all l
where a.request_id = :process_id
  and a.process_audit_id = l.process_audit_id
  and l.message_text like '%%'
order by l.process_audit_line_id desc
==========================

Run the following to check restrictions:

1. Get participant_id for the participant to switch
   sql> select participant_id, participant_name from cn_srp_participants_all
         where participant_name like :participant_name 

2.  Check if participant is not used in any credit rule
   sql> select * from cn_rs_rule_assignments_all 
         where assigned_object_id in (:participant_id)

3. Create if participant is associated to transactions and credits
   sql> select participant_id, count(1)
          from cn_tp_transactions_all
         where participant_id in (:participant_id)
      group by participant_id

     select credited_participant_id, count(1)
       from cn_tp_credits_all
      where credited_participant_id in (:participant_id)
    group by credited_participant_id

4.  Check if assigned to compensation plan
    select * from cn_srp_comp_plans_all 

====================================

update cn_srp_participants_all
        set participant_type = 'ANALYST'
      where participant_id  in (:participant_id )
        and participant_type = 'PARTICIPANT' ;
===================


select * from cn_srp_comp_plans_all where participant_id = 40348
==================
Query for finding compensation plans

select a.comp_plan_name, c.plan_component_name , d.formula_sequence, d.formula_weight, d.incentive_formula_flag , e.formula_name , g.expression_name,
h.RENDERED_EXPRESSION_DISP , h.SQL_SELECT , h.SQL_FROM , h.PIPED_SQL_SELECT,
h.PIPED_SQL_FROM
               from CN_COMP_PLANS_ALL_TL a ,
                           CN_COMP_PLAN_COMPONENTS_ALL  b,
                           CN_PLAN_COMPONENTS_ALL_TL c ,
                           CN_PLAN_COMPONENT_FORMULAS_ALL d,
                           CN_FORMULAS_ALL_tl e ,
  CN_FORMULAS_ALL_B f ,
  CN_EXPRESSIONS_ALL_TL g ,
                     CN_EXPRESSIONS_ALL_B h
where a.comp_plan_name = 'Q12015RANK_TEST'
and a.comp_plan_id = b.comp_plan_id
and b.plan_component_id = c.plan_component_id
and b.plan_component_id = d.plan_component_id
and d.formula_id = e.formula_id
and e.formula_id = f.formula_id
and f.output_exp_id = g.expression_id
and g.expression_id = h.expression_id

Axioms of FIC

1. Only one credit category can be attached to classification rules.
2. We cannot attach classification rule to category.
3. Since the payment module does not include nonmonetary earnings, you can
export those earnings and set their statuses to Posted to ensure that the same
nonmonetary earnings are not included the next time that you export them.
4.

The sum of the target incentive weights does not have to equal 100. 5. You can specify multiple rate tables for an  incentive formula, provided that the • Effective dates of the rate table associations do not overlap

• Number of dimensions for the rate tables are the same

6. Typically, set up the formula to access quantifiable attributes through the

performance measure attainment, and non-quantifiable attributes directly from

the transaction or credit.

-------------------
7. Mappings can be shared across BUs.
8. Participants and Analysts cannot be imported together in same batch.
-------------------------------

9. We can same calender for different BUs but periods need to be opened for each BU.
--------
10. Lightweight Directory Access
Protocol (LDAP) user accounts for users of Oracle Fusion applications. OIM also
stores the definitions of abstract, job, and data roles and holds information about
roles provisioned to users.
=---
11
You must add the following Oracle Identity Management (OIM) roles to the IT
Security Manager job role's role hierarchy to enable the super user to create one
or more initial implementation users.
• Identity User Administrators
• Role Administrators
Additionally, you must assign the Xellerate Users organization to the IT Security
Manager role.
============

Tip
If you have multiple incentive compensation business units and want to
segregate all transactional data, include Business Unit as one of your qualifiers.
Do not include it as a qualifier if you plan to use cross business unit Crediting
and Rollup processes.

====================

12 .
You can only create a payment batch for a paygroup when there are no payment batches in unpaid status fro that particular paygroup.  Any unpaid paymentt batch either has to be paid or deleted before a new payment batch can be created for that paygroup.
===============================

13.

Note that instead of note :
How To Change Participant Type From Participant To Analyst (Doc ID 1586644.1)
There are no pre-requisites checking to run before doing this update (as soon script pass participant_type from ANALYST to PARTICIPANT

 ========================

FIC Essentials cousework

Incentive Compensation Course Overview

• Lesson and Course Objectives
• Course Schedule
• Outline of Course Hands-On Activities
• Oracle Fusion Resources

Introducing Oracle Fusion Applications and Incentive Compensation

• Introduce Oracle Fusion Applications User Interface
• Introduce Oracle Fusion Customer Relationship Management, Human Capital Management, and Incentive Compensation
• Introduce the Manage Incentive Compensation Main Business Activities
• Introduce Incentive Compensation Transaction Processing

Model and Configure Incentive Plans

• Compensation Plans and Building Blocks Overview
• Rate Tables and Dimensions
• Expressions
• Performance Measures
• Classification Rules and Credit Category Hierarchies

Assign Participants

• Introducing Participants, Plans, and Pay Groups
• Assigning Participants, Payment Plans, and Pay Groups

Credit Participants

• What is Sales Crediting?
• How Much Is Appropriate?
• Crediting Defined
• Oracle Fusion Incentive Compensation Crediting

Activity: Credit, Classify, Calculate Earnings, and Determine Payments

• Incentive Compensation Processing Lifecycle
• Base Transactions
• Credited and Classified Transactions
• Earnings and Payment Transactions

Transaction Lifecycle

• Importing, Collecting, and Managing Transactions
• Crediting
• Rollup
• Classification
• Calculation
• Payment Entities and Processes Overview
• Payment Transactions

Sales Compensation

• Monitor Participant and Team Performance
• Manage Disputes

Introducing Oracle Fusion Functional Setup Manager

• Application Implementation Lifecycle
• Functional Setup Enterprise Roles
• Planning an Implementation
• Configuring Offerings
• Generating Setup Task Lists
• Assigned Implementation Tasks
• Maintaining Setup Data
• Accessing Implementation Projects and Tasks

Define Enterprise Structures for Incentive Compensation

• Oracle Fusion Applications Business Units and Business Functions
• Reference Data Sharing
• Reference Data Partitions
• Workforce Structures: Jobs and Job Families
• Incentive Compensation and HCM Jobs

Define Security and Persons for Incentive Compensation

• IC Security Setup and Maintenance
• Roles Assigned to Users
• Role-Based Access Control
• Job and Duty Roles
• Manage Incentive Compensation Main Business Activities
• The Security Reference Implementation
• User Accounts and Role Provisioning
• Managing Security Using HCM, OIM, and APM

Define Trading Community Details for Incentive Compensation

• Customer Data Model Data and Incentive Compensation Rules
• How Oracle Fusion IC Uses Geography Reference Data
• Implementation Considerations
• Manage Geographies
• File-Based Import Process

Define Parties and Resources for Incentive Compensation

• Why Parties and Resources?
• Importing Customer Data Flow
• Partner, Person, and Resource Application Data
• Parties and Party Relationships and Data Model
• How Data Objects Reference Each Other
• Using Text and XML Files for Import

Maintain Common Reference Objects

• Application Taxonomy Overview
• Define ISO Reference Data
• Profile Options, Levels, and Values
• Lookups
• Flexfields
• Value Sets
• Set Activity Stream
• Manage Menu Customizations

Define Incentive Compensation Shared Configuration

• Calendar Period Types and Periods
• Multicurrency Support
• Converting IC Currencies

Define Incentive Compensation Business Unit Configuration

• Setting Calendar, Currency, and Processing Parameters
• Configuring Tables and Columns
• Enabling Attributes for Calculation
• Manage Earning Types
• Period Statuses
• Participant Import

Define Incentive Compensation Custom Qualifiers and Lookups

• Manage Incentive Compensation Custom Qualifiers and Lookups

Define Approval Management for Incentive Compensation

• Approval Management Overview
• Setting Up Approval Management
• Manage Task Configurations
• Approval Groups

Define Application Toolkit Configuration

• Map Reports to Work Areas

Define Extensions for Incentive Compensation (ESS)

• Manage Job Concepts
• Updating Custom Processes
• Manage List of Values Sources

Course Summary

• Key Conceptual Concept Summaries
• Transactional Course Activities Summary
• Implementation Course Activities Summary