Using a Temporary Table in SSRS
-
Hello, I have created a temporary table in SQL Server Management Studio.. I want to use in in SSRS so that I can schedule the report to run.
Can anyone help me? Can temporary tables be accessed in SSRS?Thank you,
Charmaine -
What you're asking here is a little vague, but this might help you anyway.
Can you use a Temporary Table in SSRS? Yes, you can. SSRS simply executes the SQL you put in the query window, or the SP you put in. If that SQL creates and uses a temporary table, then SSRS will make use of it.
What you can't do, however, is use a temporary table that was made in a separate connection; for example in SSMS or a different dataset (unless the report is configured to use the same connection). Temporary tables only persist while the connection used to create them are open, and they can only be referenced in the same connection. If you create a temporary table in SSMS, you won't be able to reference in SSRS, and it won't exist for that connection. The same, however, would be true if you have 2 query windows open. For example, open a query window and run the following SQL:
CREATE #T (ID int);
INSERT INTO #T
VALUES (1),(2);
SELECT *
FROM #T;
Now, open another Query window in SSMS (Ctrl+N), and don't close your existing query window. In your new query window, try the following SQL:SELECT *
FROM #t;
Note that the query fail, as the table does not exist; at least for that connection.If you want to use a table that you've created in SSMS, you'll need to create a permanent table, not a temporary one.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Thank you.
-
Good Morning, I am trying to enter my query into SSRS. It doesn't think anything I put in the query window is valid.. .lol.
Is there a document somewhere or a tutorial that shows how to set up a temporary table in SSRS?
Thank you in advance.
Charmaine - cagray - Thursday, June 14, 2018 7:39 AM
What is the query you're putting into SSRS?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
I assumed that I could put the same query I have in SSMS in SSRS. I did change some of the data.
This is to create the table and to load the table.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 -
I can't see anything wrong with the query, however, there isn't a SELECT statement that returns data, so SSRS can't use that query on it's own. SSRS needs a statement that returns a dataset (otherwise, what does it report on?).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
this is the second part of the query.. I need these both stuck together to get one report.
the first query creates one record.. this query will create a larger result set.Select
z.aaaaa as [aaaaa]
,z.bbbbb as [bbbbb]
,z.ccccc as [cccccc]
,z.sssssssss as [dddd]
,z.eeeee as [eeeee]
From (select
m.state as [aaaaastate]
,CONVERT(VARCHAR(2), GETDATE(), 2) as [CurrentYear]
,5 as [CurrentM]
,t.bachnumb as [bach_num]
,m.aaaaaaa as [aaaaaa]
,case
when m.vndclsid = 'bbbb' then m.vndclsid
end [Class_ID]
,'S' as [Vaaaaa]
,m.cccccccc as [ccccccccc],case
when left(m.xxxxxxxx,2) LIKE '[a-Z][a-Z]' then SUBSTRING(m.xxxxxx,1,(CHARINDEX(' ',m.xxxxxxxx))-1)
else substring(m.xxxxxx, charindex('.',m.xxxxxxx) -1, charindex('.',m.xxxxxxx,charindex('.',m.xxxxxx) - 1) - (charindex('.',m.xxxxxxx) - 1) )
end [FName],case
when m.xxxxxxxxxx = 'jjjjjjjjjjjjj'
then 'jjjjjjjjjjj'
when left(m.xxxxxxxxxx,2) LIKE ' [a-Z]'
then substring(m.xxxxxxxx,charindex(' ',m.xxxxxxxx,charindex(' ',m.xxxxxxx)+1),len(m.xxxxxxx))
when left(m.xxxxxx,1) LIKE ' '
then LTRIM(SUBSTRING(SUBSTRING(m.xxxxxx,CHARINDEX(' ',m.xxxxxxx)+1,LEN(m.xxxxxx)),1,CHARINDEX(' ',m.xxxxxxx)-2))
when left(m.xxxxxx,2) LIKE '[a-Z][a-Z]'
then substring(m.xxxxxx,charindex(' ',m.xxxxxx,charindex(' ',m.xxxxxx)+1),len(m.xxxxxx))
else LTRIM(SUBSTRING(SUBSTRING(m.xxxxxx,CHARINDEX('.,',m.xxxxxx)-1,LEN(m.xxxxxx)),1,LEN(m.xxxxxx)))
end [LName]
,m.TXIDNMBR as [Ssssssss]
,DOCAMNT AS [wwww],Year(getdate()) as [Report_Year]
,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 [Pppppp]
,replace(convert(varchar,t.posteddt,101),'/','') as [TranDate]from PM00200 m
left outer join
pm30200 t
on m.xxxxxxxxx = t.xxxxxxxx
where m.VENDNAME <> 'aaaaaa'
AND m.VENDNAME <> 'bbbbbbbbbbbbbb
AND t.bachnumb = 'cccccccccccccccc') z
where
z.Class_ID = 'wwww'
AND z.VendorSTATE = 'ww'
AND z.bach_num = 'ddddddddddddd'
and z.CurrentM = z.PayMonth
group by
z.aaaaaa
,z.bbbbb
,z.ccccc
,z.gggggggggg
,z.Wage
END -
Would you mind editing your code to at least be in code markup? That's really hard to read.
Thanks.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
I am sorry, I have tried lining the code up better. The first query has one record.. I am currently getting the first query to not give me as many errors. I am not sure how to put the first query and this query together in SSRS to create one report..
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 -
I noticed when I posted the formatting went away.. I attached a file.
-
You're not putting it in mark up. See my signature, or highlight your code and press the SQL Code button.
I',m afraid I'm not happy to downlaod a document from a stranger on the internet.Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
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 = 'xxx' 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 Nnnnnn'
then 'Nnnnnnn'
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 <> 'rrrrrrr'
AND m.VENDNAME <> 'Ccccccccc'
AND t.bachnumb = 'Iiiiiiiiiiiiiiii') z
where
z.Class_ID = 'xxx'
AND z.VendorSTATE = 'IL'
AND z.bach_num = 'aaaaaaaaaaa'
and z.CurrentM = z.PayMonth
group by
z.VType
,z.FName
,z.LName
,z.Social_Security_Number
,z.Wage - cagray - Thursday, June 14, 2018 10:10 AM
OK, but where is your temporary table in all that?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Also, there's no need for that huge CASE expression you have to get the quarter. DATEPART(QUARTER, GETDATE()) will do exactly the same thing.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply