ReportServer Forum

abhijitMehta

Member

Registered: 2015-09-28

Creation of Temporary table for input list

Hello ,

I Want to have a facility to load a list of account numbers to a temporary table and use that table in my report , something like

 select A , B , C 
           from MY_ACCOUNT_TABLE 
           where account_numbers in ( Select account_numbers from MY_TEMPORARY_TABLE )

I Evaluated using parameters , but that eventually would go down to an oracle query's where clause which has a limitation of 1000 values only , whilst I want to be able to support upti 5K values.

This is why I Wanted to explore if can create a temporary table and then load values to it from a file .Can this be done on Reportserver ?
Any advice of any alternative approaches to achieve the requirement ?

Regards
Abhijit

abhijitMehta

Member

Registered: 2015-09-28

Re: Creation of Temporary table for input list

Any comments or advise on this please ?
Is there a sample groovy script that I Can use for this purpose ?

Arno Mittelbach

datenwerke

Registered: 2012-02-14

Re: Creation of Temporary table for input list

Hi Abhijit,

I am not quite sure I fully understand the use case. Could you explain a bit more where the data in MY_TEMPORARY_TABLE is coming from. If all that is not working is the limitation of 1000 values, then you could try restating your query using a RIGHT (or LEFT) JOIN. Something along the lines

 select TA.A , TA.B , TA.C 
           from MY_ACCOUNT_TABLE TA RIGHT JOIN MY_TEMPORARY_TABLE TB ON (TA.account_number = TB.account_number)

I hope this helps.

Cheers
--Arno

abhijitMehta

Member

Registered: 2015-09-28

Re: Creation of Temporary table for input list

Hi Arno ,

Problem is not with using the data in the table ( either subquery or join would have same effect) . My problem is to get data into that table in the first place.

Use Case :  I want to allow Business user to enter input set , lets say customer ids. Which various reports should look from and work query on. As a text parameter they can enter only 1000 customer ids separated by comma which I can read as $!P{cultist} . But if I want more customer ids that 1K , I need to find a way for it. I was planning to let them upload a csv with list of customers and  load the csv to MY_TEMPORARY_TABLE ( which actually I have named RS_INPUT_LIST) . In my report queries I have used this table .

I wanted to know if this can be done using the report server itself ?
( If not , I will have to run a script on the server where cvs list is uploaded - say a shell script that could then use sqlldr to load the data into the table)

Hope I have explained the scenario , but do let me know if you want me to elaborate any further .

Cheers
Abhijit

Thorsten J. Krause

datenwerke

Registered: 2012-02-15

Website

Re: Creation of Temporary table for input list

Hi Abhijit,

thanks for explaining, I think we now understand what you are trying to do. Unfortunately we don't have a good solution to this problem, either. We will think about adding a "list" input field in a future version of ReportServer, that splits the input into individual IN-clauses, but I can make no promises when this might become available.

Cheers,
Thorsten