Passing a list of around 60 thousand strings into Crystal Reports

[abisesa]

Hello,
I am relatively new to crystal reports. I regularly run into the problem of needing to run one report to gain a list of account numbers that meet a specific qualification, and then plug them into another report. I do this, because running the first report with the information from the second takes too long and crashes crystal reports.

Usually, these lists are under 1000 entries, so I can use an array and specify that the account number must be within an array. However, I have a recent issue in which I have a list of 60 thousand account numbers and I need to plug them all into a report. Is there any way to do this?

[DellSC]

Are you running a different query every time to get the account numbers? Or are you passing parameters to an existing report/query to get them? Are you using commands or joining tables together?

I have some thoughts for this, but need to figure out which way to go with them.

Thanks!

-Dell

[abisesa]

Hello Dell,

I am new to crystal reports so bear with me, as I am not super familiar with your terminology. I am creating a report within crystal reports, with all of my prompts being in the record and group selection areas. There are multiple tables present on the report, I linked them with the database expert. The reason I believe I need two reports is that there are too many tables linked and the report can not run with the size of the data I am searching. So I am trying to split up the work, find a list of accounts (my data in this case) that meet one qualification, then pass them into a second report that gets more specific information about those accounts. Doing both in one report seems as if it is too much for crystal to bear without crashing. Perhaps there is a solution for the initial problem however, as I said earlier, I am new to crystal reports and have only been using it for around a year with most of my experience being modifying existing reports made prior to me.

[JohnClark · Accepted solution]

I don't think so. The problem with most of the prompts inputs for Crystal Reports is there is a character limit to how many you can pass in. I would expect 60,000 account numbers to exceed that.

You might want to look into doing something in your database to compile your data into what you need for the second report in a table that you could point the report to. For SQL Server, I would think you could do this with a stored procedure and then point your report to the table output from the stored procedure.

An alternative to that would be to create a stored procedure to compile your data and then use the stored procedure as the data source for your report. I've never actually done this but our report developers have created a lot of stored procedure based Crystal Reports.

[JWiseman]

Hi Abigail, you can accomplish the desired result via your current report in conjunction with an On-Demand Subreport.

passcustomerstoondemandsubreport.txt

Download the attached file and change the extension from .txt to .rpt. This is a sample report that I'll use to explain how to do this. First have a look at the report in the Crystal Reports designer. There are 2 Design tabs as well as 2 Preview tabs. One for the main report, and one for the On-Demand subreport. The On-Demand subreport is in the Report Footer of the main report. Also have a look at the parameter values and the customer IDs.

1. The main report is used for finding the customers that will be passed to an On-Demand Subreport, which is in the Report Footer.
2. The main report has some Parameters which are used in the main report's Record Selection Formula.
3. These parameters are copied over to the On-Demand Subreport.
4. The main report's parameters are then linked to the On-Demand Subreport's parameters.
5. The main report's Record Selection Formula is copied to the On-Demand Subreport's Record Selection Formula.
6. By having the Subreport in the Report Footer formatted as On-Demand, this negates it from running until you click on it...i.e. after you're done selecting the customers.
7. By using the same parameters and the same Record Selection, you are not passing the individual IDs, but the necessary values to create the same selection of IDs in the Subreport.

Let me know if you have any questions at all.

[DellSC]

Please read https://blogs.sap.com/2022/07/11/how-to-speed-up-reports-and-prevent-memory-crashes-in-crystal/ for information about how to make reports more efficient and take less time to run. Some of this may make it so that you don't need to run two reports to get your data.

Also, if you're using a client/server database (like SQL Server, Oracle, etc.) and you have reasonably good SQL skills, you could always use a Command (SQL Select statement) to get all of your data for your report. This pushes much of the processing down to the database where it can be handled more efficiently. See https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/ for more information about using commands. If you aren't comfortable doing this type of thing directly in SQL, you could post the queries that Crystal generates for the reports and I'll see what I can do to write it for you.

-Dell