Solved

SSRS 2008 using temp files and stored procedures

Posted on 2014-09-11
16
791 Views
Last Modified: 2014-09-13
I'm working on something I've never done before and I don't quite know how to do it.  I have a report that I'm rewriting in SSRS.  Because it lists a combination of unlike items I thought it best to run a stored procedure ahead of time and enter the final counts and totals into a temp table.   I have 2 questions on how to do this.  

Because multiple people could run this file at the same time with different parameters, how do I insure the table the values are being written into are unique for the user running the report?  Do I have to call the table something special or do something special with it?  One person could run it with current dates and one person while someone else is running it with last year dates and a single person.

Second, how do I add the stored procedure to a SSRS report and make sure it runs before the report runs?
0
Comment
Question by:cindyfiller
  • 9
  • 6
16 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Because multiple people could run this file at the same time with different parameters, how do I insure the table the values are being written into are unique for the user running the report?
Use temporary tables so each user connection can only see his own temp table.
0
 
LVL 12

Assisted Solution

by:Koen Van Wielink
Koen Van Wielink earned 500 total points
Comment Utility
You want to store the values generated by your stored procedure in a local temp table. These are created in MSSQL by putting a # in front of the table name. For example:

Create table #temp
(a int,
b nvarchar(50)
)

Open in new window


would create a temp table #temp which is only accessible for the user running the procedure. (Global temp tables can be created using ## instead of a single #).

In order to then use this in your report you would have to add the remaining SQL scripts of your report into the same procedure, so that the part of the SQL where you select the report values can actually have access to the temp table. By definition a temp table declared inside a stored procedure only has a scope of that stored procedure. When the procedure ends, the temp table is dropped.
So run your procedure and end it with a select statement which returns your final data set. Simple example:

create procedure uSP_example

create table #temp
(a int
,b nvarchar(50)
)

insert into #temp
select 1
	,	'abc'

select *
from #temp

drop table #temp

Open in new window


When you use this procedure for your data set it would return the selected records from #temp for you to use in the report. Obviously you can make the temp table insert and the following select statement as complex as you like.
Hope this helps.
0
 

Author Comment

by:cindyfiller
Comment Utility
I have made some progress - did get the stored procedure added to a report.  It works, kind of.  I still have the question on multiple people running the report at the same time but need to add a few more questions.  

This report is a sales report and I need to select multiple people for one of the parameters.  The stored procedure is updating the table but will only work for one person at a time.  I've told the parm to allow multiple values, but when I try and run it I get a conversion failed when converting varchar value to data type int.  I even changed the data type in the table to varchar thinking that would take care of the problem, but it didn't.  (I reran/saved the stored procedure to make sure it was updating the value and I've checked the SP to make sure the parm was also defined as varchar there).
0
 
LVL 12

Accepted Solution

by:
Koen Van Wielink earned 500 total points
Comment Utility
In order to use multple selection parameters you need a "split" function of some kind that separates the individual values from the string returned by the report. This is the function we use:

CREATE FUNCTION [dbo].[fn_Split] (@sList AS VARCHAR(MAX))
RETURNS  @retTable TABLE(Val VARCHAR(MAX))  AS  

BEGIN 

 IF @sList IS NULL RETURN

 --preparing the input list, removing IN keyword and parenthesis
 SET @sList = REPLACE(@sList, 'IN(','')
 SET @sList = REPLACE(@sList, 'IN (','')
 SET @sList = REPLACE(@sList, '(','')
 SET @sList = REPLACE(@sList, ')','')
-- SET @sList = LTRIM(RTRIM(@sList))
 SET @sList = @sList


 --scrolling thru values in @sList and populating temporary table
 DECLARE @Index INT, @Delimiter CHAR(1)
 DECLARE @Result VARCHAR(MAX)


 SET @Delimiter = ','

 WHILE @sList <> ''
 BEGIN
  SET @Index = CHARINDEX(@Delimiter, @sList)
  IF @Index <> 0
  BEGIN
   SET @Result = LEFT(@sList, @Index - 1)
   SET @sList = SUBSTRING(@sList, @Index + 1, LEN(@sList))
  END
  ELSE
  BEGIN
   SET @Result = @sList
   SET @sList = ''
  END

  INSERT @retTable SELECT LTRIM(RTRIM(REPLACE(@Result,'''''','''')))
 END

 RETURN 

END

Open in new window


You use it in your "Where" clause like this:

where	yourColumn in (Select val from dbo.fn_split(@yourParameter))

Open in new window


The function is a table function which returns a single value for each selected value in the parameter.

By the way, credit for the function goes to our previous DBA, not me.
0
 

Author Comment

by:cindyfiller
Comment Utility
Thank you so much!  Your answers were excellent and complete.   I had read enough to know I had to split the multi values some how, but the items I found online and tried weren't working for me and I wasn't familiar enough with what was wrong.  I had about 20 errors on one option I tried.  

If you don't mind I'd like to leave this open until Monday so I have time to try the code.  I may have one more question as well.
0
 

Author Comment

by:cindyfiller
Comment Utility
I do have an additional question.  I've been building out this stored procedure/SSRS report using a dimid value for each sales person as that is what I'll use behind the scenes to create the data in the stored procedure.  When completed, staff won't have a clue what a dimid is so I need to use the sales person's name for the actual parameter.  So do I put the sales person's name in the parameter on the SSRS report and then when the stored procedure is run I some how tell it don't use the name, use the dim id instead... not sure how or where to do this (report side or SP side) .
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
To do this, create a second dataset (can be procedure also, easy for reuse) to list your parameters. The result set is 2 columns. The first is your ID value, the second the name which will serve as the label. In the parameter configuration of the report, set the parameter source to this procedure, and set the name column as the label and the ID as the value. The value is passed to your report SP, and the label is used for display to the user.
0
 

Author Comment

by:cindyfiller
Comment Utility
Oh my goodness - that is so cool.  I didn't realize you could set the value as one thing and the label for something else!  Learning all kinds of things!  Thank you - what an easy fix for that problem.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
I was in your position once too. Glad I can help.
0
 

Author Comment

by:cindyfiller
Comment Utility
I've worked out multiple other problems... had to recreate the SP so it was inserting into a temp file instead of updating the static file.  I think I've linked the SSRS report with the SP (report 1) to another report that will actually run and print.  On the SP report (report 1) I have used an action where I click to go to Report 2.  (I think this is what I'm supposed to do).  But I get an error - value provided for report parameter 'BeginDate' is not valid for its type.  I've checked the SP and the begin and end dates are defined as datetime.  Both report 1 and report 2 parameters are defined as date/time.  I removed all default values in case that was an issue.  I'm not quite sure what the problem is and I'm not quite sure if I've linked report 1 & report 2 together properly.  

Thanks!
0
 

Author Comment

by:cindyfiller
Comment Utility
Figured that one out....  hope to not have any more issues but I'm betting I do.   Wish you knew all the issues I ran into and have figured out...
0
 

Author Comment

by:cindyfiller
Comment Utility
Sorry but I do have a question.  I hooked up my Report 1 (with the SP) to a report I'd already built out just to see if it would bring in the right parms and run the report.  It did after a bit of testing.  But now I need to create a new report with the values from the temp table.  But when I go to select tables in the brand new report, that temp table isn't listed.  The temp table includes all the values needed for the report - but maybe that is a mistake???
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
Ok, I'm trying to understand what it is you're doing. The SP that is creating the temp table is used to populate Report 1, correct? And then report 2 is called through a link in report 1? What's the data source of report 2 then?
Like I explained in an earlier post, the temp table only has a scope of the stored procedure that creates it. So only a report that uses that SP directly can read from this temp table. If you link a second report off this report, the temp table would not be visible, unless this report also uses the same stored procedure.
Sorry, but I'm not entirely clear what it is that you're trying to achieve with the multiple reports.
0
 

Author Comment

by:cindyfiller
Comment Utility
I'm guessing that what I was trying to do was wrong.  I built all of the data fields that I need for report 2 into the stored procedure.  But since report 2 can't access that stored procedure, it doesn't work the way my brain imagined it would.  I realize now what I do need to do.  I did learn a lot along the way and appreciate very much what you shared with me.
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
Even though 2 reports might be linked, they still require their own data source. Each of the reports should be executable on its own for the thing to work. So building the data fields for report 2 into the procedure for report 1 will indeed not work. Make sure both reports have their own procedure, and only pass the required parameters from report 1 to the procedure for report 2.
This is how we learn. It's painful sometimes, but very satisfying when things finally come together.
Good luck, let me know if you have more questions.
0
 

Author Closing Comment

by:cindyfiller
Comment Utility
Excellent feedback - some of the best I've received on the site
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

763 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

12 Experts available now in Live!

Get 1:1 Help Now