Page 1 of 1

filtering by Skill Number

Posted: Wed Nov 10, 2004 12:05 pm
by billyg73
Hi there,

I'm trying to create a custom report based on Skill Numbers that people may or may not have, and need your help/advice.

In our database, we assigned Skill Number 1 as Plan to Protect, a training for those dealing with minors. Skill Number 1100 is for those involved in Youth Ministry.

What I want to do is create a report where I list all members currently involved in Youth Ministry, but who have NOT yet finished the Plan to Protect training.

i.e. Skill Number equal to 1100 and Skill Number not equal to 1

However, I cannot get the Custom Report to correctly select these people. Any thoughts?

Blessings,

Graham Faulkner
gfaulkner@watpen.org

Posted: Wed Nov 10, 2004 12:31 pm
by jeffkoke
Are you sure that there are people in your database that meet the filter criteria?

If not, put in a test family/profile with those settings and then run the report.

Jeff

Posted: Wed Nov 10, 2004 1:33 pm
by billyg73
Hi Jeff,

Yes, we have double checked to make sure that the people in question have the appropriate skill numbers.

What is happening, for example, in running the query, is that it will pick all the people with skill 1100 and even those with skill number 1. i.e. it's not properly applying the "Skill Number is not equal to 1" filter.

Any further thoughts?

Blessings,

Graham

Posted: Wed Nov 10, 2004 1:43 pm
by jeffkoke
It sounds like you don't have your statement bounded correctly.

Make sure that the match must equal the output of both statements (they should be "anded" together).

You can use the parentheses to so indicate, I believe.

Posted: Wed Nov 10, 2004 1:47 pm
by billyg73
Hi Jeff,

Yes, I've tried it with and without parantheses. I believe the outputted SQL wording is something like (I'm not in front of the computer):

Skill Number is not equal to 1 and
Skill Number equals 1100

With parantheses it was:

( Skill Number is not equal to 1 and
Skill Number equals 1100 )

Is there a different way to arrange/word this?

Blessings,

Graham

Posted: Wed Nov 10, 2004 1:49 pm
by Jeff
Graham,

This sounds like this is going to be a fairly complex custom report. Just to make sure I understand you correctly you need a report that shows everyone who is in skill 1100 that is not also in skill 1.

Going under that assumption here is the basic outline of the report.

The selection criteria is skill number = 1100 or skill number = 1

You are then going to have to group by name in the report layout portion and write variables to check to see if you have seen both skill numbers or not. Then in the group footer you only output if you have seen the 1100 activity and not seen activity 1.

If you don't have a lot of experience with report writers this could be very confusing. We do offer a service where we will design this report for you. Contact our support department for more details on us doing that.