Solved

Passing Linked Array Values to SubReport in Crystal Reports

Posted on 2016-08-15
7
81 Views
Last Modified: 2016-08-24
I use a Crystal report that takes a numeric parameter list as input and passes that as a link to the subreport.
The main report appiles each of the numeric parameters to the SQL script and works just fine.
However, the subreport takes the linked array as Pm-?InputList but only uses the last element of the array.
I am also linking a field returned in the SQL from the main report as string array, Pm-Command.fieldname, but only see the last element of the array there also.
I tried playing with WhilePrintingRecords but was unsuccessful.
How do I code this report to link a numeric parameter array and a string field array from the main report which is used for record selection in the subreport?
0
Comment
Question by:Member_2_7971919
  • 3
  • 2
  • 2
7 Comments
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
I just built a simple report.
I added a parameter which allowed multiple values
I used the parameter as a link to the subreport.
It worked as expected.

WHy do you think that only the last value is being passed?

Can you upload your RPT file?

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
I assume you mean for the second subreport.  You have it linked on the AC9 field and not the parameter for AC9

mlmcc
0
 

Accepted Solution

by:
Member_2_7971919 earned 0 total points
Comment Utility
So, thanks to mimcc for pointing out that a parameter list can be linked from the main report to the subreports, but attempts to link values obtained from a query in the main report are not as easily linked to the subreport.
Since the subreports reside in the report footers, these only have access to the last record from the main report.
Since I still need to use all of the values returned from the query in the main report for record selection in the sub report, it appears that I need to construct an array in the main report that can be linked to the subreport in the report footer.
Does that approach make sense?
What code template would I use in the main report to build this array?
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
Comment Utility
So what you need is to capture all the IDs from the main report and use them to filter the subreport?  Yes that can be done.

Basic idea

In the report header add a formula
Name - DeclareVariables
WhilePrintingRecords;
Global StringVar strIDs;
''

Open in new window


In the ID group header add a formula
Name - BuildList
WhilePrintingRecords;
Global StringVar strIDs;
strides := strides & ", " & {IDField};
''

Open in new window


Add a formula to use as the link
Name - DisplayIDs
WhilePrintingRecords;
Global StringVar strIDs;
strIDs

Open in new window


After adding the link, edit the subreport selection formula
{IDField} IN Join({?pm_DisplayIDs},',')

mlmcc
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 250 total points
Comment Utility
To add to mlmcc's post ...

 You can't link an array variable to a subreport, because a formula can't produce an array result.

 If you need to send multiple values to a subreport, one option is to combine the values in a string, as in mlmcc's post.  Then the subreport can check the values in that string.  However, in the subreport record selection formula, you would use Split, not Join (as in mlmcc's post).  Join combines the values in a string array into one string, but you need to do the opposite at that point.  Split will turn the string containing a list of separated values into an array.

 FWIW, another option would be to use a shared array variable (instead of global) and then the subreport would be able to use that variable directly.  But I believe that using a shared variable in the record selection formula would force the filtering to be done locally, not on the server, so that's probably a bad idea.  I just wanted to mention that it was an option, and explain why it was probably not your best choice.  :-)

 James
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
If you can combine the values in a string, as in mlmcc's example, then his suggestion will probably work.  You link the string to a parameter in the subreport, and then the subreport record selection splits that string back into an array, and the resulting test will hopefully then be translated into SQL and sent to the server.  You could use Database > "Show SQL Query" to check.

 James
0
 

Author Closing Comment

by:Member_2_7971919
Comment Utility
Unfortunately, while Crystal Reports can link or share variables, these cannot be used in SQL statments in the subreports.
Therefore the subreports need to select all of the records (sometimes in the tens of millions) so that a linked or shared variable could be used as part of the selection criteria, which is inefficient.
If a subreport's input parameter could be linked (possibly a future enhancement), then the SQL in the subreport could be adjusted by the main report.
Bottom line: my approach using subreports was a poor choice to begin with and I ended up breaking it up into separate reports.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now