Print Page | Close Window

Suppressing a Section

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=11802
Printed Date: 01 May 2024 at 5:15am


Topic: Suppressing a Section
Posted By: riker
Subject: Suppressing a Section
Date Posted: 07 Dec 2010 at 8:22am

I'm hoping to get some help from the experts on here.

 
I'm very new to Crystal Reports and might be trying to accomplish something that just isn't possible.

 

Using Crystal XI I'm trying to write a report that will give us a list of parts that we haven't used in a pre-determined amount of time. I've collected all the data I believe is necessary, and just need to figure out how to exclude the parts we have used.

 

I thought I would be able to do this by suppressing sections based on the most recent dates for transactions. The problem I'm running into is it seems to only suppress the section based on 1 date not 2 like I need it to.

 

I created a Running Total Field based on the max date for each type of transaction. Then in the Section Expert I entered a formula like this.

 

{#MaxDateSTK-MTL} > CurrentDate -730

 

And that seems to work good. But when I change it to...

 

{#MaxDateSTK-MTL} > CurrentDate -730

     or

{#MaxDatePUR-STK} > CurrentDate -730

 

It doesn't work for both dates.

 
Maybe it can't be done the way I'm trying to do it.
 
Any and all help on this would be greatly appreciated!


Replies:
Posted By: c16271
Date Posted: 07 Dec 2010 at 9:01am
Are both of the summary fields for two different parts?

{#MaxDateSTK-MTL} and {#MaxDatePUR-STK}

If so, couldn't you group those records together?

Posted By: DBlank
Date Posted: 07 Dec 2010 at 9:02am
can explain your report design a little more or post some sample data and how you want it to look in the end.
...I am surprised you are having luck using the RT to suppress unless it is suppressing footers.

Posted By: riker
Date Posted: 07 Dec 2010 at 9:09am
Thanks for the reply c16271!
 
They are both for the same part, but different transaction types.
 
STK-MTL is the trans for using the part on a job.
PUR-STK is the trans for purchases.
 
When I group them I miss parts that have recently been purchased but not used.

Posted By: riker
Date Posted: 07 Dec 2010 at 9:47am
Originally posted by DBlank

can explain your report design a little more or post some sample data and how you want it to look in the end.
...I am surprised you are having luck using the RT to suppress unless it is suppressing footers.
 
I'll do best to clarify...
 
IMO only 3 fields matter when it comes to figuring out the last time we used something.
 
Part Number
Transaction Type
Transaction Date
 
I grouped to data by Part Number then Tran Type and finally Date. I then created the Running Total Fields based on each transaction types max date.
 
The section I'm trying to suppress is the Group Header and the only other data in the header is costing info.
 
I hope this helps. I'm still thinking I'm going about it wrong...

Posted By: DBlank
Date Posted: 07 Dec 2010 at 9:59am
So you want a list of anything (part number) not used in the last 2 years.
What is your sample data under 1 part number that is the transaction type that defines what youa re looking at (basically your RTs per group).

Posted By: riker
Date Posted: 07 Dec 2010 at 10:23am
Exactly what I'm looking to do.
 
partnum
000530003
STK-MTL
8/13/2010
PUR-INS
4/25/2008
ADJ-QTY
10/25/2008
 
Here is a sample of the data I get in my groups, I then Hide GH2 and GH3 leaving just GH1 that includes my costing data.
 
The RTs are based on the Max date for each tran type. I put them in GF1, which is then hidden.  The RTs are told to Evaluate and Reset on the change of Group#2.
 
Hope this is what you were asking for.

Posted By: DBlank
Date Posted: 07 Dec 2010 at 10:29am
If I understand you correctly:
Group on PartNUM only
create a formula field to flag these called @flag
if table.transtype in ['STK-MTL','PUR-STK'] then table.datefield
insert a SUmmary (sigma sign) as the MAXIMUM of @Flag at the Partnum group
go into the select expert and expand it.
flip it to group selection and enter your criteria here:
MAXIMUM( mailto:%7b@flag%7d,%7btable.partnum - {@flag},{table.partnum }) <dateadd('yyyy',-2,currentdate)

Posted By: DBlank
Date Posted: 07 Dec 2010 at 10:31am
make sure to flip the group select criteria option to use 'Default values for nulls' in case you have any part numbers with no details rows

Posted By: riker
Date Posted: 08 Dec 2010 at 3:20am
Worked great! Thank you very much DBlank!
 
I'm hoping to get some clarification on a couple things. Because like I said I'm new to Crystal and report writing in general.
 
In the line...
 MAXIMUM( mailto:%7B@flag%7D,%7Btable.partnum - {@flag},{table.partnum }) <dateadd('yyyy',-2,currentdate)
You show ('yyyy',-2,currentdate) but I would like to be able to use how many months since the last use instead of years.
I tried..
('mm',-24,currentdate) but that didn't work.
I'd also like to have the user prompted for how many months they want to go back. So I added a Parameter Field for number of months back. When I put the PF in the formula should it look like this?
MAXIMUM( mailto:%7B@flag%7D,%7Btable.partnum - {@flag},{table.partnum }) <dateadd('mm',{?fieldname},currentdate)?
 
I hope that makes since :)

Posted By: DBlank
Date Posted: 08 Dec 2010 at 4:01am
month is indicated as 'm'
make sure your parameter is set to numeric data type
 
MAXIMUM( mailto:%7B@flag%7D,%7Btable.partnum - {@flag},{table.partnum }) <dateadd('m',{?parameter},currentdate)?
 
for clarity for your future reports, this process is buildingt he groups and the group values and then excluding the groups. If you look int he group tree you will still see all the original groups (the tree is built before the group select criteria is applied). Also group select criteria can only be on group summaries that can be created using the insert summary function (not running totals). Running totals do not work in group headers because they do not exist at that point in time.

Posted By: riker
Date Posted: 08 Dec 2010 at 8:36am

That worked thanks!

I hope you don't mind a couple more simple questions
 
First, I'd like to display the last used date. I can get this by displaying the Max @flag field but that leaves me blanks for the parts with no dates. When exported to excel they show up as 01/00/1900. Which is fine for when we do export it, but when printed out of crystal they are all blank. I tried a formula like this...
 
if isnull (Maximum ( mailto:%7b@@flag - {@@flag }, {parttran.partnum})) = true then
"xxx" else
Maximum ( mailto:%7b@@flag - {@@flag }, {parttran.partnum})
 
I was hoping to put something in the nulls like "never" but it doesn't like that. Am I on the right path here?
 
Second, I mentioned the costing data I have. It's so we can tell how much value this old inventory is worth. I bring in the current on hand QTY and have a formula adding the material, labor , etc... to give me the unit price. I then have a formula multiplying them together for my on hand value. When I then summarize the on hand value to get my report total I get the total for all detail lines returned instead of just what is displayed. We know we have a lot of old inventory but not 1.5 trillion worth! lol
 
Any help is greatly appreciated!

Posted By: DBlank
Date Posted: 08 Dec 2010 at 10:01am
1. You can convert the item to text if you want:
if isnull ((Maximum ( mailto:%7b@flag - {@flag }, {parttran.partnum}))) then "Never" else
totext((Maximum ( mailto:%7b@flag - {@flag }, {parttran.partnum})),"M/d/yy")
 
2. Sums always give you all rows, not displayed data, unless you create formulas (usually using variables)or RT's to handle the duplication.
I would have to see your row level data and your formula to help you fix that, but the concept is that you can use an RT with specific evalaution criteria to avoid the duplication of the numbers. Or you can try and convince the higher ups that your report is correct and the storage wharehouse is larger than they think and they need to have a really big sale.


Print Page | Close Window