Record Selection

[almaler]

I have a report that I am putting together about desktop PCs. I am pulling the information out of a SQL db and the end result is a graph that says how many PCs are "locked down" and how many PCs are "unlocked".

In order to find out whether the PC is locked down, I have to evaluate what is contained in the SMX_Local_Admins_DATA.Account00 field. I created the Record Status formula below for this purpose:

if not ({SMX_Local_Admins_DATA.Account00} like '*desktop"' OR {SMX_Local_Admins_DATA.Account00} like '*security"' OR {SMX_Local_Admins_DATA.Account00} like '*admin"')
then toNumber(1)
else toNumber(0)

So, if there is something other than the three accounts above, a number 1 is generated for that record. So, for each record, 1 equals Locked and 0 equals Unlocked.

So, here is what this looks like on the report:

MACHINE NAME
Test Machine
Record Status SMX_Local_Admins_DATA.Account00

1.00 \\test machine\root\scott
0.00 \\test machine\root\admin
0.00 \\test machine\root\desktop
0.00 \\test machine\root\security

Running Total = 1.00


I have included a Running Total field that calculates the total number. The next formula I have for evaluating the status of the overall PC, not each record is called Computer Status:

if {#RTotal0} >=1 then "Unlocked"
else "Locked"

MACHINE NAME COMPUTER STATUS

Test Machine Unlocked

Record Status SMX_Local_Admins_DATA.Account00

1.00 \\test machine\root\scott
0.00 \\test machine\root\admin
0.00 \\test machine\root\desktop
0.00 \\test machine\root\security

Running Total = 1.00


So, at this point I know if each PC is locked or unlocked which is what I need. Now, my problem is that I cannot group by the Computer Status formula field in order to list all of the locked PCS and all of the unlocked PCs. Where do I go from here?

 

[almaler]

I went to File -> Report Options and the convert null values to default was not checked.

Also, please explain the links between the two tables and the command.

Currently, I have the following tables:

Command
=======
Cnt
Name0

Computer_System_Data
====================
Machine ID
Name0

SMX_Local_Admins_Data
=====================
Machine ID
Account00

Here are the links:

Command.Name0 --> Computer_System_DATA.Name0 (left outer - enforce both - =)
Computer_System_DATA.MachineID --> SMX_Local_Admins_DATA.MachineID (Inner Join - not enforced - =)

All machines are showing as Unlocked and there is not a hidden group.

When I insert the Command.Cnt field into the report there are only two values returned. 2779 and 5558. I've noticed that all of the machines where the Command.Cnt field is 5558 have each Account00 listed twice and I'm not sure why.

 

[lbass]

You have the link wrong. Should be:

Computer_System_DATA.Name0 ->Command.Name0 (left outer - enforce both - =)

Computer_System_DATA.MachineID --> SMX_Local_Admins_DATA.MachineID (Inner Join - not enforced - =)

-LB

 

[almaler]

I changed the link. I am still getting the same results, but the report takes FOREVER to run now.

 

[lbass]

I can't really troubleshoot this from a distance. I think you should lay out the {SMX_Local_Admins_DATA.Account00} field and other fields in the detail section, along with {command.cnt} to see where the error is occurring. It could be that all machines have some value other than the three in the formula, for example. Or maybe some of the accounts don't contain "root\".

Otherwise, as I said earlier, maybe you should go back to my first suggestion and use running totals for charting, following the guidelines in the Business Objects paper "charting on print-time formulas".

-LB

 

[almaler]

OK - thank you for all of your help!!