Search code examples
sqlvisual-studio-2010temp-tablesrdl

Microsoft Visual Studio Create temporary Tables


Good day, I am trying to create a data set in SSRS unfortunately I need to create a temporary table in order to create my report. My code is as follows

create table #Tax_Admin (AWD varchar (255), Name varchar(255))  
insert into #Tax_Admin  
values  ('MDSTM','Agent 1'),
        ('MDSAK','Agent 2'),    
        ('ITDPM','Agent 3'),    
        ('MASEL','Agent 4'),    
        ('ITDEK','Agent 5'),    
        ('ITDFM','Agent 6');    

with #transactions_workingwith  
as  
(   
SELECT distinct 
      [TRANSACTION_ID]  

  FROM [mmcenvsqlprd01].[MOMDWL1].[ibrststdatEQ].[MOVEMENT_DATA] A

    where   

  (a.TO_QUEUE like '%FINTAX%'   
  OR  a.TO_QUEUE like '%TAXQ%'  
  OR  a.TO_QUEUE like '%TAXCERT%')  

  and a.TO_STATUS_DATE >= '2017-06-01') 



  , User_Movements as   

  ( 


  Select    
  a.TRANSACTION_ID  
 ,a.POLICY_NUMBER   
  ,a.USER_ID    
  ,a.BILLING_CYCLE_ID   
  ,a.TRANSACTION_TYPE   
  ,a.FROM_STATUS    
  ,a.TO_STATUS  
  ,a.TO_STATUS_DATE 
  ,a.TO_QUEUE   
  , ROW_NUMBER() over (Partition by a.TRANSACTION_ID order by a.TO_STATUS_DATE) as User_Movement_Nr 

  ,case when   (a.TO_QUEUE like '%FINTAX%'  
  OR  a.TO_QUEUE like '%TAXQ%'  
  OR  a.TO_QUEUE like '%TAXCERT%') then 'Tax Queue' else 'Other Queue' end as Queue_Taype   

  FROM [mmcenvsqlprd01].[MOMDWL1].[ibrststdatEQ].[MOVEMENT_DATA] A  
  inner join #transactions_workingwith b on a.TRANSACTION_ID Collate SQL_Latin1_General_CP1_CI_AS = b.TRANSACTION_ID Collate SQL_Latin1_General_CP1_CI_AS   

    where   

   a.TO_STATUS_DATE >= '2017-06-01')    


   Select distinct  

     a.TRANSACTION_ID   
     ,a.POLICY_NUMBER
     ,convert(varchar,a.TO_STATUS_DATE,112) + isnull(c.EMPLOYEE_NR,d.EMPLOYEE_NR)  as EmployeeKey ---Why do we need this empoyee key?
  ,a.USER_ID    
  ,isnull(c.EMPLOYEE_NR,d.EMPLOYEE_NR) as Emplyee_Nr    
    ,f.[BusinessArea]   



  ,a.TRANSACTION_TYPE   
  ,a.FROM_STATUS    
  ,a.TO_STATUS  
  ,b.TO_STATUS as Next_Status   
  ,b.USER_ID as Next_Status_User    
  ,isnull(e.Name,'Other') as Tax_Admin_Name 
  ,case when b.TO_STATUS like '%FAIL%' THEN 1 ELSE 0 END AS Is_Fail 
    ,case when b.TO_STATUS like '%INFO%' THEN 1 ELSE 0 END AS Is_Info   
     ,case when b.TO_STATUS like '%TRAIN%' THEN 1 ELSE 0 END AS Is_Train

     ,
     case when b.TO_STATUS like '%FAIL%' THEN 1 ELSE 0 END
     + case when b.TO_STATUS like '%INFO%' THEN 1 ELSE 0 END
     + case when b.TO_STATUS like '%TRAIN%' THEN 1 ELSE 0 END as Returned

  ,a.TO_STATUS_DATE 
  ,a.TO_QUEUE   
  ,a.User_Movement_Nr   
  ,a.BILLING_CYCLE_ID   
  ,1 as Movement    

   from User_Movements a    
   inner join User_Movements b 
   on a.TRANSACTION_ID = b.TRANSACTION_ID and  a.User_Movement_Nr + 1 = b.User_Movement_Nr  
   left  JOIN [mmcenvsqlprd01].momdwl1.ibrststcnfEQ.IBRS_USER_INFO c 
   on a.USER_ID Collate SQL_Latin1_General_CP1_CI_AS = c.USER_ID Collate SQL_Latin1_General_CP1_CI_AS   
   left  JOIN [mmcenvsqlprd01].momdwl1.ibrststcnfWealth.IBRS_USER_INFO d 
   on a.USER_ID Collate SQL_Latin1_General_CP1_CI_AS     = d.USER_ID Collate SQL_Latin1_General_CP1_CI_AS       
   left join #Tax_Admin e on b.USER_ID Collate SQL_Latin1_General_CP1_CI_AS  = e.AWD Collate SQL_Latin1_General_CP1_CI_AS       
   inner join [mmcenvsqlbi01].[Master_Data].[Trans].[Employee_Snapshot] f 
   on convert(varchar,a.TO_STATUS_DATE,112) + isnull(c.EMPLOYEE_NR,d.EMPLOYEE_NR) Collate SQL_Latin1_General_CP1_CI_AS = f.[EmployeeKey]     Collate SQL_Latin1_General_CP1_CI_AS   
   where a.Queue_Taype = 'Tax Queue'

   drop table #Tax_Admin

How would I go about Moving this code over to SSRS? I have tried the With Clause and that is not working. I have tried building a Null table and then updating the information, which didn't work. can someone please help me?


Solution

  • What's the problem with creating a temporary table in SSRS? I ran a quick test to make sure you can do this in SSRS 2012, and it appears that this runs fine, e.g. my dataset is defined as:

    CREATE TABLE #temp(id INT, name VARCHAR(10));
    INSERT INTO #temp SELECT 1, 'Harry';
    INSERT INTO #temp SELECT 2, 'Megan';
    SELECT * FROM #temp;
    

    What's the actual error that you are getting when you try to do this in SSRS?

    If you can't get this to work then you could create a stored procedure (no parameters) and simply paste your script into that, then use the Query Type = Stored Procedure and pick the one you just created in SSRS.

    The most common problem with using WITH is when your previous statement hasn't been terminated with a ; correctly, but in your script this doesn't seem to be the case? People often right ;WITH to workaround this issue.

    You could try using a table variable, i.e. change #Tax_Admin to @Tax_Admin and DECLARE it as a TABLE.