Date parameter works in report designer but not when deployed.

I use the following expression as the default parameter for a report
called 'Start':
=IIF(DATEPART("d",today()) >= "12",DateSerial(Year(today()),
Month(today()), 1) ,DateSerial(Year(today()), Month(now)-1, 1))
The query then uses the parameter to filter the data so I only get the
transactions I want depending on what day of the month it is 'today',
i.e., 'Date >= @.start'
This works fine in Report Designer in VS. However, it doesn't work once
the report is deployed and I run it through Report Manager - I get all
the transactions.
Anyone know why this is happening? I'm still using SQL2000I see two problems with your expression:
1) DatePart returns an integer not a string
2) With DateSerial(Year(today()), Month(now)-1, 1) what happens when now is
January? What Month is January minus 1?
I suggest you use the following expression:
=Iif( DatePart(DateInterval.Day, Today()>=12, DateSerial(Year(Today()),
Month(Today()),1), DateAdd(DateInterval.Month, -1, DateSerial(Year(Today()),
Month(Today()),1)))
HTH
--
Magendo_man
Freelance SQL Reporting Services developer
Stirling, Scotland
"paddydog" wrote:
> I use the following expression as the default parameter for a report
> called 'Start':
> =IIF(DATEPART("d",today()) >= "12",DateSerial(Year(today()),
> Month(today()), 1) ,DateSerial(Year(today()), Month(now)-1, 1))
> The query then uses the parameter to filter the data so I only get the
> transactions I want depending on what day of the month it is 'today',
> i.e., 'Date >= @.start'
> This works fine in Report Designer in VS. However, it doesn't work once
> the report is deployed and I run it through Report Manager - I get all
> the transactions.
> Anyone know why this is happening? I'm still using SQL2000
>

Date not evaluating in an expression

I have been looking all over for some info about other people having this problem, but haven't found anything.

I have a package that needs to download a dated file from an ftp site. I am using a couple script objects to set variables, and one of them is the filename based on the date. I use an expression to get the date:

@.[User::varFileName] = (DT_WSTR,4) Year( GetDate()) + (DT_WSTR,2) Month( GetDate()) + Substring((DT_WSTR, 29) GETDATE(), 9, 2)

Everything works really well when I am debugging it locally. However once it is on the server or even once I come back to it in a day or two, I am still seeing the old date. I thought it might be because my variable needed to be set to evaluateexpression = true, however once I did this it hung me and prevented me from debugging and I had to end bus dev studio. Not sure if its because it is being evaluated in two places (as a global and then in a script) but when I took it out of my script it hung again. Its strange in order to get it to work when I am debugging it locally I have to go to each process and evaluate the expressions in there, then it seems to work. thanks!

Doriss wrote:

I have been looking all over for some info about other people having this problem, but haven't found anything.

I have a package that needs to download a dated file from an ftp site. I am using a couple script objects to set variables, and one of them is the filename based on the date. I use an expression to get the date:

@.[User::varFileName] = (DT_WSTR,4) Year( GetDate()) + (DT_WSTR,2) Month( GetDate()) + Substring((DT_WSTR, 29) GETDATE(), 9, 2)

Everything works really well when I am debugging it locally. However once it is on the server or even once I come back to it in a day or two, I am still seeing the old date. I thought it might be because my variable needed to be set to evaluateexpression = true, however once I did this it hung me and prevented me from debugging and I had to end bus dev studio. Not sure if its because it is being evaluated in two places (as a global and then in a script) but when I took it out of my script it hung again. Its strange in order to get it to work when I am debugging it locally I have to go to each process and evaluate the expressions in there, then it seems to work. thanks!

Not quite sure of the whole picture here but when you say its being evaluated in two places I start to worry. So, two questions:

Where is the expression (is it on a variable or elsewhere)?

What are you attempting to do in your script task?

-Jamie

|||

The script portion might just be my inexperience with ssis, but I read online somewhere that using script objects to set your variables was good practice and I was originally having problems getting my variables to be read at all, and this solved the problem. So basically they are just empty script objects where I am using the expressions for those objects to set variables that will be used throughout the process. I used two of them because at the time I couldn't figure out how to use them to set just a plain variable without using one of the properties (ex: readonlyvariable). So I use the readonlyvariable and the readwrite variable in each object to set my expression. I thought for a moment just the other day that I could actually take this out and set the variables globally, but as I mentioned, that is when I had problems with the app crashing when I tried to set the expression to be evaluated at a global level. I actually tried changing my program today to also retrieve the date from the database by running a query that would return it to a variable and it still did not work. It seems as if the variables get set at some point in time and then they are not re-evaluated.

|||

Doriss wrote:

The script portion might just be my inexperience with ssis, but I read online somewhere that using script objects to set your variables was good practice and I was originally having problems getting my variables to be read at all, and this solved the problem. So basically they are just empty script objects where I am using the expressions for those objects to set variables that will be used throughout the process. I used two of them because at the time I couldn't figure out how to use them to set just a plain variable without using one of the properties (ex: readonlyvariable). So I use the readonlyvariable and the readwrite variable in each object to set my expression. I thought for a moment just the other day that I could actually take this out and set the variables globally, but as I mentioned, that is when I had problems with the app crashing when I tried to set the expression to be evaluated at a global level. I actually tried changing my program today to also retrieve the date from the database by running a query that would return it to a variable and it still did not work. It seems as if the variables get set at some point in time and then they are not re-evaluated.

Woah. That's alot of information.

I wouldn't agree that using script tasks to set a variable is considered better practice than using an expressoin on the variable. In fact I would argue to the contrary:

Variables evaluated by an expression are more reusable|||

Wanted to post that I found the resolution to this. I did end up getting rid of the script object stuff. I think I suffered from too much information on the web that sent me in the wrong direction. I decided to use global variables, and got rid of my script objects. But what really made it work was removing the reference to the variable name. So this:

@.[User::varFileName] = (DT_WSTR,4) Year( GetDate()) + (DT_WSTR,2) Month( GetDate()) + Substring((DT_WSTR, 29) GETDATE(), 9, 2)

Now becomes:

(DT_WSTR,4) Year( GetDate()) + (DT_WSTR,2) Month( GetDate()) + Substring((DT_WSTR, 29) GETDATE(), 9, 2)

When I did it the other way it was locking my variables (which was why it was crashing though it actually wasn't - it was just taking a really long time to tell me what the problem was). I also changed some of my other variables that were referencing this variable, which I just read about.

For the record, I think the documentation on variables is a bit lacking. It seems some important things to know from my experience is to make everything global (work with it in the variables window and set it's properties). Make sure to set the evaluateexpression to True if thats what you want (in the properties window). Do not reference another variable in your expression and do not set the variable equal as mentioned above (if you are doing this in the properties expression window). Other things to know that it took me forever to figure out is that you should save the package as a server package (you have to use the copy as) - this is the easiest way to port over your package. You need to give the user account that this is running under user mapping to msdb (SQLAgentOperatorRole, SQLAgentReaderRole, SQLAgentUserRole) as well as create a proxy account. Best info for that is here:http://www.codeproject.com/useritems/Schedule__Run__SSIS__DTS.asp

Phew - it only took me forever to actually get this to work!

Date issue with Derived Column / Expression Language

Can someone confirm this for me? The expression language in SSIS has the same limitations on date ranges as Sql Server? That limitation is that valid date ranges are from Jan 1, 1753 to Dec 31, 9999.

When ever I try to do a date function (DATEPART, for example) in a Derived Column Transformation on a date less than 1/1/1753, I get an error. I initially discovered this when bringing data over from Oracle to Sql Server. Just as a test, I created a text file filled with various dates and tried to import it. Whenever a date is less than 1/1/1753, it blows up.

For example, this expression code - DATEPART("YEAR",Date) will yield this error - [Derived Column [24]] Error: The "component "Derived Column" (24)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "YEAR" (80)" specifies failure on error. An error occurred on the specified object of the specified component.

As a workaround, I've been using a Script Component to do date checking, but this is obviously not ideal.

Jeff,

I don't think that's the case. I have just created a package containing a DT_DBTIMESTAMP, DT_DBDATE & DT_DATE and managed to put the value "1500-12-31" into each of those columns.

-Jamie

|||Hey Jamie, thanks for taking the time to answer....but, did you attempt a date function on any of the dates. Try doing a DATEPART("YEAR",date_col) and see what happens.|||

Hey. That function works on any date after 1753-01-01, nothing before that.

Looks like you were right!!

-Jamie

|||Great. I wanted some independent verification. I just submitted this as a bug.

date formatting or localization

I have written this ugly expression because I didn't know any other way. What I am trying to do is convert an English date string to a French string.

example

January 2005 > Janvier 2005

Thank you,

Pavel

=Switch(

Month(CDate(Fields!Month.Value) ) = 1, "Janvier" & " " & Year(CDate(Fields!Month.Value)),
Month(CDate(Fields!Month.Value) ) = 2, "Fevrier" & " " & Year(CDate(Fields!Month.Value)),
Month(CDate(Fields!Month.Value) ) = 3, "Mars" & " " & Year(CDate(Fields!Month.Value)),
Month(CDate(Fields!Month.Value) ) = 4, "Avril" & " " & Year(CDate(Fields!Month.Value)),
Month(CDate(Fields!Month.Value) ) = 5, "Mai" & " " & Year(CDate(Fields!Month.Value)),
Month(CDate(Fields!Month.Value) ) = 6, "Juin" & " " & Year(CDate(Fields!Month.Value)),
Month(CDate(Fields!Month.Value) ) = 7, "Juillet" & " " & Year(CDate(Fields!Month.Value)),
Month(CDate(Fields!Month.Value) ) = 8, "Aout" & " " & Year(CDate(Fields!Month.Value)),
Month(CDate(Fields!Month.Value) ) = 9, "September" & " " & Year(CDate(Fields!Month.Value)),
Month(CDate(Fields!Month.Value) ) = 10, "Octobre" & " " & Year(CDate(Fields!Month.Value)),
Month(CDate(Fields!Month.Value) ) = 11, "November" & " " & Year(CDate(Fields!Month.Value)),
Month(CDate(Fields!Month.Value) ) = 12, "December" & " " & Year(CDate(Fields!Month.Value))

)The only thing I see wrong with this is that you have Year(CDate(Fields!Month.Value) and that will not produce a result, correct?|||The field Month.Value stores "January 2004". I get the value from Analysis Services.

Pavel

Date formatting issue

I've Googled and MSDNed to no avail. All I want is to display, in a
concatenated expression with other text, say today's date as 2008-03-14,
regardless of the Windows local settings (I want to hard code the date
format). If all I had in a textbox were a date, I would use the Format
property and set it to yyyy-MM-dd. But I want an VB.NET function that will
take a date value as a parameter (and I suppose a second parameter like
"yyyy-MM-dd") and return 2008-03-14. Pretty simple, huh?
--
Thank you,
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.comFormat(Date,"yyyy-mm-dd")

Date format used in expression

I am an Oracle PL/SQL guy, and am used to formatting dates with a to_date
conversion function. I can't figure out how to do this in RS.
I have a couple of date parameters, used to filter by date range, with the
default beginning value set at 1/1/1900 and the end value at 12/13/2050.
What I want to be able to do in the report header (where I display the
parameter values that the user inputs at runtime), is have "All" appear when
the user chooses the default values (i.e. the user is filtering by "all"
dates for that range). If the user actually chooses dates, then I show those
dates (which has worked pretty easily with teh following:
Parameters!CertRecordDtBegin.Value + " to " +
Parameters!CertRecordDtEnd.Value)
So I am trying to build an expression to display this in the header. I've
tried:
=IIf(Parameters!CertRecordDtBegin.Value = DATE(1900,1,1),
IIf(Parameters!CertRecordDtEnd.Value = DATE(2050,12,31), "All",
Parameters!CertRecordDtBegin.Value + " to " +
Parameters!CertRecordDtEnd.Value), Parameters!CertRecordDtBegin.Value + " to
" + Parameters!CertRecordDtEnd.Value)
I've also tried:
=IIf(FormatDateTime(Parameters!CertRecordDtBegin.Value, vbShortDate) = 01/01/1900, IIf(FormatDateTime(Parameters!CertRecordDtEnd.Value, vbShortDate)
= 12/31/2005, "All", Parameters!CertRecordDtBegin.Value + " to " +
Parameters!CertRecordDtEnd.Value), Parameters!CertRecordDtBegin.Value + " to
" + Parameters!CertRecordDtEnd.Value)
For the former, I got 2 errors:
- The value expression for the textbox contains an error: Input string was
not in a correct format, and
- The value expression for the textbox contains an error: [BC30108] 'Date'
is a type, and so is not a valid expression
For the latter, I don't get errors in my task list, but on the output, I get
"#Error" for that textbox.
Any ideas?
ThanksSee if any of these tips might help.
Case 1:
* Try using CDATE() or CSTR():
* CDATE(Parameters!CertRecordDtBegin.Value) // returns a Date type
* CSTR(DATE(1900,1,1)) // returns a string
* Use & instead of +
Case 2:
* Quote the dates to the right of the equals sign: "01/01/1900"
* Use & instead of +
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Derek in Richmond" <DerekinRichmond@.discussions.microsoft.com> wrote in
message news:4171551B-520F-4F8F-BA83-80FAAFF06252@.microsoft.com...
>I am an Oracle PL/SQL guy, and am used to formatting dates with a to_date
> conversion function. I can't figure out how to do this in RS.
> I have a couple of date parameters, used to filter by date range, with the
> default beginning value set at 1/1/1900 and the end value at 12/13/2050.
> What I want to be able to do in the report header (where I display the
> parameter values that the user inputs at runtime), is have "All" appear
> when
> the user chooses the default values (i.e. the user is filtering by "all"
> dates for that range). If the user actually chooses dates, then I show
> those
> dates (which has worked pretty easily with teh following:
> Parameters!CertRecordDtBegin.Value + " to " +
> Parameters!CertRecordDtEnd.Value)
> So I am trying to build an expression to display this in the header. I've
> tried:
> =IIf(Parameters!CertRecordDtBegin.Value = DATE(1900,1,1),
> IIf(Parameters!CertRecordDtEnd.Value = DATE(2050,12,31), "All",
> Parameters!CertRecordDtBegin.Value + " to " +
> Parameters!CertRecordDtEnd.Value), Parameters!CertRecordDtBegin.Value + "
> to
> " + Parameters!CertRecordDtEnd.Value)
> I've also tried:
> =IIf(FormatDateTime(Parameters!CertRecordDtBegin.Value, vbShortDate) => 01/01/1900, IIf(FormatDateTime(Parameters!CertRecordDtEnd.Value,
> vbShortDate)
> = 12/31/2005, "All", Parameters!CertRecordDtBegin.Value + " to " +
> Parameters!CertRecordDtEnd.Value), Parameters!CertRecordDtBegin.Value + "
> to
> " + Parameters!CertRecordDtEnd.Value)
> For the former, I got 2 errors:
> - The value expression for the textbox contains an error: Input string was
> not in a correct format, and
> - The value expression for the textbox contains an error: [BC30108] 'Date'
> is a type, and so is not a valid expression
> For the latter, I don't get errors in my task list, but on the output, I
> get
> "#Error" for that textbox.
> Any ideas?
> Thanks
>

Date Format in expression

Hi all,

I would like to generate a file name according to the current date, like 20070716.TXT. In expression, the function Month(Getdate()) will return 7 instead of 07.

How can i do it.

TIA

Patrick

Try this... Going by memory.

RIGHT("00" + (DT_WSTR,50)MONTH([DateColumn])),2)

|||

Start for using this...

=Format(cdate(Fields!Dia.Value),"dd-MM-yyyy")

and concactenate with string you want!!

regards

|||

PedroCGD wrote:

Start for using this...

=Format(cdate(Fields!Dia.Value),"dd-MM-yyyy")

and concactenate with string you want!!

regards

In SSIS? Looks like Excel or something to me.

Date format - Parameter/Field

Hi,

I have some difficulties to format a Date value in my reporting service.

As you know you can use this kind of expression:

=DateTime.Parse(Parameters!ReportingDate.Value).ToString("dd/MM/yyyy")

But it does not work, and when I just want to use the format property of the textbox content such as "dd/MM/yyyy", it displays "dd/MM/yyyy", not the real formatted data, this property works for "=Today()" but not for parameters and fields. What is the diffrence?

Have you an idea to format a Parameter/Field of Date type such as dd/MM/yyyy?

Many thanks!

Vin,

You can make your parameter of data type DateTime, this would eliminate the need to parse a string. If not, try

Format(CDate(Parameters!Report_Parameter_0.Value),"d")

Ham

|||

With your code, I get this error:

Cast from string "20061222" to type 'Date' is not valid.

Strange because the string has a correct date format :S

|||

Vin,

What's your Parameter data type?

Ham

|||

Also,

the value that is stored in this parameter.

Ham

|||

The parameter is an integer, it s the reason why I m using now:

=DateTime.Parse(Parameters!ReportingDate.Value.ToString())

The problem is the value of ReportingDate is "20061222" and to work as DateTime you have to have "2006/12/22", after you can display it as you want with ToString function.

I would like to have this format 2006/12/22 with / in fact, but properly...

|||

Vins,

That's a tough one, if you're using integer data type for Datetime. I guess my approach would be to change to integer into a string value

LEFT(Parameters!ReportingDate.Value.ToString,4) & "/" & Mid(Parameters!ReportingDate.Value.ToString,5,2) & "/" & RIGHT(Fields!SSN.Value,2)

The catch here is that you'll have to return always 4 digit years, 2 digit months and 2 digit days, instead of 2007/1/1 you would need 2007/01/01

Ham

|||Thanks Ham, it s working very well ;-)

Date expression in Access

I'm pretty new to this whole "programming" game. The date field I'm using is [date]. I need to create two queries. One will pull all records year-to-date this year. The other should pull all records year-to-date last year. I'm sure there is an expression that accomplishes this, without having to type in parameters each time I execute the query. Examples, please.datediff(yy, [YourDateValue], getdate())

...will give the difference in the years between your data value and the current date.

Value 0 means YourDateValue is current year-to-date.
Value 1 means YourDateValue is prior year-to-date.
.
.
.
etc

blindman

date expression for param - start of current month

I have a report that I want to run for the current month. I have two
params, datestart and dateend and am looking for an expression to use
that will calculate the start of the current month. I have been
playing with datediff and dateadd but can't figure it out!
Please helpUse the datepart function to get the current month and year, then reconstruct
a date using those parts and just use '1' as the day... something like this:
firstday = datepart(mm, getdate()) + '/01/' + datepart(yyyy, getdate())
uhm... pretty sure you have to do some casting in there because datepart
returns integers... but otherwise it should work to get the actual first of
the month.
"gavinator@.nospam.nospam" wrote:
> I have a report that I want to run for the current month. I have two
> params, datestart and dateend and am looking for an expression to use
> that will calculate the start of the current month. I have been
> playing with datediff and dateadd but can't figure it out!
> Please help
>|||One of the easiest ways to accomplish this is
Date() - Day(Date()) + 1
and the easiest way to get the last day of the
previous month is to exclude the "+ 1" part.
GeoSynch
"Timm" <Timm@.discussions.microsoft.com> wrote in message
news:EEEEAAEB-B288-4F23-B67E-6DCEA4C35C32@.microsoft.com...
> Use the datepart function to get the current month and year, then reconstruct
> a date using those parts and just use '1' as the day... something like this:
> firstday = datepart(mm, getdate()) + '/01/' + datepart(yyyy, getdate())
> uhm... pretty sure you have to do some casting in there because datepart
> returns integers... but otherwise it should work to get the actual first of
> the month.
> "gavinator@.nospam.nospam" wrote:
>> I have a report that I want to run for the current month. I have two
>> params, datestart and dateend and am looking for an expression to use
>> that will calculate the start of the current month. I have been
>> playing with datediff and dateadd but can't figure it out!
>> Please help

Date Expression

I am trying to write an expression that says
iif (field.receipt date is < currentdate by 30 days, then grab field.cost,
if not then blank " ")
Basically, I am trying to write a report for aging buckets.
Thanks,
RyanOn Jun 14, 3:07 pm, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
wrote:
> I am trying to write an expression that says
> iif (field.receipt date is < currentdate by 30 days, then grab field.cost,
> if not then blank " ")
> Basically, I am trying to write a report for aging buckets.
> Thanks,
> Ryan
Something like this should work:
=iif(Fields!ReceiptDate.Value < Dateadd("d", -30, Now()), Fields!
Cost.Value, Nothing)
Regards,
Enrique Martinez
Sr. Software Consultant