Max rows limit in "Report as a view"

Joan Poblet shared this question 2 years ago
Answered

Hi,


I have a question fter looking at the ticket http://community.yellowfin.bi/topic/can-i-save-my-report-as-a-view


When you create a report as a view, is the report max rows limit kept or not? If it is, is there any way to avoid the max rows limit in this kind of report?


I would like to create a report as a view with around 250.000 rows, in order to apply a double aggregation but our configured max rows limit for reports is 20.000.


That's why I'm asking about this topic.


Thank you.


Best Regards,

Joan.

Comments (8)

photo
1

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.

photo
1

Thanks Joan,


I will look into this shortly.


Regards,

Nathan

photo
1

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

photo
1

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.

photo
1

Hi,

Anyway, please find more information about the view:

f9fb824f2c799598a0a435c89c0190ad

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.

photo
1

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

photo
1

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.

photo
1

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

photo
1

Hi Nathan,


We have finally decided to change the data load process to avoid having duplicated information. If it works, we won't longer need to create a report without row limit.


So, you can close the ticket. If we finally need this option, we will try it and, if we have any doubt, we will let you know.


Thank you.


Best Regards,

Joan.

photo
1

Hi Joan,

That sounds good, I will close this now but if you have any trouble, just respond and it will be automatically re-opened.

Regards,

Nathan

photo