June 14, 2018 at 10:21 am
If OBJECT_ID('#cgILSUTA') IS NOT NULL drop table #cgILSUTA
create Table #cgILSUTA (VendorType varchar(1), FName text, LName text, SSN nvarchar(20), Wages nvarchar(20))
-- This is the first Query - calling the procedure to enter the Employer Info
INSERT INTO #cgILSUTA (VendorType, FName, LName, SSN, Wages)
EXEC spERInfoSUTAReporting
-- This is the second query - calling the procedure for the ISR info
Insert into #cgILSUTA (VendorType, FName, LName, SSN, Wages)
EXEC spILMnthlySUTAReport
-- if i need to see it just uncomment the select statement below
--select * from #cgILSUTA
June 15, 2018 at 1:38 am
cagray - Thursday, June 14, 2018 10:21 AM
If OBJECT_ID('#cgILSUTA') IS NOT NULL drop table #cgILSUTA
create Table #cgILSUTA (VendorType varchar(1), FName text, LName text, SSN nvarchar(20), Wages nvarchar(20))-- This is the first Query - calling the procedure to enter the Employer Info
INSERT INTO #cgILSUTA (VendorType, FName, LName, SSN, Wages)
EXEC spERInfoSUTAReporting-- This is the second query - calling the procedure for the ISR info
Insert into #cgILSUTA (VendorType, FName, LName, SSN, Wages)
EXEC spILMnthlySUTAReport
-- if i need to see it just uncomment the select statement below
--select * from #cgILSUTA
I still don't understand, I'm afraid. Your first query you posted (and the one quoted above), don'tr return a dataset. The second (long one), never references a temporary table, so they have nothing in common. The long query will run fine in SSRS; it's just a normal query. The one quoted above would run, but would cause a problem as it doesn't return a dataset. Either way, this doesn't seem relevant to your initial question anymore on how you use a temproary table in SSRS.
I think that what we have here is an xy question; you might want to try re-explaining your actual goals and needs, as we don't seem to get getting anywhere and the question you've assked doesn't match the information I'm getting.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 15, 2018 at 7:18 am
I am sorry for the confusion..the above query has an "Insert into "tempTableName" then a statements executes the first query.
Then there is another "Insert into "tempTableName" then there is a statement that executes my second query. this appends my second query to the first. It is awesome.
I was trying to get SSRS to recognize my queries.. SSRS was not liking anything that I was trying to do.
Yesterday before leaving work.. I got my first query to work in SSRS.. now I am trying to append my second query, to put these both together in one report, so I can schedule it to run.
Thank you for all your time and effort.. It was appreciated.
Charmaine
June 15, 2018 at 10:29 am
cagray,
FYI, SSRS doesn't like queries that don't return a recordset. If you need to execute multiple stored procedures to get all the data you need into a temp table, and then want to SELECT from that temp table, that's something that might work best as yet another stored procedure. The way you explained what you wanted to accomplish, however, didn't make sense because the queries you posted didn't give us any clue as to how they are related. It's usually considered a best practice with SSRS to do all your query work within a stored procedure that in the end, SELECTs the data you are looking to report on.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
June 15, 2018 at 10:38 am
Please bear with me just a little longer.. I have put all my code into one stored procedure and it creates the temp table with all my wonderful data as it should. 🙂
Is there a way to then move this data to another table that I can create a procedure to put through SSRS?
Thank you,
Charmaine
June 15, 2018 at 10:48 am
cagray - Friday, June 15, 2018 10:38 AMPlease bear with me just a little longer.. I have put all my code into one stored procedure and it creates the temp table with all my wonderful data as it should. 🙂
Is there a way to then move this data to another table that I can create a procedure to put through SSRS?Thank you,
Charmaine
Why don't you just do the SELECT in that stored procedure that you just created (at the end of it) ? That would make it much easier to deal with. Plus, you'll have encapsulated everything you need into one stored procedure, rather than needing yet another one to supply to SSRS.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
June 15, 2018 at 12:33 pm
Hi, I thought for other readers, that might need to know if this worked.. No, it did not.. when I put the stored procedure into SSRS, SSRS does not recognize any columns. I review it and all that is there is an empty table.
Thank you,
Charmaine
June 15, 2018 at 12:36 pm
I am sorry... yes it did work.. I just went back to my Dataset and my columns were there.. I must have been blind.. once adding them to my report and previewed them... everything is there as it should be... wow... I am one happy woman.
Thank you for all your Help!!!
Charmaine
June 18, 2018 at 10:41 am
cagray - Friday, June 15, 2018 12:36 PMI am sorry... yes it did work.. I just went back to my Dataset and my columns were there.. I must have been blind.. once adding them to my report and previewed them... everything is there as it should be... wow... I am one happy woman.Thank you for all your Help!!!
Charmaine
Glad to be able to assist.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
July 15, 2018 at 2:29 am
Where do you use #cgILSUTA, I just see you create the table and put some date into it, no join or select to #cgILSUTA
If OBJECT_ID('#cgILSUTA') IS NOT NULL drop table #cgILSUTA
Create Table #cgILSUTA (Title1 varchar(1), title2 text, title3 text, title4 nvarchar, title5 nvarchar)
INSERT INTO #cgILSUTA (Title1, title2, title3, title4, title5)
Select 'E' as [Title1]
,999999999 as [Title2]
,7777777 as [Title3]
,sum(DOCAMNT) as [title4]
,0 as [title5]
from pm00200 m
left outer join
pm30200 t
on m.xxx = t.xxx
and m.state = 'IL'
and t.bachnumb = 'aaaaaaaa'
and t.vendorid <> 'bbbbb'
and t.vendorid <> 'ccccc'
Where bachnumb = 'aaaaaaaaa'
and month(posteddt) = 5
Select
z.VType as [VType]
,z.FName as [FName]
,z.LName as [LName]
,z.Social_Security_Number as [SSN]
,z.Wage as [Wages]
From (select
m.state as [VendorState]
,CONVERT(VARCHAR(2), GETDATE(), 2) as [CurrentYear]
,5 as [CurrentM]
-- ,month(GETDATE()) as [CurrentM]
-- ,Concat(MM,YY) as [Reporting_Period]
,t.bachnumb as [bach_num]
,m.VENDORID as [VENDOR_ID]
,case
when m.vndclsid = 'xxxx' then m.vndclsid
end [Class_ID]
,'S' as [VType]
,m.VENDNAME as [VendorName]
-- column B first name
,case
when left(m.VENDNAME,2) LIKE '[a-Z][a-Z]' then SUBSTRING(m.VENDNAME,1,(CHARINDEX(' ',m.VENDNAME))-1)
else substring(m.VENDNAME, charindex('.',m.VENDNAME) -1, charindex('.',m.VENDNAME,charindex('.',m.VENDNAME) - 1) - (charindex('.',m.VENDNAME) - 1) )
end [FName]
-- column D Last name
,case
when m.VENDNAME = 'GGGGG MMMMM'
then 'MMMMM'
when left(m.VENDNAME,2) LIKE ' [a-Z]'
then substring(m.VENDNAME,charindex(' ',m.VENDNAME,charindex(' ',m.VENDNAME)+1),len(m.VENDNAME))
when left(m.VENDNAME,1) LIKE ' '
then LTRIM(SUBSTRING(SUBSTRING(m.VENDNAME,CHARINDEX(' ',m.VENDNAME)+1,LEN(m.VENDNAME)),1,CHARINDEX(' ',m.VENDNAME)-2))
when left(m.VENDNAME,2) LIKE '[a-Z][a-Z]'
then substring(m.VENDNAME,charindex(' ',m.VENDNAME,charindex(' ',m.VENDNAME)+1),len(m.VENDNAME))
else LTRIM(SUBSTRING(SUBSTRING(m.VENDNAME,CHARINDEX('.,',m.VENDNAME)-1,LEN(m.VENDNAME)),1,LEN(m.VENDNAME)))
end [LName]
,m.TXIDNMBR as [Social_Security_Number]
,DOCAMNT AS [Wage]
-- report year
,Year(getdate()) as [Report_Year]
-- Report Quarter- one digit 1-first quarter, 2-second quarter, 3-third quarter, 4-fourth quarter
,case month(GETDATE())
when 1 then '1'
when 2 then '1'
when 3 then '1'
when 4 then '2'
when 5 then '2'
when 6 then '2'
when 7 then '3'
when 8 then '3'
when 9 then '3'
when 10 then '4'
when 11 then '4'
when 12 then '4'
end [ReportQuarter]
,month(t.posteddt) as [PayMonth]
,replace(convert(varchar,t.posteddt,101),'/','') as [TranDate]
from PM00200 m
left outer join
pm30200 t
on m.vendorid = t.vendorid
where
m.VENDNAME <> 'xxxxxxxxx'
AND m.VENDNAME <> 'cccccccccccc'
AND t.bachnumb = 'bbbbbbbbbbb') z
where
z.Class_ID = 'zzzz'
AND z.VendorSTATE = 'IL'
AND z.bach_num = 'bbbbbbbbbbbbbb'
and z.CurrentM = z.PayMonth
group by
z.VType
,z.FName
,z.LName
,z.Social_Security_Number
,z.Wage
September 17, 2020 at 12:10 pm
please, Can you explain? How I used Temp table?
September 17, 2020 at 6:09 pm
please, Can you explain? How I used Temp table?
It's already been explained in the first reply to the original post. Please read this post in the thread:
https://qa.sqlservercentral.com/forums/topic/using-a-temporary-table-in-ssrs#post-1993987
Sue
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply