Solved

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

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

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…
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.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

679 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