Ssrs 2008 stored procedure is not working

SSAS and SSRS
1

In an existing SSRS 2008 report, I added the parameter of milestonecode which is a 'text' and there can be multiple values selected. The t-sql is
the following:
SELECT DISTINCT MILESTONE_CODE as MILESTONE_CODE_VALUE, CONVERT(VARCHAR, CONVERT(INT, MILESTONE_CODE)) + ' Days' AS MILESTONE_LABEL FROM AtnLtrMilestone alm INNER JOIN AtnLtrASTU als ON alm.STULINK = als.STULINK
WHERE alm.SCHOOLNUM = @pSchoolNumber
AND alm.SCHOOLYEAR = @pSchoolYear
ORDER BY MILESTONE_CODE_VALUE;

When I change a stored procedure to using sql within the ssrs report, individual or multiple values can be selected correctly. The sql is the following:

SELECT m.SCHOOLNUM
,sch.NAME
,m.SCHOOLYEAR
,m.STULINK
,s.permnum
,s.LastName
,s.MiddleName
,s.FirstName
,m.semester
,m.MILESTONE_CODE
,m.MILESTONE_DATE
,m.PRT_DECLINED_DT
,CONVERT(VARCHAR(10), m.PRT_DECLINED_DT, 112) AS ConvertedDate
,m.PRINTED
,m.PRT_USERID
FROM AtnLtrMilestone m INNER JOIN AtnLtrASTU s ON m.STULINK = s.STULINK
INNER JOIN AtnLtrASCH sch ON m.schoolnum = sch.schoolnum
WHERE m.PRINTED IS NOT NULL
AND (m.SCHOOLNUM = @pSchoolNumber OR @pSchoolNumber IS NULL)
AND m.SCHOOLYEAR = @pSchoolYear
AND cast(m.MILESTONE_CODE as int) in (@pMilestone)
ORDER BY CONVERT(VARCHAR(10), m.PRT_DECLINED_DT, 112) DESC, s.lastname
, s. firstname , s.middlename

However when I use the modified stored procedure only one first milestone value is selected. The modified stored procedure looks like
the following:

ALTER PROCEDURE [dbo].[procAtnLtrAttendanceDetailSchool]
@pSchoolNumber varchar(3) = NULL
,@pSchoolYear int
,@pMilestone char(3)
AS

BEGIN

SELECT m.SCHOOLNUM
	  ,sch.NAME
	  ,m.SCHOOLYEAR
	  ,m.STULINK
	  ,s.permnum
	  ,s.LastName
	  ,s.MiddleName
	  ,s.FirstName
	  ,m.semester
	  ,m.MILESTONE_CODE
	  ,m.MILESTONE_DATE
	  ,m.PRT_DECLINED_DT
	  ,CONVERT(VARCHAR(10), m.PRT_DECLINED_DT, 112) AS ConvertedDate
	  ,m.PRINTED
	  ,m.PRT_USERID
FROM AtnLtrMilestone m INNER JOIN AtnLtrASTU s ON m.STULINK = s.STULINK
INNER JOIN AtnLtrASCH sch ON m.schoolnum = sch.schoolnum
WHERE m.PRINTED IS NOT NULL
AND (m.SCHOOLNUM = @pSchoolNumber OR @pSchoolNumber IS NULL)
AND m.SCHOOLYEAR = @pSchoolYear
AND m.MILESTONE_CODE in (@pMilestone)  
	ORDER BY CONVERT(VARCHAR(10), m.PRT_DECLINED_DT, 112) DESC, s.lastname , s. firstname , s.middlename

END
GO

Thus would you tell me what I can do to get the modified stored procedure to work instead of using the t-sql in the ssrs 2008 report?

2

Since the proc receives @pMilestone as text, you will need to use Dynamic SQL, like:

declare @pSchoolNumber int = 42, @PSchoolYear int = 2016, @PMilestone nvarchar(max) = '''A'',''b''';

declare @sql nvarchar(4000);
set @sql = N'
SELECT m.SCHOOLNUM
	  ,sch.NAME
	  ,m.SCHOOLYEAR
	  ,m.STULINK
	  ,s.permnum
	  ,s.LastName
	  ,s.MiddleName
	  ,s.FirstName
	  ,m.semester
	  ,m.MILESTONE_CODE
	  ,m.MILESTONE_DATE
	  ,m.PRT_DECLINED_DT
	  ,CONVERT(VARCHAR(10), m.PRT_DECLINED_DT, 112) AS ConvertedDate
	  ,m.PRINTED
	  ,m.PRT_USERID
FROM AtnLtrMilestone m INNER JOIN AtnLtrASTU s ON m.STULINK = s.STULINK
INNER JOIN AtnLtrASCH sch ON m.schoolnum = sch.schoolnum
WHERE m.PRINTED IS NOT NULL
AND (m.SCHOOLNUM = '''
+ CAST(@pSchoolNumber AS nvarchar(20)) 
+ N''' OR  '''
+  CAST(@pSchoolNumber AS nvarchar(20)) 
+ N''' IS NULL)
AND m.SCHOOLYEAR = '''
+  CAST(@pSchoolYear AS nvarchar(20)) 
+ N'''
AND m.MILESTONE_CODE in ('
+ @pMilestone 
+ N') '
+ N'ORDER BY CONVERT(VARCHAR(10), m.PRT_DECLINED_DT, 112) DESC, s.lastname , s. firstname , s.middlename'
exec sp_executesql @sql
1 Like
3

needed to increase the size of the varchar(35)

4

Heh... and now you also need to watch do something to ensure that SQL Injection doesn't occur. :wink:

5

I would not recommend using this option - instead you need to use a string split utility. How you use it will depend on your requirements but the simplest would be:

AND m.MILESTONE_CODE IN (SELECT a.Value FROM dbo.{your split function})

1 Like
6

yes, that is better by far and avoids the injection problems that Jeff refers to as well.

7

Also - don't convert the datetime to a string, do any date formatting in the report. Also remove the ORDER BY and do all sorting in the report.

This way, you can actually sort on the datetime correctly and allow for different sorts based on the users selections and requirements.

8

Why? SQL Server is pretty good at sorting (and does it correctly), I hear. FWIW I always push sorting to the server when I can.

1 Like
9

Because SSRS is going to sort the report based on how you create the tablix - so any ordering done in SQL Server is just wasting cycles and causing an unnecessary (and expensive) sort operation to be performed.

In SSRS - if you ever setup and configure the report to allow the user to sort the columns, then what good does sorting in SQL Server do except to waste cycles?

If you setup a report in SSRS that does any type of grouping - SSRS has to perform the sorting and grouping regardless of how the data is returned, so again - just wasting cycles on the server.

10

Thanks Jeff, sounds interesting, especially the user-driven-reordering. OTOH if SQL returned ordered results in the default report order, the initial results may be delivered faster but once you do something else with the results, resulting in reordering, the benefit is gone.

Note that sorting is always expensive (well, O(n log n)) so it matters little if it is done by the DBE or RS (or IS/AS for that matter). If I were a SQL dev, I'd use the same sort code for every sort op. Then I'd only ever have one to support and it would be best of breed.

11

I'm not familiar with SSRS, but I'm curious how SSRS handles Sorting and Grouping if the resulting data set is Absolutely Massive ... we pass the User's "Sorting and Grouping Request" back to SQL and re-get the data, sorted/grouped as the user now wants it, but if I could figure a way to do this in our middle-layer I would be tempted. I've always avoiding doing it there because of the need to, potentially, handle massive amounts of "stuff"

1 Like
12

I wouldn't recommend sending massive amounts of data to any downstream process where that process then has to group and sort the data, unless the requirement is to be able to drill-down or drill-through to the detail data and calling SQL Server for each detail section would be too intrusive on the system.

The type of grouping in SSRS is not the same - and not what I am referring to...in reports you group the data and may or may not have totals.

Example would be grouping by Location | Customer so you can have all items by location on a single page - and only showing that location a single time with it sorted by customer and a date.

The reason you don't sort the data in SQL is because you can have multiple reports using the same stored procedure where one is "sorted/grouped" by location | customer - another report could be "sorted/grouped" by Region | Location | Customer - or a single report where you prompt the user for different sorting/grouping and adjust that at run-time.

If your process requires sending millions of rows to the client for a report - I have a feeling your report request is not correct.

1 Like
13

Thanks :toast:

Yes, you are quite right, the data volume is not humongous. I was just looking for ideas on whether I should be handling Sort-by-user-chosen-column and filter-on-column using something between SQL box and the User's browser. Increasingly I see snazzy APPs that have a drop-down on each column heading and then provide facilities similar to Excel - list all distinct values in the column and allow multi-select of values to filter on, also A-Z sort on that column and so on. We offer similar (although more "clunky") features on our reports, but we just pass the user's choices back to SQL and re-run the report completely. I think we have too many rows to make it worthwhile trying to do it "locally". For example, we might have an aggregate reports where the user is only seeing a few hundred rows, but the detailed data, behind, may be 100,000 rows. In order to allow sorting / filtering etc. that 100,000 rows would have to be available to the Server producing the report so that it could include the relevant rows (e.g. for a user-selected Filter set) in the aggregate totals / Pivots etc.

Perhaps I should just stick to re-querying SQL each time a user changes a Filter / Column-Sort, as I am doing now?

14

Most of those features I see are done in some type of grid control - not sure if that is a standard control in VS. A combination of the two would be ideal - allow the user to filter and within the results returned they could sort as they wished.

I have also seen applications that are configurable on the number of rows returned - and also have the ability to return additional rows (e.g. get next 100 rows and add to the rows already pulled). It really depends on the application and whether or not that is usable.

As for the report - it really depends on the usage of the report. For most reports you are going to pull all data anyways - and having the users call back to SQL for every page or number of rows isn't really efficient.

1 Like
15

If my report returns, say, 1,000 rows and I display 100 rows per page, do you think it matters that my APP goes back to SQL 10x to get each page in turn? (Assuming that my query is reasonably well targeted to just select "Rows for Page 2", rather than selecting them all again and THEN working out which rows are for Page 2 :slight_smile: )

I expect that in some/many? cases not all 10 pages will be viewed, so I have saved some time (compared to pulling all 1,000 rows at the outset).

I'm still struggling to get my head around how I would build (i.e. most likely from scratch, not using a control available in VS etc.) something that allows Filter and Sort of a multi-page resultset. I will have to cache the data somewhere, and then decide when that data is stale (a "session" might last for days ...)

1 Like