Solved

Passing Linked Array Values to SubReport in Crystal Reports

Posted on 2016-08-15
7
113 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
ID: 41756695
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
ID: 41756778
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
ID: 41757876
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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 41757909
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
ID: 41758708
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
ID: 41763333
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
ID: 41768453
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
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…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

773 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