expression syntax

How would I do a select from a container using the previous container's starttime as a condition in the variable?

select publisher,publisher_db,subscriber,subscriber_db,article
from msdb.dbo.sysreplicationalerts
where error_id <> 0
and alert_error_code = 20574
and [time] >= " + @.[System::ContainerStartTime] + "

Thanks,

Phil

Tackett,

I dont know the requirements of your project, but try to run the SQL statment inside a OLEDB command and define @.[System::ContainerStartTime] as parameter.

For example, you can create your sql statment as stored procedure in database and inside OLEDB Command write in SQL command :

EXEC SP_NAMESTOREDPROCEDURE ?

And in the second tab link the parameter to your system variable.

If you want i can show you an example.

Regards,

Pedro

|||

Isn't there a way to refer to the containerstarttime in a execute sql task via expression syntax? That's what I'm trying to do.

select publisher,publisher_db,subscriber,subscriber_db,article
from msdb.dbo.sysreplicationalerts
where error_id <> 0
and alert_error_code = 20574
and [time] >= ' + (DT_STR,50)@.[System::ContainerStartTime] + '

|||

Sorry Tacket, I was "sleeping"...

Try this post to help your problem...

Tomorrow morning I will think better about this.

Regards,

Pedro

|||

NP. Actually I need to do a BETWEEN where time between 'previous container' and 'current container'. I'm sure that involves system variables and namespaces .

Thanks,

Phil

|||

Check this:

http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx

|||

Ok, so I need to create a variable and then evaluate it as an expression and put the code in there, correct? NP, accept, I can't get it to evaluate in the "evaluate as expression" part. Here's what I have so far in the expression builder and it's not working..

"select publisher,publisher_db,subscriber,subscriber_db,article
from msdb.dbo.sysreplicationalerts
where error_id <> 0
and alert_error_code = 20574
and [time] >= " + (DT_STR,50)@.[System::ContainerStartTime] + "

Any help?

Thanks,

Phil

|||

One thing I noticed is that your DT_STR type conversion is missing the thrid parameter (Code Page)

(DT_STR,50) should be (DT_STR,50,1252) -- Assuming you are using the standard 1252 code page.

|||

TITLE: Expression Builder

Expression cannot be evaluated.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476

ADDITIONAL INFORMATION:

Attempt to parse the expression ""select publisher,publisher_db,subscriber,subscriber_db,article
from msdb.dbo.sysreplicationalerts
where error_id <> 0
and alert_error_code = 20574
and [time] >= " + (DT_STR,50,1252)@.[System::ContainerStartTime] + "" failed. The token """ at line number "5", character number "68" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.

(Microsoft.DataTransformationServices.Controls)

BUTTONS:

OK

Here is my code in the variable's expression builder:

"select publisher,publisher_db,subscriber,subscriber_db,article
from msdb.dbo.sysreplicationalerts
where error_id <> 0
and alert_error_code = 20574
and [time] >= " + (DT_STR,50,1252)@.[System::ContainerStartTime] + "

|||Well I took off the + " and now it's working |||

Working syntax for those who care:

"select publisher,publisher_db,subscriber,subscriber_db,article
from msdb.dbo.sysreplicationalerts
where error_id <> 0
and alert_error_code = 20574
and [time] between '" + (DT_STR,50,1252)@.[System::ContainerStartTime] + "' and '" + (DT_STR,50,1252)GETDATE() + "'"

Expression for Running Total

I want to write an expression where the running total should be
calculated if the previous value for a column(invoice code) is not
equal to the current value for the invoice code.
The expression should be something like this:
iif( current invoice code is not equal to the previous invoice
code,sum(payment),0)
How can I compare the current and previous value of a column in the
expression?
In Crystal we can say calculate running total with the change of
Invoice Code in the Edit Running Total Field Box. Trying to do the same
concept in RS where the running total should be calculated with the
change of invoice code.
Please help!
ThanksI think you just need to add a (table) grouping to your data (based on
InvoiceCode). In the group header you would do the calculation of the total
payment. In the group details you can show the invoice detail rows.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"bak" <bakgroup@.gmail.com> wrote in message
news:1117132892.250745.323640@.o13g2000cwo.googlegroups.com...
>I want to write an expression where the running total should be
> calculated if the previous value for a column(invoice code) is not
> equal to the current value for the invoice code.
> The expression should be something like this:
> iif( current invoice code is not equal to the previous invoice
> code,sum(payment),0)
> How can I compare the current and previous value of a column in the
> expression?
> In Crystal we can say calculate running total with the change of
> Invoice Code in the Edit Running Total Field Box. Trying to do the same
> concept in RS where the running total should be calculated with the
> change of invoice code.
> Please help!
> Thanks
>|||This did not help. The solution to my problem is to write an
expression where if the current invoice code is not equal to the
previous invoice code then calculate sum. I am unable to find a VB.NET
function which helps me do this.
Can anyone tell me some good sites where I can get information about
all the VB.NET functions that can be used in Reporting Services. I
have been looking for the function in VS.NET documentation but unable
to find something that could help me compare the current and previous
value of a column.
Thanks.

exporting to csv

Hi
have had a look this morning through previous postings but cant quite find
what Im looking for, as a complete beginner to reporting services Im a bit
behind in how to do things and where configurations are set
what I have is a report that needs to be approved by human eye before being
exported(csv) for import to financial package
what I want to do is have a lot of information in the report to aid the
manual check(name,address etc), but then only export the information required
for the import(account number,amount) when the user selects the export option
if someone could point me in the general direction I would be grateful
many thanks
STuartHere is how I do this. Create your report as normal. Then copy it and remove
all the extraneous stuff so all you have is. For CSV you really want to only
have a table, don't have any extra fields on the report. Then at the top of
the page of the first report have a textbox. Have the text say something
like, Export Data, underline it and make it blue. Right mouse click
properties, advanced properties, navigation, jump to URL. Call the second
report (I hide this report in list view so the user doesn't normally see
it). In my case I wanted it to come up in another window automatically
(which pulls it up into Excel if you click open, in your case they would
click save and save to whatever filename they want).
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
A few things to note, I specify ASCII because the default is Unicode. I
assume you want ASCII. If you want unicode then just leave off that part.
Also be sure to be careful with the parameters, the parameter names are case
sensitive.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stuart" <Stuart@.discussions.microsoft.com> wrote in message
news:FF0C86A2-D53C-4EE1-B952-3C7DE218B0E3@.microsoft.com...
> Hi
> have had a look this morning through previous postings but cant quite find
> what Im looking for, as a complete beginner to reporting services Im a bit
> behind in how to do things and where configurations are set
> what I have is a report that needs to be approved by human eye before
> being
> exported(csv) for import to financial package
> what I want to do is have a lot of information in the report to aid the
> manual check(name,address etc), but then only export the information
> required
> for the import(account number,amount) when the user selects the export
> option
> if someone could point me in the general direction I would be grateful
> many thanks
> STuart