Business Intelligence – Oracle

Oracle BI EE 10.1.3.3/2 – Achieving Financial Template Layouts using Pivot Tables

Posted by Venkatakrishnan J on January 10, 2008

I had a couple of emails referring to a particular new features guide here. It has been said in this new features guide that one can achieve highly formatted financial layouts and also an example is given in the form of screenshots. A couple of users wanted to know how to go about achieving the same. Now lets start with exactly the same pivot table as shown in the guide above and try to achieve the same.

      

In the final layout, we only need the BalanceGroup1, AccountDesc and Amount columns. So lets hide the remaining 2 i.e BalanceGroup2 and BalanceGroup3. We are not removing these columns since we need sub group sums.

      

Once this is done, if you look at the final layout required, there is a line break between BalanceGroup1 and Account Desc. So, we would have to move the BalanceGroup1 column to the Sections area.

      

If you notice, we still have to position the Assets to the left and then remove the tabular headings. In order to achieve this, go to the Section Properties and insert Page Break on the BalanceGroup1 column. After that go to the Measure Labels section and hide the headers.

      

      

Now, we need to include to the sub-totals for both BalanceGroup2 and BalanceGroup3.

      

Next is to left Align the Assets value. Go to the Section Properties and change the Horizontal Cell Alignment to Left.

      

Next we need to change the label names to something Total Assets etc. In order to do this we shall use “Total @”. @ will bring the value of the column.

      

Now the next step is to add indentation to all the Totals and subtotals. Go to the hidden BalanceGroup2 total Label properties and add a left indentation of 25. Similarly add the left indentation for BalanceGroup2 to 50 and AccountDesc values to 75.

      

      

Our result so far will be like this.

      

Now the next step is to remove all the border lines. In order to do this go to all the Columns and Section properties and change the Border Position to None. For example go to AccountDesc Format Values and change the position to None as shown below.

      

      

The following pic shows all the places that you need to change the Border Position to None.

      

The next step is to change the background color of the subtotals and the AccountDesc to #FFFF (that will give a white background). And lastly if you want total values to have 2 horizontal bars on the top just go to border position of the sub total and include the top two borders.

      

And this should give you the desired Financial Template Layout.

      

I know some of the above is pretty trivial. But this should give you an idea of how to achieve very good formatting with pivot tables.

Advertisements

16 Responses to “Oracle BI EE 10.1.3.3/2 – Achieving Financial Template Layouts using Pivot Tables”

  1. Bala said

    The Financial Statement was brilliant. Thanks a ton for the same. I am also looking for how to do GAP analysis where generally the data should be cumulatively added/deducted based on column values.

    Thanks
    bala

  2. […] J on January 18, 2008 Well, this is an extension of what we saw earlier the other day here. I was told by one user that though they were able to mimic the report in terms of look and feel, […]

  3. Heidi said

    I am new to OBIEE and this posting was very helpful–Thank You!

    On a similar note, I need to create an income statement and was wondering if you could offer any tips. So I start with the account_type column (values of Revenue, Cost of Sales, General Exp, Depreciation, Interest, Tax). I need to total each account_type and also create totals between accounts (essentially a running total). The grand total is the net income/loss. The intermediary totals I need are Gross Margin (Revenue – COS), EBITDA (Gross Margin – General Exp), Income from Operations (EBITDA – Depreciation and Amortization), etc. Essentially this is a running total of level breaks. Any advice or tips?

  4. Sam said

    What we need is running sum of subtotals and also the flexibility to do calculations what Heidi described above. This is impossible to achieve using OBIEE. It is not fit for financial reports yet.

  5. Carol said

    It looks like it has been a few months since the last post. Has anyone found a solution for creating an income statment with subtotals as Heidi describes for EBITDA?

  6. Sam said

    Yeah I did find a solution to this. Send me an email and I will get back to you. It is just too complicated to explain in this text form. Even though we managed to achieve it, but I would recommend going for Hyperion solution for financial reporting if you can afford to buy the licence. OBIEE is good, but not fit for EBS reporting yet I believe.

  7. Ivan said

    Hi…

    Sam, can you e-mail me your solution at divko78@yahoo.com

    I’m having the same problem and I’m trying to find a solution..

    tnx in advance..

    Ivan

  8. Anton said

    Hi, guys!

    I also need your solution to construct income statement, could you email in to me: jh7rnn (at) gmail (dot) com

    Thanks,
    Anton.

  9. car jacks said

    I must say, that I could not agree with you in 100%, but it’s just my IMHO, which could be very wrong.
    p.s. You have an awesome template . Where have you got it from?

  10. […] Oracle BI EE 10.1.3.3/2 – Achieving Financial Template Layouts using Pivot Tables […]

  11. Benito Camelas said

    Hi, i see that you call the column as “Total @” this is ok but… if you call other column?? i saw in gauge´s that you can call other columns as “@1″,”@2″,”@3”,…[“@+position Column”] but in pivot table i can´t,why?

    😦

    Any ideas?

  12. Vikas Barsaiyan said

    Hi,

    I am trying to create a Profit and Loss statement using the above information and I am able to build it properly.But I have 9 hierarchies for my GL Codes as compared to 3 shown in the above example.Here because there are only 3 level final format for the report looks fine instead of that sub totals are repeating for some level(Fixed assets and investments) as it is a unbalanced hierarchy.I have 9 level so when I create the same format for me it doesn’t look good as the same subtotals are repeated 6-7 times.
    Can we suppress these repeating sub totals So that final format will be properly viewable?

    anyone can send me solution at vikas.barsaiyan@gmail.com.

    Thanks in advance.

    Regards,
    Vikas

  13. Andre said

    Thanks for the above section. We are in the process of implementing OBIEE, and I would like roll it out to our financial layout as well. Due to the simplixity of the GL, we want to use the above recommendation, however I have found that the sort order remains alphabetically. I would like to change this within the different sections – any way of doing this?

    Regards

    Andre

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: