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?

 

[lbass]

First you don't need tonumber() around a number:

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 1 else 0

Place this formula (Let's call it {@locked}) in the detail section. There is no need for a running total. Right click on the formula and insert a sum on it. Then go to report->group sort and choose "sum of {@locked}" as the sort field. This will cluster all locked PCs together, followed by all unlocked PCs.

Then create a second formula:

if sum({@locked},{table.machine}) >= 1 then "Locked" else "Unlocked"

Place this in the group section or in the detail section. I'm unclear on your criteria. Your second formula seems backwards to me. If one or more accounts are locked, I think you would want to call the machine locked. If this isn't the case, I think you need to change the test.

There is a way of creating a faux group header/footer for these clusters. Let me know if you want to do that. Also, please identify your version of CR. You could also use a SQL expression or command to do this test, depending on your version.

-LB

 

[almaler]

I am using Crystal Reports 10. I have implemented all of your suggestions and I am getting the desired results. Let me try to explain my criteria better. If any other administrative account exists other than these three (desktop, security, admin), the machine is considered unlocked because someone who is not authorized to have administrative rights on that PC has administrative rights.

So, now I need to insert the graph. This is the part I am having trouble with. I need to show the total number of locked and unlocked PCs, but I can't figure out how to do it. I also need to show the total number of PCs, but when I try to insert a summary on the group I am unable to.

 

[lbass]

For charting purposes, I think you'd be better off returning a summary to your report by using a command. Go to database->database expert->your datasource->add command and enter something like the following (this assumes you are working with one table:

SELECT
count(SMX_Local_Admins_DATA.`Machine_Name`) as Cnt, SMX_Local_Admins_DATA.`Machine_Name`
FROM
`SMX_Local_Admins_DATA` SMX_Local_Admins_DATA
WHERE
mid(SMX_Local_Admins_DATA.`Account00`,instr(SMX_Local_Admins_DATA.`Account00`,'root\')+1) not in ('admin','desktop','security')
Group by
SMX_Local_Admins_DATA.`Machine_Name`

Link this to SMX_Local_Admins_DATA, using a left join FROM SMX_Local_Admins_DATA to {Command.MachineName}. Select link options and choose "enforce both".

In the main report, create a formula {@grp}:

if isnull({Command.Cnt}) then 'Locked' else
if {Command.Cnt} > 0 then 'Unlocked'

Insert your group on this. In the chart expert, use {@grp as the "on change of" field and use distinctcount of {SMX_Local_Admins_DATA.Machine_Name} as the summary field.

-LB

 

[almaler]

When I try to add that command, I get an error that says instr is not a recognized function name.. ?

 

[almaler]

Also, I am working with two tables. The table and field that stores the machine name is Computer_System_Data.Name0 so would this be correct?

SELECT 
count(Computer_System_Data.Name0) as Cnt, Computer_System_Data.Name0
FROM
Computer_System_Data
WHERE
mid(SMX_Local_Admins_DATA.Account00,instr(SMX_Local_Admins_DATA.Account00,'root\')+1) not in ('admin','desktop','security')
Group by
Computer_System_Data.Name0

 

[lbass]

Try:

SELECT
count(Computer_System_Data.`Name0`) as Cnt, Computer_System_Data.`Name0`
FROM
`Computer_System_Data` Computer_System_Data, `SMX_Local_Admins_DATA` SMX_Local_Admins_DATA
WHERE
mid(SMX_Local_Admins_DATA.`Account00`,{fn locate('root\',(SMX_Local_Admins_DATA.`Account00`)}+1) not in ('admin','desktop','security')
Group by
Computer_System_Data.`Name0`

I added the backward quotes around the field names--not sure if that's right for your datasource. If you don't ordinarily work with commands and are unsure of the correct punctuation, take a look at another report that uses the same datasource and go to database->show SQL query and note how the punctuation for fields (and table names are used), and then use that kind of punctuation in the command in
this report.

Instr() worked for me in both the SQL expression expert in 8.5 and in a command in 11.0, but the functions that are available will vary based on your datasource.

-LB

 

[almaler]

I took a look at another report and found that I need to use double quotes. I tried the below command and am getting an error that says "Incorrect syntax near '}'"

SELECT 
count("Computer_System_Data"."Name0") as Cnt, "Computer_System_Data"."Name0"
FROM
"Computer_System_Data" Computer_System_Data, "SMX_Local_Admins_DATA" SMX_Local_Admins_DATA
WHERE
mid("SMX_Local_Admins_DATA"."Account00",{fn locate('root\',("SMX_Local_Admins_DATA"."Account00")} +1) not in ('admin','desktop','security')
Group by
"Computer_System_Data"."Name0"

 

[lbass]

I think that should be:

SELECT
count("Computer_System_Data"."Name0") as Cnt, "Computer_System_Data"."Name0"
FROM
"Computer_System_Data" "Computer_System_Data", "SMX_Local_Admins_DATA" "SMX_Local_Admins_DATA"
WHERE
mid("SMX_Local_Admins_DATA"."Account00",{fn locate('root\',"SMX_Local_Admins_DATA"."Account00")} +1) not in ('admin','desktop','security')
Group by
"Computer_System_Data"."Name0"

Note that I removed an extra paren that shouldn't been there in between 'root\',"SMX_, etc. Sorry about that.

If that doesn't work, try removing the where clause to test whether the locate function might not be available to you.

-LB

 

[almaler]

Ok, I tried that and I am now getting this error:
'mid' is not a recognized function name. *sigh*

 

[lbass]

Maybe you have the right() and len() functions:

SELECT
count("Computer_System_Data"."Name0") as Cnt, "Computer_System_Data"."Name0"
FROM
"Computer_System_Data" "Computer_System_Data", "SMX_Local_Admins_DATA" "SMX_Local_Admins_DATA"
WHERE
right("SMX_Local_Admins_DATA"."Account00",len("SMX_Local_Admins_DATA"."Account00")-{fn locate('root\',"SMX_Local_Admins_DATA"."Account00")} +1) not in ('admin','desktop','security')
Group by
"Computer_System_Data"."Name0"

-LB

 

[almaler]

YIPPEE!! That worked. Now, I'm trying to figure out the rest of it in your earlier post. I have three tables now:
Command
Computer_System_Data
SMX_Local_Admins_Data

I have created a left outer join FROM Computer_System_Data.Name0 to Command.Name0 and I chose "Enforced Both" and the Link Type is =.

I have added the formula called @grp:

if isnull({Command.Cnt}) then 'Locked' else
if {Command.Cnt} > 0 then 'Unlocked'

Since I don't really understand what the SQL statement is doing, I am lost at this point. Could you explain it in simple terms? Just the where clause..

By the way, I really appreciate all of your help!

 

[lbass]

The command is counting the machine name if one of the records in the machine group has values other than the three you mention. This would mean they were unlocked. Otherwise, if there are no other values besides those three, the count for the machine group will be null. The formula {@grp} can be used for grouping and as the on change of field in the chart because the count value has been returned during the first pass.

-LB

 

[almaler]

So, at this point I can create a group based on @grp and then insert the Computer_System_Data.Name0 field in the detail section. I should have one group that says Locked and one that says Unlocked and each should have all the system names listed in the details section, correct?
Or do I need to choose the Name0 field under the Command table?

 

[lbass]

Yes. You can group on {@grp} and then use {Computer_System_Data.Name0} in the detail section. Feel free to experiment a little--it won't break the report or anything.

-LB

 

[almaler]

Well, the reason I ask is because when I try to run the report, it takes forever and then finally I get a popup box that just says "Not Supported

 

[lbass]

I'm not sure what that message means. I looked at the Business Objects knowledge base and the only thing I saw was an issue with linking a command with a table in 9.0 that was solved with a service pack.

I did test my suggestion above with XI and didn't run into trouble, but we are using different databases, etc.

You might want to start another thread on this topic, as I can't really offer any help with this new problem.

Otherwise, you could go back to my first suggestion and try creating running totals to use in the chart. I think you would have to use the method outlined in the Business Objects paper entitles "Charting on Print-time Formulas".

-LB

 

[almaler]

This is very strange, but I am no longer getting the "Not supported" error. I was able to successfully run the report, but all machines are showing as being unlocked.

When I browse the data on the @grp formula, it only shows the Unlocked value.

 

[lbass]

Two things: First, go to file->report options and make sure that "convert null values to default" is NOT checked. Also, please explain the links between the two tables and the command.

Also, you say you are showing only the Unlocked value. Is there an unnamed group that appears?

-LB

 

[DannyBill]

I'm working on a report where I need to find the numbers of hours worked within a specified date range. This report will be used year after year so I'm wanting to declare start and end dates, then create formulas based off those to specify date range.
I've created a formula called Start date for the first week:
WhilePrintingRecords;
Shared DateTimeVar StartDate;
DateTimeVar EndDate;

StartDate := DateTimeValue (2005,02,20);
EndDate := DateTimeValue (2005,02,26);
0

Next I've created a formula for the second week to show hours worked:
WhilePrintingRecords;
dateTimeVar StartDate;
dateTimeVar EndDate;

if {VW_EmpJobs.EndDate} in dateTimeVar StartDate+7 to dateTimeVar EndDate +7
then {VW_EmpJobs.WorkedHours}
else 5555

All I get is 5555 in the details section for each job.
I will eventually create formulas to show hours worked for each week of the year using this formula if I can ever get it figured out.

Any help as to what I'm doing wrong would be appreciated.

Thanks,
Dan