Group & Record Selection 1

[mnmdesigns]

Hi,
I am having problem with Group and record selection in my report. Here is what I am trying to do.

First I need to have a group where the {Employee.Position} has to be manager. In my case it would be {Employee.Position} = "MANAGER"

Then I only need to show those reocords in the deatial that fall under this manager. {Employee.Position} = "FRONT DESK ASSOCIATE} and {Employee.Position} = "ASSISTANT MANAGER"

The database has a field which shows their relationship in the field {Employee.ParentID}.

So my end result would be:

Group = Manager Name and ID.
Records = ID and Name of the Employee that work for this manager.

I will be very grateful if someone can show me how to do this.

Thanks in advance.
MD

 

[Ngolem]

I assume that there are other {Employee.Position}'s.

If you only have these requirements then your Record selection formula would be

{Employee.Position} in [ "MANAGER", "FRONT DESK ASSOCIATE", "ASSISTANT MANAGER"]

Then you would Group first by {Employee.Position} ... to get them in that specific order you would group "By Specified order" here you can arrange the order anyway you ant.

Then Group By {Employee.ID} and construct your report accordingly



Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[mnmdesigns]

Thanks I did what you told me to do. Let me explain better what I am trying to do.

Here is what my report should look like.

Group Header : Employee Name (Manager)
Employee ID (Manager)

Detail section : Employee Name (Employee for that Manager)
Employee ID (Employee for that Manager)

The header will only have one Employee which is of course the manager. Whereas the details section will have all the employee which are under that Manager.

Any idea how I can get these results? Thanks for your help.
MD

 

[lbass]

You could try the following:

Add the Employee table twice and use a left join from{Employee.ParentID} to {Employee_1.EmployeeID}. This allows the highest level employees who have no manager to be included in the report.

Group on {Employee.ParentID} and add {Employee_1.EmployeeName} to the group header. For the details section add {Employee.EmployeeID} and {Employee.Name}.

You could also explore using the hierarchical grouping option under "Report."

-LB

 

[mnmdesigns]

Lbass, Thanks for your help. I am very close to getting the desired solution. I have done exactly what you mentioned in your post. Now when I run the report, the first 2-3 pages are showing me all the ID of Managers, Assistant Managers and Front Desk Associates.

In the preview mode, under the group tree the first link is blank and showing up like this:
-Report 1
|_ _ _
|_ _ _1234
|_ _ _7862

When I click on the Group id 1234 it shows me the desired data. Can you please guide me how to remove the records from the first three pages.

Thanks,
MD

 

[mnmdesigns]

I think I found out what I was doing wrong. I had to change the left outer join from {Employee.ParentID} to {Employee_1.EmployeeID} equal join.

Thanks everyone for all your help.

 

[lbass]

If you don't want to show people who don't have managers, then you can use an equal join. That first blank link probably included the company president, for example.

To change my first suggestion a bit, you could just group on {employee_1.EmployeeID} and add {employee_1.EmployeeName}. The link between tables should still be between {employee.parentID} and {employee_1.employeeID} though.

I neglected to address the issue that you have multiple parent IDs, some for other job titles. In your record select statement, use:

{employee_1.position} = "Manager"

-LB

 

[Ngolem]

There must be some relationship between the "Manager" and those employees under him/her....you haven't identified a field in the database yet that does that.

For example
original database Aliased Database

Employee.ID ------------| Employee_1.ID
Employee.Position | Employee_1.Position
Employee.SIN | Employee_1.SIN
Employee.WorksForID |-----> Employee_1.WorksForID

then your record select formula would be

Employee.position = "MANAGER" and
Employee_1.position in ["FRONT DESK ASSOCIATE", "ASSISTANT MANAGER"] and
Employee.ID = Employee_1.WorksForID


something like that...there must be a way of identifying who works for who.


Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[mnmdesigns]

Ngolem,

Yes you are right there is a field in the database which is the {employee.parentid}. This field identifies that the manager for every employee.

The problem was fixed by changing the link from left outer join to equal join.

I appreciate all your help.
MD

 

[mnmdesigns]

Ok now I am facing another problem. I need to create two parameter values. The user should be able to run the report on :

Parameter 1 : Manager ID (All or one ID)
OR
Parameter 2 : Manager Last Name (All or one Last Name)

Both ID and Last Name are string fields. Can someone please point me in the right direction?

Thanks in advance.
MD

 

[Ngolem]

Parameter Name : {?ManagerID}
default: "ALL"

Parameter Name : {?ManagerName}
Default "ALL".....NOTE THE CAPS!!!

One problem with {?ManagerName} is that normally people make mistakes on the spelling. It is fine if the name is "Smith" or "Jones" but more complicated names are a problem. Wildcards are a big help in the search...this way you can simply have the user enter a partial match for the name

So your complete record select could be

(if {?ManagerID} <> &quot;ALL&quot; then
{Employee.position} = {?ManagerID}
else if {?ManagerID} = &quot;ALL&quot; then
True and
(if {?ManagerName} <> &quot;ALL&quot; then
{Employee.name} like &quot;*&quot; + {?ManagerName} + &quot;*&quot;
else {?ManagerName} = &quot;ALL&quot; then
True

I don't know what yur database values are....are they all caps or mixture of smalls and caps...you might have to force everything into uppercase to bulletproof it a bit.

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[mnmdesigns]

Ngolem,

Thanks for your reply. Here is what I have done so far. I created a new parameter called {?ManagerID}. I went to set default values selected browse table {Employee_1} and browse field {Employee.ParentID} and typed in &quot;ALL&quot;. Now when I go to the select expert and try to create a new condition I am not able to see the {?ManagerID} parameter there. Do I need to create a formula? Also do I have to place this parameter in the report?

All your help is much appreciated.
MD

 

[lbass]

I think you need to set this up so only one of the parameters is used, so I would create three string parameters:

{?ID or Name} - a discrete string parameter with two defaults:
ID
Name

{?ID} - a discrete, multiple value parameter with a default of &quot;All&quot;

{?Name} = a discrete, multiple value parameter with a default of &quot;All&quot;

Then go to edit selection formula->record and enter:

if {?ID or Name} = &quot;ID&quot; then
(if {?ID} <> &quot;All&quot; then
{?ID} = totext({Employee_1.EmployeeID},0,&quot;&quot else true) else

if {?ID or Name} = &quot;Name&quot; then
(if {?Name} <> &quot;All&quot; then
{?Name} = {Employee_1.EmployeeName} else true)

-LB

 

[Ngolem]

lbass - I don't think the extra parameter is required. If you want to select by ID then set the Name to &quot;ALL&quot; and if you want to select by Name then set ID to &quot;ALL&quot;

This could be explained in the parameter description that the user sees

mnmdesigns - Don't use the Select Expert....drop this formula directly into the record selection formula

(if {?ManagerID} <> &quot;ALL&quot; then
{Employee.ID} = {?ManagerID}
else if {?ManagerID} = &quot;ALL&quot; then
True and
(if {?ManagerName} <> &quot;ALL&quot; then
{Employee.name} like &quot;*&quot; + {?ManagerName} + &quot;*&quot;
else {?ManagerName} = &quot;ALL&quot; then
True and
{Employee.position} = &quot;MANAGER&quot; and
//where Employee_1 is the aliased table of Employee.
{Employee.ID} = {Employee_1.parentid};

I did make a couple of mistakes in the formula which I have bolded



Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[mnmdesigns]

Lbass, Thanks for being so patient. I am new to Crystal reports. Ok I tried what you suggested in your post but that did not work. Let me give you some more information. Here is what I currently have in my records selection formula.

{Employee_1.position} = &quot;Manager&quot;
and
{Employee.position} in [&quot;Front Desk Associate&quot;, &quot;Assistant Manager&quot;]
and
{Employee_1.id} = {Employee.parentId}

Once I create those paramaters, should I just paste the records selection formula underneath this one. Sorry I am new in Crystal and not sure how to do this. Thanks for your help. I really appreciate it.
MD

 

[lbass]

You don't really need the last clause since you have already linked on those two fields.

{Employee_1.position} = &quot;Manager&quot;
and
{Employee.position} in [&quot;Front Desk Associate&quot;, &quot;Assistant Manager&quot;]
and
if {?ID or Name} = &quot;ID&quot; then
(if {?ID} <> &quot;All&quot; then
{?ID} = totext({Employee_1.EmployeeID},0,&quot;&quot else true) else

if {?ID or Name} = &quot;Name&quot; then
(if {?Name} <> &quot;All&quot; then
{?Name} = {Employee_1.EmployeeName} else true)

When you say something doesn't work, you should explain the specific result you get.

Jim,

Since the ID and name field correspond to each other, I thought the formula should prevent choices of each parameter that could conflict with the value of the other parameter.

-LB

 

[mnmdesigns]

Jim,

Let me take one step back and explian everything that I have done. I created 2 Parameters {?ManagerID} and {?ManagerName}. {?ManagerID} is getting value from {Employee_1.parentID} and set the default to &quot;ALL&quot;. {?ManagerName} is getting value from {Employee_1.Name} and set the default to &quot;ALL&quot;. Then I used the formula in your last post. Now when I run the report I get no records.

MD.

 

[mnmdesigns]

Lbass,

When I use the formula you sent me it gives me an error
&quot;too many arguments have been given to this function&quot; and the cursor is before this argument
totext({Employee_1.EmployeeID},0,&quot;&quot else true) else

MD.

 

[mnmdesigns]

This might be a very silly question, but I had to ask. I am using the &quot;ALL&quot; field in the parameter value. Dont I have to go and define it somewhere. Meaning when I am creating the parameter do I need to &quot;>>&quot; all the ID's that are present in the list?
MD.

 

[lbass]

Sorry, I didn't notice you had said it was a string and I assumed that the EmployeeID was a number. Therefore, just remove the totext function from

totext({Employee_1.EmployeeID},0,&quot;&quot else true) else

and make it:

{Employee_1.EmployeeID} else true) else

You set &quot;All&quot; (without the parens) as a default when creating the parameter, right? That's all you need to do. But if you want all the IDs to appear for the user to select from when &quot;All&quot; is not chosen you could import a list. Basically, if &quot;All&quot; is chosen no selection occurs on the parameter field, i.e., no records are excluded.

-LB