Solved

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

Posted on 2011-03-23
7
276 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

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…
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 Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

809 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