Financial Statements (P&L, Balance sheet, etc) how to create in YF and emulate Excel Calculated Item
Hey there, hope everything is fine.
I'm trying to create some financial statements in YF for a customer and I have stumbled upon a roadblock. In the following image you can see the result I'm trying to accomplish.
Currently I have a table with each of the white rows shown above with includes each of the budget, execution this year, exec. previous year and exec. two years before. However, I'm not sure what is the best approach to calculate each of the blue rows, as those involve adding and subtracting some of the white rows. For example, the 6th row (Total Ingresos Operación) is calculated by adding the 3rd row and subtracting the 4th and the 5th ones. Even there is a row (margin) that is obtained by dividing two rows. Right now the customer uses this report by querying the same table in Excel and creating calculated items, this way they are able to filter data and calculate it dynamically.
I'm considering three approaches to solve this:
1- Create the totals and insert them in the table I'm using for the report, however this will affect the granularity of data and generate duplicate data inside the table.
2- Use Union Subqueries to create each of the totals by means of case statements, however as there are multiple total rows it might be an slow query to make.
3- Add empty rows for the totals using Union Subqueries and then make the calculations manually in Java using an Advanced Function. However, this option makes the report more difficult to update in the future.
However, each has it drawbacks. I have read how other BI tools solve this and it seems like this is something that BI tools suffer from: Tableau, Power Pivot and OBIEE, however just wanted to know what is the recommended path in Yellowfin or maybe how other customers have accomplished this kind of report which I believe is frequent.
¿Any ideas? your help is welcomed!