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!
Cheers,
Gabriel
The same question
Hi Gabriel,
Just letting you know we have received this request and will get back to you in the next day or two, apologies for delays.
Also side note, we've moved this to a private ticket as you have some data showing which you may not want to be visible by all community users.
In future please use the 'Submit Post' only when you're ok with all users being able to see your post.
Regards,David
Hi Gabriel,
Just letting you know we have received this request and will get back to you in the next day or two, apologies for delays.
Also side note, we've moved this to a private ticket as you have some data showing which you may not want to be visible by all community users.
In future please use the 'Submit Post' only when you're ok with all users being able to see your post.
Regards,David
Hi David,
Thanks for your response. If you look at the image it is actually a wireframe, so there is no issue about displaying it to other users. Just in case that you can edit my previous post, I have translated the column headers in the following image to improve it. On the other hand, I wanted it to be public because I think is something other users can find useful as financial statements are a frequent requirement.
Thanks,
Gabriel
Hi David,
Thanks for your response. If you look at the image it is actually a wireframe, so there is no issue about displaying it to other users. Just in case that you can edit my previous post, I have translated the column headers in the following image to improve it. On the other hand, I wanted it to be public because I think is something other users can find useful as financial statements are a frequent requirement.
Thanks,
Gabriel
Hi Gabriel,
apologies for my 2 colleagues who thought that your figures such as 123'456.789, 123'456.789, 123'456.789 and also 123'456.789, not to mention your percentages of 100%, 100%, 100% and also 100% should be kept from the prying eyes of the general public!
I have moved the private ticket back to a public Question as requested (let's hope nobody steals this top secret company data!)
I think your 3 solutions are probably the best available ones, probably I would favour no. 2 myself, and I would definitely give it a try first, it mightn't be as slow as you think. Also, if your data isn't changing every minute, or hour or day (because it looks like yearly data to me) then you could also employ view or report caching which would also improve the performance of the report execution.
Another idea would be the use of Virtual Tables in your view, this would be a variation on your solution no. 1. The difference being that instead of introducing aggregations in your original database table (or indeed, introducing an aggregation table) you could achieve the same by using a Virtual Table at the view level in Yellowfin.
Also, I wonder if you have considered the use of Co-Display reports to break up the Financial Statement into its constituent components, for example, a Co-Display report for Ingresos Operación, and a 2nd Co-Display report for Egresos and so forth, that way you could use the fact that totals are at the bottom of each sub-section, and yet when all the Co-Display reports are combined, those totals would appear throughout the whole report instead of just one total at the bottom.
I hope some of these suggestions might be of some use, I would certainly be interested to hear how you get on with this.
And because this ticket has been made public again, you never know - someone else might like to join in the discussion and offer their opinion.
regards,
David
Hi Gabriel,
apologies for my 2 colleagues who thought that your figures such as 123'456.789, 123'456.789, 123'456.789 and also 123'456.789, not to mention your percentages of 100%, 100%, 100% and also 100% should be kept from the prying eyes of the general public!
I have moved the private ticket back to a public Question as requested (let's hope nobody steals this top secret company data!)
I think your 3 solutions are probably the best available ones, probably I would favour no. 2 myself, and I would definitely give it a try first, it mightn't be as slow as you think. Also, if your data isn't changing every minute, or hour or day (because it looks like yearly data to me) then you could also employ view or report caching which would also improve the performance of the report execution.
Another idea would be the use of Virtual Tables in your view, this would be a variation on your solution no. 1. The difference being that instead of introducing aggregations in your original database table (or indeed, introducing an aggregation table) you could achieve the same by using a Virtual Table at the view level in Yellowfin.
Also, I wonder if you have considered the use of Co-Display reports to break up the Financial Statement into its constituent components, for example, a Co-Display report for Ingresos Operación, and a 2nd Co-Display report for Egresos and so forth, that way you could use the fact that totals are at the bottom of each sub-section, and yet when all the Co-Display reports are combined, those totals would appear throughout the whole report instead of just one total at the bottom.
I hope some of these suggestions might be of some use, I would certainly be interested to hear how you get on with this.
And because this ticket has been made public again, you never know - someone else might like to join in the discussion and offer their opinion.
regards,
David
Hi Gabriel,
I just I'd pass on to you some exciting news I heard about this subject matter!
In the next major release of Yellowfin there will be new functionality that allows you to fully customise your dashboards, so in terms of financial statements this means that you'll be able to set up separate minor reports on a dashboard tab that represent the different parts of a financial statement, and then be able to pass their totals or sub-totals to a final report that combines together all of the separate calculations.
regards,
David
Hi Gabriel,
I just I'd pass on to you some exciting news I heard about this subject matter!
In the next major release of Yellowfin there will be new functionality that allows you to fully customise your dashboards, so in terms of financial statements this means that you'll be able to set up separate minor reports on a dashboard tab that represent the different parts of a financial statement, and then be able to pass their totals or sub-totals to a final report that combines together all of the separate calculations.
regards,
David
Replies have been locked on this page!