Help using the Not operator 1

[GPowers]

I am using CR 8.5. Trying to generate a report using the not operator and getting erroneous information. I need the report to list all machine names that do not have a particular software on it. I am using the (Not(PCSOFT.Sftfileid) in [8420,8421,8422]). I am grouping the report by machine name. The PCSOFT.Sftfileid field list all software on each machine.

Any help would be appreciated.

 

[pgtek]

hi
Just click on the report select expert
select your {PCSOFT.Sftfileid}field
and from the filter select from the pick list the
is not one of
and type your number in there or pick from the list
Taht will gileter out the computer
thats does not have the number you select in the criteria

cheers

pgtek

 

[lbass]

Your select statement will return all RECORDS except those with {PCSOFT.Sftfileid} in [8420,8421,8422]. This means that COMPUTERS with these codes could still be selected because they have other software that does meet the selection criteria.

Instead, do not use record selection on this field, but group on {table.computerID} and then write a formula {@soft} for the details section:

if {PCSOFT.Sftfileid} in [8420,8421,8422] then 1 else 0

Then go to report->edit selection formula->GROUP and enter:

sum({@soft},{table.computerID}) = 0

Your report will then display only those computers which do not have the specified software.

-LB

 

[GPowers]

Lbass,

Your suggestion worked! Thanks.

I am also grouping by facility and business unit. I need to get the count for each facility and each business unit. If I have 15 machines at a facility that meet the criteria you suggested, then I need the facility group count of 15.

Any suggestions?

 

[lbass]

Once you have used a group select, you need to use running totals to do counts. Use the running total editor, and select {table.computerID}, distinct count, evaluate for each record, reset on change of Group (Facility). For the the Business Unit level, do a reset on change of Group(Business Unit). These running totals must be placed in the corresponding group footer (not header).

-LB

 

[GPowers]

LBass,
Your sugestions have worked great. This report continues to be expanded. I now need to have the Hardware Type field totals by business unit. In other words I need to count the number of laptops per business unit and the number of desktops per business unit. The following is what I have now.
Assigned_User SerialNnumber Date Status HW_Type
xxxxxxx 12345 102003 Active Laptop
YYYYYYY 23456 102003 Active Laptop
ZZZZZZZ 34567 102003 Active Desktop

Total For This Facility: 3
Total For This Business Unit: 3
Laptops: 2
Desktops: 1
Total For This Coalition: 3

There are approximately 80 facilities and 8 coalitions.

When I attempt to add the Laptop total, I am getting the total for all machines for the business unit. Can I still use the running total or do I need to use a subreport to get the total laptop/desktop for each business unit?

 

[dgillz]

Create a formula:

If {HW_Type} = "Laptop" then 1 else 0.

Then perform a simple sum on this formula field.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[lbass]

Is the problem that in your above example your results are laptops = 3? What are your groups--Coalition = Grp 1, Facilities = Grp 2, and Business Unit = Grp 3? You show sample results--where are the results displayed? Are you suppressing details?

Yes, I think you can use running totals, but I need to know a little more about your overall design. Probably can't get back to you for several hours though.

If you are also grouping on {table.HW_type} you should be able to reset on change of that group to get the correct results in the HW_type group footer.

-LB

 

[GPowers]

LBass,

Grp 1 = Coalition
Grp 2 = Business Unit
Grp 3 = Facility
Grp 4 = SerialNumber and I am using group 4 as my detail line.
I am wanting to total the number of Laptops by the business unit or grp 2 and total the number of Desktops by business unit or grp 2.
I am trying to use the running total on the serial number. I am evaluating using a formula "if{UDEFINE.U_HWType} = "Laptop" then 1 else 0".
I am also resetting on change of group business unit.
The result is 0.

 

[bdreed35]

Use dgillz suggestion. Don't use it in a running total. There is no need to.

Create a new formula and add this line from dgillz's post:

If {HW_Type} = "Laptop" then 1 else 0

Next, go to the Insert->Summary, choose your formula name in the second drop down list, then choose SUM in the first drop down list, and then choose your Business Unit db field in the last drop down list.

When you click OK the sum field will be added to the Group Footer for the Business Unit group.

~Brian

 

[lbass]

dgillz and Brian,

I think running totals are necessary since the report is using a group select.

GPowers,

For the evaluation formula in the running total, try using:

{UDEFINE.U_HWType} = "Laptop" //or "Desktop" for the other running total

...with the reset on change of Business. This must then be placed in the group 2 (Business) footer.

-LB

 

[bdreed35]

lbass*-

You are exactly right regarding the need for running totals. I stand corrected.

~Brian

 

[GPowers]

LBass,

Your suggestion worked! Thanks very much for your input. And thanks to the others for their information.