Solved

SSRS 2008 using temp files and stored procedures

Posted on 2014-09-11
16
939 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
16 Comments
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40316856
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 13

Assisted Solution

by:Koen Van Wielink
Koen Van Wielink earned 500 total points
ID: 40316978
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
ID: 40317053
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
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 
LVL 13

Accepted Solution

by:
Koen Van Wielink earned 500 total points
ID: 40317138
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
ID: 40319161
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
ID: 40319258
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 13

Expert Comment

by:Koen Van Wielink
ID: 40319273
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
ID: 40319340
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
 
LVL 13

Expert Comment

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

Author Comment

by:cindyfiller
ID: 40320079
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
ID: 40320095
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
ID: 40320138
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 13

Expert Comment

by:Koen Van Wielink
ID: 40320543
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
ID: 40320742
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 13

Expert Comment

by:Koen Van Wielink
ID: 40320818
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
ID: 40321002
Excellent feedback - some of the best I've received on the site
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Time Corrections for Reports Working with a report, we made some interesting discoveries about the time corrections/updates We are using the following Parameters: Starting Entered Date (Date) formatted as Data type: "Date/Time" Ending Entered …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

759 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