Ed Giardina's .NET Blog

Blogging about Hobbyist Adventures in C#, XNA, ASP.NET and other stuff

SQL Server Reporting Services - Optional Parameters

Right now, for our most quick-and-dirty reporting needs from Relational and OLAP databases, we are toying with the idea of using SQL Server 2005 Reporting Services to generate our web reports. Using this server means we get ubiquitous access to our reports in an easy-to-use, consistent manner.

 A major hurdle to using Reporting Services is that by default, Parameters cannot be flagged as 'optional'. After much delay, I found there is a way to 'Hack' a report to have an optional Parameter.

 First, place your parameter in the Query as such:

 SELECT * FROM Table
WHERE
(AssetID = @AssetID OR @AssetID IS NULL)

(Coincidentially, this is the same method I used to make parameters optional in .NET DataSets for web applications).

 Second, in the dataset to populate the Parameters, make sure at least one row returns NULL. You'll need this to have the null row appear in the dropdown that the parameter creates. It might look like this:

SELECT DISTINCT AssetID, AssetName FROM Table
UNION
SELECT NULL, 'All'
ORDER BY 1,2

 

Third, configure the parameter as such; with the default value as null, to allow you to select 'All' 

 
 

 Hope this helps someone!


 

Posted: 06-26-2007 1:26 PM by Richthofen | with 13 comment(s)
Filed under: , , ,

Comments

Joao said:

Hi.

this is great stuff.

but if you want to do an optional parameter not to a foreign key but to a field itself?

Imagine

Table1

COD   NAME FK_DEP AGE

Ok.. we can do your tip on FK_DEP, but if i want to search Name one time and exclude Age, and another time use Age and not name?

Thanx

# July 18, 2007 2:33 PM

Ed said:

Well, I don't see why we couldn't do that in my example:

WHERE

(Age = @Age OR @Age IS NULL)

AND

(COD = @COD OR @COD IS NULL)

That should do the trick. Create a new dataset for every search parameter you want. If you're still having trouble use the contact form on my blog and write me.

Cheers

# July 20, 2007 5:29 PM

WSD said:

As usual Microsoft comes up with a great idea and flounders on the details Doesn't it just make common sense to offer parameters as optional?

Thanks for the heads up and code snippet.

# August 23, 2007 4:06 PM

Shaun said:

I do a similar thing with isnull() which is a little more compact, eg:

someField = isnull(@someField, someField)

# September 11, 2007 12:47 PM

Turbonetics said:

Great post. I was just searching google for this issue and came across your post. It worked flawlessly.

Thank you so much!

# January 11, 2008 12:47 AM

Ricardo Cruz said:

this is great, as it was i was wondering to do in my job. however, here i got an almost finished project and I have to have 4 optional parameters in order to help designing my report. my problem is that the query i got is just to complicated, and i can't get rid of syntax errors, maybe you could help me with that! i am using a data source of the type of microsoft sql server analysis services in sql server 2005; and i wanted to make the parameters Unidade, Local, Piso and Sala optionally in the following query (is that possible?):

SELECT NON EMPTY { [Measures].[Número de Pontos], [Measures].[Fee Proxy], [Measures].[Fee Ponto de Rede], [Measures].[Fee Print PB] } ON COLUMNS, NON EMPTY { ([Tempo Real].[Mes].[Mes].ALLMEMBERS * [Organização Factura].[Organização Factura].[Unidade].ALLMEMBERS * [Localização].[Local].[Local].ALLMEMBERS * [Localização].[Piso].[Piso].ALLMEMBERS * [Localização].[Sala].[Sala].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@[OrganizaçãoFacturaEmpresa], CONSTRAINED) )

ON COLUMNS FROM ( SELECT ( STRTOSET(@TempoRealMes, CONSTRAINED) ) ON COLUMNS FROM [Pontos Acesso]))

WHERE ( IIF( STRTOSET(@[OrganizaçãoFacturaEmpresa], CONSTRAINED).Count = 1, STRTOSET(@[OrganizaçãoFacturaEmpresa], CONSTRAINED), [Organização Factura].[Empresa].currentmember ) )

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

thanks in advance.

best regards,

ricardo cruz

# January 21, 2008 5:14 PM

Ricardo Cruz said:

Finally I did it!

thanks anyway.

# January 22, 2008 12:01 PM

Richthofen said:

Next time, shoot me an email. I don't read the comments on older posts as often. FYI, I did not know how to make it optional on an OLAP cube. I rarely write direct queries to OLAP DBs. I instead use the PIVOT TABLE control to generate the code. OLAP DBs are very complex and it saves me the sanity :)

Feel free to share your solution to optional Params on an OLAP cube and I will gladly post it.

# January 23, 2008 7:44 PM

singhswat said:

i got through this problem from other report parameters but for date i am not able to get a solution. I am not using a Datetime picker but instead using a date range so when i select all in start date and try to view it gives an error- "

Query execution failed for data set 'ds_xyz'.

Range operator ( : ) operands have different levels; they must be the same.

"

Is there any work around for this... need a solution for this.:-((((

But if i select start date-"all" and end date -"All" it generates report. No errors.

But want i want is select-"ALL" and view for all dates but not select range

# May 2, 2008 11:27 AM

Rodion Melnichenko said:

Hi.

I have parameter - Product.

It is based on DataSet (SELECT ProductID, Title FROM tblProduct).

This parameter is displayed like a list of products on the report.

What is needed for me:

  - if product is selected - use its ID in WHERE statement

  - if product is NOT selected - show all products

In my WHERE statement i used (ProductID = @ProductID OR @ProductID IS NULL)

But in preview mode of Reporting Services i cant preview data without selecting product in dropdown.

Error appears: "Please select a value of the parameter 'Product'"

Thank in advance, Rodion

# May 20, 2008 11:18 AM

Richthofen said:

Hey Rodion,

I noticed you said you have the Data Set (SELECT ProductID, Title FROM tblProduct). But please note that you need a value in this dataset to represent 'All'. So in the article I wrote I had a UNION statement in my dataset to get NULL value.

Please use the contact form on the website if you need more info! I don't read the comments daily :)

- Ed

# May 21, 2008 7:26 PM

MRM said:

HI,

I have a column which gives the status of the employee. This column contains null values too.

now the parameter passes null also as one of the status. how to fetch data from the table using this parameter?

# July 1, 2008 3:24 PM

Diana Rhoades said:

I need to put a parameter in a report in two ways: one is Start Account and End Account (2 parameters). The other is an Account List where they put in a string delimited list of non-contiguous account numbers. Do you know of an example on the web where something similar has been done or have any other thoughts about this?

My primary dataset is built by a somewhat complex SQL code that ends with a table with no name. So I don't know how to query on that and get DISTINCT(account) and I don't know if this method above gives them the two options they need. Obviously my SQL would change but I need to set up the new Account List parameter before I know how to change my SQL.

Thanks.

diana.rhoades@agvantis.com

# July 10, 2008 9:43 PM
Leave a Comment

(required) 

(required) 

(optional)

(required)