Solved

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

Posted on 2011-03-23
7
297 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
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 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 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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. …
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 …
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

734 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