How to consolidate several rows in one line in OTBI Pivot table?

Paul Younes

Hi All,

Thank you in advance for your assistance. I have a query regarding the OTBI report I was working on for asset depreciation. I was wondering if it is possible to merge the lines and rows of the pivot table so that they appear in a single line.

Allow me to explain the expected report:

The desired report should feature a pivot table where selecting the date (specifically, As of Dec 2019) would display the brought forward depreciation as of December 2019. From January to December 2020, the table should show the monthly depreciation amounts for each month in 2020, with the rest of the table being calculations.

However, the report that I have created shows the depreciation amounts diagonally and shows $116.37 (Which is the brought forward depreciation) for Dec 2020 in column I. My primary concern is how to consolidate all these diagonal amounts into a single row, similar to the layout shown in the above table.

I have attached the Excel file for your reference.

DGJ - Asset depreciation V1.3 (6).xlsx

Best regards,

Paul

RichardChan

You probably need to dip into some SQL such as the following https://blogs.oracle.com/sql/post/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot