Can a subreport be done from data in the main report?

I am working on a report that was written by someone else. This stored procedure for the report puts a pretty heavy load on the server, which I am going to rewrite, but is also puts all the data for the report in one record with many many columns. Because the data really does need to be output this way it is all printing at detail so when there is a break in a section of the detail the headings for that section do not reprint, there is no grouping or any way to create grouping since it is just one row.

My thought is to create subreports for all the different sections but I do not want to go back out to the database and have it run a new stored procedure for the subreports, I would just like to be able to use the data that gets created when the report is requested. Is there anyway to do this?

Thanks in advance for any and all input.
Peggy BakosAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
Yes it can but not easily.

You could use a shared variable for each column in the record and a formula that sets them in the main report.  You then create a subreport which uses the shared variables.

Basic idea
In the main report header add a formula
WhilePrintingRecords;
Shared StringVar PersonName = {PersonNameField};
Shared NumberVar PersonAge = {PersonAgeField};
etc
''

Open in new window


In the subreport create a formula for each field you need to display
WhilePrintingRecords;
Shared StringVar PersonName;
PersonName

Open in new window


mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vastoCommented:
Crystal reports supports 2 ways to get data: PUSH and PULL. Most of the times you will use PULL method , which is to provide the connection information and ask the report to get the data. PUSH method is used in cases like yours , when there are too many subreports and you want to reduce the calls to the database or there is summary data calculated in the subreport , which should be printed before the subreport and shared variable will not work. If you are writing your own application you need to retrieve the data in a dataset and use SetDataSource method to pass it to the report.  You can prepare  the dataset by calling one stored procedure (or command) one time and generating multiple select statements , which will return multiple tables. The number of returned tables should be the number of subreports +1. Let's say you have one subreport,  you need to prepare a dataset with 2 tables , the first table will be set as a datasource for the report and the second one as a datasource for the subreport. If you have 5 subreports you need to return 6 tables.

If you are not writing your own application you need to use viewer , which supports external datasources. I know one , which supports this feature. You can check it her:  http://www.r-tag.com/Pages/default.aspx.
The external datasource is explained in this document : http://www.r-tag.com/Documents/RTag%20DataSource.pdf 
There is a free version of the tool ( no tech support though), which supports this feature and is available from this page: http://www.r-tag.com/Pages/CommunityEdition.aspx
0
James0628Commented:
Depending on how much data you're passing to the subreport, parameters might be easier than using shared variables.  Using a single parameter would be simpler than using a shared variable, but if you have to pass too many different fields, trying to use parameters could get messy.

 If you could give us some more information about the data and how it's used in the report, we might be able to offer an alternative approach.

 James
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.