Monday, March 26, 2012
forcing report to refresh
a menu page that gathers some user parameters (report dates etc) and
then opens the report in a new browser window, passing the user
parameters as querystring variables. I have noticed that the report
does not refresh if you use the same parameters twice in a row. For
example, I opened the report, tweaked the underlying data then ran it
again with the exact same parameters. The report did not channge. It
did change when I hit the refresh button in the report itself though. I
would prefer if my users did not have to perform this additional step.
Is there a parameter I can pass, or some other way to force this to
refresh every time?I never found a parameter that RS recognizes to automatically refresh
the report. What I ended up doing was to make up my own. Basically, I
pass all the parameters the report takes, plus another querystring
variable I called "GenDate", which contains the current date and time.
Than way the report parameters are always different, so the report
always refreshes. The problem with this is that RS does not like to
receive any parameters that it is not expecting, so I have to add this
parameter to every report. I just add it as a report parameter, but
don't associate it with anything in the recordset.
If anyone knows of a parameter the RS will recognize, please let me
know. For that matter, is there a master list somewhere? I have a few
that RS tacks on automatically (rs:Command=Render, rs:Format=HTML4.0,
rc:Toolbar=True, rc:JavaScript=True, rc:LinkTarget=_top,
rc:Area=Report), but the documentation is rather sparse.|||I finally came across rs:ClearSession=True which appears to accomplish
what I'm after.sql
Friday, February 24, 2012
For Loop Container with Dates
I'm trying to set up a For Loop Container that steps through the loop based on date logic.
Here's an example of what I'd like to do:
InitExpression | @.SomeDate = 1/1/2007
EvalExpression | @.SomeDate < GETDATE()
AssignExpression | @.SomeDate = DATEADD(DAY, 1, @.SomeDate)
The above syntax does not work, and I can't find an example that uses date logic with the For Loop Container.
Can anyone help me with this, or point me to an example that uses the For Loop Container with dats?
Thanks in advance for the help!
Is @.SomeDate an SSIS variable of DateTime type? Make it so.Otherwise it looks fine. I tried this expression as my AssignExpression and it seemed to work:
@.SomeDate = dateadd("d",1,@.SomeDate)
Are you getting any errors that you care to share with us? That usually helps the most.|||
Thanks for the prompt reply!
I think this is my problem. Here's the message I get:
Error at SomeDate Loop: The data types "DT_DATE" and "DT_I4" are incompatible for binary operator "<". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.
Where would I configure the data type for @.SomeDate? I created a package variable called SomeDate and set the data type there. I guess there is not a direct link between these variable?
Thanks again for the help!
|||Yep, you have that right, but you are missing quotes.InitExpression: @.SomeDate = "1/1/2007"
EvalExpression: @.SomeDate < getdate()
AssignExpression @.SomeDate = dateadd("d",1,@.SomeDate)|||
Phil Brammer wrote:
Yep, you have that right, but you are missing quotes. InitExpression: @.SomeDate = "1/1/2007"
EvalExpression: @.SomeDate < getdate()
AssignExpression @.SomeDate = dateadd("d",1,@.SomeDate)
I guess it's not strictly required to have the double quotes when it comes to the parser. Just make sure that the user variable, SomeDate that you created in SSIS has a DateTime data type and that it is scoped properly.|||
Please pardon my ignorance, I'm still not getting this.
I have the following variable:
Name Scope Data Type
SomeDate Package DateTime
The For Loop has the following:
InitExpression: @.SomeDate = "1/1/2007"
EvalExpression: @.SomeDate < getdate()
AssignExpression @.SomeDate = dateadd("d",1,@.SomeDate)
Here is the error I receive:
Error at SomeDate Loop: The data types "DT_DATE" and "DT_WSTR" are incompatible for binary operator "<". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.
Thanks again for your patience and assistance.
|||Select the For loop and ensure that you don't have another variable of the same name, SomeDate, scoped to the for loop. (You can have the same named variables but with different scopes.) I'm expecting to see a SomeDate listed somewhere with a data type of String.