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

This is a multi-part message in MIME format.
--=_NextPart_000_0017_01C74FB3.C419E480
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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=3DFirst(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.
--=_NextPart_000_0017_01C74FB3.C419E480
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
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=3DFirst(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 MTSPersistent Systems Pvt. Ltd.Ph:+91 712 2226900(Off) = Extn: 7026
Mob : 099605 93699www.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.


--=_NextPart_000_0017_01C74FB3.C419E480--This is a multi-part message in MIME format.
--=_NextPart_000_0110_01C74F57.382E3590
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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
"Anubhav Jain" <anubhav_jain@.persistent.co.in> wrote in message =news:%23KfLvW4THHA.528@.TK2MSFTNGP03.phx.gbl...
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=3DFirst(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.
--=_NextPart_000_0110_01C74F57.382E3590
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

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-CongerMVP =SQL Server Reporting Services
"Anubhav Jain" wrote in message news:%23KfLvW4THHA.5=28@.TK2MSFTNGP03.phx.gbl...
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=3DFirst(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 MTSPersistent Systems Pvt. Ltd.Ph:+91 712 =2226900(Off) Extn: 7026
Mob : 099605 93699www.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.

---

--=_NextPart_000_0110_01C74F57.382E3590--|||This is a multi-part message in MIME format.
--=_NextPart_000_0011_01C7501F.49516DB0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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) =3D @.Address and =@.datecreated >=3D @.currentDate
where @.Address and @.datecreated are fields from the dataset A.
So the subreport wont help me in this case
Anubhav Jain
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message =news:ee%23fEm4THHA.4632@.TK2MSFTNGP04.phx.gbl...
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
"Anubhav Jain" <anubhav_jain@.persistent.co.in> wrote in message =news:%23KfLvW4THHA.528@.TK2MSFTNGP03.phx.gbl...
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=3DFirst(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.
--=_NextPart_000_0011_01C7501F.49516DB0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

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) =3D @.Address and @.datecreated >=3D @.currentDate
where @.Address and @.datecreated are =fields from the dataset A.
So the subreport wont help me in this case
Anubhav Jain
"Bruce L-C [MVP]" wrote in message news:ee%23fEm4THHA.=4632@.TK2MSFTNGP04.phx.gbl...
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-CongerMVP =SQL Server Reporting Services



"Anubhav Jain" wrote in message news:%23KfLvW4THHA.5=28@.TK2MSFTNGP03.phx.gbl...
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=3DFirst(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 MTSPersistent Systems Pvt. Ltd.Ph:+91 712 =2226900(Off) Extn: 7026
Mob : 099605 93699www.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.

---

--=_NextPart_000_0011_01C7501F.49516DB0--

Need textboxs in a table to show zeros if no record found - not a NoRows message

I need a way to have the text boxes in a table to show a 0 if there is no record found for the query (not looking for a NoRows message). I've tried setting a default value for the textbox, but it isn't displayed since the query is empty. Is there a way to setup the query to have an if statement that would return a value of zero for the fields as in: If recordcount =0 then set field to 0?

Here is an example of how to substitute data on the report when none is available in the database. The"0" is the character returned and displayed on the report. This example is from the layout designer and goes into your column. In this example CB0StockStart is the value from the database being returned. I think it is possible to use NULL instead of 0 but can't remember of the top of my head.

=Iif((Fields!CB0StockStart.Value)=0,"0",Fields!CB0StockStart.Value)

|||u should try NOTHING instead of NULL
there is also a COUNT()-function if i remember right
|||

The syntax below is placed within the <Value> expression for the textbox, unfortunantely the textbox still does not appear within the table if there is no data. I think the solution needs to be at the table/query level rather than at the textbox level since the table is associated with a <DataSetName>. Any suggestions on how to return default data with the query.

<Value>=Iif((Fields!SubTotalHours.Value)=Nothing,"0",(Fields!SubTotalHours.Value * Fields!ProcessPercent.Value)/100)</Value>

|||

try something like this in your query

isnull(sum(fieldabc),0)

That will return a '0' when the field is null.

Good luck.

|||Using the ISNULL, but the textbox still does not appear. This seems to be a field level solution, is there something that works at the record level? My hunch is that the table is driven from the record, not the field.|||

stupid question, but is your textbox set to visible?!?

try the expression ="test" and test if you see it, if this works,

the IIF should also work

you could also try to change the format of the cell

greets

|||

The textboxes are visible when the query returns data.

I solved it by creating a record on the database that had zeros and then selecting that record if the original query is null.

Basically the query is:

If exists(Select * from table1 where id='1') select * from table1 where id='1' else select * from table1 where id='0'

sql

Need textboxs in a table to show zeros if no record found - not a NoRows message

I need a way to have the text boxes in a table to show a 0 if there is no record found for the query (not looking for a NoRows message). I've tried setting a default value for the textbox, but it isn't displayed since the query is empty. Is there a way to setup the query to have an if statement that would return a value of zero for the fields as in: If recordcount =0 then set field to 0?

Here is an example of how to substitute data on the report when none is available in the database. The"0" is the character returned and displayed on the report. This example is from the layout designer and goes into your column. In this example CB0StockStart is the value from the database being returned. I think it is possible to use NULL instead of 0 but can't remember of the top of my head.

=Iif((Fields!CB0StockStart.Value)=0,"0",Fields!CB0StockStart.Value)

|||u should try NOTHING instead of NULL
there is also a COUNT()-function if i remember right|||

The syntax below is placed within the <Value> expression for the textbox, unfortunantely the textbox still does not appear within the table if there is no data. I think the solution needs to be at the table/query level rather than at the textbox level since the table is associated with a <DataSetName>. Any suggestions on how to return default data with the query.

<Value>=Iif((Fields!SubTotalHours.Value)=Nothing,"0",(Fields!SubTotalHours.Value * Fields!ProcessPercent.Value)/100)</Value>

|||

try something like this in your query

isnull(sum(fieldabc),0)

That will return a '0' when the field is null.

Good luck.

|||Using the ISNULL, but the textbox still does not appear. This seems to be a field level solution, is there something that works at the record level? My hunch is that the table is driven from the record, not the field.|||

stupid question, but is your textbox set to visible?!?

try the expression ="test" and test if you see it, if this works,

the IIF should also work

you could also try to change the format of the cell

greets

|||

The textboxes are visible when the query returns data.

I solved it by creating a record on the database that had zeros and then selecting that record if the original query is null.

Basically the query is:

If exists(Select * from table1 where id='1') select * from table1 where id='1' else select * from table1 where id='0'

Need some help with update query

Hi There ,

I run the query below and get the error message below the query.
How can i do this update?

UPDATE GEO_Plaats
SET NetNummer = (SELECT GEO_Netnummers.Netnummer
FROM GEO_Netnummers
WHERE GEO_Netnummers.Plaats = GEO_Plaats.Plaats)
WHERE EXISTS
(SELECT GEO_Netnummers.Netnummer
FROM GEO_Netnummers
WHERE GEO_Netnummers.Plaats = GEO_Plaats.Plaats);

ERROR:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Cheers WimmoThis is untested but it might be of assistance.

UPDATE GEO_Plaats
SET NetNummer = n.Netnummer
FROM GEO_Plaats p
JOIN GEO_Netnummers n
ON p.Plaats = n.Plaats

Good Luck!|||As you see problem is in your subquery

(SELECT GEO_Netnummers.Netnummer
FROM GEO_Netnummers
WHERE GEO_Netnummers.Plaats = GEO_Plaats.Plaats)

which returns more than 1 record from GEO_Netnummers table for specific Plaats in GEO_Plaats table. Simply speaking one "Plaat" from GEO_Plaats have more records in GEO_Netnummers table (cardinality is 1:N)

If all records in GEO_Netnummers have the same Netnummer for specific GEO_Plaats.Plaats you can use distinct to retrive 1 record:

UPDATE GEO_Plaats
SET NetNummer = (SELECT DISTINCT GEO_Netnummers.Netnummer
FROM GEO_Netnummers
WHERE GEO_Netnummers.Plaats = GEO_Plaats.Plaats)
WHERE EXISTS
(SELECT GEO_Netnummers.Netnummer
FROM GEO_Netnummers
WHERE GEO_Netnummers.Plaats = GEO_Plaats.Plaats);

If GEO_Netnummers.Netnummer differs for specific GEO_Plaats.Plaats you have 2 options:
1) use max() or min() function to retrieve 1 record. But in this case you have to realise if your subquery returns Netnummers: 1, 2, 5, 70 you select max() it means 70 but what if 5 is correct one?

UPDATE GEO_Plaats
SET NetNummer = (SELECT max(GEO_Netnummers.Netnummer)
FROM GEO_Netnummers
WHERE GEO_Netnummers.Plaats = GEO_Plaats.Plaats)
WHERE EXISTS
(SELECT GEO_Netnummers.Netnummer
FROM GEO_Netnummers
WHERE GEO_Netnummers.Plaats = GEO_Plaats.Plaats);

2) use another restrictions in your WHERE clause to retrieve 1 unique number. This restrictions depend on your "business'' rules

UPDATE GEO_Plaats
SET NetNummer = (SELECT GEO_Netnummers.Netnummer
FROM GEO_Netnummers
WHERE GEO_Netnummers.Plaats = GEO_Plaats.Plaats
AND GEO_Netnummers.SOMETHING = SOMETHING
...)
WHERE EXISTS
(SELECT GEO_Netnummers.Netnummer
FROM GEO_Netnummers
WHERE GEO_Netnummers.Plaats = GEO_Plaats.Plaats);

It seems to me, Netnummer is primary key in GEO_Netnummers table, so I think DISTINC won't help. In this case I'd use 2. scenario and option 2|||As you see problem is in your subquery
[...]
which returns more than 1 record from GEO_Netnummers table for specific Plaats in GEO_Plaats table. Simply speaking one "Plaat" from GEO_Plaats have more records in GEO_Netnummers table (cardinality is 1:N)

It might be a data problem. The table and column names indicate this is about the Dutch phone system; plaats = city, netnummer = area code.

As far as I know, a 'plaats' is supposed to have exactly 1 'netnummer'; several 'plaatsen' may share a 'netnummer'. If that's true and this error occurs, there may be invalid data in the GEO_netnummers table.

The error might also occur if the joining is done on the city's name; several cities may exist with identical names but different 'netnummers'.

Need some help with a Triggers (FOR INSERT)

Hi there

I' d like to make a trigger which will come with some kind of message ( maybe jscript?) or printout, each time

I insert new client into the Client table. Clent table has ClientID, ClientName, Address, EMail, and ContactPerson.

Please help

M.D.

If you would like to send e-mail you can use dbmail, I think that Java script will not work inside SQL trigger, and remember that trigger should work as short as possible, because control will be returned to caller after trigger finish. The best will be to insert record to special table or mark record as to notify and run Job on SQL Job Agent to check records in the table periodically and send reports or print them. Insider job you can do more stuff like run SSIS package, call application and more

Thanks

JPazgier

Need more descriptive error message

Hi,

Currently I have an event handler on my package that executes a script task on the "OnError" event (at the package level).

One of the error handler's script task's job is to capture the "System::ErrorDescription" and store it to a user variable which is then later sent in an email. That part works fine.

But what I am wondering is, there a way to make the error description more descriptive? I would like to send a custom error message from one of the tasks in my package, if possible. The task is a script task.

Currently, the "System::ErrorDescription" is just:

The Script returned a failure result.

Which isn't too terribly descriptive! But this is the default error description.

Is there something I can do to change the default error message for my script task? Is there a way I can set the "System::ErrorDescription" on this script task, perhaps? Or is there another way?

Thanks much

Usually when there is an error, there will be some more additional errors raised by the SSIS. Hence Error Description will always have the last error message only, unless you handle all the previous ones.

jwelch has explained it more clearly here:

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/05/handling-multiple-errors-in-ssis.aspx

Thanks

|||

I already have this code in place.

My question is how to add my own custom error message. But I think I have a solution.

I can create a user variable, populate it in the event of an error, and append it to my emailMessage variable.