Max rows limit in "Report as a view"

Hi,

Just in case it isn't possible, I'm providing you more information, in order to help you to think the best option.

We have a Invoices view that has, among others, the following fields:

- Id invoice (unique)

- Cost center

- Department

- Invoice cost

The main report wants to display the sum of cost by cost center. The problem is that an invoice could be linked to more than one department but the cost is only invoiced once.

So, if there are 2 invoices for the cost center CC1, one with a cost of 500 and two departments and the other with a cost of 300 and 1 department, when I apply the aggregation the displayed result is:

CC1 1.300,00 €, what is wrong because the expected result should be 800,00 €.

Therefore I thought of creating a "report as a view" with the fields:

- id invoice

- cost center.

- New cost= max (cost)

With this, I can avoid the duplicated costs and then create a second report with the fields:

- cost center.

- sum (New cost).

Hope this information helps, maybe there is an easier way to do it without using "report as a view".

Many thanks.

Regards,

Joan.

Thanks Joan,

I will look into this shortly.

Regards,

Nathan

Hi Joan,

I think I have asked this before and you said it wasnt possible, but: Would you be interested in scheduling a time to screen-share? If so, please let me know what time works best for you. (I am on US mountain time)

Regards,

Nathan

Hi Nathan,

I am in CET. So, I work from 12PM (USMT) to 9:30AM (USMT). We could try whether it works or not. Let me know if there is any time slot in that time frame that could fit to you.

Thank you.

Regards,

Joan.

Hi,

Anyway, please find more information about the view:

An invoice oid has a unique num_contract, a unique cost and is invoiced only once but can be linked to more than a partner.

The problem is that if we want to sum the cost without grouping by the partner and some invoices are linked to more than one partner, the final aggregated price won't be good (it will display a higher price than the real one).

I know that a solution could be changing the view model to display the Partner as an isolated Folder but it would be difficult because there are several reports linked to this view.

Hope it helps to better understand the problem.

Thank you.

Best Regards,

Joan.

Hi Joan,

So in answer to your first question, a report from view will return the result of underlying report, which means that if a row limit is applied, the second report will inherit this. To insure that the report has all of the necessary rows, I would recommend entirely removing row limits.

Regarding your problem, would it be possible to provide me access to this report so that I can mess around with a few alternative ideas to get this done in one report?

Regards,

Nathan

Hi Nathan,

Is there any way to drop the max row limit for just one report?

Regarding the test report, how would you like us to provide you access to this report?

Thank you.

Best Regards,

Joan.

Hi Joan,

You can drop row limits on one report by removing row limits from the data source and view first and then specifying 0 as the reports row limit. Typically these are applied in hierarchical ordering, so data source will override view which will override report limits. Please let me know if this makes sense.

Regarding access, if your Yellowfin instance is publicly accessible, you can create me an account with report writer permissions and access to the report so that I can log in and look at the report directly (I will work in a copy of it)

Regards,

Nathan