Record Selection by parameter

[AKPolarBear]

I am using CR7 and a SQL database.

I have item numbers formatted as XXXX-XXXXX where the 1st 4 X's are used to group like items and the second 5 X's are used to subgroup by other means resulting in unique item numbers. In the later group of 5, the number 99990 is used as the group name but can also be used as an item number for transactions in certain circumstances that will generate data.. All groups use the same 99990 suffix for this purpose (in other words, a catch all item).

The summary report I am writing, needs to group on the first four X's only but use the group name XXXX-99990 for the name.

The report is built and working but I am having troubles getting it to use the 99990 item as the name in each case. CR is using the first record it finds in the group if the 99990 was not used or is non-existent.

I tried a subreport link but it ran too slow. I think my solution is something like an array but can't remember how to set it up. It went something like if X =1 then use ( {X},{name} ) where the name corresponding to item X would be used.

 

[lbass]

If I understand you correctly, you should be able to create a formula:

left({table.itemno},4) + "-99990"

Then group on this formula to create Group #1. Your second group could be based on:

mid({table.itemno},6)

-LB

 

[AKPolarBear]

Thanks for the suggestions.

First grouping is already as you suggest and that works okay. Second suggestion did not work as I need.

After the first grouping, all the transactions are grouped under it correctly (all the items who first 4 numbers match the group number) and a summary is then produced.

The display is then the group name and the summary information. Detail is hidden. The problem is that the name of the last item in the group detail ends up being used as the group name. If that item was not the 99990 item, then the name is incorrect.

 

[lbass]

If you used the formula I gave you, the group name would always end in "-99990". If you are using the group footer, you should be dragging (or copying) the group name from the group header, not dragging the detail field into the group footer, which I'm guessing must be what you did.

-LB

 

[AKPolarBear]

It did, along with all the other item numbers in the group.

I worked out a solution using a subreport. It does the job nicely, but it runs much slower.