nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Accounting Format in OBIEE

partycrane

I'm writing a series of analyses that include dollar amounts, and the stakeholders wish to use a specific accounting format. However, I have not yet figured out how to do this in OBIEE.

For cells with dollar amounts, only the first row and totals/subtotals should have a dollar sign. The dollar sign must be left-aligned, while the numbers must be right-aligned, as shown below:

Is there a way to achieve this that doesn't require administrator privileges or RPD changes? They are not an option at this point.

Find more posts tagged with

OBIEE 12c

Answers

Accepted answers

All comments

#Mayur

Hi partycrane,

Please try with the Conditional Formatting.

Thanks,

#Mayur

partycrane

What is the condition? It can't be based on the current sort--so I can't pick the value of the first row and make that the condition. The formatting needs to stay the same regardless of sort. In other words, the first line always has a dollar sign. There's also the requirement that the dollar sign be left-aligned while the amount is right-aligned.

I've tried setting up a column with a control value that's 1 if it's the first row, and 0 if it's not, but I haven't figured out how to express that it's the first row, and not the value of the first row. If possible and successfully implemented, this would take care of the first row issue, which could open some more doors formatting-wise (i.e., using a separate column for the dollar sign that only populates for the first and total rows).

#Mayur

Hi partycrane,

I have added the rank column based on the fact and applied the Conditional Formatting. When Rank is one it will applying the conditional formatting for it.

And for the total we don't have the option for the formatting. For this I have added Union with the same columns set in the analysis and added again the same Conditional Formatting.

I am not sure how to pick the first value from the analysis. If we got that we can easily achieve it.

Thank you,

#Mayur

Gianni Ceresa

#Mayur wrote:And for the total we don't have the option for the formatting. For this I have added Union with the same columns set in the analysis and added again the same Conditional Formatting.

Just a thing to try ...

Why don't you set the $-sign format as the default format of the column, which is then also used by the total (in theory), and don't use the conditional format to remove the $ sign from all the other rows?

Conditional formatting don't affect totals as you saw, if you set a condition matching rows > 1 (so from second on) you are supposed to get what you are doing without a dirty union.

Gianni Ceresa

partycrane wrote:The dollar sign must be left-aligned, while the numbers must be right-aligned, as shown below:

You must know that this will be not doable or at least not easily. In HTML you can't easily align a piece of text on the left and one of the right from the same element. It would require some "exotic" CSS to add the $ sign on the left while having number aligned to the right.

And of course it will not really work in an export etc.

Analysis aren't pixel perfect reports (which is what BI Publisher does), so they will need to accept the $ to not be left-aligned but just added in front of the number right-aligned.

partycrane

Having the condition be that the rank is 1 is the same as having the condition that it be the highest measure value. What I need are row numbers that are independent of the data--no matter how I sort, the first line must be 1. I've been experimenting with RCOUNT() and RSUM(), but I haven't yet been able to achieve what I'm trying to do.

partycrane

Without the financial authority to purchase BI Publisher, and with stakeholders who want what they want, I must do my due diligence prior to saying it can't be done with our current resources. I actually have some ideas to handle the formatting--the greater issue is identifying the first row of the table, independent of data.

SonPat99

Hello Partycrane,

Are you going to sort runtime or you are doing it by defining it in the analysis itself?

partycrane

Runtime. The report in question looks at the grant production rates for faculty, subtotaled for tenure status. The default sort is alphabetical within each tenure status, but the stakeholders also want to be able to sort by production rate.