Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSRS filter applied inconsistently between cells in a single tablix Expand / Collapse
Author
Message
Posted Friday, March 7, 2014 1:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 8:47 AM
Points: 33, Visits: 72
I have a report developed using report builder 3, and deployed on SSRS 2008 server. It's a very basic table with four compartments, each with a different chart showing the same data. The dataset is connected to a view of 8 weeks of data, but I've set up a filter on the rdl to filter results based on execution time and the report autorefreshes. Here's the filter expression on the dataset itself in the RDL:

=choose
(
second(Globals!ExecutionTime) mod 3 + 1
, DateAdd("d",1-DatePart("d" , Today() ) , Today() )
, DateAdd("d", -(WeekDay( Today() , 2 ) )+1,
Today() )
,


today()

)

The idea is that this report shows on a TV screen in an employee common area, and it displays live data relating to the work that they are collectively doing. Each time it refreshes, it will show either activity "today", "This Week" or "This Month." On the whole, it works as intended. I was trying to get a few aesthetic results such as getting rid of the toolbar (since no one will be interacting with it) and discovered the URL Access options for reports.

A side benefit to turning off the toolbar using the URL access command RC:Toolbar=false I discovered, was that the report renders way faster - no "loading" animation comes up at all, it just redraws lightning fast, instead of greying out the screen for several seconds each time it refreshes the data (which is several times a minute).

Here's the problem though - when I call the report using URL access, the four quadrants of the report, just different cells in a single tablix - they render with different filters applied. So one chart is showing "Today" and another chart shows "this month". The whole report for any given moment, should always have the same filter applied, based on execution time. Clearly, the sections of the report are rendering independently from each other - at least I can't think of any other reason they would come up different, when they are all part of the same scope, on the same tablix.

How can I ensure that all the parts of the report render/execute/query/filter from the same moment? I explored and experimented with other URL access options, but I don't know what many of them do, and was not able to get predictable results with anything I tried.

To be clear, when I just browse to the report and execute it through the regular interface I do not have this problem, even each time it refreshes - only when I call it through the URL Access does it show this behavior.

Ideas? Please? I like the fast rendering, and I want the toolbar gone. Those are my primary goals. Is there a different way to approach it outside of URL Access? Or is there a property or setting on the report itself that would yield predictable results?
Post #1548876
Posted Thursday, March 20, 2014 12:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 8:47 AM
Points: 33, Visits: 72
Was this phrased too poorly? Does no one have any idea what the question is?

Surprised to have not had any ideas come up here.

Crickets.
Post #1553187
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse