Relation across multiple databases with parameters

Stimulsoft Reports.WPF discussion
jcc_colin
Posts: 9
Joined: Wed Aug 08, 2012 11:34 am

Relation across multiple databases with parameters

Post by jcc_colin »

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.
Alex K.
Posts: 6488
Joined: Thu Jul 29, 2010 2:37 am

Re: Relation across multiple databases with parameters

Post by Alex K. »

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.
Attachments
ReportSample.mrt
(12.07 KiB) Downloaded 428 times
jcc_colin
Posts: 9
Joined: Wed Aug 08, 2012 11:34 am

Re: Relation across multiple databases with parameters

Post by jcc_colin »

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...
Attachments
Stimulsoft.mrt
(7.11 KiB) Downloaded 561 times
HighAley
Posts: 8430
Joined: Wed Jun 08, 2011 7:40 am
Location: Stimulsoft Office

Re: Relation across multiple databases with parameters

Post by HighAley »

Hello.

You are trying to use the same name for parameter as the field name.
Please, try to change it.

Thank you.
jcc_colin
Posts: 9
Joined: Wed Aug 08, 2012 11:34 am

Re: Relation across multiple databases with parameters

Post by jcc_colin »

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.
Attachments
Stimulsoft2.mrt
(7.22 KiB) Downloaded 365 times
HighAley
Posts: 8430
Joined: Wed Jun 08, 2011 7:40 am
Location: Stimulsoft Office

Re: Relation across multiple databases with parameters

Post by HighAley »

Hello.
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.
Please, remove the IdPar parameter from the data source B and try to view your report.
If the error will occurs then send us sample data for your report.

Thank you.
jcc_colin
Posts: 9
Joined: Wed Aug 08, 2012 11:34 am

Re: Relation across multiple databases with parameters

Post by jcc_colin »

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.
Attachments
stimulsoftb.txt
(1.38 KiB) Downloaded 382 times
stimulsofta.txt
(1.64 KiB) Downloaded 361 times
Alex K.
Posts: 6488
Joined: Thu Jul 29, 2010 2:37 am

Re: Relation across multiple databases with parameters

Post by Alex K. »

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.
Attachments
Stimulsoft2_modified.mrt
(7.92 KiB) Downloaded 401 times
jcc_colin
Posts: 9
Joined: Wed Aug 08, 2012 11:34 am

Re: Relation across multiple databases with parameters

Post by jcc_colin »

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.
Attachments
Stimulsoft3.mrt
(7.9 KiB) Downloaded 355 times
Alex K.
Posts: 6488
Joined: Thu Jul 29, 2010 2:37 am

Re: Relation across multiple databases with parameters

Post by Alex K. »

Hello,

Please check the last prerelease build.

Thank you.
Post Reply