Feeds:
Posts
Comments

So it seems that by default, BI Publisher uses CSS style sheets as opposed to inline CSS when generating reports in HTML format. This is perfectly fine as the reports render fabulously in our favorite browsers. However, in certain popular email clients like Gmail, Hotmail, MSN and so on not so well. The inline HTML reports look distorted. My personal experience with this is within Gmail and looking at tables. The tables are aligned but the table borders and color formatting is gone. So the Emailed report does not look polished or 100% presentable. It seems these Email clients don’t like CSS stylesheets and prefer CSS embedded in the HTML body.

There is a solution for this. However, it is not something easily setup in the front end via a property or setting. Also this solution will be a system wide setting for BIP for all HTML output generation. By embedding the CSS inline, the HTML message will now be larger in size. So that is the downside to embedding CSS inline.

As per an Oracle Master Note (DOC ID 1270136.1), what needs to be done is a modification to the “xdo.cfg” file located in the folder:

{MIDDLEWARE_HOME}/user_projects/domains/bifoundation_domain/config/bipublisher/repository/Admin/Configuration
Here you will need to add the following property tag highlighted in bold within the properties element

<?xml version = ‘1.0’?>

<config xmlns=”http://xmlns.oracle.com/oxp/config/&#8221; version=”1.0.0″>

<properties>

   <property name=”html-css-embedding”>embed-to-element</property>
</properties>

<fonts>

</fonts>

</config>

Save the document and restart BI Services.

–(Updated/Revised 14-Jul-15)–

Overview: Working with a bursting BIP report to be sent out to X number of recipients. The bursting job never completes and stays in ‘Job processor completed’ status indefinitely. Looking at the job details, N number of recipients’ reports are created and delivered. M number, however, are stuck in ‘Rendering Report Document’ statuses. BIP job never completes.

Analysis: Looking into the issue. It is not clear what is causing this issue. Is it a memory issue? Is it stuck threads? Is there something wrong with the bursting definition query?

First course of action is to look at log files, specifically bipublisher.log, and bump up debug tracing level within BIP log configuration. To enable this:

  1. Login into Enterprise Manager
  2. In left pane, expand the folder ‘Application Deployments’
  3. Two options based on install
    1. Simple Install – right click on bipublisher(AdminServer)
    2. Enterprise Install– right click on bipublisher(bi_server1, bi_server2,…)
  4. From the pop-up menu, choose Logs-> Log Configuration
  5. In the right pane, open the log configuration window, showing the ‘Log Levels’ tab
  6. At the bottom of page, tick the checkbox ‘Persist log level state across component restarts’
    1. This ensures that the changed log level will persist across OBI restarts
  7. Change the Oracle Diagnostic Logging Level to TRACE:32(FINEST)
  8. Click Apply

The log files did not show anything before and after extended debugging. So the log files were not helpful.

I looked at both the memory and threads and did not see anything that would suggest this was an issue. I broke down the data set and bursting SQL query to one of the users/recipients that were having an issue. That sample user always had an issue! So by breaking nit down to test a smaller sample set and down to the one sample problematic recipient helped me isolate and make a decision that it was not memory or thread related.

I then evaluated the bursting SQL query definition. Thinking it must be it. But wait, nothing was wrong, as many others seemed to get their reports created and sent. So it must not be the bursting SQL query definition.

I then ran the report by itself for the test user having issues. That ran and produced a document! So what gives!? It can run by itself;but, bursting has issues.

I did notice in the stand alone run of the report two graphs were not being produced and just showing the running clock. Hmmm…

I looked at the dataset for that one recipients data. All looked good. Example:

<DATA_DS>

<G_1>

<NAME>FOO</NAME>

<ADDR>123 ELM ST…</ADDR>

</G_1>

</DATA_DS>

I looked at a comparable dataset for another user/recipient that ran fine. Here it is! Example:

<DATA_DS>

<G_1>

<NAME>FOO</NAME>

<AMOUNT>100.00</AMOUNT>

<ADDR>123 ELM ST…</ADDR>

</G_1>

</DATA_DS>

The problematic users where missing a few elements in their data set rows. Like, the AMOUNT element/attribute in the examples above. So, this was causing the BIP report engine to freak out and churn forever. Specifically, this was causing the gauge objects, within the BI Publisher Layout (XPT) format, to churn and cause this issue. Churn meaning showing the running clock symbol forever if running the report separate or the ‘Rendering Document’ status in bursting mode. So, if any data elements are null in the data set that are being referenced in the XPT report format for gauges, it is a problem!

The XPT (BI Publisher Layout) format does not have or allow conditional sections, like RTF format. The RTF format is a more flexible format in that regard; but I had to use the XPT format for this particular circumstance.

Now, as I specified in the first version of this document I thought it was due to the fact that the null values were not being added as tags and thus were missing. But that was not the case. I found out that there is a setting under the data model->Properties that will allow the data set results to add the xml tags/elements for any null values. This setting is a checkbox with label: ‘Include Empty Tags for Null Elements’. Checking this box will produce an XML like so, keep not of AMOUNT as that is the one that is null.

<DATA_DS>

<G_1>

<NAME>FOO</NAME>

<AMOUNT/>

<ADDR>123 ELM ST…</ADDR>

</G_1>

</DATA_DS>

 

I believe that the BIP XPT format should allow null elements or at least allow a setting default values for the gauge objects so that this error/issue would no longer exist. At the very least, throw an error or warning giving a clue to the issue.

Resolution/Summary:

Ensure that all data elements (aka columns) from the data set query return a value and not NULL, especially for metrics that will be used in gauge objects in the XPT format. My particular work around was to put in a NVL( ) in the aggregate functions to ensure they do not result in a NULL and default to a 0. For my particular issue, the data set SQL had an aggregate function which was returning NULL. Example:

SELECT

MAX(AMT) as AMOUNT

FROM

I just needed to modify accordingly and viola. The bursting jobs will complete and send to all recipients with out issue or get stuck.

SELECT

MAX(NVL(AMT,0)) as AMOUNT

FROM

While I was looking into a calculation issue today for a calculated percentage, let’s say the formula is ((b-a)/c)*100, the numbers just were not coming out correctly. Actually they were all coming out to 0!!! I know this was not right, so I investigated. I looked at my physical query and noticed OBI was creating that formula as FLOOR((b-a)/nullif(c,0))*100. Well, that explains it! Why was the BI Server adding this FLOOR function? It did not make sense to me. So how do I make or trick the BI Server to not do this. I asked my friend google. He had no answers. I then went to pal support.oracle.com. They did within Doc ID 1460809.1. As per Oracle, the cause was,

There was a change in the calculation engine in OBIEE 11g pertaining to how integer division is performed.  In OBIEE 10g, you could do integer division but it might not return an integer, you might get a decimal.  In OBIEE 11g this is no longer true, it always returns an integer.   As a result in DBFeatures in the repository, FLOOR_SUPPORTED is checked by default.

So there are 2 solutions:

  • Turn off FLOOR_SUPPORTED database feature. This would be a global change and I think too drastic
  • Within the Answers report do the following, wrap a cast( …. As double) around the numerator like so: (CAST(b-a AS DOUBLE)/c). The results where that the FLOOR was replaced with the cast-> cast(b-a as DOUBLE PRECISION)/nullif(c,0))*100

Cross drilling! Or in star schema design, otherwise known as “Drilling Across”. This is where you want to report off metrics from 2 or more fact tables in the same report. This is easy when your report is using all conformed dimension attributes. But in most cases, the requirement is for these fact tables’ metrics and some non-conformed dimension attributes.

In OBIEE, we typically solve this by making LBMs of all a fact table’s metrics to the non-conformed dimensions total/all level. This will then allow users to be able to build reports across different fact tables with conformed and non-conformed dimension attributes. What happens behind the scene is the following:

  1. A query is issued for each fact table, aggregating them to the common level of the other fact tables.
  2. The results, or datasets, of each fact table query from #1 above is merged/joined based on the conformed dimension attributes using a full outer join.

This is the correct behavior and solution for solving cross drilling, which OBIEE has been doing forever. Now as of 11.1.1.7.1, if you are now on that release, the full outer joins are not there! Instead, it is using Left Outer joins. This will alter all the reports that you have written that required cross drilling and have different results. So what happened!?

Researching this for a client whom upgraded from 11.1.1.5 to 11.1.1.7.x, I came across this issue and investigated. It threw me for a loop.   Upon investigating, I found support doc Doc ID 1601703.1. As per that doc, “There was a change in behavior in 11.1.1.7.1. The issue matches this change. Basically there was an inconsistency in how filters were working with grand totals and subtotals. As a result, full outer joins were replaced with left outer joins.

I guess it can work with left outer joins; but, more than likely this solution will not give you what you want as a result. First of all, before I get to the solution to re-introduce full outer joins, you may want to know what table is being used left of the left outer join. From my experimentation, the table with the most conformed dimensions seems to be the one on the left. So if fact table A has the most conformity, then the SQL would be A LEFT OUTER JOIN B ON (…).

The question then is how do I get back to the FULL OUTER join. The answer lies in the session variable FOJ_GRAND_TOTAL_LBM, FOJ standing for FULL OUTER JOIN. As the Oracle doc states there are 2 ways to resolve: REPORT ONLY or INIT BLOCK approach. The REPORT ONLY solution will only solve this for an individual report, and you would have to make changes to each report in which you wanted the full outer join to be used. The INIT BLOCK solution will make this change global.

REPORT ONLY

In the advanced tab of the report, enter the following within the PREFIX box:

SET VARIABLE FOJ_GRAND_TOTAL_LBM=1;

INIT BLOCK

  1. Create a new session variable in the RPD: FOJ_GRAND_TOTAL_LBM
  2. Set the default value of this session variable: 1
  3. Under Initialization Block, click (This is to create the dummy init block)
  4. Set the init block’s name: SET_FOJ_GRAND_TOTAL_LBM
  5. Within the init block, click the Disabled check box.
  6. Click OK, within init block.
  7. Click OK, within the session variable.

(Note: the default initializer is all that is needed to set the value; but, all session variables need a init block assigned. Thus the need to create the disabled init block)

Row-level security in OBI is a very powerful tool. It is used to limit data seen by users/groups/roles based on a business’s security model. It really is not hard; but, you need to understand when and how data filters will be applied. This is the key that is not documented thoroughly (or that I have seen by searching on the web for clarification). So I did the work to clarify it for you, or I hope I am in this writing. Note: I said it is easy, because once you understand how OBI works in this regard it pretty much is. The biggest issue, from my experience, is getting the business to define their security model and how they want all the scenarios to play out.

 

What I will be showing is a bunch of scenarios and what happens behind the scenes. Based on these scenarios and outcomes, one should understand what BI Server is doing and put that knowledge to use for setting up their security based on security model requirements.

 

 

Scenario 1: User is assigned 2 app roles, ‘A’ and ‘B’, both app roles are total unrelated (meaning no hierarchy or membership of each other). One of the app roles (‘A’) has security filters, the other (‘B’) does not.

Here I am adding filter on presentation column for “Company Name” = ‘Foo’ for app role ‘A’.

security1

I built a simple report including that presentation column to test.

Outcome: The security filters for app role ‘A’ are added to the query. Here is the query produced for the report, as you can see the filter was applied!

SELECT T528077.PROFIT_CENTER_NAME AS c2,
T556965.MCAL_PERIOD_NAME         AS c3,
T556965.MCAL_PERIOD_WID         AS c4,
T528077.ROW_WID                 AS c5,
SUM(T551274.BALANCE_GLOBAL1_AMT) AS c6,
T556965.ROW_WID                 AS c7
FROM W_MCAL_DAY_D T556965,
W_LEDGER_D T551773,
W_PROFIT_CENTER_D T528077,
W_INT_ORG_D T538674,
W_GL_BALANCE_A T551274
WHERE ( T528077.ROW_WID   = T551274.PROFIT_CENTER_WID
AND T538674.ROW_WID       = T551274.COMPANY_ORG_WID
AND T538674.ORG_NAME       = ‘Foo’
AND T538674.COMPANY_FLG   = ‘Y’
AND T551274.BALANCE_DT_WID = T556965.ROW_WID
AND T551274.LEDGER_WID    = T551773.ROW_WID )
GROUP BY T528077.ROW_WID,
T528077.PROFIT_CENTER_NAME,
T556965.ROW_WID,
T556965.MCAL_PERIOD_WID,
T556965.MCAL_PERIOD_NAME
Summary: For mutually exclusive app roles, meaning no hierarchical relationship, when a security filter is placed on one role and not the other the security filter placed on the one role will be applied implicitly to the physical query.

 

 

Scenario 2: User is assigned 2 app roles, ‘A’ and ‘B’, both app roles are total unrelated (meaning no hierarchy or membership of each other). Each app role has a security filter on the same object. The data filter may be on the same object or different.

Same Object Data Filter:

App Role ‘A’ has same filter as scenario 1. Just added filter for app role ‘B’, on “Company Name” = ‘BAR’.

security2

Different Object Data Filter:

App Role ‘A’ has same filter as scenario 1. Just added filter for app role ‘B’, on “Account Name” = ‘ACME’. Please note: this is on the same presentation object but the Data Filter expression is on different logical column.

 

security3

Outcome: If the data filters are on the same logical object, it will treat them in an IN clause. If the data filters are on different logical objects, it will treat as ORs.

Same Object Data Filter:
SELECT T528077.PROFIT_CENTER_NAME AS c2,
T556965.MCAL_PERIOD_NAME         AS c3,
T556965.MCAL_PERIOD_WID         AS c4,
T528077.ROW_WID                 AS c5,
SUM(T551274.BALANCE_GLOBAL1_AMT) AS c6,
T556965.ROW_WID                 AS c7
FROM W_MCAL_DAY_D T556965,
W_LEDGER_D T551773,
W_PROFIT_CENTER_D T528077,
W_INT_ORG_D T538674,
W_GL_BALANCE_A T551274
WHERE ( T528077.ROW_WID   = T551274.PROFIT_CENTER_WID
AND T538674.ROW_WID       = T551274.COMPANY_ORG_WID
AND T538674.COMPANY_FLG   = ‘Y’
AND T551274.BALANCE_DT_WID = T556965.ROW_WID
AND T551274.LEDGER_WID     = T551773.ROW_WID
AND (T538674.ORG_NAME     IN (‘BAR’, ‘Foo’)))
GROUP BY T528077.ROW_WID,
T528077.PROFIT_CENTER_NAME,
T556965.ROW_WID,
T556965.MCAL_PERIOD_WID,
T556965.MCAL_PERIOD_NAME
Different Object Data Filter:

SELECT DISTINCT T528077.PROFIT_CENTER_NAME AS c1,
T556965.MCAL_PERIOD_NAME                 AS c2,
T556965.MCAL_PERIOD_WID                 AS c3,
T528077.ROW_WID                         AS c4
FROM W_MCAL_DAY_D T556965,
W_LEDGER_D T551773,
W_PROFIT_CENTER_D T528077,
W_INT_ORG_D T538674,
W_PARTY_D T605218,
W_PARTY_ORG_D T605644,
W_STATUS_D T529754,
W_AP_XACT_F T532384
WHERE ( T532384.LEDGER_WID       = T551773.ROW_WID
AND T528077.ROW_WID               = T532384.PROFIT_CENTER_WID
AND T532384.COMPANY_ORG_WID       = T538674.ROW_WID
AND T556965.ADJUSTMENT_PERIOD_FLG = ‘N’
AND T532384.CUSTOMER_WID         = T605644.ROW_WID
AND T529754.ROW_WID               = T532384.DOC_STATUS_WID
AND T532384.MCAL_CAL_WID         = T556965.MCAL_CAL_WID
AND T605218.PARTY_ORG_WID         = T605644.ROW_WID
AND T532384.DELETE_FLG           = ‘N’
AND T538674.COMPANY_FLG           = ‘Y’
AND T532384.POSTED_ON_DT_WID     >= T556965.MCAL_DAY_DT_WID
AND (T538674.ORG_NAME     IN (‘Foo’)
OR T605218.NAME           IN (‘ACME’))
AND (T529754.W_STATUS_CODE IN (‘POSTED’, ‘REVERSED’)))
Summary: For mutually exclusive app roles (meaning no hierarchical relationship), when security filters are applied for both app roles on the same object, OBI treats them as additive. Meaning, they will either be written as ORs or added within an IN clause.

 

 

Scenario 3: User is assigned to only the ‘A’ app role. ‘A’ app role is made a member of the ‘B’ app role. Only the ‘B’ app role has a security filter on it.

Only app role ‘B’ has a filter on the presentation column “Company Name” = ‘Foo’. All security filters on app role ‘A’ where removed. So ‘B’ looks like:

security4

Outcome: App role ‘B’s security filter is applied to the query.

SELECT T528077.PROFIT_CENTER_NAME AS c2,
T556965.MCAL_PERIOD_NAME         AS c3,
T556965.MCAL_PERIOD_WID         AS c4,
T528077.ROW_WID                 AS c5,
SUM(T551274.BALANCE_GLOBAL1_AMT) AS c6,
T556965.ROW_WID                 AS c7
FROM W_MCAL_DAY_D T556965,
W_LEDGER_D T551773,
W_PROFIT_CENTER_D T528077,
W_INT_ORG_D T538674,
W_GL_BALANCE_A T551274
WHERE ( T528077.ROW_WID   = T551274.PROFIT_CENTER_WID
AND T538674.ROW_WID      = T551274.COMPANY_ORG_WID
AND T538674.ORG_NAME       = ‘Foo’
AND T538674.COMPANY_FLG   = ‘Y’
AND T551274.BALANCE_DT_WID = T556965.ROW_WID
AND T551274.LEDGER_WID     = T551773.ROW_WID)
GROUP BY T528077.ROW_WID,
T528077.PROFIT_CENTER_NAME,
T556965.ROW_WID,
T556965.MCAL_PERIOD_WID,
T556965.MCAL_PERIOD_NAME
Summary: In app role hierarchical relationships, even though a user is not assigned to the parent app role, they will inherit the security filters from the immediate parent app role that has security filters applied to it.

 

 

Scenario 4: User is assigned 2 app roles, ‘A’ and ‘B’. ‘A’ app role is made a member of the ‘B’ app role. Each app role has security filters on the same object. We will follow the same setup as Scenario 2, except the app roles are now hierarchical. We will have 2 separate sub scenarios, the data filter may be on the same object or different.

App Role ‘A’ has the following filter: “Company Name” = ‘Foo’.

security5

Same Object Data Filter:

App role ‘B’ has the following filter: “Company Name” = ‘BAR’.

security6

Different Object Data Filter:

App role ‘B’ has the following filter: “Account Name” = ‘ACME’. Please note: this is on the same presentation object but the Data Filter expression is on different logical column.

security7

Outcome: If the data filters are on the same logical object, it will only use the lowest app role in the hierarchy. For our example that would be app role ‘A’, as ‘A’ is a member of ‘B’. If the data filters are on different logical objects, it also only uses the data filter on the lowest app role.

Same Object Data Filter:
SELECT T528077.PROFIT_CENTER_NAME AS c2,
T556965.MCAL_PERIOD_NAME         AS c3,
T556965.MCAL_PERIOD_WID         AS c4,
T528077.ROW_WID                 AS c5,
SUM(T551274.BALANCE_GLOBAL1_AMT) AS c6,
T556965.ROW_WID                 AS c7
FROM W_MCAL_DAY_D T556965,
W_LEDGER_D T551773,
W_PROFIT_CENTER_D T528077,
W_INT_ORG_D T538674,
W_GL_BALANCE_A T551274
WHERE ( T528077.ROW_WID   = T551274.PROFIT_CENTER_WID
AND T538674.ROW_WID       = T551274.COMPANY_ORG_WID
AND T538674.ORG_NAME       = ‘Foo’
AND T538674.COMPANY_FLG   = ‘Y’
AND T551274.BALANCE_DT_WID = T556965.ROW_WID
AND T551274.LEDGER_WID     = T551773.ROW_WID)
GROUP BY T528077.ROW_WID,
T528077.PROFIT_CENTER_NAME,
T556965.ROW_WID,
T556965.MCAL_PERIOD_WID,
T556965.MCAL_PERIOD_NAME
Different Object Data Filter:

SELECT T528077.PROFIT_CENTER_NAME AS c2,
T556965.MCAL_PERIOD_NAME         AS c3,
T556965.MCAL_PERIOD_WID         AS c4,
T528077.ROW_WID                 AS c5,
SUM(T551274.BALANCE_GLOBAL1_AMT) AS c6,
T556965.ROW_WID                 AS c7
FROM W_MCAL_DAY_D T556965,
W_LEDGER_D T551773,
W_PROFIT_CENTER_D T528077,
W_INT_ORG_D T538674,
W_GL_BALANCE_A T551274
WHERE ( T528077.ROW_WID   = T551274.PROFIT_CENTER_WID
AND T538674.ROW_WID       = T551274.COMPANY_ORG_WID
AND T538674.ORG_NAME       = ‘Foo’
AND T538674.COMPANY_FLG   = ‘Y’
AND T551274.BALANCE_DT_WID = T556965.ROW_WID
AND T551274.LEDGER_WID     = T551773.ROW_WID )
GROUP BY T528077.ROW_WID,
T528077.PROFIT_CENTER_NAME,
T556965.ROW_WID,
T556965.MCAL_PERIOD_WID,
T556965.MCAL_PERIOD_NAME
Summary: In app role hierarchical relationships, the security filter that will be used will be the first one with a data filter going bottom up in the app role hierarchy. So let’s say you have three app roles A, B and C where A is a member of B, and B is a member of C. If A has security placed on it, it will use A’s security. If A does not have security on it nor does B; but, C has security C’s security will be used.

 

 

Now, knowing these behaviors and how OBI treats data filters on app roles and the app roles hierarchy, one can pretty much setup OBI to how they want security to act. For example, you have a user who belongs to N number of app roles. One of the app roles is very restrictive. What will happen? Well since the user is part of the restricted group he will be restricted. But, what if they belong to a group which should have no restrictions like a super user? This can be fixed in one or two ways.

Option 1: If there is no app role hierarchy setup, or you do not want app roles to be setup in a hierarchical manor, you can just add a simple data filter for the superuser app role on the same object(s) as the restricted app role but just put the expression: 1=1! This will always return to true. So looking back at scenario 2, OBI will make the security filters additive in OR logic. OBI is smart enough that it understands that 1=1 is always true. So if you add that to another relation via ORs it is always true. So OBI does not add any filters whatsoever. NO FILTER APPLIED!

Option 2: You can setup the superuser app role as a member of the restricted app role. Then add simple security filter on the same objects that are restricted with the expression: 1=1. As we saw with hierarchies from Scenario 4, the security filter at the lowest app role will be used. So in this case since superuser app role is a member of the restricted role and we placed an overriding security filter on it with 1=1, OBI will not place the filter. NO FILTER APPLIED! Now if we don’t place any security on the superuser role it will use the next security filter applied up the app role hierarchy. So in this case the restricted app roles security will be applied.

OBIA comes with a set of domain values, dependent on the bi app that will be implemented.  These domain values are distinct sets of values/codes that act as conformed warehouse code sets.  In the sense, that multiple sources can map their codes to warehouse specific codes (source independence); thus, giving the ability to utilize multiple sources with one code set.  The OOTB domain value sets are used to create and calculate prepackaged metrics which are used in OOTB reports.  Now there are cases where the source data can not be easily mapped to a domain value or the list of available domain values does not fit your model and you wish to extend the list of available domain values to map to.  In prior OBIA implementations, this was done via files. Starting with OBIA 11.1.1.7+ which uses ODI this is now controlled and done within CM (Configuration Manager) and/or FSM (Functional Setup Manager).  However, some domain value sets do not allow you to extend or add new domain codes.  This leaves you with just the OOTB list of values which you may or may not like.  The solution I propose will allow you to add values to these none extensible domain value sets.

 

NOTE: This is not supported by Oracle. Use at your own risk. Basically there might be OOTB measures that are using these DW code/value pairs in pre-built metrics. With changes to these non-extensible domain values, one can potentially break these pre-built metrics and OOTB reports. Any changes will need the RPD metrics and OOTB reports to be analyzed and modified accordingly.

 

The RCU schema XXX_BIACOMP houses these domains and their members (or value lists). The workaround to make a non-extensible domain extensible, so that it’s value list can be changed and used in Functional Setup Manager, is the following:

  1. Launch Configuration Manager in browser: http://<host&gt;:9704/biacm
  2. Log in as bi apps admin user: (ie. biadmin)
  3. Navigate to Domains Administration -> Manage Warehouse Domains
  4. Search for your Offering and Domain in question
  5. Note the Code for the Warehouse Domain that needs to be extensible.
  6. Now log into the XXX_BIACOMP schema, using the XXX_BIACOMP user.
  7. Run the following DML to enable the domain. Use the Code noted from step 5:

UPDATE C_DOMAIN

SET EXTENSIBILITY_TYPE_CODE = ‘E’

WHERE DOMAIN_CODE = ‘<WAREHOUSE DOMAIN CODE>‘;

COMMIT;

8. Log back into Configuration Manager, and navigate to Domains Administration -> Manage Warehouse Domains.

9. Search for the warehouse domain to modify. Select the warehouse domain within the Warehouse Domains pane.

10. In the lower pane, Warehouse Members, click the (+) icon to create the new domain member code(s). This icon will now be enabled. Note: At this time, there are no easy ways to delete/remove members from domains, without hacking into the database. So it is not advised. 

11. Now, these new members are available for mapping to source codes using either one of the following two ways

a.  Navigate to Domains Administration -> Manage Domain Mappings and Hierarchies. Search for the domain(s) that were changed. You will observe existing OOTB source domain codes mapped to corresponding warehouse codes and also any new source codes, which have not been mapped. You can finish mapping source to warehouse domains here.

b. Change/modify source to warehouse domain mappings in Functional Setup Manager via implementation projects .

 

-Frank

It has been awhile, I think a year now, since my last blog.  It has just been a very busy past year.  In any event, I came across a problem that involves presentation hierarchy columns.  The ever-beloved new feature of 11g , which all users and clients love to use for pretty much all reports.

The issue: How to create a navigational report which will filter the presentation hierarchy column based on the selected record from the calling report?  This is otherwise known as report navigation via action links.

Well, there is few solutions already out there by Venkat from Rittman Mead here.  And similar others like this one, here. Pretty much, almost the same idea and way of doing it.  Each will work, but takes lots more development and maintenance.  There should be an easier way, right!  Like the “is prompted” option in filters.  But presentation hierarchy columns cannot be used in the filter section, only their corresponding level attribute counterparts can be added in the filter section.  This leads to the 2 solutions linked above to some extent.  To use any sort of filtering or pruning of the hierarchy columns however, remember it would need to be done in the  “selection steps” section.

So what is the easier way that I found, which is simple and easier to manage!?

First it all centers around selection steps and the “overridable” attribute for the xml attribute “saw:selectionStep” within the advanced tab. But you do not even have to go there to change anything, as I will show you the simple steps to be able to set this using the UI.  However, if you have an older version of 11g instance (I am using 11.1.1.6 version) you will need to modify the xml, which I will describing later.

The first step is to open up the “Selection Steps” pane in the target report.

Then “Edit” the 1 step within the presentation hierarchy column.

Step1

Third step is to switch the value of the “Action” drop down to: “Start with all members”, which will change the interface.  Now you will only see “Override with Prompt” checkbox.  Just mark this box and you are all set.

step2

Step3

This will add and set the “overridable” attribute = “true” as seen and highlighted below.

Important Advanced xml attribute setting

Now in older versions of 11g, I can not remember which version, this checkbox was added.  Before that, you did not have an easy way with the UI to set this.  Instead, what you needed to do is go into the advanced tab and set the overridable=”true” attribute.  Sometimes, you will not see the “saw:dimensionSelection” so you would need to create it.  Easiest way to do that, was to go to the  “Selection Steps” pane and select one value in the first selection step-> “Start with members”.  Then you would go back to the selection step and edit it to change back to “Start with all members”.  Now once that action is done, the xml section is already created for you and all you have to do is ensure that the “overridable” attribute = “true” for the “saw:selectionStep”.

My results can be seen here in the example:

Here is the calling, or high level report:

High level report - run

Here is what the low level or target report looks like with no filtering

Low level report - before

Here is the same exact low level, target report navigated from the high level report based on selecting the “Accounts Receivable” value.

Low level report - after

Follow

Get every new post delivered to your Inbox.