Need to suppress a portion of total unsuppressed groups

[ScorpioMCN]

This might sound easy however I'm using a bunch of criteria (incl. a shared subreport value) to determine which items (based on their ABC code and last date counted) get picked for cycle counts in the current week. The report totally works great however being that there are 20 weeks left in the year I don't want them to get 100+ page report with 2,400 items to count this week (should be no more than 150 items per week). I have a formula that figures out there's xx weeks left in the rest of the year however how do I make the group footer suppress 19/20ths of the final results ??

I tried setting up manual running total formulas to count the total picked items only (ie footers that are NOT suppressed) and then take a portion if the running total (separate formula) exceeds total # of items picked / # of weeks) it then is SUPPOSED to supress those group footers. However it ends up suppressing ALL but 4 items...either I have the formulas in the wrong place and/or there is an evaluation that needs to happen first - I have tried a bunch of things to no avail.

I am an advanced user and have checked all prior posts for a similar situation but this one has me really stumped - just got a brain freeze. Any help would be greatly appreciated!

 

[dpatrickcollins]

Can you qualify what portion you need to suppress? For example, any portion so long as it is 19/20ths of the total? Please qualify the portion that needs to be suppressed.

 

[ScorpioMCN]

Here's my dilemma:

The "total" I'm speaking of is not the total records selected but rather the unsupressed records based on specific criteria. Now I need to suppress an additional 19/20ths of those unsupressed records. Not sure if this is possible but I would think there would be a way. Would you mind taking a look at my report?

 

[lbass]

The description of what you are trying to do is too abstract, and I for one am confused. It also doesn't seem very systematic to suppress 19/20 of the results, since as the year progresses, you probably will want a different percentage displayed. How about instead explaining what it is you would like the user to see? It would help to know your group structure, and to see the contents of any formulas you reference.

-LB

 

[ScorpioMCN]

No I gave 19/20ths as an example only - I already have a built-in formula that calculates the correct ratio for any given week eg if I ran it today it would calculate 19 weeks left in the year (therefore I would like to suppress 18/19ths of the final results)...I group on Item No and show only the Group Footer based on conditional suppression formula. What I'm saying is I want it to show only 1/19th of the results of the suppression. The formulas are just simple running total counters but like I said they aren't working as I had hoped.

 

[lbass]

You need to share the contents of the formula you are using and explain exactly where you are using it (and if a running total, how it is set up), along with a description of your report structure. You cannot really get help without being more specific.

-LB

 

[ScorpioMCN]

Hmmm I thought I was pretty specific in my responses - did you read my original question thoroughly? I don't know how much more specific I can get but I will post more info (incl. formula content & placement) here a bit later when I get into work. Anyways I do appreciate the help - I'll be back.

 

[lbass]

Yes, I did read it thoroughly, but you didn't share the formula, your report structure, or the placement of the formula, etc.

-LB

 

[ScorpioMCN]

Here are my formulas to attempt to limit the records to just one week of counts...I guess to summarize this I'm basically asking Crystal to do a "3rd pass" by evaluating the results of the first criteria FIRST and then cutting that down to one week's worth of counts rather than the entire list of parts...

@U count
//placed in Item_No Group Footer b
WhilePrintingRecords;
EvaluateAfter({@Pick 2});
NumberVar UCount;
if {@Pick 2}=1 then UCount := UCount + 1 else UCount := UCount

@U123
//also in Group Footer b
WhilePrintingRecords;
EvaluateAfter({@Pick 2});
EvaluateAfter({@Total count});

NumberVar U123;
if {@Pick 2}=1 then U123 := U123 + 1 else U123 := U123

@Total count
//placed in Report Footer
WhilePrintingRecords;
NumberVar UCount;
TCount := UCount;
TCount;

@Pick 3
//placed in Item_No Group Footer b
WhilePrintingRecords;
EvaluateAfter({@Total count});
NumberVar UCount;
NumberVar U123;
if {@Pick 2}=0 then 0 else
if TCount>0 and U123 <= (UCount / {@Time Remaining-rounded}) then 1 else 0

@Pick 2
//placed in same Group Footer b
WhilePrintingRecords;
EvaluateAfter({@Pick 1b});
if {@Pick 1b}=1 AND (sum({@TransCount},{Item.No_})<>0 OR sum({Item_Ledger_Entry.Quantity},{Item.No_})<>0) then 1 else 0
//Pick 2 formula works perfectly - it picks only the items I want however it's ALL items and I want to cut it down to just one week's worth

Group Footer c (the only section that shows) is where I have this conditional suppression formula:
WhilePrintingRecords;
EvaluateAfter({@Total count});
{@Pick 3}=0

These formulas below work perfect - just copying them here fyi
@Time Remaining
//I show the user this in the report header
if {?Type}="Weekly" then {@BusinessDaysExcludingHolidays}/5 else {@BusinessDaysExcludingHolidays}

@Time Remaining-rounded
//used in the formulas above
Round({@Time Remaining},0)

@BusinessDaysExcludingHolidays
//from Ken Hamady's tips>

http://www.kenhamady.com/form01.html

//WhileReadingRecords;
EvaluateAfter({@Holidays});
Local DateVar Start := CurrentDate; // place your Starting Date here
Local DateVar End := Date(Year(CurrentDate),12,31); // place your Ending Date here
Local NumberVar Weeks;
Local NumberVar Days;
Local Numbervar Hol;
DateVar Array Holidays;

Weeks:= (Truncate (End - dayofWeek(End) + 1
- (Start - dayofWeek(Start) + 1)) /7 ) * 5;
Days := DayOfWeek(End) - DayOfWeek(Start) + 1 +
(if DayOfWeek(Start) = 1 then -1 else 0) +
(if DayOfWeek(End) = 7 then -1 else 0);

Local NumberVar i;
For i := 1 to Count (Holidays)
do (if Holidays in start to end then Hol:=Hol+1 );

Weeks + Days - Hol

@Holidays
//Holiday Listing formula to go into the report header of the report.
//BeforeReadingRecords;
DateVar Array Holidays := [
Date (2006,9,4),
Date (2006,11,23),
Date (2006,11,24),
Date (2006,12,25),

Date (2007,1,1),
Date (2007,2,19),
Date (2007,5,28),
Date (2007,7,4),
Date (2007,9,3),
Date (2007,11,22),
Date (2007,11,23),
Date (2007,12,25),

Date (2008,1,1),
Date (2008,5,26),
Date (2008,7,3),
Date (2008,7,4),
Date (2008,9,1),
Date (2008,11,27),
Date (2008,11,28),
Date (2008,12,25)

];
0

 

[ScorpioMCN]

this formula was missing a line ... but that was already in there - just missed it in copying

@Total count
//placed in Report Footer
WhilePrintingRecords;
NumberVar TCount
NumberVar UCount;
TCount := UCount;
TCount;

 

[lbass]

This is really hard to follow, but note that your {@TotalCount} formula if placed in the report footer, cannot be accessed in a group footer above it. Also, I don't know what this means:

"//Pick 2 formula works perfectly - it picks only the items I want however it's ALL items and I want to cut it down to just one week's worth"

Are you saying you want the count to only reflect one week of data? If so, then you should set up a variable for that particular week.

Have you thought about whether you could eliminate some of these variables and use inserted summaries on conditional formulas instead? This would allow you to access a total count for use in a formula before the report footer.

-LB

 

[ScorpioMCN]

You're not following me - I'm not selecting a particular week of history (that would be easy) - I'm pulling parts that are due for cycle counting based on specific criteria (this part I have working just fine - that's why I noted that on the Pick 2 formula). My problem is>> THEN I only want to show a PORTION of that final list to the stockroom - ie one week of parts instead of ALL parts that need counting (ie 1/19th of ONLY the parts the report picks).

 

[ScorpioMCN]

I can't use traditional summaries because all these parts are determined WHILE PRINTING RECORDS so I have to use running totals...ie I have to know the TOTAL # of parts picked FIRST to determine the portion I want to show...if the total turns out to be 3000 then I want to show only 158 parts (Total/Weeks left in year = 3000/19)

 

[lbass]

Well, I agree I am not following totally, but if you think that being able to use the report total in an earlier formula is your primary issue, then try a subreport in the report header that returns the total count and set it equal to a shared variable so you can access it later. You can suppress all sections within this subreport so that it does not appear.

-LB

 

[ScorpioMCN]

That's why I used the EvaluateAfter command in my formula - so you're saying that it is never possible to grab a result from the Report Footer even using EvaluateAfter ?? Since this is all whileprinting I don't see why not (??).

I can't run it as a subreport in the header to grab the total because it already contains a subreport to determine the last count date (ie a subreport within a subreport is not permitted).

 

[Jcfx]

You have 3000+ parts to count in a year, you are using this report to comeup with how many parts you have left uncounted yet this year. Then you want to take that remainder divide it by the weeks remaining, and display a report that shows the first 150 some parts that they should count this week. <not even getting into if you count by bin location etc>

I don't know what database you are using, but it seems like you are expecting the report writer to do all the processing work that a stored procedure <or equivalent> could be doing. Unless you have a db admin that will not work with you?

Julie
CRXI CE10 / RS2005 Sql DB

 

[ScorpioMCN]

Actually we have more than 10,000 different items to count in a year!! this is just one stockroom and only those that are due for counting at any given time...but I'm almost there - the report I've written works and pulls just the items I want - I just need to get the counters to work to print a daily or weekly list. Never had this problem before with any other report - always found a workaround.

Any help would be appreciated - I would even email the report to anyone who thinks they can help.

 

[ScorpioMCN]

correction - not all of those 10,000 will be counted - only the ones that have on-hand qty OR that have been active in the last 30 days.

 

[lbass]

Yes, I'm saying that evaluateafter will not force an even later pass (at least I've never seen that work). I think it just orders the evaluation within the same pass if two formulas are in the same section and one should be evaluated after the first.

-LB

 

[ScorpioMCN]

Well that's all I need is the other formulas to evaluate first (incl. the counter total)...I don't actually need a 3rd pass unless the total count can't be done first - then I'm SOL I guess.