Solved

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

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

760 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

18 Experts available now in Live!

Get 1:1 Help Now