Global random generation of groups

[lauriesamh]

Hi, I'm using CR10 and have a report grouped as follows

GR#1 Store ID
GR#2 employee ID
GR#2 Txn
Details (multiple lines per txn)

I need to find a percentage random sample of the txns over the entire report (each store & employee). I can't change the grouping of the report though.
I created a field @Random Txn = Rnd() and placed it in GR#2 header. I also have a running total of each Txn. @RTotal0.

I used a supression formula in the detail:

{#RTotal0} > ({?Show X Percent of Claims}/100) * DistinctCount ({table.TXN_ID})


It pulls the correct percentage of TXN correctly but it is only pulls from the first or second stores and employees. Is there a way I could pull randomly say 2% of total Txn across all stores & employee's txn without changing the base grouping and sort of the report?

 

[Charliy]

Do it in a subreport in either the Report Header or Report Footer

 

[lbass]

Insert a subreport in the report header that uses a formula {@concat} in the detail section that concatenates the store ID and employee ID field (no groups):

totext({table.storeID},"0000")+ " " + totext({table.employeeID},"000") //add zeros for the maximum
//length for each of the ID fields

Check database->select distinct records. Then add a formula {@rnd}:

rnd()

...to report->sort records.

Then create two formulas:

//{@accum} for the detail section:
whileprintingrecords;
shared stringvar x;
if recordnumber > .2*distinctcount({@concat}) then
x := x + {@concat} + "^";

//{@suppress} for the subreport report footer:
whileprintingrecords;
shared stringvar x;

Then in the main report, go into the section expert->GH#2->suppress->x+2 and enter:

whileprintingrecords;
shared stringvar x;
totext({table.storeID},"0000")+ " " + totext({table.employeeID},"000") in x

So that the sub won't show, you can suppress all sections within the subreport, format the subreport to "suppress blank subreport", and also format the report header section to "suppress blank subreport". Do not suppress the repoert header or suppress the subreport object.

-LB

 

[lauriesamh]

Questions:
The 2% is an example and part of a parameter field. So I assume I just pass that from the main report?

Why would you not concat Store, Employee and TXN ID? Just curious..

 

[lauriesamh]

LB,
Hi I tried this but am having problems I'm getting an error "A String can be at most 65534 characters long" I'm guessing it has to do with the fact in the subreport the a transaction may have many detail lines. The subreport selection criteria pulls information on the detail line.

Subreport selection criteria;

{TxnLine.CAP_IND} <> "Y" and
{CMC_GRGR_GROUP.GRGR_ID} = {?LOB } and
{TxnLine.IPCD_ID} = {?Code} and
{Txn Header.CLCL_PAID_DT} in {?StartDate} to {?EndDate} and
{TxnLine.CUR_STS} = "02

 

[lbass]

What IS the txn? I thought you just had two groups, and that you were merely placing that field in group header #2. Was that a typo in your first post?

Did you use select distinct in the subreport? How many different combinations of storeID and employeeID are there? What is the datatype of each ID field?

-LB

 

[lauriesamh]

LB,
I have 3 groups (Store, Employee ID, and TXN).
There is the txn which is a sale and each sale can have many detail lines 12+. The Store, Employee ID and TXN (sale) are each string (length 12). Yes I selected distinct but the report has to pull the detail of each sale so the distinct is much larger due to this. Combinations of storeID and Employee ID are very numerous Stores (40+) and employees (128+).

Thanks very much!!!!!

 

[lbass]

Can you show a sample of the Txn field? Is this field a unique ID field, i.e., it is never the same value twice?

-LB

 

[lauriesamh]

LB,
Yes the TXN is a unique code example: 0008001087, but note that the TXN can have several lines,
example:
0008001087 01
0008001087 02
etc...

Report is grouped
GR1 STORE ID
GR2 Employee ID
GR3 TXN (Sale)
detail - lines of sale

I hope this helps and thanks!

 

[lbass]

In the subreport then, ONLY add the txn field (no sales fields), and choose "select distinct".

Then add a formula {@rnd}:

rnd()

...to report->sort records.

Then create two formulas:

//{@accum} for the sub detail section:
whileprintingrecords;
shared stringvar x;
if recordnumber > .2*distinctcount({table.txn}) then
x := x + {table.txn} + "^";

//{@suppress} for the subreport report footer:
whileprintingrecords;
shared stringvar x;

Then in the main report, go into the section expert->GH#3->suppress->x+2 and enter:

whileprintingrecords;
shared stringvar x;
{table.txn} in x

So that the sub won't show, you can suppress all sections within the subreport, format the subreport to "suppress blank subreport", and also format the report header section to "suppress blank subreport". Do not suppress the report header or suppress the subreport object.

-LB

 

[lauriesamh]

LB, I think it is close... still having the max string error due to the fact that it is accumulating the txn ID (string 12) and the number of sales is over 9000 so if I select a 2% sample then the accume exceeds easily the string max.

 

[lauriesamh]

LB,
I'm trying something with the @accum and still having errors.. would something along this line work?

whileprintingrecords;
shared stringvar x;
shared stringvar x2;
//if recordnumber > ({?Show X Percent of TXN}/100)*distinctcount({table.TXN_ID}) and then
//x := x+{table.TXN_ID} + "^";

if (recordnumber > ({?Show X Percent of TXN}/100)*distinctcount({table.TXN_ID}) and
len (x + {table.TXN_ID} + "^")<65534) then
x := x + {table.TXN_ID} + "^";

if (recordnumber > ({?Show X Percent of TXN}/100)*distinctcount({table.TXN_ID}) and
len (x + {table.TXN_ID} + "^")>65534) then
if (recordnumber > ({?Show X Percent of TXN}/100)*distinctcount({table.TXN_ID}) and
len (x2 + {table.TXN_ID} + "^")<65534) then
x:=x2+{table.TXN_ID} + "^";