Passing object properties to variables

Hi.

I was wondering if it's possible to pass object properties to variables? For example, if I have a ConnectionString property for a SQL Server connection, would it be possible to pass this value to a User-scoped variable?

Any ideas would be appreciated. Thanks!

One way you could accomplish this is by using a Script task.

You can access the properties of all the connections in your Connection Manager through the Dts ScriptObjectModel .

For example, the following code grabs the ConnectionString of my "currentFolder" connection object, and stores it in the "myConnectionString" variable.

PublicSub Main()

Dim value AsString

value = Dts.Connections("currentFolder").ConnectionString

Dts.Variables("myConnectionString").Value = value

'MsgBox(Dts.Variables("myConnectionString").Value)

Dts.TaskResult = Dts.Results.Success

EndSub

Be sure to include your variable (ie. User::myConnectionString) in the ReadWriteVariables property of your script task.

Is this what you meant?

|||Yes, this is exactly what I meant. Thanks!

Passing Multi-Value parameters to a Data Driven Subscription

Hi,

I've been asked to set up a data driven subscription for a number of reports which use multi value parameters. For example, show me all transactions against the following departments: IT, Building Services, Accounts.

As an interactive report it's simple, the user just selects the relevant departments, but as a data driven subscription I can't seem to find the correct format to pass the selections through.

Has anyone tried this before?

Thanks,

Dan

please search on "Multi-Value parameters to a Data Driven Subscription" in this forum.

Passing in own Variable to print

I want to display some of the information not in the datasource selection but
through application passing in information. For example: personName,
reportName (dynamical defined depending on which button user click) and etc.
These variable only use in header or footer.
How can I pass these information to the report viwer? Any help is greatly
appreciated.I've done similar things using report parameters.
"Locus" <Locus@.discussions.microsoft.com> wrote in message
news:1FF52F89-D3F6-44C3-B42D-45BBABBDE2F0@.microsoft.com...
>I want to display some of the information not in the datasource selection
>but
> through application passing in information. For example: personName,
> reportName (dynamical defined depending on which button user click) and
> etc.
> These variable only use in header or footer.
> How can I pass these information to the report viwer? Any help is greatly
> appreciated.|||Hi Bill, how you do it?
I tried adding parameters, it seems to automatically pop up to ask for input
value, not allowing me to passing in value programmatically.
-- Locus
"Bill Miller" wrote:
> I've done similar things using report parameters.
> "Locus" <Locus@.discussions.microsoft.com> wrote in message
> news:1FF52F89-D3F6-44C3-B42D-45BBABBDE2F0@.microsoft.com...
> >I want to display some of the information not in the datasource selection
> >but
> > through application passing in information. For example: personName,
> > reportName (dynamical defined depending on which button user click) and
> > etc.
> > These variable only use in header or footer.
> >
> > How can I pass these information to the report viwer? Any help is greatly
> > appreciated.
>
>|||Try setting the Parameters Hidden parameter to checked.
"Locus" wrote:
> Hi Bill, how you do it?
> I tried adding parameters, it seems to automatically pop up to ask for input
> value, not allowing me to passing in value programmatically.
> -- Locus
> "Bill Miller" wrote:
> > I've done similar things using report parameters.
> >
> > "Locus" <Locus@.discussions.microsoft.com> wrote in message
> > news:1FF52F89-D3F6-44C3-B42D-45BBABBDE2F0@.microsoft.com...
> > >I want to display some of the information not in the datasource selection
> > >but
> > > through application passing in information. For example: personName,
> > > reportName (dynamical defined depending on which button user click) and
> > > etc.
> > > These variable only use in header or footer.
> > >
> > > How can I pass these information to the report viwer? Any help is greatly
> > > appreciated.
> >
> >
> >

Passing datetime parameters to Reporting Services

Hi all...
How should I pass datetime parameters to a report in order for it to work?
This is the current scenario:
Dataset has, for example, this query:
SELECT *
FROM POLIZA
WHERE FECHA BETWEEN ? AND ?
where FECHA is of DATETIME type in an Oracle Database.
Both unnamed parameters are DateTime report parameters, which retrieve the
correct date values (using code embedded in the report)
When I run the report, I get the typical error message when date format is
incorrect:
"a non-numeric character was found where a numeric was expected"
It seems that report tries to pass the "-" of the date value to the unnamed
parameter.
Formerly I had this query that made the report to work:
SELECT *
FROM POLIZA
WHERE FECHA BETWEEN TO_DATE(?, 'DD-MM-YYYY') AND TO_DATE(?, 'DD-MM-YYYY')
In that case, parameters where of String type (forced to have 'dd/mm/yyyy'
format). But using this way I can have (and I already had) problems with
incompatibilities in Regional Settings where the Reporting Server is
installed. For example, using this latter way, I forced the date to have the
format dd/mm/yyyy (ex. 31/08/2005). In some server where Reporting Services
is installed, regional settings are different, so Report crashed because it
sent 08/31/2005 where database (in different server) expected 31/08/2005.
That's why I need to make it independent of regional settings of the target
server.
Any way to manage this?
Thanks a lot in advance
JaimeJaime
I believe this is an issue with the data being returned from the Query.
Not the rendering of the report.
If you run the report in the data tab and scroll to the bottom do you
get the same error?
You might want to take a look at the function Isdate(). Be advised this
will return valid dates so it will filter out any bad dates.
Sounds like someone put a character string in a date field. Does your
database allow this? Like an ASAP or somthing?
Leo
Jaime Stuardo wrote:
> Hi all...
> How should I pass datetime parameters to a report in order for it to work?
> This is the current scenario:
> Dataset has, for example, this query:
> SELECT *
> FROM POLIZA
> WHERE FECHA BETWEEN ? AND ?
> where FECHA is of DATETIME type in an Oracle Database.
> Both unnamed parameters are DateTime report parameters, which retrieve the
> correct date values (using code embedded in the report)
> When I run the report, I get the typical error message when date format is
> incorrect:
> "a non-numeric character was found where a numeric was expected"
> It seems that report tries to pass the "-" of the date value to the unnamed
> parameter.
> Formerly I had this query that made the report to work:
> SELECT *
> FROM POLIZA
> WHERE FECHA BETWEEN TO_DATE(?, 'DD-MM-YYYY') AND TO_DATE(?, 'DD-MM-YYYY')
> In that case, parameters where of String type (forced to have 'dd/mm/yyyy'
> format). But using this way I can have (and I already had) problems with
> incompatibilities in Regional Settings where the Reporting Server is
> installed. For example, using this latter way, I forced the date to have the
> format dd/mm/yyyy (ex. 31/08/2005). In some server where Reporting Services
> is installed, regional settings are different, so Report crashed because it
> sent 08/31/2005 where database (in different server) expected 31/08/2005.
> That's why I need to make it independent of regional settings of the target
> server.
> Any way to manage this?
> Thanks a lot in advance
> Jaime
>|||Thanks Leo for answering...
When using the Data tab and run the query, I have to enter the date this
way:
30-apr-05 and query returns results.
The date field in database is correct, since as I told, when I use
TO_DATE(xxxx, 'DD-MM-YYYY') report works, defining parameters as Strings,
not DateTime as it should be. I cannot use the parameter as String as I
explained in my first post. In conclusion, the problem is only related to
passing parameters between Reporting Services and Oracle database.
Those DateTime parameters are calculated, for example, using this custom code:
Function LastDay(ByVal iYear As Integer, ByVal iQuarter As Integer) As
DateTime
Return DateSerial(iYear, 4 * iQuarter + 5, 1).AddDays(-1)
End Function
Finally, I'm going to tell you that I could display parameters just before
rendering the report (I'm using API to do it) and these are :
CUATRIMESTRE=1
AÃ?O=2005
NUMERO_POLIZA=5506666
FECHA_INICIO=5/1/2005 12:00:00 AM
FECHA_TERMINO=8/31/2005 12:00:00 AM
CUA_MESES=May - August
CUATRIMESTRE, AÃ?O and NUMERO_POLIZA are parameters the user enters.
FECHA_INICIO and FECHA_TERMINO are calculated parameters depending on
CUATRIMESTRE parameter, and CUA_MESES is a calculated parameter depending on
FECHA_INICIO and FECHA_TERMINO.
It's clear that Reporting Service is passing the datetime parameter formated
in a manner that isn't accepted by Oracle. I thought date time would be
passed as is, as a DATE datatype.
Any further help would be greately appreciated,
Thanks
Jaime
"cte25117@.centurytel.net" wrote:
> Jaime
> I believe this is an issue with the data being returned from the Query.
> Not the rendering of the report.
> If you run the report in the data tab and scroll to the bottom do you
> get the same error?
> You might want to take a look at the function Isdate(). Be advised this
> will return valid dates so it will filter out any bad dates.
> Sounds like someone put a character string in a date field. Does your
> database allow this? Like an ASAP or somthing?
> Leo
>
>
> Jaime Stuardo wrote:
> > Hi all...
> >
> > How should I pass datetime parameters to a report in order for it to work?
> > This is the current scenario:
> >
> > Dataset has, for example, this query:
> > SELECT *
> > FROM POLIZA
> > WHERE FECHA BETWEEN ? AND ?
> >
> > where FECHA is of DATETIME type in an Oracle Database.
> >
> > Both unnamed parameters are DateTime report parameters, which retrieve the
> > correct date values (using code embedded in the report)
> >
> > When I run the report, I get the typical error message when date format is
> > incorrect:
> > "a non-numeric character was found where a numeric was expected"
> >
> > It seems that report tries to pass the "-" of the date value to the unnamed
> > parameter.
> >
> > Formerly I had this query that made the report to work:
> > SELECT *
> > FROM POLIZA
> > WHERE FECHA BETWEEN TO_DATE(?, 'DD-MM-YYYY') AND TO_DATE(?, 'DD-MM-YYYY')
> >
> > In that case, parameters where of String type (forced to have 'dd/mm/yyyy'
> > format). But using this way I can have (and I already had) problems with
> > incompatibilities in Regional Settings where the Reporting Server is
> > installed. For example, using this latter way, I forced the date to have the
> > format dd/mm/yyyy (ex. 31/08/2005). In some server where Reporting Services
> > is installed, regional settings are different, so Report crashed because it
> > sent 08/31/2005 where database (in different server) expected 31/08/2005.
> >
> > That's why I need to make it independent of regional settings of the target
> > server.
> >
> > Any way to manage this?
> > Thanks a lot in advance
> >
> > Jaime
> >
> >
>|||Have you tried CASTing the dates as Integers in the Oracel SQL query?
"Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in message
news:B960FCF6-7317-4197-8B76-55F79733BC71@.microsoft.com...
> Thanks Leo for answering...
> When using the Data tab and run the query, I have to enter the date this
> way:
> 30-apr-05 and query returns results.
> The date field in database is correct, since as I told, when I use
> TO_DATE(xxxx, 'DD-MM-YYYY') report works, defining parameters as Strings,
> not DateTime as it should be. I cannot use the parameter as String as I
> explained in my first post. In conclusion, the problem is only related to
> passing parameters between Reporting Services and Oracle database.
> Those DateTime parameters are calculated, for example, using this custom code:
> Function LastDay(ByVal iYear As Integer, ByVal iQuarter As Integer) As
> DateTime
> Return DateSerial(iYear, 4 * iQuarter + 5, 1).AddDays(-1)
> End Function
> Finally, I'm going to tell you that I could display parameters just before
> rendering the report (I'm using API to do it) and these are :
> CUATRIMESTRE=1
> AÑO=2005
> NUMERO_POLIZA=5506666
> FECHA_INICIO=5/1/2005 12:00:00 AM
> FECHA_TERMINO=8/31/2005 12:00:00 AM
> CUA_MESES=May - August
> CUATRIMESTRE, AÑO and NUMERO_POLIZA are parameters the user enters.
> FECHA_INICIO and FECHA_TERMINO are calculated parameters depending on
> CUATRIMESTRE parameter, and CUA_MESES is a calculated parameter depending on
> FECHA_INICIO and FECHA_TERMINO.
> It's clear that Reporting Service is passing the datetime parameter formated
> in a manner that isn't accepted by Oracle. I thought date time would be
> passed as is, as a DATE datatype.
> Any further help would be greately appreciated,
> Thanks
> Jaime
> "cte25117@.centurytel.net" wrote:
>> Jaime
>> I believe this is an issue with the data being returned from the Query.
>> Not the rendering of the report.
>> If you run the report in the data tab and scroll to the bottom do you
>> get the same error?
>> You might want to take a look at the function Isdate(). Be advised this
>> will return valid dates so it will filter out any bad dates.
>> Sounds like someone put a character string in a date field. Does your
>> database allow this? Like an ASAP or somthing?
>> Leo
>>
>>
>> Jaime Stuardo wrote:
>> > Hi all...
>> >
>> > How should I pass datetime parameters to a report in order for it to work?
>> > This is the current scenario:
>> >
>> > Dataset has, for example, this query:
>> > SELECT *
>> > FROM POLIZA
>> > WHERE FECHA BETWEEN ? AND ?
>> >
>> > where FECHA is of DATETIME type in an Oracle Database.
>> >
>> > Both unnamed parameters are DateTime report parameters, which retrieve the
>> > correct date values (using code embedded in the report)
>> >
>> > When I run the report, I get the typical error message when date format is
>> > incorrect:
>> > "a non-numeric character was found where a numeric was expected"
>> >
>> > It seems that report tries to pass the "-" of the date value to the unnamed
>> > parameter.
>> >
>> > Formerly I had this query that made the report to work:
>> > SELECT *
>> > FROM POLIZA
>> > WHERE FECHA BETWEEN TO_DATE(?, 'DD-MM-YYYY') AND TO_DATE(?, 'DD-MM-YYYY')
>> >
>> > In that case, parameters where of String type (forced to have 'dd/mm/yyyy'
>> > format). But using this way I can have (and I already had) problems with
>> > incompatibilities in Regional Settings where the Reporting Server is
>> > installed. For example, using this latter way, I forced the date to have
>> > the
>> > format dd/mm/yyyy (ex. 31/08/2005). In some server where Reporting Services
>> > is installed, regional settings are different, so Report crashed because it
>> > sent 08/31/2005 where database (in different server) expected 31/08/2005.
>> >
>> > That's why I need to make it independent of regional settings of the target
>> > server.
>> >
>> > Any way to manage this?
>> > Thanks a lot in advance
>> >
>> > Jaime
>> >
>> >

Passing data to SQL Server

How to pass big set of data from ASP to SQL Server stored procedure (for example 30 rows and 5 columns - content of html table) ?Stored procedures cannot accept recordsets as parameters, so you will need to pass the data to the procedure one record at a time.

Another alternative is to insert your data into a staging table. Then your application either calls the stored procedure or you create a scheduled job that calls the stored procedure. The procedure checks the staging table for records and processes them as a set.

blindman|||What about XML?sql

Passing constants in data flows

Hi

Does anyone know what would be the best technique to use for passing constants into data flows shapes?

For example if I had a lookup that required a static value to be passed into it as part of a concatenated key etc...

Cheers

Al

Use a derived column transform with a constant as expression...sql

Passing Collections to Custom Code

In RS2000 it was possible to pass the entire fields or parameters collection to a custom code function. So for example,

Custom Code:

Function Calc(pFields) as String
Return pfields("fldname1").Value + pfields("fldname2").Value
End Function

Report Expression:
Code.Calc(Fields)

This also worked with the Parameter Fields Collection. It made it possible to move a considerable amount of logic to the custom code section.

In RS2005 this still works in the VS.Net Report Designer but the expression returns an error when the report is viewed through the report manager.

Has anyone else used this in RS2000? Did you get it to work in 2005?

Try the following function - it should work just fine:

Public Function Calc(pFields As Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Fields)
return pFields("name1").Value
End function

-- Robert

|||Thanks, this works. Too bad you can't put an imports statement at the beginning of the custom code section to avoid typing this for every function.|||

Hi we are highly dependent on this sort of thing.

We have created a couple of utility libraries to return needed values.

This worked fine in RS2000 but breaks in RS2005.

Here is a small snippet of the code -

<code>

using Microsoft.ReportingServices.ReportProcessing.ReportObjectModel;

using HermesReportLibrary;

namespace HRA2V2ReportLibrary

{

/// <summary>

/// Methods for determining if Onsite is required

/// </summary>

public class Onsite

{

/// <summary>

/// Determine if onsite needed

/// </summary>

/// <param name="fields">survey record</param>

/// <returns>true if onsite needed</returns>

public static TrueFalse NeedsOnsite(Fields fields)

{

if (Onsite.NeedsOnsiteWithMedical(fields) == TrueFalse.True

|| Onsite.NeedsOnsiteWithTobaccoCounselor(fields) == TrueFalse.True

|| Onsite.HasInjuryPrevention(fields) == TrueFalse.True)

return TrueFalse.True;

return TrueFalse.False;

}

</code>

RS2005 cannot seem to find my custom assembly. In RS2000, I only had to put the assembly in the right directory and it found it. I cannot determine what that directory might be in RS2005. I tried to load the assembly into my instance of the RS2005 database using the SQL2005 Server Management Studio by right clicking on the Programmability\Assemblies folder under that instance and selecting from the pop up menu 'New Assembly' however it will not load because of the following error.

An excepton occured while executing a Transact SQL statement or batch. --> Assembly 'microsoft.reportingservices.processing' version=8.0.242.0, culture=neutral ... was not found in the SQL Catalog. (Microsoft SQL Server, Error: 6503)

How can I get this custom assembly to be loaded by RS2005 or do I need to throw away hours of developer work and start over from scratch to be able to use RS2005.

Thanks guys

|||

Hi, John,

See if this helps -- Deploying a Custom Assembly for RS2005 is described here: http://msdn2.microsoft.com/en-US/library/ms155034.aspx

--
This posting is provided "AS IS" with no warranties, and confers no rights.

Passing Collections to Custom Code

In RS2000 it was possible to pass the entire fields or parameters collection to a custom code function. So for example,

Custom Code:

Function Calc(pFields) as String
Return pfields("fldname1").Value + pfields("fldname2").Value
End Function

Report Expression:
Code.Calc(Fields)

This also worked with the Parameter Fields Collection. It made it possible to move a considerable amount of logic to the custom code section.

In RS2005 this still works in the VS.Net Report Designer but the expression returns an error when the report is viewed through the report manager.

Has anyone else used this in RS2000? Did you get it to work in 2005?

Try the following function - it should work just fine:

Public Function Calc(pFields As Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Fields)
return pFields("name1").Value
End function

-- Robert

|||Thanks, this works. Too bad you can't put an imports statement at the beginning of the custom code section to avoid typing this for every function.|||

Hi we are highly dependent on this sort of thing.

We have created a couple of utility libraries to return needed values.

This worked fine in RS2000 but breaks in RS2005.

Here is a small snippet of the code -

<code>

using Microsoft.ReportingServices.ReportProcessing.ReportObjectModel;

using HermesReportLibrary;

namespace HRA2V2ReportLibrary

{

/// <summary>

/// Methods for determining if Onsite is required

/// </summary>

public class Onsite

{

/// <summary>

/// Determine if onsite needed

/// </summary>

/// <param name="fields">survey record</param>

/// <returns>true if onsite needed</returns>

public static TrueFalse NeedsOnsite(Fields fields)

{

if (Onsite.NeedsOnsiteWithMedical(fields) == TrueFalse.True

|| Onsite.NeedsOnsiteWithTobaccoCounselor(fields) == TrueFalse.True

|| Onsite.HasInjuryPrevention(fields) == TrueFalse.True)

return TrueFalse.True;

return TrueFalse.False;

}

</code>

RS2005 cannot seem to find my custom assembly. In RS2000, I only had to put the assembly in the right directory and it found it. I cannot determine what that directory might be in RS2005. I tried to load the assembly into my instance of the RS2005 database using the SQL2005 Server Management Studio by right clicking on the Programmability\Assemblies folder under that instance and selecting from the pop up menu 'New Assembly' however it will not load because of the following error.

An excepton occured while executing a Transact SQL statement or batch. --> Assembly 'microsoft.reportingservices.processing' version=8.0.242.0, culture=neutral ... was not found in the SQL Catalog. (Microsoft SQL Server, Error: 6503)

How can I get this custom assembly to be loaded by RS2005 or do I need to throw away hours of developer work and start over from scratch to be able to use RS2005.

Thanks guys

|||

Hi, John,

See if this helps -- Deploying a Custom Assembly for RS2005 is described here: http://msdn2.microsoft.com/en-US/library/ms155034.aspx

--
This posting is provided "AS IS" with no warranties, and confers no rights.

Passing an indeterminate number of parameters

Hi all,
Does anyone know if it's possible to somehow pass an unknown number of
parameters to a storerd procedure.
For example via an array of some description? Alternatively, in C# you can
use the keyword "params" when the number of parameters to be passed is
unknown at design time. I don't think there is any equivalent in SQL server
though is there?
Thanks everyone
Simon
Array datatype is not supported in SQL Server. However you can use certain
workarounds for such requirements, some of which are detailed at:
http://www.sommarskog.se/arrays-in-sql.html
Anith
|||Simon
As far as I know there is not a way to do what you ask. What I feel you will have to do is program your proc for the most parameters possible and give them default values. That way if you only pass a few then your proc knows what to do with the remainin
g values. You can easily build the string you pass to the proc in your code.
Hope this helps
Jeff Duncan
MCDBA, MCSE+I
|||Thanks guys. Both very useful answers
Kindest regards
Simon

Passing an indeterminate number of parameters

Hi all,
Does anyone know if it's possible to somehow pass an unknown number of
parameters to a storerd procedure.
For example via an array of some description? Alternatively, in C# you can
use the keyword "params" when the number of parameters to be passed is
unknown at design time. I don't think there is any equivalent in SQL server
though is there?
Thanks everyone
SimonArray datatype is not supported in SQL Server. However you can use certain
workarounds for such requirements, some of which are detailed at:
http://www.sommarskog.se/arrays-in-sql.html
Anith|||Simon
As far as I know there is not a way to do what you ask. What I feel you wil
l have to do is program your proc for the most parameters possible and give
them default values. That way if you only pass a few then your proc knows w
hat to do with the remainin
g values. You can easily build the string you pass to the proc in your code
.
Hope this helps
Jeff Duncan
MCDBA, MCSE+I|||Thanks guys. Both very useful answers
Kindest regards
Simon

Passing an indeterminate number of parameters

Hi all,
Does anyone know if it's possible to somehow pass an unknown number of
parameters to a storerd procedure.
For example via an array of some description? Alternatively, in C# you can
use the keyword "params" when the number of parameters to be passed is
unknown at design time. I don't think there is any equivalent in SQL server
though is there?
Thanks everyone
SimonArray datatype is not supported in SQL Server. However you can use certain
workarounds for such requirements, some of which are detailed at:
http://www.sommarskog.se/arrays-in-sql.html
--
Anith|||Simo
As far as I know there is not a way to do what you ask. What I feel you will have to do is program your proc for the most parameters possible and give them default values. That way if you only pass a few then your proc knows what to do with the remaining values. You can easily build the string you pass to the proc in your code
Hope this help
Jeff Dunca
MCDBA, MCSE+I|||Thanks guys. Both very useful answers
Kindest regards
Simon

Passing an array to query in a table adapter

I'm looking for a way to pass an array of values as a parameter to a query in a table adapter. For example I want to run a query something like:

SELECT * FROM menu WHERE menu_role IN (@.roles)

And I could pass something like 'RegisteredUser, SuperUser, OtherUser' to the @.roles parameter.

For some reason I can't figure out a way to do this. Any help would be greatly appericated.

Thanks,

Ryan.

This is possible from only code behind. Dynamically create the string and assrign it to the select command of tableadapter.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=319884&SiteID=1

check this link

|||Thanks, but I don't think that that helps my situation. I guess I will just have to create a query string on the fly even though I hate doing that.|||

Check if this helps:http://weblogs.sqlteam.com/dinakar/archive/2007/03/28/60150.aspx

|||

There's more than one way to skin a cat...

I was trying to figure out the same thing when I realized I could filter the data AFTER it was returned. I put it in a dataview and used the rowfilter property. Hope that helps!

|||My solutions was to create a stored procedure that dynamically setup the query on the fly before sending the results back. All in all it turned out to be a fairly elegant solution, although I do wish I could just pass an array in through a parameter.

Passing a value between datasets?

Hi
Does anyone know if it is possible to use a value from one dataset as a
parameter to the query in a second dataset?
Example: Datset1 returns 8 rows, my report produces a page for each
row, but each page requires additional data relevant to that page of
the report.
Is there a way I can take (for instance) the current value of the
ReportType field from dataset1 and use that in a select statement in
Dataset2?
TIA
StewartWhat you are describing is perfect for subreports. Subreports are regular
reports that you can design and test by themselves. You create the report
with a parameter. Make sure it all works and then drag and drop the report
onto the main one. Do a right mouse click on the report, properties,
parameter tab and then map the parameter to the field of you first dataset.
The last thing I do is I hide the subreport for list view via report manager
(go to the properties of the report and there is a check box to do this).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sdm" <macneisd-www@.dcs.gla.ac.uk> wrote in message
news:1108719942.123540.216560@.c13g2000cwb.googlegroups.com...
> Hi
> Does anyone know if it is possible to use a value from one dataset as a
> parameter to the query in a second dataset?
> Example: Datset1 returns 8 rows, my report produces a page for each
> row, but each page requires additional data relevant to that page of
> the report.
> Is there a way I can take (for instance) the current value of the
> ReportType field from dataset1 and use that in a select statement in
> Dataset2?
> TIA
> Stewart
>|||Bruce
That sounds just what I need! Apologies for not acknowledging it
sooner, I'm just looking into RS in 'spare' moments.
Thanks very much for your reply, I really appreciate it.
Stewart|||Bruce, worked great, thanks again.
Stewart
Bruce L-C [MVP] wrote:
> What you are describing is perfect for subreports. Subreports are
regular
> reports that you can design and test by themselves. You create the
report
> with a parameter. Make sure it all works and then drag and drop the
report
> onto the main one. Do a right mouse click on the report, properties,
> parameter tab and then map the parameter to the field of you first
dataset.
> The last thing I do is I hide the subreport for list view via report
manager
> (go to the properties of the report and there is a check box to do
this).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "sdm" <macneisd-www@.dcs.gla.ac.uk> wrote in message
> news:1108719942.123540.216560@.c13g2000cwb.googlegroups.com...
> > Hi
> >
> > Does anyone know if it is possible to use a value from one dataset
as a
> > parameter to the query in a second dataset?
> >
> > Example: Datset1 returns 8 rows, my report produces a page for
each
> > row, but each page requires additional data relevant to that page
of
> > the report.
> >
> > Is there a way I can take (for instance) the current value of the
> > ReportType field from dataset1 and use that in a select statement
in
> > Dataset2?
> >
> > TIA
> >
> > Stewart
> >

Passing a multi-value parameter to a Subreport

How do I pass a multi-value parameter to a subreport?
For example, if my main report has a multi-value parameter named @.p1 and my
subreport's dataset is generated by SQL
SELECT a,b FROM MyTable WHERE a IN (@.LongList)
how do I then pass the user-selected @.p1 values from the main report to the
@.LongList parameter in the subreport?
I tried specifying JOIN(@.P1.Value,",") for a parameter value, but it did not
work.
Any help/suggestions will be appreciated. Thanks in advanceThis is just a guess based on what I know is going on behind the covers but
try the following. In your subreport make the Report Parameter @.LongList a
multi-value parameter. Test the subreport stand alone and make sure it
works. Then drop the subreport into your main report, right mouse click on
it, parameters and map the report parameter from the subreport to the report
parameter from the main report. You should not have to do anything fancy.
Let me know if it works.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"isaak" <isaak.peretsman at usa.dupont.com (no spam)> wrote in message
news:4785EFE4-9436-4715-8AA4-A57CC8E9AC78@.microsoft.com...
> How do I pass a multi-value parameter to a subreport?
> For example, if my main report has a multi-value parameter named @.p1 and
> my
> subreport's dataset is generated by SQL
> SELECT a,b FROM MyTable WHERE a IN (@.LongList)
> how do I then pass the user-selected @.p1 values from the main report to
> the
> @.LongList parameter in the subreport?
> I tried specifying JOIN(@.P1.Value,",") for a parameter value, but it did
> not
> work.
> Any help/suggestions will be appreciated. Thanks in advance
>|||Bruce,
This works. Once again - thanks for your help.
Isaak
"Bruce L-C [MVP]" wrote:
> This is just a guess based on what I know is going on behind the covers but
> try the following. In your subreport make the Report Parameter @.LongList a
> multi-value parameter. Test the subreport stand alone and make sure it
> works. Then drop the subreport into your main report, right mouse click on
> it, parameters and map the report parameter from the subreport to the report
> parameter from the main report. You should not have to do anything fancy.
> Let me know if it works.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "isaak" <isaak.peretsman at usa.dupont.com (no spam)> wrote in message
> news:4785EFE4-9436-4715-8AA4-A57CC8E9AC78@.microsoft.com...
> > How do I pass a multi-value parameter to a subreport?
> >
> > For example, if my main report has a multi-value parameter named @.p1 and
> > my
> > subreport's dataset is generated by SQL
> >
> > SELECT a,b FROM MyTable WHERE a IN (@.LongList)
> >
> > how do I then pass the user-selected @.p1 values from the main report to
> > the
> > @.LongList parameter in the subreport?
> >
> > I tried specifying JOIN(@.P1.Value,",") for a parameter value, but it did
> > not
> > work.
> >
> > Any help/suggestions will be appreciated. Thanks in advance
> >
>
>|||Appreciate you getting back and letting me know the result. As I said it was
a guess on my part.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"isaak" <isaak.peretsman at usa.dupont.com (no spam)> wrote in message
news:D71C1BB0-7AB0-4CFF-BD5F-D6B789D59906@.microsoft.com...
> Bruce,
> This works. Once again - thanks for your help.
> Isaak
> "Bruce L-C [MVP]" wrote:
>> This is just a guess based on what I know is going on behind the covers
>> but
>> try the following. In your subreport make the Report Parameter @.LongList
>> a
>> multi-value parameter. Test the subreport stand alone and make sure it
>> works. Then drop the subreport into your main report, right mouse click
>> on
>> it, parameters and map the report parameter from the subreport to the
>> report
>> parameter from the main report. You should not have to do anything fancy.
>> Let me know if it works.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "isaak" <isaak.peretsman at usa.dupont.com (no spam)> wrote in message
>> news:4785EFE4-9436-4715-8AA4-A57CC8E9AC78@.microsoft.com...
>> > How do I pass a multi-value parameter to a subreport?
>> >
>> > For example, if my main report has a multi-value parameter named @.p1
>> > and
>> > my
>> > subreport's dataset is generated by SQL
>> >
>> > SELECT a,b FROM MyTable WHERE a IN (@.LongList)
>> >
>> > how do I then pass the user-selected @.p1 values from the main report to
>> > the
>> > @.LongList parameter in the subreport?
>> >
>> > I tried specifying JOIN(@.P1.Value,",") for a parameter value, but it
>> > did
>> > not
>> > work.
>> >
>> > Any help/suggestions will be appreciated. Thanks in advance
>> >
>>|||Isaak,
Since yours works, may I ask you a question?
In my main report, the parameter that I want to pass to subreport is
"Fields!CallID.value", so I right-click the subreport in main report,
properties, parameter, parameter name is "CallID", parameter value is
"Fields!CallID.value". In my subreport, my sql query is (In subreport)
select Tracker, EntryDate, EntryText from dbo.Journal
where CallID = @.CallID
I go to paramters tab, I see the name is "@.CallID" and value ="Parameters!CallID.value".
when I run from main report, there is no error but subreport is not showing
in the main report. Do I miss something here? Thanks!
Henry
"isaak" wrote:
> Bruce,
> This works. Once again - thanks for your help.
> Isaak
> "Bruce L-C [MVP]" wrote:
> > This is just a guess based on what I know is going on behind the covers but
> > try the following. In your subreport make the Report Parameter @.LongList a
> > multi-value parameter. Test the subreport stand alone and make sure it
> > works. Then drop the subreport into your main report, right mouse click on
> > it, parameters and map the report parameter from the subreport to the report
> > parameter from the main report. You should not have to do anything fancy.
> >
> > Let me know if it works.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "isaak" <isaak.peretsman at usa.dupont.com (no spam)> wrote in message
> > news:4785EFE4-9436-4715-8AA4-A57CC8E9AC78@.microsoft.com...
> > > How do I pass a multi-value parameter to a subreport?
> > >
> > > For example, if my main report has a multi-value parameter named @.p1 and
> > > my
> > > subreport's dataset is generated by SQL
> > >
> > > SELECT a,b FROM MyTable WHERE a IN (@.LongList)
> > >
> > > how do I then pass the user-selected @.p1 values from the main report to
> > > the
> > > @.LongList parameter in the subreport?
> > >
> > > I tried specifying JOIN(@.P1.Value,",") for a parameter value, but it did
> > > not
> > > work.
> > >
> > > Any help/suggestions will be appreciated. Thanks in advance
> > >
> >
> >
> >|||Issak,
I got it to work. Thanks!
Henry
"Henry Chen" wrote:
> Isaak,
> Since yours works, may I ask you a question?
> In my main report, the parameter that I want to pass to subreport is
> "Fields!CallID.value", so I right-click the subreport in main report,
> properties, parameter, parameter name is "CallID", parameter value is
> "Fields!CallID.value". In my subreport, my sql query is (In subreport)
> select Tracker, EntryDate, EntryText from dbo.Journal
> where CallID = @.CallID
> I go to paramters tab, I see the name is "@.CallID" and value => "Parameters!CallID.value".
> when I run from main report, there is no error but subreport is not showing
> in the main report. Do I miss something here? Thanks!
> Henry
> "isaak" wrote:
> > Bruce,
> >
> > This works. Once again - thanks for your help.
> >
> > Isaak
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > This is just a guess based on what I know is going on behind the covers but
> > > try the following. In your subreport make the Report Parameter @.LongList a
> > > multi-value parameter. Test the subreport stand alone and make sure it
> > > works. Then drop the subreport into your main report, right mouse click on
> > > it, parameters and map the report parameter from the subreport to the report
> > > parameter from the main report. You should not have to do anything fancy.
> > >
> > > Let me know if it works.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "isaak" <isaak.peretsman at usa.dupont.com (no spam)> wrote in message
> > > news:4785EFE4-9436-4715-8AA4-A57CC8E9AC78@.microsoft.com...
> > > > How do I pass a multi-value parameter to a subreport?
> > > >
> > > > For example, if my main report has a multi-value parameter named @.p1 and
> > > > my
> > > > subreport's dataset is generated by SQL
> > > >
> > > > SELECT a,b FROM MyTable WHERE a IN (@.LongList)
> > > >
> > > > how do I then pass the user-selected @.p1 values from the main report to
> > > > the
> > > > @.LongList parameter in the subreport?
> > > >
> > > > I tried specifying JOIN(@.P1.Value,",") for a parameter value, but it did
> > > > not
> > > > work.
> > > >
> > > > Any help/suggestions will be appreciated. Thanks in advance
> > > >
> > >
> > >
> > >|||Sorry Isac,
I was trying to do the same thing but it did not work for me. Am I missing
something?
"Bruce L-C [MVP]" wrote:
> This is just a guess based on what I know is going on behind the covers but
> try the following. In your subreport make the Report Parameter @.LongList a
> multi-value parameter. Test the subreport stand alone and make sure it
> works. Then drop the subreport into your main report, right mouse click on
> it, parameters and map the report parameter from the subreport to the report
> parameter from the main report. You should not have to do anything fancy.
> Let me know if it works.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "isaak" <isaak.peretsman at usa.dupont.com (no spam)> wrote in message
> news:4785EFE4-9436-4715-8AA4-A57CC8E9AC78@.microsoft.com...
> > How do I pass a multi-value parameter to a subreport?
> >
> > For example, if my main report has a multi-value parameter named @.p1 and
> > my
> > subreport's dataset is generated by SQL
> >
> > SELECT a,b FROM MyTable WHERE a IN (@.LongList)
> >
> > how do I then pass the user-selected @.p1 values from the main report to
> > the
> > @.LongList parameter in the subreport?
> >
> > I tried specifying JOIN(@.P1.Value,",") for a parameter value, but it did
> > not
> > work.
> >
> > Any help/suggestions will be appreciated. Thanks in advance
> >
>
>|||Sorry I forgot to mention, did you manage to get working with multivalue
parameter?
"Henry Chen" wrote:
> Issak,
> I got it to work. Thanks!
> Henry
> "Henry Chen" wrote:
> > Isaak,
> > Since yours works, may I ask you a question?
> >
> > In my main report, the parameter that I want to pass to subreport is
> > "Fields!CallID.value", so I right-click the subreport in main report,
> > properties, parameter, parameter name is "CallID", parameter value is
> > "Fields!CallID.value". In my subreport, my sql query is (In subreport)
> >
> > select Tracker, EntryDate, EntryText from dbo.Journal
> > where CallID = @.CallID
> >
> > I go to paramters tab, I see the name is "@.CallID" and value => > "Parameters!CallID.value".
> >
> > when I run from main report, there is no error but subreport is not showing
> > in the main report. Do I miss something here? Thanks!
> > Henry
> >
> > "isaak" wrote:
> >
> > > Bruce,
> > >
> > > This works. Once again - thanks for your help.
> > >
> > > Isaak
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > This is just a guess based on what I know is going on behind the covers but
> > > > try the following. In your subreport make the Report Parameter @.LongList a
> > > > multi-value parameter. Test the subreport stand alone and make sure it
> > > > works. Then drop the subreport into your main report, right mouse click on
> > > > it, parameters and map the report parameter from the subreport to the report
> > > > parameter from the main report. You should not have to do anything fancy.
> > > >
> > > > Let me know if it works.
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "isaak" <isaak.peretsman at usa.dupont.com (no spam)> wrote in message
> > > > news:4785EFE4-9436-4715-8AA4-A57CC8E9AC78@.microsoft.com...
> > > > > How do I pass a multi-value parameter to a subreport?
> > > > >
> > > > > For example, if my main report has a multi-value parameter named @.p1 and
> > > > > my
> > > > > subreport's dataset is generated by SQL
> > > > >
> > > > > SELECT a,b FROM MyTable WHERE a IN (@.LongList)
> > > > >
> > > > > how do I then pass the user-selected @.p1 values from the main report to
> > > > > the
> > > > > @.LongList parameter in the subreport?
> > > > >
> > > > > I tried specifying JOIN(@.P1.Value,",") for a parameter value, but it did
> > > > > not
> > > > > work.
> > > > >
> > > > > Any help/suggestions will be appreciated. Thanks in advance
> > > > >
> > > >
> > > >
> > > >

passing @parameters and TEXT to xp_sendmail message!

How do you pass parameters and text to the message area... i know how to pass parameters or pass text how do i do both... for example.
Exec master.dbo.xp_sendmail @.recipients = 'johndoe' @.message = 'hello and @.number'
PLZ HELP!!!!!!DECLARE @.strSQL varchar(8000)

SELECT @.strSQL = 'master.dbo.xp_sendmail @.recipients = ''johndoe'' @.message = ''hello and ' + @.number + ''''

EXECUTE(@.strSQL)

Pass Group into variable then query NOT IN variable?

Greetings all.

I'm looking for a clever way to pass a group into a variable and then query the variable using NOT IN

For example

DECLARE @.myvariable varchar(100)

SET @.myvariable = (1, 2, 3, 4, 5)

SELECT * FROM myTable

WHERE myField NOT IN @.myvariable

I swear I've seen this and I'm sure it's simple but my brain is not calculating the algorithm.

Thanks for all help,

Adamus

This works, but does require the dreaded dynamic SQL:

if object_id('myTable') is not null drop table myTable

go

create table myTable (myField int)

go

insert myTable values (1)

insert myTable values (2)

insert myTable values (3)

insert myTable values (4)

insert myTable values (5)

insert myTable values (6)

insert myTable values (7)

go

DECLARE @.myvariable varchar(100)

SET @.myvariable = '(1, 2, 3, 4, 5)'

exec('SELECT * FROM myTable WHERE myField NOT IN ' + @.myvariable)

Ron

|||

try

Code Snippet

DECLARE @.myvariable varchar(100),

@.stmt varchar(8000)

SET @.myvariable = (1, 2, 3, 4, 5)

SET @.stmt = 'SELECT * FROM myTable WHERE myField NOT IN ' + @.myvariable

exec sp_sqlexec @.stmt

|||

That's it!

Thanks,

Adamus

|||

Rice31416,

Unfortunately, your example demonstrates the use of "the dreaded dynamic SQL." That what you are executing in the EXEC() function.

Perhaps you meant something else...

|||

Dale,

I suspect you meant:

EXECUTE sp_executesql @.Stmt

|||

Yup, thanks.

Leftover from days gone by. Also need to change varchar to nvarchar...

|||

Arnie Rowland wrote:

Rice31416,

Unfortunately, your example demonstrates the use of "the dreaded dynamic SQL." That what you are executing in the EXEC() function.

Perhaps you meant something else...

This was the answer I was looking for but what do you mean by "unfortunately?"

Is there something bad about using this approach?

Adamus

|||

Hi Adam,

Using Dynamic SQL is sometimes a necessary 'evil'. At least some folks consider its usage to be tandamont to 'evil'. (My comment about the "dreaded" was to reflect back to Rice31416 that while he wrote that his approach didn't use "the dreaded dynamic SQL", in fact, his approach was using dynamic SQL."

I recommend this article from Erland Sommarskog. It gets into advanced issues, but keep it for reference.

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

|||

Arnie,

Yes, I meant that it *does* involve dynamic SQL. And yes, I got lazy and used "execute" in the example instead of sp_executesql!

Ron

Pass a array how parameters to stored procedure.

Boy's, I write why I need a example about pass an array how parameters to
stored procedure.
I need an example from a stored procedure and a c# code call.
I'm from Argentina, I wait you understand me and your answer.
Javier
There is no array type in SQL. Generally people fake arrays using a comma
separated value or delimited string as a single parameter to get around
this. For a detailed list of options you can refer to Erland's article at :
http://www.sommarskog.se/arrays-in-sql.html
Anith