Solved

How to reference a result set from the main report within the subreport

Posted on 2011-03-23
7
266 Views
Last Modified: 2012-05-11
My selection statement returns a list of users.  These users are not in any groupings.  My subreport needs to know the user result set from the main report to be able to build its own selection query.  How do I do this?  I know I need to create a formula for the SubReport Link.. is there a way to create an array of the users returned from the selection criteria in the report header?  And then do I just use where {SubReportTable.Users} in {?pm-myuserarray} in the subreport?
0
Comment
Question by:c0fee
  • 3
  • 2
7 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 35202058
No because a formula cannot return an array as its value.  You could return a string of the names concatenated by a separator like ", "

Where will the subreport be in the report?  To be able to use the full list it will need to be in the report footer

mlmcc
0
 
LVL 2

Author Comment

by:c0fee
ID: 35202098
I can put the subreport in the footer...
0
 
LVL 2

Author Comment

by:c0fee
ID: 35202206
Can that comma delimited string be used in the query?  Wouldn't SQL see that as a single value?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 100

Expert Comment

by:mlmcc
ID: 35202322
I don't know about the SQL.

You could use something like

InStr({YourField}, {?pmParameter}) > 0

mlmcc
0
 
LVL 34

Accepted Solution

by:
James0628 earned 500 total points
ID: 35204699
The array thing just won't quite work.  You can create a multi-value parameter (which CR handles like an array) in the subreport, and create an array in a formula in the main report, but (as mlmcc said) a formula can't produce an array as a result, so you can't link the array in the main report to the parameter in the subreport.

 You could make the array a shared variable and then it would be available in the subreport, but you can't use a shared variable in a record selection formula, so the subreport would have to read all of the records with any value in the field that you're trying to check, and then suppress the records that aren't in the array.  That's probably not a good solution, unless you're dealing with a very small number of records.

 Which brings us to the idea of combining the values in a delimited string.

 Which db are you using?  MS SQL (and I imagine most other db's) would see the comma-delimited string as a single value.

 You could do the test in the record selection formula, although there are a couple of issues with mlmcc's suggestion.

 First of all, unless I'm missing something (possible, but I don't think so), the arguments should be the other way around:

InStr({?pmParameter}, {YourField}) >  0

 But that test may be too simple anyway.  For example, if your field is a string and the user enters "ABC" as one of the values, that test would also include any records where the field contained "A", "B", "C", "AB" or "BC", because they are all contained in the string "ABC".

 If you put the delimited string of values in the parameter together carefully, and assuming that YourField is a string, you could use something like this:

InStr ("," + {?pmParameter} + ",", "," + {YourField} + ",") >  0


 The idea is to put your delimiter (",") around the field value, so if the value is "A", you get ",A,", if the value is "ABC" you get ",ABC,", and so on.  That way you won't get any false positives, like "A" being picked up because the parameter includes "ABC", and "A" is a part of that.  You just have to make sure that the values in the parameter and the values in the field are formatted the same (no leading or trailing spaces if they're strings, etc.).

 I also added ","s around the parameter string.  Those may or may not be necessary, depending on how you built the string.  You need to have a "," before the first value and after the last one, so that each value is surrounded by ","s.  If you include them when you're creating the delimited string, then you don't need to add them here.

 With luck, this test will be passed to the server.  If not, you could try changing it to something like:

"," + {?pmParameter} + "," like "*," + {YourField} + ",*"


 If your db supports LIKE, that test should probably be passed to the server.


 Another option would be to create a manual query (like a stored procedure or CR Command) and pass the delimited string to it as a parameter, and split the delimited string into separate values in SQL.  That could probably be done (depending on your db), but if one of the above options works and performs well enough, then it may not be necessary.

 James
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35452377
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Crystal Report 2008 script 4 56
need to suppress group in group tree when it is blank/null 6 70
C# Crystal Reports 15 79
Crystal Reports total formula with groups? 4 37
Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 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

12 Experts available now in Live!

Get 1:1 Help Now