parent child package with expressions

Hi there,

sorry if the message seems a bit garbled i cannot see the textbox properly with ie7/ff

anyways. the situation.

childpackage contains a loop-container. in there i use an ole-db-source with a variable

based on an expression. ie. "select * from foo where bar=" + len(@.[bar]) == 0 ? "initial" : @.[bar] + " and etc=1"

where bar is the variable that is set by the loop.

this works great.

however i need to call this package several times, only the expression is a tad different.

so i need a scripttask that sets the expression correctly, then i call the childpackage

and map the current-expression to the expression in childpackage.

how do i do that? or am i doing it wrong?

my script-task looks something like:

dts.Variable("theVar").Expression = " ""select * from foo where bar="" + len(@.[bar]) == 0 ? ""initial"" : @.[bar] + "" and etc=1"" "

in the childpackage i have a package-conf that maps thevar to thequery with target-object expression.nevermind this. i got it working now.

i had to assign it to a variable. that was passed via package-configuration to expression and then it works.

parametized inserts in a loop

Hi,

I have a for next loop that I am using to write some data into a table.

The problem I have is one of the fields is an @.parameter

The relevant bit of VB.net code is:

Dim dbpm_theDateTimeAs System.Data.IDataParameter =New System.Data.SqlClient.SqlParameter

dbConnection.Open()

For intCounterAsInteger = 0To ds.Tables(0).Rows.Count - 1

querystring ="Insert into tblActionsToDo ([TheDT], [username]) VALUES (@.theDateTime, '" & username &"')"

dbpm_theDateTime =New System.Data.SqlClient.SqlParameter

dbpm_theDateTime.ParameterName ="@.theDateTime"

dbpm_theDateTime.Value = tmpstrDT

dbpm_theDateTime.DbType = System.Data.DbType.DateTime

dbCommand.Parameters.Add(dbpm_theDateTime)

dbCommand.CommandText = querystring

rowsAffected = dbCommand.ExecuteNonQuery

Next

The first time through the for next loop, everything works fine. The next time though I get an error that @.theDateTime has already been defined. To be fair, it is true; but how do I get round this? I've tried making the @.parameter a string so that I can manipulate it as "@.parameter" & intcounter, and thus have a uniquely-named varaible for every loop, but it doesn't work.

thanks for any pointers

I fixed this one out on my own.

All I had to do was move everything outside of the loop except for

dbpm_theDateTime.Value = tmpstrDT

and it works just fine :)

Parameters option for "All"

hey there, relatively new new SRS, but lots of Crystal background. I'm having
a bit of difficulty with parameters/filters. I have a run-time parameter
setup which then filters the report. The parameters themselves work fine,
but I want them to be optional. In other words, I want a parameter option
that shows me all the records. I would think an expression would work, but
I can't seem to get the syntax to work quite right. Is there an expression
or another setup?My first suggestion is to stay away from filters unless you absolutely have
to use them. With a filter all the data is brought down from the database
and then filtered by RS. What is much better (much higher performance) is to
apply it with the query.
First, how to do an all. Use a union query (or if you have hard coded the
parameters just add this).
select somefield as value, another field as label from a table union
select 'All' as value, 'All' as label
next your query for the data.
Select blah, blahblah from mytable where anotherfield = @.MyParam or @.MyParam
= 'All'
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Shawn" <crmpro@.gmail.com> wrote in message
news:12107632558864480156250@.news.west.cox.net...
> hey there, relatively new new SRS, but lots of Crystal background. I'm
> having a bit of difficulty with parameters/filters. I have a run-time
> parameter setup which then filters the report. The parameters themselves
> work fine, but I want them to be optional. In other words, I want a
> parameter option that shows me all the records. I would think an
> expression would work, but I can't seem to get the syntax to work quite
> right. Is there an expression or another setup?
>