Solved

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

Posted on 2010-11-17
5
426 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now