Page 1 of 1
Master-detail with parameter, SQL per detail request?
Posted: Tue Feb 02, 2016 3:17 am
by 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?
Re: Master-detail with parameter, SQL per detail request?
Posted: Tue Feb 02, 2016 7:49 am
by 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.
Re: Master-detail with parameter, SQL per detail request?
Posted: Tue Feb 02, 2016 11:20 am
by FrenkR
is it possible to do this using scripting via events as shown in a picture?
- ssevents.png (7.37 KiB) Viewed 3131 times
I have 2015.3 with latest patches.
Re: Master-detail with parameter, SQL per detail request?
Posted: Tue Feb 02, 2016 12:39 pm
by 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.
Re: Master-detail with parameter, SQL per detail request?
Posted: Tue Feb 02, 2016 12:51 pm
by FrenkR
I can't find this option in a designer(dictionary-datasource). Do I have to add it manually in a code?
Re: Master-detail with parameter, SQL per detail request?
Posted: Tue Feb 02, 2016 1:03 pm
by 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.
Re: Master-detail with parameter, SQL per detail request?
Posted: Tue Feb 02, 2016 1:56 pm
by 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.
Re: Master-detail with parameter, SQL per detail request?
Posted: Wed Feb 03, 2016 9:09 am
by 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.