Caching/Clearing Parameters

Question

Post reply

Caching/Clearing Parameters

Andy Gerard

SSC Eights!

Points: 832
More actions
September 14, 2010 at 9:23 am

#229288

I am a SQL developer that has inherited some RDL bug fixing... We have a set of reports that have several parameters. One of the parameters is I guess what you could call a "master" parameter. The value of that parameter is a key lookup on a mapping table that tells the RDL the values of several primary keys to filter the data for. When this "master" parameter changes and the "view report" button is pressed, the sub parameter values are persisting.
Let me give a hypothetical hotel example which might help to explain what I mean. There are 10 floors, with 10 rooms each. The rooms on each floor all have the same configuration, but from floor to floor they are different. Floor one is twin bed, stall shower. Floor two is full bed, full bath, floor three is king bed, full bath, kitchenette, floor four has 2 queen beds, a sofa, a stall shower, etc., etc. There are pulldown menus for floor and room. When you view the report, sub parameters come down to let you select the bed, the bath, the kitchenette, the sofa etc. Let's say you've pulled up the report for the 4th floor, and then selected "brown sofa" in the parameters. Then you change to the first floor. The "brown sofa" persists on the report even though there is no sofa in the rooms on the 1st floor...
How do I get this sub parameter to clear itself out when I change the main parameter? In checking out the properties of the parameter, the only option i see is on the advanced tab "Refresh data when the parameter changes:". But what I want to do is clear the parameter if one of the other parameters has changed... Is there some place I can slap some VB code into the report to say "if parameter 1 has changed, then clear the value of parameter 3?"
Hope this makes sense, as I said I'm new to this RDL thing and trying to reverse engineer an experienced person's work... Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Niraj-627298 Hall of Fame Points: 3641 More actions · Answer 1

Hi Andy,

Jst to make sure about your requirement, will discuss a scenario here:

Suppose we have 3 SSRS report parameters namely, Country, State and City. Now, the report users wish to view the customer details based on a particular city filter, but before that users have an option to select country followed by the appropriate state and then city.

Like: Select Country (frm Country parameter) -> Select State (Obviously for that particular country) -> Select City (with in that selected State)...

Generate report and get the most relevant data out of this filter. 🙂

Is this scenario somewhere matches with your requirement??? Please confirm so that we can discuss it to further level. 🙂

Thanks,

Niraj

Andy Gerard SSC Eights! Points: 832 More actions · Answer 2

Thanks Niraj, that's a much simpler example... but unfortunately the report in question has two layers of parameters. Let's say Country and State are on the first layer, passed in through a "toolbar" with a "vew report" button. After you bring up the report by Country and State, you can then select a city to further restrict the query. The list of cities in this case is being pulled from a cube via .mdx code.

The scenrio is this: if a customer selects "USA" and then "Massachusetts" and runs the report, they will then see the "City" pulldown. But If they change the state to "Vermont", the list of Cities doesn't refresh, allowing an invalid parameter to be passed in (city of Boston, state of Vermont)...

Thanks

--Andy

Niraj-627298 Hall of Fame Points: 3641 More actions · Answer 3

Hi Andy,

Thanks for the explanation. 🙂

Definitely, I'll try to solve this issue on my end.

Meanwhile if you get any solution for this, please share it. 🙂

Thanks,

Niraj

Andy Gerard SSC Eights! Points: 832 More actions · Answer 4

I would imagine this would be done in the "Code" dialog box... Just add an "AfterUpdate" event to the parent parameter, forcing it to clear out the value of the child parameter. I cannot find any documentation or help file content that relates to using this type of code. It's all talking about IIF statements, changing colors based on values, etc... Nothing that discusses something like performing some action based on an event... Anyone have any clues?