Filtering Report Based on Sum of Field 2

[longlam]

Hello All,

I'm trying to figure out how to filter rows out of a report where the sum of a field is less than 50.

Thanks.

 

[longlam]

Forgot to mention that I'm working on CR XI and using cross tabs.

 

[IdoMillet]

Report, Selection Formulas, Group...

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[longlam]

Would the group function be

sum(item_count) > 50

If you group in this way it will filter out those that are less than 50?

 

[longlam]

Also, I am using cross tabs so I don't know if the grouping option will work out for me.

 

[IdoMillet]

Describe how you are grouping and what you are trying to achiebve in more detail.

Good chance that the formula should be:
sum({Field_to_Sum}, {Group_By_Field}) > 50

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[longlam]

What I'm trying to create is a sales report for the last 9 weeks. So far for the rows I have the item information. For the columns I have the dates grouped by week. I have filtered everything so that it only gives me sales data for the last 9 weeks. For the summarized total I am using the total count of sold items. What I'd like to do filter the cross tab so that it only shows items that have a total sales count of over 50. I don't really want to do any type of grouping of the information so that is what is confusing me.

 

[IdoMillet]

You must group by Item if you wish to sum & filter by Item (unless you use a helper View, or a command with a subselect).

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

You need to insert a group on item. If you don't want your main report organized that way, then insert a subreport that has an item group and then insert your crosstab in the subreport. Then create a formula {@null} by going to field explorer->formula->new, naming it {@null} and opening and closing the formula area without entering anything. Then create two additional formulas:

//{@summaryfield} to be added as the summary, with sum as the summary (not count):
if count({table.sales},{table.item}) > 50 then
1 else tonumber({@null})

//{@label} to be used as the row field:
if count({table.sales},{table.item}) > 50 then
{table.item} else {@null}

Then go to the customize style tab->check "suppress empty rows"->check "suppress empty columns".

-LB

 

[n2nuk]

Hi Lbass

I have a similar report to produce, I need to list all staff that have had more than 12 days sickness absence in the last 12 months.

I am using CRXI and inserted a group on employee_ref and added a sum formula in the employee_ref group footer to display the total number of days absent. The database provides a totaldays column that lists the number of days per absent instance, my formula looks like this:

Sum({ABSENCE.TOTALDAYS}, {ABSENCE.EMPLOY_REF})

Adding the '>=12' filter at the end of the formula turns the response from a sum into a true/false statement.

I dont need to produce a cross tab but would appreciate any assistance how to filter the list based on the formula.

Apologies if I seem to be hijacking this thread but am sure the answer will help us all!!

Many thanks

Naz

 

[lbass]

Yes, it should have been a new thread. All you need to do is add the ">= 12" at the end of your summary formula in the group selection formula area (report->selection formula->GROUP). If you need to then do any summaries across employees, you will need to use a running total, since non-group selected records contribute to inserted summaries.

-LB

 

[longlam]

I'm still unsure what you mean by creating a subreport with an item group. What item group are you referring to in this scenerio. You then tell me to add a crosstab report on the same subreport? How would this work? And then what do I do in the main report to put this all together. Sorry I am not an advanced crystal reports user so if you can make it clear for me or if you have a demo file you can send me that would be very helpful.

Thanks,
Long

 

[lbass]

To get the results you want, you need to have a group on item, but you said you didn't want your main report to be grouped by item--so I suggested inserting a subreport in which you can insert a group on item. Then you can create the formulas I mentioned in my previous post to use in the crosstab. The formulas are necessary for the filtering by count that you want to do. Insert the crosstab in the report header of the subreport and suppress all other subreport sections. The subreport could be placed in the report header of report footer of the main report.

-LB

 

[longlam]

What should the item group look like? Should I just group by item id?

Thanks for keeping up with my questions.

 

[longlam]

Just finished it up thanks. It's funny that you have create this item grouping though just so you can create the formula fields correctly.