Relation across multiple databases with parameters
Relation across multiple databases with parameters
Hello,
I have the following situation with version 2012.1 (release version):
Database A (SQL Server)
- Table A
- Id (uniqueidentifier)
- B_Id (uniqueidentifier)
Database B (SQL Server)
- Table B
- Id (uniqueidentifier)
There's a one to one relationship between the tables in the different databases. Table A.A (B_Id) references Table B.B (Id).
I've created data sources for both tables (two different connections). I've added a relation from table A (child) to table B (parent).
I just put the datarow on the report (no master detail, no sub report, just one row with both parent and child data as it is 1-1). When I preview, it show all data, it works perfectly. But, to match the relationships, all data from B is queried (select * from B), which can become a huge table, so I don't want that.
I've searched these forums to make it so that it only collects the records from B which are actually referenced. This seems to be possible, but somehow I can't get it to work!
I've tried adding parameter with expression to Table B datasource, resulting in query: SELECT * FROM B WHERE Id = @Id. With parameter expression: A.B_Id. I've tried to change the reconnect on each row checkbox, but it all does not seem to help, it either seems to keep querying all records or I get errors like the following (I think because of the parameter with expression?):
"Specified cast is not valid."
It seems to have issues casting the values to guid or so? What I DO see is that there's a difference in type (columns seem to be guid, parameter I have set to uniqueidentifier manually).
Either way: questions:
1. Why the error and how to solve?
2a. Should it be possible to get this 1-1 relationship (no master detail) working the way I want (no select*, using a select per row?)
2b. If so, which are the exact steps to get this done? Or could it be working already if the error is solved?
3. About that 'reconnect on each row': if this setting is necessary, do I need to set it on the child datasource or the parent datasource or both?
4. Could this situation also work on two different database types? Like Oracle and SqlServer for the databases A and B?
Thanks in advance.
I have the following situation with version 2012.1 (release version):
Database A (SQL Server)
- Table A
- Id (uniqueidentifier)
- B_Id (uniqueidentifier)
Database B (SQL Server)
- Table B
- Id (uniqueidentifier)
There's a one to one relationship between the tables in the different databases. Table A.A (B_Id) references Table B.B (Id).
I've created data sources for both tables (two different connections). I've added a relation from table A (child) to table B (parent).
I just put the datarow on the report (no master detail, no sub report, just one row with both parent and child data as it is 1-1). When I preview, it show all data, it works perfectly. But, to match the relationships, all data from B is queried (select * from B), which can become a huge table, so I don't want that.
I've searched these forums to make it so that it only collects the records from B which are actually referenced. This seems to be possible, but somehow I can't get it to work!
I've tried adding parameter with expression to Table B datasource, resulting in query: SELECT * FROM B WHERE Id = @Id. With parameter expression: A.B_Id. I've tried to change the reconnect on each row checkbox, but it all does not seem to help, it either seems to keep querying all records or I get errors like the following (I think because of the parameter with expression?):
"Specified cast is not valid."
It seems to have issues casting the values to guid or so? What I DO see is that there's a difference in type (columns seem to be guid, parameter I have set to uniqueidentifier manually).
Either way: questions:
1. Why the error and how to solve?
2a. Should it be possible to get this 1-1 relationship (no master detail) working the way I want (no select*, using a select per row?)
2b. If so, which are the exact steps to get this done? Or could it be working already if the error is solved?
3. About that 'reconnect on each row': if this setting is necessary, do I need to set it on the child datasource or the parent datasource or both?
4. Could this situation also work on two different database types? Like Oracle and SqlServer for the databases A and B?
Thanks in advance.
Re: Relation across multiple databases with parameters
Hello,
We couldn't reproduce this bug.
Can you please send us a sample report with test data which reproduces the issue for analysis.
Thank you.
We couldn't reproduce this bug.
Can you please send us a sample report with test data which reproduces the issue for analysis.
Thank you.
- Attachments
-
- ReportSample.mrt
- (12.07 KiB) Downloaded 428 times
Re: Relation across multiple databases with parameters
Sorry for the late reply. Here is the report that shows the error on Preview. I tried to upload the sql for both databases as well, but I can't upload .sql files and changing the extension to .sq_l didn't work either, uploading kept hanging. Anyway, if you need more information, I'll gladly give it.
Also, another bug I noticed when reproducing, is that when adding a data source to a sql connection, and selecting uniqueidentifier columns by clicking the checkboxes and then clicking OK, it recognizes the column type as 'long' instead of 'guid', weird...
Also, another bug I noticed when reproducing, is that when adding a data source to a sql connection, and selecting uniqueidentifier columns by clicking the checkboxes and then clicking OK, it recognizes the column type as 'long' instead of 'guid', weird...
- Attachments
-
- Stimulsoft.mrt
- (7.11 KiB) Downloaded 561 times
Re: Relation across multiple databases with parameters
Hello.
You are trying to use the same name for parameter as the field name.
Please, try to change it.
Thank you.
You are trying to use the same name for parameter as the field name.
Please, try to change it.
Thank you.
Re: Relation across multiple databases with parameters
Hi,
Thanks for the response. I couldn't test it again before my vacation, but now I'm back and I've changed the name of the parameter to IdPar and the result is still the same. Also I noticed that the previous report I uploaded depended on a dll of ours, but this one does not.
So, still the same error.
Thanks for the response. I couldn't test it again before my vacation, but now I'm back and I've changed the name of the parameter to IdPar and the result is still the same. Also I noticed that the previous report I uploaded depended on a dll of ours, but this one does not.
So, still the same error.
- Attachments
-
- Stimulsoft2.mrt
- (7.22 KiB) Downloaded 365 times
Re: Relation across multiple databases with parameters
Hello.
If the error will occurs then send us sample data for your report.
Thank you.
Please, remove the IdPar parameter from the data source B and try to view your report.jcc_colin wrote:Thanks for the response. I couldn't test it again before my vacation, but now I'm back and I've changed the name of the parameter to IdPar and the result is still the same. Also I noticed that the previous report I uploaded depended on a dll of ours, but this one does not.
So, still the same error.
If the error will occurs then send us sample data for your report.
Thank you.
Re: Relation across multiple databases with parameters
Well, that works, and the report works, but then from both tables a SELECT * FROM A / B will be done (no where clause) and seeing as that can be a huge amount of data, we don't want that. The point of the parameter was to limit the amount of rows collected. From table B, we'd rather see one query per row.
In the attachment, I've now added both databases' table / data declarations.
In the attachment, I've now added both databases' table / data declarations.
- Attachments
-
- stimulsoftb.txt
- (1.38 KiB) Downloaded 382 times
-
- stimulsofta.txt
- (1.64 KiB) Downloaded 361 times
Re: Relation across multiple databases with parameters
Hello,
Please try to put the additional databand component and set the ReconnectOnEachRow property for datasource B to true.
Please check the modified report in attachment.
Thank you.
Please try to put the additional databand component and set the ReconnectOnEachRow property for datasource B to true.
Please check the modified report in attachment.
Thank you.
- Attachments
-
- Stimulsoft2_modified.mrt
- (7.92 KiB) Downloaded 401 times
Re: Relation across multiple databases with parameters
Thanks for your response.
I've added a databand with on Table B with Master component Data A, just like your example.
I've set DataSource B's property 'Reconnect on each row' to True.
I click preview, same error:
"Specified cast is not valid."
Stacktrace:
at Reports.Report.ADataSource.get_B_Id()
at Reports.Report.GetB_SqlCommand(Object sender, EventArgs e)
at Stimulsoft.Report.Dictionary.StiDataSource.InvokeConnecting()
at Stimulsoft.Report.Dictionary.StiDataSource.Connect(StiDataCollection datas, Boolean loadData)
at Stimulsoft.Report.Dictionary.StiDataSourcesCollection.Connect(StiDataCollection datas, Boolean loadData)
at Stimulsoft.Report.Dictionary.StiDictionary.Connect(Boolean loadData)
at Stimulsoft.Report.Engine.StiRenderProviderV2.ConnectToData(StiReport report)
at Stimulsoft.Report.Engine.StiRenderProviderV2.Render(StiReport report, StiRenderState state)
at Stimulsoft.Report.Engine.StiReportV2Builder.RenderSingleReport(StiReport masterReport, StiRenderState renderState)
at Stimulsoft.Report.StiReport.RenderReport(StiRenderState renderState)
at Stimulsoft.Report.StiReport.Render(StiRenderState renderState, StiGuiMode guiMode)
at Stimulsoft.Report.Design.Controls.StiDesignerPreviewControl.btRefresh_Click(Object sender, EventArgs e)
Uploaded my new version of report.
I've added a databand with on Table B with Master component Data A, just like your example.
I've set DataSource B's property 'Reconnect on each row' to True.
I click preview, same error:
"Specified cast is not valid."
Stacktrace:
at Reports.Report.ADataSource.get_B_Id()
at Reports.Report.GetB_SqlCommand(Object sender, EventArgs e)
at Stimulsoft.Report.Dictionary.StiDataSource.InvokeConnecting()
at Stimulsoft.Report.Dictionary.StiDataSource.Connect(StiDataCollection datas, Boolean loadData)
at Stimulsoft.Report.Dictionary.StiDataSourcesCollection.Connect(StiDataCollection datas, Boolean loadData)
at Stimulsoft.Report.Dictionary.StiDictionary.Connect(Boolean loadData)
at Stimulsoft.Report.Engine.StiRenderProviderV2.ConnectToData(StiReport report)
at Stimulsoft.Report.Engine.StiRenderProviderV2.Render(StiReport report, StiRenderState state)
at Stimulsoft.Report.Engine.StiReportV2Builder.RenderSingleReport(StiReport masterReport, StiRenderState renderState)
at Stimulsoft.Report.StiReport.RenderReport(StiRenderState renderState)
at Stimulsoft.Report.StiReport.Render(StiRenderState renderState, StiGuiMode guiMode)
at Stimulsoft.Report.Design.Controls.StiDesignerPreviewControl.btRefresh_Click(Object sender, EventArgs e)
Uploaded my new version of report.
- Attachments
-
- Stimulsoft3.mrt
- (7.9 KiB) Downloaded 355 times
Re: Relation across multiple databases with parameters
Hello,
Please check the last prerelease build.
Thank you.
Please check the last prerelease build.
Thank you.