Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2010-11-17
5
Medium Priority
?
479 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

715 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