Report from collecting Data from DB

[info@2next.it]

Hello,
we are testing report designer for our company... We create datasource from sql server database and we select the tables for report. Using Databand, we build the report. We have investigated by sql server Profiler:

Profiler.png (63.19 KiB) Viewed 4343 times

The report has master databand with Dept, and there is a variable on the report to show only "Dept1" (filter type is sql server)... all is ok!
We create the relations like as in image:

Structure.png (72.35 KiB) Viewed 4343 times

The second Databand shows the movement of Dept1 using relation and Master data Band association.
The report show right result, but...

The question is: Why does the report collect all rows from Movements and Product Table instead to perform an inner join statement? In the event that there are many and many records on Movement table what can we do? Also Products table is scanned for all rows...

Thanks for your time
Luca Pastore

[Alex K.]

Hello,

Relations it is a report engine feature and does not affect on the query which sending to the database. In your case, you need to change your queries.
In this case, you can use parameters in details data sources and set the ReconnectOnEachRow property for the detail data source. In this case, detail data will be reconnected for each master record and get already filtered data.
Also, you can set the RetrieveOnlyUsedData property for the report to true (available in the last builds).

Thank you.

[info@2next.it]

Thanks for your answer!

>In this case, you can use parameters in details data sources and set the ReconnectOnEachRow property for the detail data source. In this case, detail data will be reconnected for each master record and get already filtered data.

Ok, we have tested it on your report that you attached with northwind db and it is a right way to filter data and not reads all rows!

>Also, you can set the RetrieveOnlyUsedData property for the report to true (available in the last builds).

We have downloaded the last build, 2016.1.12. We check the option but we have the same result without less reads... can you attach an example of report with northwind db so we can understand how the option works?

thanks
Luca and staff...

[Alex K.]

Hello,

> We have downloaded the last build, 2016.1.12. We check the option but we have the same result without less reads... can you attach an example of report with northwind db so we can understand how the option works?

If this option set to true, then only used in the report data sources will be connected not all from the dictionary.

Thank you.

[info@2next.it]

Hello,
thanks for your answer!

>If this option set to true, then only used in the report data sources will be connected not all from the dictionary.

It's true, if checked get only data in report... but forget the related data!!!!!

like in example, into second blue bar there is a related field articoloset.dicituradescrizione.titolodicitura

test1.png (127.84 KiB) Viewed 4324 times

result with no checked RetrieveOnlyUsedData:

test2_with_option_unchecked.png (33.61 KiB) Viewed 4324 times

result with checked RetrieveOnlyUsedData:

test3_with_option_checked.png (31.16 KiB) Viewed 4324 times

Profiler: here there isn't query for related filed, table "dicituraset"... so result is empty like as second image!! With this flag checked i can't use related data... and doesn't work filter on related field like as last image... it is a big limitation...

test4.png (61.59 KiB) Viewed 4324 times

test5.png (51.49 KiB) Viewed 4324 times

I hope you have a solution... we are eager to begin to use this report solution in my company!!!

thanks
Luca and Staff

[Alex K.]

Hello,

We have some issues. We need some time for fix the problem. We will let you know about the result.

At the current moment, you can use the ReconnectOnEachRow option for your task.
Thank you.

[info@2next.it]

Hello,

thanks very much, in the meantime we are waiting for news

Luca
2next staff

[Alex K.]

Hello,

Ok.
Let us know if you need any additional help.

[info@2next.it]

Hello,
we have another question about your example ReconnectOnEachRowForDetail.mrt.
In this report you assign to parameter @CatID an expression that is Categories.CategoryID directly on object query.

If in a report I have to show of the same object 2 different list of value.. for example if a Product have CategoryID and CategoryID2... how can I use the same data soure if the parameter is already assigned? It is possible put 2 databand of the same source and then to assign CategoryID and CategoryID2 respectively to the expression into report?

We have to try to assign into filter, but i can see the parameter @CatID when exapand Product.

thanks
2next staff

Attach image:

1.png (307.96 KiB) Viewed 4205 times

2.png (84.58 KiB) Viewed 4205 times

[Alex K.]

Hello,

Can you please send us a simple report which reproduces the issue for analysis.

Thank you.