We help IT Professionals succeed at work.

SSRS 2005 The isNull function requires two arguments

588 Views
Last Modified: 2014-09-15
When I run a report in SSRS I get a "The isnull function requires 2 arguments (s)".

I do enter more then one value.

This is my SQL:

 
Select DI.PS,PD.BatchNo,tissloc.Tissue_Location_Name, DTH.Modified_DateTime --PB.BatchNo, PB.DonorPS,  
From DIST_Tissue_History DTH
Join setup_tissue_location tissloc on DTH.location = tissloc.tissue_loc_id
Join PR_Deposit PD on DTH.TissueDepositNo = PD.TissueDepositNo
Join DS_Donor_Info DI on PD.DonorId = DI.DonorId
Where (DTH.Modified_DateTime >= @ModifiedDateStart or @ModifiedDateStart is Null)  
And (DTH.Modified_DateTime < @ModifiedDateEnd or @ModifiedDateEnd is Null)  
AND (ISNULL(@LocationIdList, 'ALL') = 'ALL' or CHARINDEX(',' + cast(tissloc.Tissue_Loc_Id as varchar(max)) + ',', ',' + @LocationIdList + ',') > 0)

In the SSRS Visual Studio I set the location parameter as an integer. I tried with a string same error.
I use a multi value promt to populate a dropdow to pass the location id to the sql. The avlue field is Tissue Loc Id the lable field is the tissue location name. This is the SQL I use:

Select Tissue_Loc_Id,Tissue_Location_Name
From Setup_Tissue_Location
Where Status = 1

In the  data tab when I try with the following values it returns data.

Set @ModifiedDateStart = '1-5-14'  
Set @ModifiedDateEnd  =  '1-15-14'
Set @LocationIdList  =  '970,971'
Comment
Watch Question

IT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I changed it to:

Select DI.PS,PD.BatchNo,tissloc.Tissue_Location_Name, DTH.Modified_DateTime --PB.BatchNo, PB.DonorPS,  
From DIST_Tissue_History DTH
Join setup_tissue_location tissloc on DTH.location = tissloc.tissue_loc_id
Join PR_Deposit PD on DTH.TissueDepositNo = PD.TissueDepositNo
Join DS_Donor_Info DI on PD.DonorId = DI.DonorId
Where (DTH.Modified_DateTime >= @ModifiedDateStart or @ModifiedDateStart is Null)  
And (DTH.Modified_DateTime < @ModifiedDateEnd or @ModifiedDateEnd is Null)
AND (@LocationIdList is Null or CHARINDEX(',' + cast(tissloc.Tissue_Loc_Id as varchar(max)) + ',', ',' + @LocationIdList + ',') > 0)

Able to run in sql server and in the data tab of SSRS visual studio.

Added the parameters in parameters section of the data tab. Rebuilt the report clicked on refresh ran the report in the data tab (results returned).

When I run in preview I get an error stating, In correct syntax near ','.
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
The error doesn't help because there's many ',' in the code.

It will be good if you could retrieve the values for your variables. There's a way that you can debug it to try to find those values?
CERTIFIED EXPERT

Commented:
Any chance that this is actually dynamic SQL?  That might explain the errors.

 James
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Ok, but then you need to check in which conditions the dynamic SQL fails. Must be some empty value for one variable or so.
CERTIFIED EXPERT

Commented:
Not necessarily empty.  He said that @LocationIdList was '970,971'.  Going back to his original code, and depending on how he put things together

ISNULL(@LocationIdList, 'ALL')

 could become

ISNULL(970,971, 'ALL')


 That would presumably give him the error saying that ISNULL requires 2 arguments.

 James
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
I see. It's the comma. For decimal numbers you need to use the dot (970.971).

Author

Commented:
I changed the parameter variable for locationLIst to a float in the Layout -> Parameter section got the same results. Same error message.
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Problem it's not with the datatype but how it's passed to SQL Server. You should find a way to guaranty that a dot it's passed instead of a comma. Maybe use string datatype and control it during conversion to string?

Author

Commented:
How would I do that?
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Unfortunaly I'm not expert in SSRS but should have events or something like that so you can change values before the report being processed, right?

Commented:
Take the code and put it into a stored procedure, test it, and get it to work correctly.

Then use it in SSRS by referring to the stored procedure instead of the code.

Much easier, much more reliable.

hth

Mike

Author

Commented:
Ok will try
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.