Scheduled Reports and the Select All parameter

I have a question. I've got a user request that when they schedule reports for weekly delivery and they use "Select All" to capture all categories in a multi-select parameter situation, they want that to dynamically apply as more categories are added. Does anyone know if this is "normal" behavior for the Select All option in 2008R2? I know that it worked in 2005 simply because you had to actually CODE a select all type value. With R2, the Select All box came along, but I'm not sure how that works with a scheduled service. Does anyone know or have any advice?

 

Ex: My user has a DDL of "Problem Categories." When they subscribe to the report on a weekly basis beginning 1/1, there are only 10 available categories. A few weeks pass by and now there are actually 15 available categories. Is the scheduling process "smart" enough to apply the "Select All" to new categories that have been added since the initial subscription?

Hi lduvall.

That can be done in the report itself. Your parameter should be configured to:

- Tab General: Allow multipe Values

- Tab Available Values: Get values from a query and dataset should be mapped

- Tab Default Value: Get values from a query and dataset should be mapped (this will automatically populate the parameter with values available in dataset)

So all you must do is in your parameter dataset make sure that values are refreshed automatically based on weekly basis.

 

If you can't do this in your default report, then you should creating a data driven subscription where the parameter will be populated based on a query.

 

 

 

So I have to go back to the way we were forced to do "All" in SSRS 2005? Does this mean that my users will now see "Select All" and "All" (two different options)? Maybe I should name "All" to "Subscribe All" to make it less confusing? It seems like MS added the "Select All" for the sole purpose of letting users check all the boxes quickly but it was never meant to actually by a "dynamic" all. Is that correct?

Sorry, I misunderstood the question initially.  I withdraw my initial answer.

That is the purpose of select all and you can make it dynamic.

Imagine you have a parameter that:

select * from Dim_Time

 With this you will get all days. Let's say from 20010101 to 20120125.

If you create another parameter with a where clause like this:

select * from Dim_Time where day>=20120101

Your parameter can automatically select all days from day 1 of year 2012. You just have to define Default Values to the dataset that you want to be dynamic and check Get Values from a query to perform this.

Hope I was clear enough.

It does not work that way. My query for the DDL is a:

select id, name from table1 join table2 on table1.id=table2.id.

The settings are "get values from query." The problem is that I'm relying on 2008R2 feature that says "Allow multiple values." When my user runs the report & checks the UI box that says "Select All," it simply check-marks all the boxes representing the ids & name that exist AT THAT TIME. If any new id's are created after his initial subscription, they do not update.

Example: ID 1, 2, 3,4 existed on 1/1/12 when he subscribed. He asked for a weekly subscription. On 1/4/12, we added a new ID (ID 5). When his weekly subscription comes in, it only has 1, 2, 3, and 4 because those were the only ones available when he subscribed.

I don't understand your answer. Please elaborate.

If I understand your question correctly, you're saying that your query to get the parameter value list is sound, but, once the subscription is set up with the Select All flag, once new values appear in the query the Select All does not include the new values.  I have run into this more than once.  My solution has been to create a "subscription" copy of the report and set the parameter to use the query as a default value instead of an available value.  This way, when the parameter query finds new values, the report reflects it as well. 

 

If there is a better way to accomplish this, I would love to know it, but, that has been my workaround.

I believe you dont need to create and set parameter as query like kdevault said. I think he is talking about data driven subscription.

I have tons of reports wich parameter depends on others, like my example with Dim_Time. When creating subscription I usually just need to check "Use default".

Can you provide your rdl file so we can assist you?

kdevault said:

If I understand your question correctly, you're saying that your query to get the parameter value list is sound, but, once the subscription is set up with the Select All flag, once new values appear in the query the Select All does not include the new values.  I have run into this more than once.  My solution has been to create a "subscription" copy of the report and set the parameter to use the query as a default value instead of an available value.  This way, when the parameter query finds new values, the report reflects it as well. 

 

If there is a better way to accomplish this, I would love to know it, but, that has been my workaround.

You nailed it. That is EXACTLY what I'm saying. When you say your solution has been to create a "subscription" copy, do you mean you create a duplicate RDL & set the parameter to use the query as a default? I think that might work. It would be a maintenance challenge because it affects a LOT of reports. Just help me understand if that's what you do. (duplicate .rdl but with the default set differently in the one for subscription)

Unfortunately, yes, that is what I'm saying.  I create a duplicate .rdl with a different parameter setup.  It is not an ideal solution by any means, but, it works.  I've got to believe there is a better solution out there, but, I haven't found one yet.