Adding Columns to Cross-tab
17 November, 2013
Hello:
I am trying to recreate this Excel report in Yellowfin:
It's a report showing the past two weeks of payroll data and census data as well as the average data for the year. It includes data from two different sources (census days comes from our electronic medical record system, payroll data from our payroll software). I am comfortable using co-display top/bottom for the different sections, but would like to use a subquery for the average column if at all possible.
Using advanced (two DBs) append subqueries I've been able to replicate the 'Base+OT Wages' section for the two week period without issue. However, I'm having difficulty adding the average column. The databases themselves do not have averages, so I must do the calculation manually. I've managed to get all of the data on the report here:
. The problem is, the 2013 average data shows up under both dates and not as a separate column. Ideally the 'Average Subquery: Reg+OT for Avg', 'Average Subquery: CountD Period End Date', 'Sum Average Weekly Reg+OT' and 'Sum Avg PPD' columns would appear only under '2013', where '2013' is a third column to the right of the 1/12/2013 and 1/19/2013 columns. Is there any way to accomplish this? Even a creative workaround?
Alternatively, if I cannot use subqueries for this, is there a way to manually format the co-display so that the reports at least line up correctly? No matter what I do I can't seem to get them to line up:
Thanks!
Adam
I am trying to recreate this Excel report in Yellowfin:
It's a report showing the past two weeks of payroll data and census data as well as the average data for the year. It includes data from two different sources (census days comes from our electronic medical record system, payroll data from our payroll software). I am comfortable using co-display top/bottom for the different sections, but would like to use a subquery for the average column if at all possible.
Using advanced (two DBs) append subqueries I've been able to replicate the 'Base+OT Wages' section for the two week period without issue. However, I'm having difficulty adding the average column. The databases themselves do not have averages, so I must do the calculation manually. I've managed to get all of the data on the report here:
. The problem is, the 2013 average data shows up under both dates and not as a separate column. Ideally the 'Average Subquery: Reg+OT for Avg', 'Average Subquery: CountD Period End Date', 'Sum Average Weekly Reg+OT' and 'Sum Avg PPD' columns would appear only under '2013', where '2013' is a third column to the right of the 1/12/2013 and 1/19/2013 columns. Is there any way to accomplish this? Even a creative workaround?
Alternatively, if I cannot use subqueries for this, is there a way to manually format the co-display so that the reports at least line up correctly? No matter what I do I can't seem to get them to line up:
Thanks!
Adam
Hi Adam,
at the moment it is not possible to adjust the location of a co-display report to match that of the master report, so an enhancement request has been raised (support task 149556) so that this will be implemented at some point.
In the meantime a simple workaround would be to add an extra line in the Co-Display report description by going to the Edit->Name & Description menu item (I've added a line of hyphens):
and if the alignment is still not exactly right then you can also adjust the font size of the Title and/or Description of the Co-Display report as in the below screenshot:
I think you will have to do that because from your screenshots it looks like the alignment is out by 2.5 lines, so you'll most probably have to add 2 lines to the description, and also adjust font size of the Title or Description.
I hope that lines up the 2 reports, please let us know how it goes.
Regards,
Dave
at the moment it is not possible to adjust the location of a co-display report to match that of the master report, so an enhancement request has been raised (support task 149556) so that this will be implemented at some point.
In the meantime a simple workaround would be to add an extra line in the Co-Display report description by going to the Edit->Name & Description menu item (I've added a line of hyphens):
and if the alignment is still not exactly right then you can also adjust the font size of the Title and/or Description of the Co-Display report as in the below screenshot:
I think you will have to do that because from your screenshots it looks like the alignment is out by 2.5 lines, so you'll most probably have to add 2 lines to the description, and also adjust font size of the Title or Description.
I hope that lines up the 2 reports, please let us know how it goes.
Regards,
Dave