Solved

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

Posted on 2011-03-23
7
259 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

757 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

19 Experts available now in Live!

Get 1:1 Help Now