CODEWITHSUNDEEP
reporting-services
BigRedRPB
BigRedRPB

Reputation: 1

How to cause SSRS Action URL to rerun Now or Today when clicked

I have a report that pulls a list of ID's between @StartDate and @EndDate. I typically have this report open in my browser at all times and I periodically hit F5 to update it. Because the dates are in the parameters, F5 works great during they day but, when I come in the next morning and hit F5, it still shows yesterday's data. To make this easy on myself, I added a link for 'Go To Today' which uses a URL to run the report in the same window with a StartDate of Today() and an EndDate of Today() + 1. Again, this works fine when I'm browsing historical data and then click the Go To Today link.

The problem is that the link is generated (and hard coded) when the report is run so, if I run the report today, then come in tomorrow and click Go To Today, the Start and End dates in the generated URL are actually Yesterday and Yesterday + 1, respectively, when they should be Today() and Today() + 1.

This is my current code. I understand that the expression is evaluated when the report is run so I was hoping I could somehow force the expression to evaluate when the link is clicked. To do this, I tried sending a 'flag' date as the StartDate to the query and then using a CASE WHEN in SQL to switch the flag to getDate(). This worked, but it shows the 'flag' date in the StartDate parameter field on the report, which is not ideal.

=IIf(Globals!RenderFormat.IsInteractive
    , ("http://server/Reports/report/ReportName" & 
        "?StartDate=" & Today() & 
        "&EndDate=" & DateAdd("d", + 1, Today()) & 
        "&TicketType=" & Parameters!TicketType.Value & 
        "&DeviceType=" & Parameters!DeviceType.Value & 
        "&SortBy=" & Parameters!SortBy.Value & 
        "&ReportView=All"
        )
    ,Nothing
    )

I then tried using the 'Go To Report' action instead of 'Go To URL' and specifying parameters. Again, this worked OK (I think), but it caused the report to run without showing the Parameter pane, which is, again, not ideal. If I could pass something to cause the parameter pane to show, this solution could potentially work but I stopped messing with it because I couldn't get the parameters pane to show. I did try passing Parameters=true and rc:Parameters=true but they both came up as invalid.

Finally, I was thinking I could run the code block below using a Javascript block, similar to when opening the report in a new window. I've shown this in the second code block below but, again, the Date.Now is evaluated when the report is run so it's hardcoded to that date. 

=IIf(Globals!RenderFormat.IsInteractive
    , ("javascript:void(window.open('http://server/Reports/report/ReportName" & 
            "?StartDate=" & FormatDateTime(Date.Now(),2) & 
            "&EndDate=" & FormatDateTime(DateAdd("d", + 1, Date.Now()),2) & 
            "&TicketType=" & Parameters!TicketType.Value & 
            "&DeviceType=" & Parameters!DeviceType.Value & 
            "&SortBy=" & Parameters!SortBy.Value & 
            "&ReportView=All"
            & "','_self'))")
        ,Nothing
        )

At this point, I'm thinking my only option would be to pass some sort of Javascript in the URL that actually gets evaluated when the link is clicked (which I don't know how to do) or to use a Code Block that gets evaluated when the link is clicked, which I'm also not completely clear on how to do.

Any input would be greatly appreciated. And, yes, I realize this is a lot of work to keep me from clicking Go To Today 2 times each day instead of one, but I'm also counting my laziness as a learning experience. :)

Upvotes: 0

Views: 168

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21703

I've not tested this with a deployed report but it should work...

Set the default values for your start and end dates to 

=DateAdd("d",-1,Today())

and 

=Today()

respectively.

When you hit F5 the report should reload with the updated dates.

update I've just tried this deployed using Now() instead of Today() so it included time and it ran as expected.. Once each press of F5 the parameter values where updated

Upvotes: 1

Related Questions