Master-detail with parameter, SQL per detail request?

[FrenkR]

have following (very simplified) scenario. Master table is :

Code: Select all

select itemId, itemDesc from mdItem

detail table is:

Code: Select all

select orderId, orderQty, orderPrice from whOrder where itemId = @item

Is there any option that for each record from master databand, "detail" SQL parameter value is set to a value of master "mdItem.itemId" and detail dataset is updated(populated) according to a newly set parameter value?

[HighAley]

Hello.

Unfortunately, it's impossible.
The data are requested before report rendering.
So you should get all necessary data but not for each detail.

Thank you.

[FrenkR]

is it possible to do this using scripting via events as shown in a picture?

ssevents.png (7.37 KiB) Viewed 3112 times

I have 2015.3 with latest patches.

[Alex K.]

Hello,

In this case, you can use the ReconnectOnEachRow option for the detail datasource. Please check the sample report in the attachment.

Thank you.

[FrenkR]

I can't find this option in a designer(dictionary-datasource). Do I have to add it manually in a code?

[Alex K.]

Hello,

Unfortunately, on current moment, it is not possible to set any properties of data sources in the HTML5 designer. There is task in our to-do list, we will try to add it in the near future.
On current moment, you can set this option in the standalone designer.

Thank you.

[FrenkR]

I checked and it works for me, which is great. But I need to understand a bit more how it works. If I understand correctly, it is implemented on "detail". So How does it know or get signal that master (data) has changed? Does this property work when I have master-detail relationship on data-bands set up?

And yes, I would like to see this property in a HTML designer ASAP. Thank you.

[Alex K.]

Hello,

In this case, master band contain Text component with the following expresion:
{Products.Parameters["@CatID"].ParameterValue = Categories.CategoryID}
which set the parameter value for detail band when master band is rendering.
Then for the detail is setted the MasterComponent property (detail band) and ReconnectOnEachRow property (detail datasource). After master band printing the detail band reconnecting with current parameter value.

Thank you.