Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 208 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 76 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Accounting Format using OBIEE Analytics or BI Publisher
Hello,
I have a requirement to create a report in OBIEE 12c either using BI Publisher or Analytics. We need to mimic the Accounting format from Excel (currency sign is left justified and numbers are right justified). The Currency format in OBIEE will not be enough to satisfy the requirement.
Example:
| Product | Price |
|---|---|
| Hard Drive | $ 350.00 |
| Keyboard | $ 25.00 |
Also, we need to be able to display the report in OBIEE first and then be able to export it to Excel and PDF (so we can't just make an BIP Excel template and upload to BI Publisher as there is no preview option within OBIEE other than XLS). I attempted to create a Word RTF template that came close to achieving the Accounting format, but if the number goes over a certain character then the text will wrap.
I'm wondering if it's possible to enter some custom .js to permanently change the Currency format in Analytics to justify the dollar sign to the left and numbers to the right? Not entirely sure if this can work.
If anyone has any thoughts or suggestions on the matter, that would be great.
Thanks!
Answers
-
kamrandb wrote:I have a requirement to create a report in OBIEE 12c either using BI Publisher or Analytics. We need to mimic the Accounting format from Excel (currency sign is left justified and numbers are right justified). The Currency format in OBIEE will not be enough to satisfy the requirement.
What does that mean? Why will it not be enough? You can format anything anyway you want and thanks to the locale definitions you can set this stuff also system-wide rather than manually changing one after the other after the other after the other
0 -
The default Currency format holds the currency symbol against the number as such: $350.00. As shown in the example table above, the $ currency symbol must be left justified in the cell and the number needs to be right justified as per the requirement. Take a look at Excel's Accounting format, we need the exact same functionality.
Hope that makes sense.
0 -
Got it, thanks for the explanation. Have fun then.
0 -
Pretty sure this is not possible in Analytics unless you want to use 'tricks'.
BI Publisher may be better equipped to do this, you can have a look at formatting options here: https://docs.oracle.com/middleware/12212/bip/BIPRD/GUID-7BD3C665-851A-43FE-BA2D-7501390D95AC.htm#BIPRD2554.
0 -
Better not to do it.
The dollar sign is just noise in the presentation, it actually detracts from the number, it would be better placed in the heading, or if it changes by record as a separate column, this is Business Intelligence not 'lets recreate excel because that is the best solution (sic) for everything'.
But, if you must do what you always did then look at using rpad in a formula as the width - (length of the number cast as a char).
0 -
Agree on not doing this in Analytics, however I think BIP is a good tool for this kind of 'pixel-perfect' requirements.
rpad is one 'trick' to do it yes but there are caveats to this approach like not being able to aggregate the measures, so it all depends on the exact requirement.
0 -
Sure, but I stand by my statement that the $ here is just noise, and if the currency is dynamic then any further analysis by the user is actually made harder as they then have to find a way to separate out the concatenated currency sign...
And yes, I know you cannot then aggregate it with my rpad trick, unless you further over engineer by creating totals via a union or similar further trickery.
On BIPS again why use a good tool to deliver a sub-optimal result?
0 -
Robert Angel wrote:
On BIPS again why use a good tool to deliver a sub-optimal result?Fully agree if you need trickery to do it, but if it's possible to do it with proper formatting, why not? Hence the link to the BIPS formatting document.
BTW, of course I know you understand the rpad would disable aggregating, just wanted to make sure the OP realizes trickery is almost never the right way to go ;-)
0 -
I think the OP has abandoned this thread a long time ago
0 -
Hi,
I think the OP has lost in interest in the question...
But to explain my position, a message that is repeated,
and repeated,
and repeated,
and repeated,
and repeated,
and repeated,
and repeated,
and repeated,
and repeated,
and repeated,
and repeated,
line after line
after line
after line
after line
after line
after line
after line
after line
after line
is ignored.
A message that appears when it is really needed EMERGENCY! in the correct format to best get that message across will be received and understand.
And, by making the $ part of the format, if that part is relevant it will actually be harder to parse back out if the user needs to further process the data.
Worst of all "We need to mimic the Accounting format from Excel "
No - they don't - they need to start thinking about the best way to use the new and fundamentally different and more powerful tools that they now have and how to get the best results out of them....
0