How Do I Show Records Based On A Randomly Selected Day? 1

[Sange]

I'm currently using CRv10 and one of my reports is scheduled to run on the 3rd day of each month. The date is based on records created in the previous month ie: {DATE.FIELD} = LASTFULLMONTH.

I've now been asked whether it is possible to randomly select a number and then only display records created on the same day as this number and I'm just wondering whether this is possible. I thought I could create a formula that will generate a number between 1 and 30 (obviously it will depend on the total number of days in the previous month) and then suppress any records with a date that doesn't match that number but not having much luck.

For Example:

If the randomly generated number is 8 then only records created on the 8th of the previous month will be displayed however each time the report runs, a completely different number is generated.

Any assistance would be most appreciated.

Thanks.

 

[lbass]

Insert a group on the date field, on change of day. Then create a formula {@random}:

rnd()

Place this in the detail section and insert a minimum on it at the group level, and then go to report->group sort->all and add minimum of {@random} as your group sort field.

Then go into the section expert and for each of the group header, details, and group footer sections->suppress->x+2 and enter:

groupnumber > 1

-LB

 

[Sange]

Hi lbass,

Once again you've been helpful to me. Your solution is definitely bringing back a random number however once I put in all the suppressing I only end up with one record for the selected date when there should definitely be more than one.

Is it possible I've been a bit overzealous in my suppressing?

 

[lbass]

Please explain exactly how you implemented my suggestion. I tested this, and it returned a group of records, not just one.

-LB

 

[Sange]

Hi again,

I'm wondering whether the additional groups I have had to add to the report are causing this.

I inserted a group based on the creator date "for each day" which is in ascending order. I then created the random formula as you suggested and added this to my details section. A minimum of this forumula was added at the Group 1 level and I then changed the group sort to "All" based on "Min of @Random". I placed a conditional suppress in the Group Header, Footer and details sections ie. groupnumber > 1.

At this stage the report is working beautifully. However I then had to add additional groups to distinguish the different users as some of them had the same last names. As a result I added Group 2 for Last Name, Group 3 for First Name and then Group 4 for User Id - the details section displayed the records each user had created.

I then thought I should add the suppress condition to these additional groups but this is where it appears to fall over. I realise it's the extra groups causing this but I'm not sure how to address this. Will your suggestion still work with additional groups added and if so, what should I be doing to ensure all records created on the randomly selected date are returned because I'm clearly doing something very wrong at the moment

Thanks.

 

[lbass]

The groupnumber changes when there are multiple groups, so instead, create a formula like the following to place in the group #1 header only:

//{@grpno}:
whileprintingrecords;
numbervar grpno := grpno + 1;

Then use the following suppression formula in the section expert for all groups/detail sections:

{@grpno} > 1

-LB

 

[Sange]

Still no luck I'm afraid. I tried the formula and it's now suppressing everything below group header # 1.

 

[lbass]

Sorry, I should have tested this. By referencing the formula instead of the variable, the formula was executing in other group sections. Replace all section expert suppression formulas (for each group and detail section) with:

whileprintingrecords;
numbervar grpno;
grpno > 1

This worked when I tested it.

-LB

 

[Sange]

It worked! Thank you so much. Your efforts have been most appreciated. Sandra