Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-03-23
7
Medium Priority
?
309 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
7 Comments
 
LVL 101

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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
LVL 101

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 35

Accepted Solution

by:
James0628 earned 2000 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 101

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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. …
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…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

705 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