Having Problem with Report Parameters

i am trying to generate a report based on 3 parameters

age, location, ethnciity

every thing works fine in data and layout tab,

when i run the preview tab, it give me the option to input paramaters and then when i hit veiw report, it shows processing report.... (indefinite) time.

i tried executing the query in data tab, it takes less than a sec.

any ideas?

am i doing somethign wrong in parameters?

Raja,

I'm going on the assumption you are using SQL as your datasource, If so, you can start a SQL profiler on the database you're using.

In the profiler, you can watch the query statement from SQL reporting service being executed, It will reveal exactly what is being passed in your parameter. You can copy this statement and execute to a new query to help you debug your issue.

I hope that this helps.

Ham

|||

its some weard problem.

its hitting the dbase and i double checked it in the profiler.

i dont knows what happening. i deleted the data cache files in the folder and tried rerun again..

nothing works.

data tab works 1000 times fine.

|||

What happens when you take the statement from the profiler and run it in a new query window. Does that execution of the statement still takes a long time?

Ham

|||

Hi,

I once had sort of the same problem. I didn't set the datatype of the parameters correctly which caused problems when viewing the report.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Yes,

I checked it with the QAnalyzer, Profiler, ReportingServices Data Tab.

everything takes less than a sec to execute the 15000 records.

This is one thing i noticed now after some weird research.

when i hit the preview tab and input parameters its not showing anything (i mean it shows report processinng..... )

but now when i deploy it to the server and try to view it. ITS WORKING

Where the h... is the problem. ?

|||any one have an email id of any of the webcast instructors who have instructed for RS before...|||any help?

Have Stored Procedure with input parameters and want to Use Spreadsheet

I have a stored procedure that is able to ultimately do an create a temp table and insert into another table. I'm trying to figure out how I could execute the stored procedure that would get it's input parameters from a spreadsheet rather than execute it line by line. Can anyone suggest anything?

CREATE PROCEDURE osp_xmlSvc_UpdateItemAttribute

@.PartNumber varchar(50) = '',

@.Rev varchar(50) = '',

@.AttributeName varchar(256) = '',

@.AttributeValue varchar(1024) = '',

@.EditSource varchar(255) = '',

@.UserName varchar(50) = 'admin',

@.ReturnState int = 0 Output

WITH ENCRYPTION

AS

DECLARE @.ItemID int

DECLARE @.RevID int

DECLARE @.AssignAllRevs int

DECLARE @.ParamIndexID int

DECLARE @.CurrentValue varchar(1024)

DECLARE @.UserID int

SET @.ReturnState = -1

SET @.ItemID = -1

SET @.RevID = -1

SET @.ParamIndexID = -1

SET @.CurrentValue = ''

SET @.AssignAllRevs = 0

SELECT @.UserID = ID FROM UserProfile WHERE UserName = @.UserName

SELECT @.ItemID = ID FROM Entry WHERE PartNumber = @.PartNumber

if(NOT(@.ItemID = -1) AND @.ItemID IS NOT NULL) begin

SELECT @.RevID = ID FROM Rev WHERE ItemID = @.ItemID AND Rev=@.Rev

if(@.RevID = -1 OR @.RevID IS NULL) begin

SET @.AssignAllRevs = 1

SELECT @.RevID = ID FROM Rev WHERE ItemID = @.ItemID AND Expired=0

end

SELECT @.ParamIndexID = ID FROM ParamIndex WHERE [Name]=@.AttributeName

if(NOT(@.ParamIndexID = -1) AND @.ParamIndexID IS NOT NULL) begin

SET @.ReturnState = 0

CREATE TABLE tmp_xml_AV (AttVal varchar(1024))

DECLARE @.tmpSQL nvarchar(2024)

SET @.tmpSQL = 'INSERT INTO tmp_xml_AV (AttVal) SELECT [' + @.AttributeName + '] FROM ParamValue WHERE Expired=0 AND ItemID=' + CAST(@.ItemID AS VARCHAR)+ ' AND RevID=' + CAST(@.RevID AS VARCHAR)

EXECUTE sp_executesql @.tmpSQL

SELECT @.CurrentValue = IsNull(AttVal, '') FROM tmp_xml_AV

DROP TABLE tmp_xml_AV

if(NOT(@.CurrentValue = @.AttributeValue) OR @.CurrentValue IS NULL) begin

SET @.ReturnState = 1

SET @.tmpSQL = 'UPDATE ParamValue SET [' + @.AttributeName + ']=''' + @.AttributeValue + ''' WHERE ItemID=' + CAST(@.ItemID AS VARCHAR)

if(@.AssignAllRevs = 0) begin

SET @.tmpSQL = @.tmpSQL + ' AND RevID=' + CAST(@.RevID AS VARCHAR)

end

EXECUTE sp_executesql @.tmpSQL

-- Record history

DECLARE @.tmpInt int

SELECT @.tmpInt = Max(ID)+1 FROM EntryChangeAction

INSERT INTO EntryChangeAction (ID,EntryAffected,RevID,ActionType,Details,Tool,UserID)

VALUES (@.tmpInt,@.ItemID,@.RevID,6,@.AttributeName + ': ' + @.CurrentValue + ' to: ' + @.AttributeValue,@.EditSource,@.UserID)

end

end

end

GRANT EXECUTE ON [dbo].[osp_xmlSvc_UpdateItemAttribute] TO [public]

GO

Thanks.

Amy

In the stored procedure, you could open and read the xls file and put the values into variables. (An xml file would be simpler -Excel could save the file as xml.)

Check in Books Online about [OpenXML].

has no parameters and arguments were supplied

I'm kind of new at doing something like this. I'm trying to pull data into a results that exists in one of two databases. It works fine with query analyzer
but gives me the error "has no parameters and arguments were supplied" when I try to convert it to a stored procedure.

The procedure is as follows, any help would be appreciated.

CREATE PROCEDURE sp_getInvoiceNoTest AS

declare @.InVoiceNo as VarChar(30)

delete From Invoice_NBR

Insert into Invoice_NBR (Tran_NBr,ADDR_Name,ADDR_Line2,ADDR_Line3,CITY_NAM E,State_Name,ADDR_NAME2,ADDR_LINE4,ADDR_LINE5,CITY _NAME2,State_Name2)

select a.TranNo,b.AddrLine1,b.AddrLine2,b.AddrLine3,b.cit y,b.StateID,c.AddrName,c.AddrLine2,c.AddrLine3,c.C ity,c.StateID
from Colucw17.Acuity_App.dbo.tarInvoice as a
inner join
Colucw17.Acuity_App.dbo.tciAddress as b
on a.BilltoAddrKey=b.AddrKey
inner join
Colucw17.Acuity_App.dbo.tciAddress as c
on a.BilltoAddrKey=c.AddrKey and a.BilltoAddrKey=a.BilltoCustAddrKey
inner join
Colucw17.Acuity_App.dbo.tarCustomer as d
on a.CustKey=d.CustKey
inner join
Colucw17.Acuity_App.dbo.tciContact as f
on a.confirmtoCntctKey=f.CntctKey
where a.CreateuserID<>'admin' and a.TranNo='@.InvoiceNo'

--Insert into Invoice_NBr (Tran_NBr,ADDR_Name,ADDR_Line2,ADDR_Line3,CITY_NAM E,State_Name,ADDR_NAME2,ADDR_LINE4,ADDR_LINE5,CITY _NAME2,State_Name2)

select a.TranNo,b.AddrLine1,b.AddrLine2,b.AddrLine3,b.cit y,b.StateID,c.AddrName,c.AddrLine2,c.AddrLine3,c.C ity,c.StateID
from Colucw17.CSM_App.dbo.tarInvoice as a
inner join
Colucw17.CSM_App.dbo.tciAddress as b
on a.BilltoAddrKey=b.AddrKey
inner join
Colucw17.CSM_App.dbo.tciAddress as c
on a.BilltoAddrKey=c.AddrKey and a.BilltoAddrKey=a.BilltoCustAddrKey
inner join
Colucw17.CSM_App.dbo.tarCustomer as d
on a.CustKey=d.CustKey
inner join
Colucw17.CSM_App.dbo.tciContact as f
on a.confirmtoCntctKey=f.CntctKey
where a.CreateuserID<>'admin' and a.TranNo='@.InvoiceNo'
GOCREATE PROCEDURE sp_getInvoiceNoTest AS
(
@.InVoiceNo as VarChar(30)
)
as
set nocount on

delete From Invoice_NBR

Insert into Invoice_NBR (Tran_NBr,ADDR_Name,ADDR_Line2,ADDR_Line3,CITY_NAM E,State_Name,ADDR_NAME2,ADDR_LINE4,ADDR_LINE5,CITY _NAME2,State_Name2)

select a.TranNo,b.AddrLine1,b.AddrLine2,b.AddrLine3,b.cit y,b.StateID,c.AddrName,c.AddrLine2,c.AddrLine3,c.C ity,c.StateID
from Colucw17.Acuity_App.dbo.tarInvoice as a
inner join
Colucw17.Acuity_App.dbo.tciAddress as b
on a.BilltoAddrKey=b.AddrKey
inner join
Colucw17.Acuity_App.dbo.tciAddress as c
on a.BilltoAddrKey=c.AddrKey and a.BilltoAddrKey=a.BilltoCustAddrKey
inner join
Colucw17.Acuity_App.dbo.tarCustomer as d
on a.CustKey=d.CustKey
inner join
Colucw17.Acuity_App.dbo.tciContact as f
on a.confirmtoCntctKey=f.CntctKey
where a.CreateuserID<>'admin' and a.TranNo=@.InvoiceNo

--Insert into Invoice_NBr (Tran_NBr,ADDR_Name,ADDR_Line2,ADDR_Line3,CITY_NAM E,State_Name,ADDR_NAME2,ADDR_LINE4,ADDR_LINE5,CITY _NAME2,State_Name2)

select a.TranNo,b.AddrLine1,b.AddrLine2,b.AddrLine3,b.cit y,b.StateID,c.AddrName,c.AddrLine2,c.AddrLine3,c.C ity,c.StateID
from Colucw17.CSM_App.dbo.tarInvoice as a
inner join
Colucw17.CSM_App.dbo.tciAddress as b
on a.BilltoAddrKey=b.AddrKey
inner join
Colucw17.CSM_App.dbo.tciAddress as c
on a.BilltoAddrKey=c.AddrKey and a.BilltoAddrKey=a.BilltoCustAddrKey
inner join
Colucw17.CSM_App.dbo.tarCustomer as d
on a.CustKey=d.CustKey
inner join
Colucw17.CSM_App.dbo.tciContact as f
on a.confirmtoCntctKey=f.CntctKey
where a.CreateuserID<>'admin' and a.TranNo=@.InvoiceNo
GO|||Using the code you supplied it generated the following error message when I checked the syntax:

. .
Error 170: Line 3: Incorrect syntax near @.InvoiceNo.
Line 11: Incorrect syntax near
Must declare the variable :@.Invo!ceNo:
Must declare the variable @.InvoiceNo.

I added the declare statement but iot still errored.|||drop the AS between @.InvoiceNo As VARCHAR|||Thanks Peso, that was it. You help is greatly appreciated. Hopefully, I'll get the hang of this stuff.

Hard Coded parameters

Is it possible to force parameters into the reports so enabling me to force a user id value into every report that is picked up from the list. The user ID is a system value and I don't want end users having any knowledge of it?

Cheers

Darren

Hello Darren,

What you can do is create a hidden/internal parameter so that the end user can not see it. From BOL:

Hidden

Select this option if the parameter value should not appear on the report. Although hidden parameters do not appear on a report, they can be set in other ways (for example, in subscriptions and through URLs).

Internal

Select this option if the parameter cannot be changed at run time. On a published report, no visual evidence is provided to indicate that this parameter exists.

Hope this helps.

Jarret

|||

Hi Jarret,

I think this may be the answer, I need to provide a list of published reports , how would we then get the hidden parameter into it.

Can we display a URL with the user ID already in it which will then render the report viewer with the other optional parameters to be picked. How do we stop somebody constructing a URL with a different user ID.

A hidden parameter sounds like the right option but I'm confused how to populate it.

Cheers, Darren

|||

If you're wanting to keep it out of the URL as well, you'll need to go with an Internal parameter, since it can not be changed at run time. Hidden parameters can be changed through the URL. You can change the value of this from within your Report Manager. Go to the properties of your report, then go into the Parameters section. In there, you can change the default value to whatever you like.

Hope this helps.

Jarret

|||Is the Internal parameter fixed in the report then? I want to be able to change the value based on the user running the report but do not want them to be able to change to a user ID not associated to them.|||

I'm not sure how you were planning to change the user ID of the user running the report, but you can set an expression for the default value of any parameter through VS.

If you put =User!UserID as the Default value for your parameter, it will show the domain account used.

Just remember, if you use a hidden parameter, someone can manipulate the value via the URL. This is not possible with an internal parameter.

Jarret

|||

Thanks Jarret,

I think I know what we will do know. The web site maintains a guid session token for each user which originates from the database initially so if I use this as my hidden parameter and let the reports stored procedure calculate the user ID based on this I will be safe.

Cheers

|||

That sounds like it would work, but someone can pass in that parameter via the URL since you're using a hidden parameter. The internal parameter should work in this situation too, and you don't have to worry about it being changed by a user.

Anyway, I'm glad you have a solution. Can you mark this one as answered so others can see the solution?

Jarret

handling parameters that could be missing

I have a search app that I'm developing in ASP.NET
It has 5 parameters
MRN, Name, Sex, DOB, SSN
All or any combination can be used.
what is the best way to handle the non-supplied parameters?
i.e. if the user selects only supplies 'Name' and 'MRN'
I want the SQL for the stored procedure to read as if it were just 2
parameters:
(but have one stored procedure that has parameter holders for all 5)
Select MRN, Name, Sex, DOB, SSN
From Membership
WHERE MRN = 123456 AND Name like 'Johns%'
The way I have been handling this is to rewrite a different SQL statement
for every possible situation, and pass that string to the adapter. But I ca
n
see that I'm not taking advantage of the processing that SQL server could do
in a stored procedure, and for 1.4 Million records, my app is just timing ou
t
all the time.
This must be a common issue. Could someone show me the ideal way to handle
this?Have a look at
http://www.sommarskog.se/dyn-search.html
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"jonefer" <jonefer@.discussions.microsoft.com> wrote in message
news:0FFF3A3A-AB66-4A96-B63E-73E937F7A164@.microsoft.com...
>I have a search app that I'm developing in ASP.NET
> It has 5 parameters
> MRN, Name, Sex, DOB, SSN
> All or any combination can be used.
> what is the best way to handle the non-supplied parameters?
> i.e. if the user selects only supplies 'Name' and 'MRN'
> I want the SQL for the stored procedure to read as if it were just 2
> parameters:
> (but have one stored procedure that has parameter holders for all 5)
> Select MRN, Name, Sex, DOB, SSN
> From Membership
> WHERE MRN = 123456 AND Name like 'Johns%'
> The way I have been handling this is to rewrite a different SQL statement
> for every possible situation, and pass that string to the adapter. But I
> can
> see that I'm not taking advantage of the processing that SQL server could
> do
> in a stored procedure, and for 1.4 Million records, my app is just timing
> out
> all the time.
> This must be a common issue. Could someone show me the ideal way to
> handle
> this?
>|||"jonefer" wrote:
> I have a search app that I'm developing in ASP.NET
> It has 5 parameters
> MRN, Name, Sex, DOB, SSN
> All or any combination can be used.
> what is the best way to handle the non-supplied parameters?
> i.e. if the user selects only supplies 'Name' and 'MRN'
> I want the SQL for the stored procedure to read as if it were just 2
> parameters:
> (but have one stored procedure that has parameter holders for all 5)
> Select MRN, Name, Sex, DOB, SSN
> From Membership
> WHERE MRN = 123456 AND Name like 'Johns%'
> The way I have been handling this is to rewrite a different SQL statement
> for every possible situation, and pass that string to the adapter. But I
can
> see that I'm not taking advantage of the processing that SQL server could
do
> in a stored procedure, and for 1.4 Million records, my app is just timing
out
> all the time.
> This must be a common issue. Could someone show me the ideal way to handl
e
> this?
Have you tried something along the line of:
create procedure p_MyProc
@.pMyParm1 int = NULL, @.pMyParm2 datetime = NULL,
@.pMyParm3 varchar(128) = NULL, @.pMyParm4 bit = NULL
as
if @.pMyParm1 IS NULL do something ...
if @.pMyParm2 IS NULL do something ...
if @.pMyParm3 IS NULL do something ...
if @.pMyParm4 IS NULL do something ...|||Thank you. That was excellent.
"Roji. P. Thomas" wrote:

> Have a look at
> http://www.sommarskog.se/dyn-search.html
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "jonefer" <jonefer@.discussions.microsoft.com> wrote in message
> news:0FFF3A3A-AB66-4A96-B63E-73E937F7A164@.microsoft.com...
>
>|||I implemented my search with the 5 parameters using your Dynamic SQL example
.
But because of the way I want to present the final results... it always
times out.
So I will list what I'm trying to get in my final results as well as what
the Stored procedure looks like (perhaps you can help me tweak it)
What I want in the final selection:
SELECT DISTINCT MRN, MemNAME AS Member, DOB, SEX, SSN, GROUP, SGR,
[FROM-DT], [THRU-DT]
FROM qMembershipSelect AS Y
GROUP BY MRN, MemNAME, DOB, SEX, SSN, GROUP, SGR, [FROM-DT], [THRU-DT]
HAVING [THRU-DT] Is Null Or [THRU-DT] In (Select Max(X.[Thru-Dt]) From
qMembershipSelect As X Where X.MRN = Y.MRN And X.MRN Not in(Select Z.MRN
From qMembershipSelect As Z Where Z.[THRU-DT] Is Null))
ORDER BY MemNAME, [FROM-DT] DESC;
How I implemented this in the Stored Procedure:
CREATE PROCEDURE qMemberSelect
@.MRN int = NULL,
@.MemNAME nvarchar(40) = NULL,
@.DOB datetime = NULL,
@.SSN nvarchar(9) = NULL,
@.SEX nvarchar(1) = NULL,
@.debug bit = 0 AS
DECLARE @.sql nvarchar(4000),
@.paramlist nvarchar(4000)
SELECT @.sql =
'SELECT
[MRN],[MemName],[DOB],[SEX],[SSN],[GROUP
],[SGR],[FROM-DT],[THRU-DT] FROM
MEMBERSHIP AS Y
WHERE 1=1'
if @.MRN IS NOT NULL
SELECT @.sql = @.sql + ' AND MRN = @.xMRN'
if @.MemNAME IS NOT NULL
SELECT @.sql = @.sql + ' AND MemName like @.xMemNAME + ''%'''
if @.DOB IS NOT NULL
SELECT @.sql = @.sql + ' AND DOB = @.xDOB'
if @.SSN IS NOT NULL
SELECT @.sql = @.sql + ' AND SSN = @.xSSN'
if @.SEX IS NOT NULL
SELECT @.sql = @.sql + ' AND SEX = @.xSEX'
if @.debug = 1
PRINT @.sql
SELECT @.sql = @.sql + ' GROUP BY MRN, MemNAME, DOB, SEX, SSN, [GROUP], SGR,
[FROM-DT], [THRU-DT]
HAVING len([THRU-DT])= 0
Or [THRU-DT] In (Select Max(X.[Thru-Dt]) From
Membership As X Where X.MRN = Y.MRN And X.MRN
Not in(Select Z.MRN From Membership As Z Where len(Z.[THRU-DT])=0))
ORDER BY MemNAME, [FROM-DT] DESC'
SELECT @.paramlist = '@.xMRN int,
@.xMemName nvarchar(40),
@.xDOB datetime,
@.xSSN nvarchar(9),
@.xSEX nvarchar(1)'
EXEC sp_executesql @.sql, @.paramlist,
@.MRN, @.MemNAME, @.DOB, @.SSN, @.SEX
But there must be a way to do this in two phases because trying it all in
one pull is just not efficient.
"Roji. P. Thomas" wrote:

> Have a look at
> http://www.sommarskog.se/dyn-search.html
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "jonefer" <jonefer@.discussions.microsoft.com> wrote in message
> news:0FFF3A3A-AB66-4A96-B63E-73E937F7A164@.microsoft.com...
>
>|||How about...
SELECT MRN, Name, Sex, DOB, SSN
FROM dbo.Membership
WHERE (MRN = @.MRN or @.MRN is null)
AND (Name like @.Name or @.Name is null)
AND (Sex = @.Sex or @.Sex is null)
etc.
TIP: If there are not user specific tables, always specify the owner of the
object so that it is not ambiguous and SQL Server does not have to check for
<userid>.Membership and if not found, check for dbo.Membership. This applie
s
for referencing tables, views, and stored procedures. Also, never prefix a
stored procedure name with sp_ or xp_ as that can cause unexpected side
effects.
Just my two cents,
Joe
"jonefer" wrote:

> I have a search app that I'm developing in ASP.NET
> It has 5 parameters
> MRN, Name, Sex, DOB, SSN
> All or any combination can be used.
> what is the best way to handle the non-supplied parameters?
> i.e. if the user selects only supplies 'Name' and 'MRN'
> I want the SQL for the stored procedure to read as if it were just 2
> parameters:
> (but have one stored procedure that has parameter holders for all 5)
> Select MRN, Name, Sex, DOB, SSN
> From Membership
> WHERE MRN = 123456 AND Name like 'Johns%'
> The way I have been handling this is to rewrite a different SQL statement
> for every possible situation, and pass that string to the adapter. But I
can
> see that I'm not taking advantage of the processing that SQL server could
do
> in a stored procedure, and for 1.4 Million records, my app is just timing
out
> all the time.
> This must be a common issue. Could someone show me the ideal way to handl
e
> this?
>|||Joe
If you use sp_XXXXXX SQL server looks into MAster database first even if you
mentioned the database.sp_XXX... So, we are looking unnecessarily in master
database . that is why most folks suggest do not use sp_XXXX .I never heard
of side effects... If you know any please share with us.
--
SQL SERVER DBA
"Joe from WI" wrote:
> How about...
> SELECT MRN, Name, Sex, DOB, SSN
> FROM dbo.Membership
> WHERE (MRN = @.MRN or @.MRN is null)
> AND (Name like @.Name or @.Name is null)
> AND (Sex = @.Sex or @.Sex is null)
> etc.
> TIP: If there are not user specific tables, always specify the owner of th
e
> object so that it is not ambiguous and SQL Server does not have to check f
or
> <userid>.Membership and if not found, check for dbo.Membership. This appl
ies
> for referencing tables, views, and stored procedures. Also, never prefix
a
> stored procedure name with sp_ or xp_ as that can cause unexpected side
> effects.
> Just my two cents,
> Joe
> "jonefer" wrote:
>

Handling optional parameters

On many reports I have optional parameters, for example if the employee is filled in it will run for that employee but if it's null it runs for all employees. Which is the best way to handle this?

The two options I am currently looking at are:

select * from employee where (employee.id = @.EmpID or @.EmpID is Null)

select * from employee where isnull(@.empID, employee.id) = employee.id

Anyone else have a different solution?

Currently we use the OR approach and I wanted to see if anyone had any thoughts before switching to using IsNull, which seems a bit better.

OR can be slow. I always use the ISNULL approach.

|||

Another way to do this is, you can include "*(ALL) " in you employee dataset and you can default the parameter to "*(ALL)". Now pass back the selected value to the Stored Procedure

IF (@.EMPID= '*(ALL)'

select * from Employee

Else

Select * form Employee where employee.id=@.EMPID

This way it is more intuitive to End Users.

|||

The problem with using the if (@.EmpID is null) is that then you need to maintain two queries. And for some reports that have 3 or more optional parameters this could mean having 8 or more queries to maintain just for optional parameters. We also thought about using an expression for the query but some of the clients like to use the generic query view if they edit the report so this makes that option unavailable.

I think we will be using the isnull option for our solution.

Thanks

|||

I thought this blog post was useful for this topic. You can combine approaches if you want.

http://bloggingabout.net/blogs/egiardina/archive/2007/06/26/sql-server-reporting-services-optional-parameters.aspx

|||

Techquest wrote:

Another way to do this is, you can include "*(ALL) " in you employee dataset and you can default the parameter to "*(ALL)". Now pass back the selected value to the Stored Procedure

IF (@.EMPID= '*(ALL)'

select * from Employee

Else

Select * form Employee where employee.id=@.EMPID

This way it is more intuitive to End Users.

Not everyone uses stored procs to serve reports.

|||

Found an issue using the IsNull approach. If the optional parameter can be null in the table it will not return the null values.

EX. Middle name is a nullable field

select * from Employee where MiddleName = IsNull(@.MiddleName, MiddleName)

This won't return all values when the parameter is null, will return all values that have any non null MiddleName.

Think it may be safer to use the OR approach now because other people that use modify reports not familiar with this may use IsNull is cases it should not be used.

Anyone have any suggestions around this? I know most of the time the optional parameter does not go against a nullable field but could cause a lot of problems if you did it by mistake.

Thanks,

Kevin

|||

Yeah, avoid nullable fields

In case you do have them though you could use:

select * from Employee where COALESCE(MiddleName, '') = COALESCE(@.MiddleName, MiddleName, '')

COALESCE is basibcally like ISNULL but it takes any number of parameters rather than just 2 and returns the first non-null value.

|||

COALESCE functions differently than IsNull though. If you look at the execution plan, these both are the same:

select * from employee where employee.id = COALESCE(@.EmpID, employee.id)

select * from employee where (employee.id = @.EmpID or @.EmpID is Null)

This defeats the purpose of using IsNull for the better exectution with some indexes.

Handheld Devices

Just wondering has anyone every tried viewing reports on handheld devices. I
can to the report server but if the report has parameters they don't show up.
Any thoughts?I too am wanting to do this, here is what I have found...
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/c49464e1742ffad8/50f2ae5563f23dea

halt execution and wait for parameters

Hello,
does anybody know of a way to to have RS wait to execute (on access) until
the parameter fields have been filled?
thanks,
gregI've noticed this was a problem when I had default values for every
parameter. Try to remove default values.
"greg" <greg@.discussions.microsoft.com> wrote in message
news:DDF49D51-17BC-4D9B-ACD5-B240C559AC08@.microsoft.com...
> Hello,
> does anybody know of a way to to have RS wait to execute (on access) until
> the parameter fields have been filled?
> thanks,
> greg|||Thanks for the response. That is the same problem I'm having. I have to leave
defaults (8 fields) but the users change certian fields. thanks again good
to see others are having the same issues.
Greg
"Jason" wrote:
> I've noticed this was a problem when I had default values for every
> parameter. Try to remove default values.
> "greg" <greg@.discussions.microsoft.com> wrote in message
> news:DDF49D51-17BC-4D9B-ACD5-B240C559AC08@.microsoft.com...
> > Hello,
> > does anybody know of a way to to have RS wait to execute (on access) until
> > the parameter fields have been filled?
> >
> > thanks,
> > greg
>
>