Solved

Missing values when running report, Multiselect

Posted on 2008-06-18
17
258 Views
Last Modified: 2013-11-26
I have a report built in Sql Server Reporting Services.  It uses stored procedures.  I have designed a UDF to pass the multivalue select to my report.  All this works fine.  When I select multiple things and run the report, it leaves some of them out in the report. If I select one at a time, and run the report, I get results for each one.  Has anyone seen this happen before.
0
Comment
Question by:Christopher Gore
  • 9
  • 8
17 Comments
 
LVL 13

Expert Comment

by:rickchild
ID: 21814424
Does the stored procedure use = or IN to determine?
= Will not work for multiple items, this will need to be changed to use IN ()

There is a maximum length in SSRS Parameter passing, if when you select long, or lots of multiple values and it goes over this it can truncate.
0
 
LVL 13

Expert Comment

by:rickchild
ID: 21814557
I think the string length of a parameter is 255 (Silly I know, hopefully sorted out in SSRS 2008).

Another thing to look out for is the ampersand character within the string as this can cause truncation.
0
 
LVL 1

Author Comment

by:Christopher Gore
ID: 21815137
My multiselect is locations.  I can select multiple locations and the report will run.  There are a few locations that if I add them to the multiselect, it will not add them to report results.  It basically ignores them.  If I deselect all the others and select only the one location that is not working as a multiselect, the results will show for that one location. I am also noticing that when I multiselect some locations, others will stop showing in the results as well.  Make since?
0
 
LVL 13

Expert Comment

by:rickchild
ID: 21817322
Can you post there WHERE used by the stored procedure?

Also useful if you can post the code used to populate your Location filter
0
 
LVL 1

Author Comment

by:Christopher Gore
ID: 21818011

This is my main sproc where clause:
 

WHERE    (apt.Canceled = 1) AND  (apt.ApptKind = 1) AND (apt.OwnerId > 3) AND (ml.Description <> 'Operator Error') AND(apt.ApptStart BETWEEN @FromDate AND @ToDate)

	and ((@CancelReasons is not null and apptstatusmid in (select cast(value as integer) from dbo.CUS_DV_SPLIT(@CancelReasons, ','))) or (@CancelReasons is null))

	and ((@Resources is not null and cus.ModalityId in (select cast(value as integer) from dbo.CUS_DV_SPLIT(@Resources, ','))) or (@Resources is null))

	

	and (cus.Modality like '%CT%')
 

This is my sproc that pulls the locations:
 

ALTER PROCEDURE Centers

	@CenterID varchar(250)

	/*

	(

	@parameter1 int = 5,

	@parameter2 datatype OUTPUT

	)

	*/

AS

	/* SET NOCOUNT ON */ 

	SELECT DISTINCT Modality, ModalityID, CenterID, CenterName

FROM            dbo.CUS_DV_CenterModalities

HERE Centerid IN (select cast(value as integer) from dbo.SPLIT(@CenterID, ','))
 
 
 

This is my UDF that creates the string for the multiselect:
 
 
 

ALTER FUNCTION dbo.SPLIT

(@text varchar(8000), @delimiter varchar(20) = ' ')
 

RETURNS @Strings TABLE

(    

  position int IDENTITY PRIMARY KEY,

  value varchar(8000)   

)

AS

BEGIN

DECLARE @index int 
 

SET @index = -1 

WHILE (LEN(@text) > 0) 

  BEGIN  

    SET @index = CHARINDEX(@delimiter , @text)  

    IF (@index = 0) AND (LEN(@text) > 0)  

      BEGIN   

        INSERT INTO @Strings VALUES (@text)

          BREAK  

      END  

    IF (@index > 1)  

      BEGIN   

        INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))   

        SET @text = RIGHT(@text, (LEN(@text) - @index))  

      END  

    ELSE 

      SET @text = RIGHT(@text, (LEN(@text) - @index)) 

    END

  RETURN

END

 

Open in new window

0
 
LVL 13

Expert Comment

by:rickchild
ID: 21820223
Ok, your UDF looks good, although it is called dbo.[SPLIT] and you are using dbo.[CUS_DV_SPLIT] in the WHERE ?

Anyway the function and parameter population look good, I also broke down all your brackets on the @CancelReasons and @Resources but they are also good!

All I can think is that your @Resources string contains commas, but unlikely as they look to all be numbers in there as they can cast to integer?

In the stpred procdeure are you executing the statements directly, or using exec('') ?

How many locations are we talking here, could that @Resources string have gone longer than 255 characters?
0
 
LVL 1

Author Comment

by:Christopher Gore
ID: 21821865
I am executing the sproc directly.  It's not likely that it is over 255 characters since I can choose just 3 locations and out of those 3 only 1 will show up in the report results. The first 20 or so locations will work just fine, after that it seems random as to which ones will not work when combined with the first set.
0
 
LVL 1

Author Comment

by:Christopher Gore
ID: 21833649
I think I have determined that the issue is that my parameter is larger than 255 characters.  Each parameter being passed is about 6 characters, after about 42 of those parameters passed, the rest stop showing up in the report.  That means just over 255 is when it breaks.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 13

Expert Comment

by:rickchild
ID: 21840174
OK, sounds like the problem.  Unfortunately I haven't found an easy solution to this other than making a new "<All>" at the top, and then disabling multi select.

But this limits the user to selecting one, or all, and doesn't allow a few multi selections.
0
 
LVL 1

Author Comment

by:Christopher Gore
ID: 21841509
Do you have an example of that?
0
 
LVL 13

Expert Comment

by:rickchild
ID: 21841524
It's a little like using the NULL to say all:

SELECT '<All>' as CenterID
UNION ALL
SELECT CenterID from dbo.CUS_DV_CenterModalities
WHERE Centerid IN
(select cast(value as integer) from dbo.SPLIT(@CenterID, ','))


and then in your WHERE you would say:

and (

      (@Resources is not null and cus.ModalityId in 

         (

           select cast(value as integer) 

           from dbo.CUS_DV_SPLIT(@Resources, ',')

         )

       ) 

       or (@Resources is null) 

      or @Resource = '<All>'

      )

Open in new window

0
 
LVL 1

Author Comment

by:Christopher Gore
ID: 21843450
Does anyone have any idea where I can find Microsoft documentation about the 255 character limitation?
0
 
LVL 1

Author Comment

by:Christopher Gore
ID: 21855966
I cannot use the ALL solution.  I need to have multiselect.  I have determined that when I run my main sproc in Management Studio, it runs correctly.  My first sproc collects the locations and populates the dropdown list, it passes the id of these locations to my second sproc, which pulls additional information about these particular locations, the ids for that information populates another dropdown list.  Both of those lists are populating correctly, but when I run the report, it omits some of my locations.  Again, if I type in the values directly into the parameters when running in Management Studio, it works fine.  Any thoughts?
0
 
LVL 1

Author Comment

by:Christopher Gore
ID: 21858197
More information.  I opened up SSRS and for my parameter I needed to pass, I typed in ALL the values in by hand, separated by commas in the default value area.  When I run the report, I get the same result.  The same locations are missing. If I type in those locations in the default value area by hand, by themselves, they will run in the report.  Issue still being, I need them to show in the report with all the rest of the locations.
0
 
LVL 13

Expert Comment

by:rickchild
ID: 21864853
Check for SELECT statements in your stored procedures, SSRS will treat any selects as the result to be returned, so it may be returning a result prematurely.
0
 
LVL 1

Author Comment

by:Christopher Gore
ID: 21866932
Found the solution.  Had a piece of code blocking duplicates in my report.  Whoops.
0
 
LVL 13

Accepted Solution

by:
rickchild earned 500 total points
ID: 21874914
Ah OK, glad it's sorted.   In future if you do come accross the 255 limit, one nice way around this is to select an ID and a description for the parameter data source from the table, then use the description as the label, but just the ID as the value.  Then you will only be passing sat Integer IDs back rather than long strings.

Although in your case I think you already were using ID's.

Sympathy points for the attempted help?  ;-)
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

758 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

20 Experts available now in Live!

Get 1:1 Help Now