Joining two datasets?

Hi All,

I have got two datasets. Some 'normal' rows with state values and an addtional dataset with the translation from state value to plain text (value / text pair).

A table object is attached to the first dataset, but I would like to show the plain text from the second dataset in a group header. So I'd like to do some kind of 'look up in another dataset'.

I wasn't able to find a function that could help me solve this problem. Does anyone has any ideas on this?

Kind regards.

Create 2 new parameters: Parameter1 will have all the values from DataSet2 (dataset with text/value paur) and Parameter2 will have all the text from DataSet2.

Write a function (VB.Net ot C#) in the report code (Report -> Report Properties -> Code tab) which will accept these parameters as object arrays and then store them in a shared variable.

Create a textbox in the beginning of the report which will call the above function by passing both the parameters. If you just specify the parameter (say Parameter!Parameter1.Value), it is considered as an array as it have more than one value and it will be received in the function as an object array.

Write another function (function2) in the code which will get the state id (or value) and then loop through the shared arrays (already stored) to find the corresponding text.

Call function2 from your table header to get the text for a given value.

Hope this helps. If more examples and explanation is needed, let me know.

Shyam

|||

Hello Shyam,

thanks a lot for your post. I think this a way to solve my problem.

But also think MS should think about this for future versions. I think it is very complicated (Crystal Reports makes it much easier for developers). It would also be very helpful if datasets could be accessed directly within custom funcation.

Kind regards, CLive76

|||

An alternative approach is to combine the two datasets already in the dataset query. Some options to consider:
? Linked Server functionality (see http://msdn2.microsoft.com/en-us/library/aa213283(SQL.80).aspx)
? OpenRowSet functionality to join data from another database server into the current query (http://msdn2.microsoft.com/en-us/library/ms190312.aspx, http://msdn2.microsoft.com/en-us/library/aa276850(SQL.80).aspx)

-- Robert