[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

Missing values when running report, Multiselect

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
Christopher Gore
Asked:
Christopher Gore
  • 9
  • 8
1 Solution
 
rickchildCommented:
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
 
rickchildCommented:
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
 
Christopher GoreSolutions ArchitectAuthor Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
rickchildCommented:
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
 
Christopher GoreSolutions ArchitectAuthor Commented:

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
 
rickchildCommented:
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
 
Christopher GoreSolutions ArchitectAuthor Commented:
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
 
Christopher GoreSolutions ArchitectAuthor Commented:
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
 
rickchildCommented:
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
 
Christopher GoreSolutions ArchitectAuthor Commented:
Do you have an example of that?
0
 
rickchildCommented:
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
 
Christopher GoreSolutions ArchitectAuthor Commented:
Does anyone have any idea where I can find Microsoft documentation about the 255 character limitation?
0
 
Christopher GoreSolutions ArchitectAuthor Commented:
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
 
Christopher GoreSolutions ArchitectAuthor Commented:
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
 
rickchildCommented:
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
 
Christopher GoreSolutions ArchitectAuthor Commented:
Found the solution.  Had a piece of code blocking duplicates in my report.  Whoops.
0
 
rickchildCommented:
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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now