Capture list of Top N group sort

Datakat used Ask the Experts™
I have a report that shows sales by department. Within each department, it lists by group the top 5 stores.  I need to pass the store numbers to a subreport after they have all printed to do some other calculations using another table that won't link to this one.  How do I capture those top 5 store numbers? Or is it even possible to send them as a list to the subreport?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

As for as I know, Crystal Reports doesn't support number arrays. This requirement can be achieved if you can get your stores numbers in a string format (you can get to_char(your store number) as an additional column) and create a formula to build an array with something like below:

Shared StringVar strStoreNum;
strStoreNum := strStoreNum & ", " & toText({yourStoreNuminStringFormat}, '#');

Use this to pass on to sub report by linking this using "change subreport links" option and select the formula under "FIeld(s) to link to:" and select pm-{formula name in main report} option under "Subreport parameter field to use".

In the sub report, you need to get an additional column as in main report with a string type store number and use that in record selection using the linked parameter by including record selection as below:

{string store number field from} in {?Pm-@your main report formula field}

This will add performance degradation as the number are being converted to strings for filtering.

Hope this helps.

An alternative way can be to get the required records (Top 5 stores for each department) at query level and pass those directly to sub report via change sub report links using option "Select data in sub report based on field:"
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013
WHere do you want the subreport?
If you are trying to pass the list then it can only be in a footer section.

You can "pass" an array to a subreport through a shared variable.  The only thing is it can't be used in record selection

To add to what the others posted:

 You can use arrays in CR formulas, but the result of a formula can't be an array, so you can't, for example, pass an array to a subreport parameter by linking a formula to the parameter.  If you want to give a list of values to a subreport, then you have to use one of the methods that the others described.

 Putting the list in a string doesn't necessarily have to hurt the subreport's performance.  If the store number field is a number, then, as Raghavendra said, converting that field to a string in the subreport record selection formula could slow things down, because the subreport may have to read the records for every store and then convert the store number to a string to see if it's in the list (rather than ask the db to only send the records for the desired stores).  However, rather than convert the store number to a string, you can convert the values in the string from the main report back into numbers.  The difference is that CR should do that just once (for each subreport), and then it can send the list of numbers to the db so that the subreport only reads those stores.

 I can provide more details if you like.

OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

DatakatCrystal Report Designer


I have converted the array to characters successfully.  And used shared array to get it to subreport.  I wonder how I would do what James0628 suggested to convert it to numbers and send that to subreport? Details?
You're passing the numbers in a shared array variable?  If you want to use those numbers to select the stores in the subreport then, as mlmcc said, that won't work, because you can't use a shared variable in a record selection formula.  If you simply try to declare a shared variable in a record selection formula, CR will give you an error.  You need to put the values in a single string and link that to the subreport, as Raghavendra suggested.

 My addition to Raghavendra's suggestion is to have the record selection formula break the string back into separate values and then convert those strings back to numbers, so that you can compare the numeric store number field with those numbers, rather than convert the store number field in each record to a string.  The hope is that CR will be able to pass that list of numbers to the server, making the query more efficient, but I can't say for sure if that will happen or not.  I've had similar situations where I did some "calculations" in a record selection formula and the result was passed to the server, but I can't guarantee that that will happen in this case.

 If you'd like to try it ...

 I save the values in a Global variable instead of Shared.  FWIW, Shared might also work, since we're linking the value in the shared variable to a subreport parameter, rather than trying to use the shared variable in the record selection formula.  I just used Global to play it safe.

 I'm assuming that the store number field will never be null, and that the store numbers are integers.

 I use 999999 as a "dummy" store number and start the array with that (having an initial value in the array just simplifies things slightly).  If you could have a store number 999999, just replace that with some invalid store number (eg. 1.5).

 Create a formula like the following in the main report and put it in the report header, to declare the Global variable:

Global StringVar store_list;

Open in new window

 The "" at the end is just so that the formula doesn't produce any visible output on the report (although it wouldn't anyway in this case, since the variable is empty at this point).

 Create a formula like the following in the main report and, if you're building a list of stores for each department, put the formula in the department group header, to reset the list for each new department:

Global StringVar store_list;
store_list := "999999";

Open in new window

 Create a formula like the following in the main report and put it in the store group header, or wherever you want to save the store numbers:

Global StringVar store_list;
store_list := store_list + "," + CStr ({store number field}, "#");

Open in new window

 Create a formula like the following in the main report, to output the value in the variable:

Global StringVar store_list;

Open in new window

 You would then create a string parameter in the subreport and link the formula above to that parameter.

 Then, in the record selection formula in the subreport, you would have something like this:

Local StringVar Array store_list_str;
Local NumberVar Array store_list_num;
Local NumberVar i;

// Set our arrays to the required size
Redim store_list_str [ UBound (Split ({?store list parameter}, ",")) ];
Redim store_list_num [ UBound (store_list_str) ];

// Split the list in the string into an array
store_list_str := Split ({?store list parameter}, ",");

// Convert each value in the string array into a number
for i := 1 to UBound (store_list_str) do
  store_list_num [ i ] := Val (store_list_str [ i ]);

{store number field} in store_list_num

Open in new window

 {?store list parameter} is the string parameter that you created above.

 The code should work (ie. find the specified stores).  But I can't say for sure whether it will be more efficient than converting the store number field to a string and comparing that.  It might even depend on what db you're using (CR's capabilities can vary from one db to another).

FWIW, Raghavendra probably should have gotten some points as well, since he was the first to suggest saving the numbers in a string.  I handled it a bit differently, but it was the same basic idea.  If you like, you can ask to have the question reopened, and then you can split the points between the three of us.


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial