Multi-Select Prompt Used With Record Select Expert

[baycolor]

Hi,

I'm using CRV11.

I'm going to create a report with about 5 fields.

PERSONID COUNTRY REGION PRODUCTS FUNCTIONS
1 USA Americas 1,2,3 A,B,C
2 Canada Americas 1,4,5 F,L,P
3 Mexico Americas 3,5,7 X,Y,Z

Multiple PRODUCTS per PERSONID and multiple FUNCTIONS per PERSONID is possible. And its unknown how many PRODUCTS or FUNCTIONS will exist per PERSONID.

Because of that I will be doing something like the following for the PRODUCTS field and for the FUNCTION field to get a unique row per PERSONID with all PRODUCTS and FUNCTIONS. Example here is for PRODUCTS only...

1)
I will group by ID.
2)
In the group header I will place the following formula code and suppress it.

CODE
WhilePrintingRecords;
global stringvar Products := "";

3)
In the detail section I will place the following formula and suppress it.

CODE
WhilePrintingRecords;
global stringvar Products;
Products := Products + {table.products};

4)
In the group footer section I will place the following formula to display the results

CODE
WhilePrintingRecords;
global stringvar Products;

Products

The issue I have is that this report will also have two prompts. One will allow a user to multiselect a number of PRODUCTS and another allowing them to multiselect a number of FUNCTIONS. These prompt selections need to be used in conjunction with the Record Selection Expert in order to limit down what rows should be part of the output. Just consider PRODUCTS...

If a user selects from a multiselect prompt dropdown 1 and 7 then I want the first and third rows from my sample data above (rows for PERSONID 1 and PERSON ID 3).

I'm not sure how to do this using the Report Select Expert. I see that I can setup things like:
if table.products "is equal to" <my prompt field>
if table.products "is one of" <CANT SELECT my prompt field>

Since none of whats available meets my needs I can go to the Formual Editor and build my own condition. If I do that and knowing that I may have multiple prompt select values that I want to compare to possibly multiple PRODUCTS values what is the best and most efficient way to do this in a Record Select custom formula?

With arrays... Load the prompt value into an array and load the PRODUCTS values into an array? Then loop through both checking to see if I get any matches. If I get a match get out of the loop and set a value like 1=1 at the end of the formula (to tell CR this record should be part of the output)?

Or is there another better way?

NOTE in the real report there could be up to about 50 PRODUCT(S) ids associated with a PERSONID. And when running the report the user could select something like 20 or 30 or more PRODUCTS in the PRODUCTS multiselect prompt. Same for FUNCTIONS.

Any code/ideas/comments is appreciated.

Thanks in advance.

 

[lbass]

Go to report->selection formula->record and enter:

{table.product} = {?product} and
{table.function} = {?function}

-LB

 

[baycolor]

**********
QUESTION 1
**********
Maybe I'm missing something but I couldn't get this to work...

My PRODUCTS prompt box allows a user to enter multiple values one at a time. They enter 1 and add it to the list. They enter 7 and add it to the list.

This field is compared in the report->selection formula->record via

{table.product} = {?product}

The PERSONID 1 row of my data has a {table.product} value of 1,2,3 The PERSONID 3 row of my data has a [table.product} value of 3,5,7. Both of these rows should be on the output.

But the compare in the formula is comparing to see if
1 = any {table.product} values. which it won't ever. Doing the same for 7.

I have a prompt list of data to compare to a list of data in a field.

**********
QUESTION 2
**********
Also I'm not sure how the following would work.
{table.product} = {?product} and
{table.function} = {?function}

I ultimately will have several prompts in my report.
One for PRODUCTS.
One for FUNCTIONS.
One for another field.
One for another field.

They are all independent of each other. So how would
{table.product} = {?product} and
{table.function} = {?function}
work if the user wants to enter a filter value for the {?product} prompt but doesn't want to enter any for the {?function} prompt. The prompts are requiring a value.

 

[lbass]

First, of all, the formulas in your first post are resulting in a comma-delimited array for display purposes, but the records underlying this are still drawn based on one value at a time. If the user enters a 1 and a 3, then the formula for USA will now appear as: 1,3
assuming you have added a comma into your formula as in:

WhilePrintingRecords;
stringvar Products;
Products := Products + {table.products} + ", ";

Your display formula would then be:
WhilePrintingRecords;
stringvar Products;
left(Products,len(Products)-2);

If in fact you want USA to still display all records if one of the values attributable to it is selected, you would have to use a different approach. Please clarify.

In terms of the multiple prompts, do you mean that for each parameter you might want all records displayed or just some? If so, add an "All" option to each string parameter and then set up the record selection formula like this:

(
if {?Product} <> "All" then
{table.product} = {?Product} else
if {?Product} = "All" then
true
) and
(
if {?Function} <> "All" then
{table.function} = {?Function} else
if {?Function} = "All" then
true
)

-LB

 

[baycolor]

Not sure how USA comes into play but I think the light may have come on for me. Please correct me if I'm wrong.

Yes my grouping formula that builds PRODUCTS will put a comma between values.

You said: "If in fact you want USA to still display all records if one of the values attributable to it is selected, you would have to use a different approach. Please clarify."

Assuming that you mean all records for a PERSON ID then no I don't want that I just want one row displayed for a PERSON ID if any of the {?product} prompt values they entered exists in the PRODUCT field I built.

If they didn't enter any {?product} prompt value and I did the ALL thing your talking about and they entered a {?function} value that existed in the FUNCTION field I build then I would want the one row for that PERSON ID to still be displayed.

****************

What you are saying is the {table.product} = {?Product} evalution is being done by CR at a lower level then the grouping. I don't have to worry about comparing an array of prompted values (?product} to an array of built values PRODUCTS.

Correct?

 

[lbass]

No, that is not what I meant. I think you are confusing the report display with what records are being returned to the report.

Your formula that allows you to display the comma-delimited values in the group section for personID reflects the records that are returned to the report. I am asking whether you want:

1-All values for product to appear for each person IF the formula shows at least one of those values, or

2-Only those product values that are selected by parameter.

For example, using your example in your first post, let's say that the user selects 3 and 7. Do you want to see the following:

PERSONID COUNTRY REGION PRODUCTS FUNCTIONS
1 USA Americas 1,2,3 A,B,C
3 Mexico Americas 3,5,7 X,Y,Z

Or do you want to see:

PERSONID COUNTRY REGION PRODUCTS FUNCTIONS
1 USA Americas 3 A,B,C
3 Mexico Americas 3,7 X,Y,Z

Person 2 would not appear, since no selected products appeared for that group.

I don't know what you mean by your last paragraph. The grouping doesn't affect what records are returned to the report.

-LB

 

[baycolor]

Oh you are most definitely correct about my confusion. I'm struggling.

If the person selects 3 and 7 I want the following back:

PERSONID COUNTRY REGION PRODUCTS FUNCTIONS
1 USA Americas 1,2,3 A,B,C
3 Mexico Americas 3,5,7 X,Y,Z

The entire contents of the PRODUCTS field can be returned as a matter of fact the entire contents of all fields should be returned.

I'm more concerned about what rows appear as output. I guess I don't understand this because my PRODUCTS field has #,#,#... and I'm comparing multiple values that are selected via a prompt to that. Is CR doing work internally parsing out the prompt values and checking them against parsed out PRODUCTS values? And if a match is made (or a match is made on multiple values) it will allow the 1 grouped row to be displayed?

I question this because I did a test with a SELECT statement that returned only the following row:
PERSONID COUNTRY REGION PRODUCTS FUNCTIONS
1 USA Americas 1,2,3 A,B,C

And when I added a multiselect prompt that had a value of 1,2,3 entered the report returned the one row. When I entered 1 at the prompt individually and 2 individually the report didn't return the row.

Perhaps because I'm not doing grouping and so forth this isn't a valid test?

 

[baycolor]

Ok I believe I see your point now...

I got a couple tests with a prompt to work.
TEST 1:
First entered 3 for a {?product} and both rows 1 and 3 were returned.

TEST 2:
Then ran it again and entered 2 and 7 into the prompt box. Again rows 1 and 3 were returned. Which is correct.

The output however was not correct...

The output field value for PRODUCTS is just the value(s) I limited down by in my prompt. In TEST 1 it was 3. In TEST 2 it was 2,7

So the question is down to how do I get around that and see all the values I built via the grouping in the PRODUCTS field after applying the prompt filter?

 

[lbass]

I think you should unsuppress the detail section and look at your report. Just because you are displaying products in one row per person, doesn't mean there is only one row in the report. There are still all the rows contributing to that formula. This is only for display.

Remove the record selection criteria that I earlier suggested, and instead create a formula in the formula expert {@products} and place it in the detail section:

if {?Product} <> "All" then
(
if {table.product} = {?Product} then 1
) else 0

Then go to report->edit selection formula->GROUP and enter:

sum({@products},{table.personID}) > 0

If you want to do the same thing with functions, and you want functions to appear even if the person doesn't have selected products, create a similar formula for functions:

if {?function} <> "All" then
(
if {table.function} = {?function} then 1
) else 0

Then add onto the GROUP selection formula so that it reads:

sum({@products},{table.personID}) > 0 or
sum({@function},{table.personID}) > 0

-LB