Providing year-to-date and month-to-date columns without a subquery
7 June, 2016
Hi,
I have a client who wants to be able to report on average sales and margin percentage for invoice transactions in the current day, current week, current month, and each month for the year, and current year. One way to do this would be to create a sub-query for each different timespan, but the end user wants to create many reports with columns like this, and the subquery mechanism is a little laborious. Their optimal interface would be to have a set of columns in the dataview "Sales Last Month (May 2016)", "Sales Prior Month (Apr 2016)", and so on. They could drag in whichever column they wanted.
So our thoughts for implementation:
(1) create a calculated column for each timespan (CASE WHEN months_back = 1 THEN sales ELSE 0 END)
(2) use a virtual table doing same
(3) rely on a database view
Any suggestions on the best way to achieve this in YF?
Also, in all of these cases is there a way to add new fields via the XML import or programmatically add new fields (calculated or not) to a view?
Thanks,
Rand
I have a client who wants to be able to report on average sales and margin percentage for invoice transactions in the current day, current week, current month, and each month for the year, and current year. One way to do this would be to create a sub-query for each different timespan, but the end user wants to create many reports with columns like this, and the subquery mechanism is a little laborious. Their optimal interface would be to have a set of columns in the dataview "Sales Last Month (May 2016)", "Sales Prior Month (Apr 2016)", and so on. They could drag in whichever column they wanted.
So our thoughts for implementation:
(1) create a calculated column for each timespan (CASE WHEN months_back = 1 THEN sales ELSE 0 END)
(2) use a virtual table doing same
(3) rely on a database view
Any suggestions on the best way to achieve this in YF?
Also, in all of these cases is there a way to add new fields via the XML import or programmatically add new fields (calculated or not) to a view?
Thanks,
Rand
Hi Rand,
Hope you're well.
I believe in this case your best option would be what you've raised in point 2. You'd likely be able to write some freehand SQL in a virtual table to get the data and fields you need. This would also be the same way you'd "programatically" add fields to view. You cannot add non-existing fields via XML, however creating a virtual table and writing your SQL to provide you with the values you need should achieve this.
Unfortunately, other than using a virtual table with freehand SQL you'd need to do it through subqueries on each report, or create a new field in your database itself that contains this information.
Please let me know if you have any questions or concerns, or if you need further clarification.
Cheers,
Cadell.
Hope you're well.
I believe in this case your best option would be what you've raised in point 2. You'd likely be able to write some freehand SQL in a virtual table to get the data and fields you need. This would also be the same way you'd "programatically" add fields to view. You cannot add non-existing fields via XML, however creating a virtual table and writing your SQL to provide you with the values you need should achieve this.
Unfortunately, other than using a virtual table with freehand SQL you'd need to do it through subqueries on each report, or create a new field in your database itself that contains this information.
Please let me know if you have any questions or concerns, or if you need further clarification.
Cheers,
Cadell.
Hi Cadell,
Thanks for the response. My concern with the virtual table is that it appears that the entire virtual table query statement is included in the SQL for the report. So if there are over 100 columns in the view, this could have a run-time performance impact when returning a report large dataset even if we are only using one column from the virtual table in a report.
Given that I am going to test adding a couple hundred columns of this type in a view, I was hoping for a way to load the field definitions into the Yellowfin database and not have to add them through the UI.
Thanks,
Rand
Thanks for the response. My concern with the virtual table is that it appears that the entire virtual table query statement is included in the SQL for the report. So if there are over 100 columns in the view, this could have a run-time performance impact when returning a report large dataset even if we are only using one column from the virtual table in a report.
Given that I am going to test adding a couple hundred columns of this type in a view, I was hoping for a way to load the field definitions into the Yellowfin database and not have to add them through the UI.
Thanks,
Rand