I have a table with 3 columns: CLIENT_ID, STORE_ID and MADE_PURCHASE. Basically I'm trying to get a list of CLIENT_ID and an array of STORE_ID where a customer made a purchase. For the following data, here is the expected result:
DATA:
| CLIENT_ID | STORE_ID | MADE_PURCHASE |
|---|---|---|
| 1 | a | YES |
| 1 | b | YES |
| 1 | c | YES |
| 2 | a | YES |
| 2 | b | NO |
| 2 | c | YES |
| 3 | a | NO |
| 3 | b | NO |
| 3 | c | NO |
Expected result:
| CLIENT_ID | STORE_ID |
|---|---|
| 1 | a,b,c |
| 2 | a,c |
I was able to achieve the desired result by creating a query to filter out lines where MADE_PURCHASE = 'NO'. Then I created a list in the report. The first column is CLIENT_ID then I insert a repeater in the second column that contains STORE_ID.
The problem is that the repeater slows my report by a factor about equal to the number of CLIENT_ID retrieved. For example if I run the query without a repeater and it returns 10 unique CLIENT_ID in 10 seconds, then adding the repeater slows the report to 100 seconds. As soon as I enter more than a few hundred CLIENT_ID in the prompt the report takes multiple hours to run.
I tried editing the master-detail relatioship between the list and the repeater without much change. Anyone has any idea how I could make it run faster?
P.S. I know the desired output format is not ideal but the goal is to mimic a legacy report that was built on excel using concatenate on STORE_ID, as such, the client wants to keep the original format.
