Need to acess a field in a dataset from the other dataset.

[Anubhav Jain]

Hi,

I am using MS SQL Server 2005 Reporting Services to develop the reports.
In my report I will be using 2 datasets say A and B.
Dataset A connects to the XML type Data Source Provider(i.e. querying Web services through the XML data provider).So I am able to get the data into the dataset A from the XML webservice.
Dataset A connects to the MS SQL Server Provider. Now I want to use fields from the dataset A into the query of the dataset B.
I tried to access the fields of the dataset A using query parameter @Address in the dataset B(like @Adress=First(Fields!Address.Value, "DecryptedAddressDS")) but getting error as "Fields cannot be used in query parameter expressions".

Are there other ways to acess a field in a dataset from the other dataset.

Thanks in Advance,
Anubhav Jain
MTS
Persistent Systems Pvt. Ltd.
Ph:+91 712 2226900(Off) Extn: 7026
Mob : 099605 93699
www.persistentsys.com
Persistent Systems -Software Development Partner for Competitive Advantage. Persistent Systems provides custom software product development services. With over 15 years, 140 customers, and 700+ release cycles experience, we deliver unmatched value through high quality, faster time to market and lower total costs.

 

[Bruce L-C [MVP]]

You can only use aggregate functions outside a dataset (min, max, first). So unless the field doesn't change value (in which case you can use first) you cannot do this the way you want. However, if I understand what you are trying to do you can do this with subreports. Turn the dataset B into a subreport (which is just a normal report that you drag and drop unto the other report. Create the dataset B report with parameters and thoroughly test. Then put it on the main report and do a right mouse click on the subreport, parameters and then map the parameter to the field in dataset A.


--
Bruce Loehle-Conger
MVP SQL Server Reporting Services



Hi,

I am using MS SQL Server 2005 Reporting Services to develop the reports.
In my report I will be using 2 datasets say A and B.
Dataset A connects to the XML type Data Source Provider(i.e. querying Web services through the XML data provider).So I am able to get the data into the dataset A from the XML webservice.
Dataset A connects to the MS SQL Server Provider. Now I want to use fields from the dataset A into the query of the dataset B.
I tried to access the fields of the dataset A using query parameter @Address in the dataset B(like @Adress=First(Fields!Address.Value, "DecryptedAddressDS")) but getting error as "Fields cannot be used in query parameter expressions".

Are there other ways to acess a field in a dataset from the other dataset.

Thanks in Advance,
Anubhav Jain
MTS
Persistent Systems Pvt. Ltd.
Ph:+91 712 2226900(Off) Extn: 7026
Mob : 099605 93699
www.persistentsys.com
Persistent Systems -Software Development Partner for Competitive Advantage. Persistent Systems provides custom software product development services. With over 15 years, 140 customers, and 700+ release cycles experience, we deliver unmatched value through high quality, faster time to market and lower total costs.

 

[Anubhav Jain]

Hi Bruce,

Thanks for the reply.
Actually I want to use the fields from dataset A into the one query of dataset B. Like the query given below

Select count(distinct ani) from callsfromaug where applicationid in (3,5) and convert(varchar,callsfromaug.ani) = @Address and @datecreated >= @currentDate

where @Address and @datecreated are fields from the dataset A.
So the subreport wont help me in this case

Anubhav Jain
You can only use aggregate functions outside a dataset (min, max, first). So unless the field doesn't change value (in which case you can use first) you cannot do this the way you want. However, if I understand what you are trying to do you can do this with subreports. Turn the dataset B into a subreport (which is just a normal report that you drag and drop unto the other report. Create the dataset B report with parameters and thoroughly test. Then put it on the main report and do a right mouse click on the subreport, parameters and then map the parameter to the field in dataset A.


--
Bruce Loehle-Conger
MVP SQL Server Reporting Services



Hi,

I am using MS SQL Server 2005 Reporting Services to develop the reports.
In my report I will be using 2 datasets say A and B.
Dataset A connects to the XML type Data Source Provider(i.e. querying Web services through the XML data provider).So I am able to get the data into the dataset A from the XML webservice.
Dataset A connects to the MS SQL Server Provider. Now I want to use fields from the dataset A into the query of the dataset B.
I tried to access the fields of the dataset A using query parameter @Address in the dataset B(like @Adress=First(Fields!Address.Value, "DecryptedAddressDS")) but getting error as "Fields cannot be used in query parameter expressions".

Are there other ways to acess a field in a dataset from the other dataset.

Thanks in Advance,
Anubhav Jain
MTS
Persistent Systems Pvt. Ltd.
Ph:+91 712 2226900(Off) Extn: 7026
Mob : 099605 93699
www.persistentsys.com
Persistent Systems -Software Development Partner for Competitive Advantage. Persistent Systems provides custom software product development services. With over 15 years, 140 customers, and 700+ release cycles experience, we deliver unmatched value through high quality, faster time to market and lower total costs.