Solved

cascade parameter in SQL reporting service, parent parameter can not be reset

Posted on 2010-11-17
5
439 Views
Last Modified: 2013-11-05
I created a cascade parameter in sql report 2005, with two parameters, one parent and one child, all multivalue. I can select all or each value from the parent parameter, but can not unselect all of them from the parent parameter. Can anyone help? I need to uncheck select all from the parent parameter, which will unselect all the values.
0
Comment
Question by:dowlohnesIT
  • 3
  • 2
5 Comments
 
LVL 15

Expert Comment

by:Tim Humphries
ID: 34162781
Ok, if I understand correctly you simply have the problem of when you click 'Select All' everything becomes selected and need a way to enable everything to become unselected.
To do this, simply click 'Select All' again and they will be deselected.

Tim
0
 
LVL 1

Author Comment

by:dowlohnesIT
ID: 34163393
I tried what you suggested, it doesn't work. It would work in a single parameter, or a child parameter in the cascade parameter. But not work for the parent parameter in the cascade parameter. Looks like in the parent pameter, I have to select at least one item, I can not leave all unselected. What have I missed?
0
 
LVL 15

Expert Comment

by:Tim Humphries
ID: 34164734
Hi,

Ok - seems you can do this in 2008, but in 2005 you cannot select 'allow blank value' or 'allow null value' if you have a parameter dependant on entering a value.

If you need to be able to pass a blank or null value to the child parameter, I suggest adding an explicit null or Blank into the dropdown selection list for the parent parameter.

If your parent parameter is based on a sql query you could do this by adding something like :

Union '<Blank>' as Label, '' as Value

to your query.

Post you parameter queries if you want more help.

Tim
0
 
LVL 1

Author Comment

by:dowlohnesIT
ID: 34165546
Thanks for the reply!!
Here is query for dataset1, which provide values for parent parameter @category
SELECT [Name]
FROM [AdventureWorks].[Production].[ProductCategory]

query for dataset2, which provide values for child parameter @product
SELECT
      [Name]    
  FROM [AdventureWorks].[Production].[Product]
where [ProductSubcategoryID] in (select [ProductCategoryID] from [Production].[ProductCategory]
where [Name] in (@Category))

Both parameters are set multivalue, when I tried to unselect all values in parent parameter, it won't accept, at least one item has to be checked. SQL 2008 works the same way.
You suggestion seems to be a work around, but the blank looks a little wierd, you have to uncheck all the other items and check the blank to make a unselect work.
0
 
LVL 15

Accepted Solution

by:
Tim Humphries earned 500 total points
ID: 34165814
Your first parameter query would become:

SELECT [Name] As Value, [Name] As Label
FROM [AdventureWorks].[Production].[ProductCategory]
union
select '' as Value, '<Blank>' As Label

I use '<...>' as that (usually) forces the value to the top of the selection list. To deslect all you should only have to click 'select  all' twice (once to turn them all on, once to turn them all off, and then click '<Blank>'. Notice that I've added separate Value and Label fields, otherwise you would be looking for items with a value of '<blank>' and not an empty string.

If you do this,however, I don't think your child parameter with have any selectable values. Under what circumstances do you expect someone to want to pick a blank value for name? Uncategorised products - is that possible?

Tim
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question