Linkng/ Showing (and Not Showing) records

[brendanj68]

Hi, this one has me a bit stumped, am running CR v8 with SQL 2000. The report requires me to do the following:

For a given person whose role is Project Sponsor, their name should only appear in the column heading Sponsor, the same applies to Lead Consultant and Project Coordinator (each being a seperate column). Conversly the users name should not appear in a column that does not reflect their role.

The name and role fields are seperate but linked. Hope I've explained this properly? Any pointers a plus

Thanks B

 

[brendanj68]

Apologies, must be a Friday thing, but thought this would work:

If({Employee.Rn_Descriptor}) = 'Project Sponsor' THEN {Employee.Rn_Descriptor} ELSE " "

Still gettin "the result of selection formula must be boolean". Is there a bug in CR v8.0 that causes this to occur no matter how you structure the clause

 

[lbass]

I think you are trying to create this formula in the selection formula area. Instead, go to insert field object->formula and click on new and create it there. It sounds like you're trying to create a manual crosstab, and your formula syntax is fine, except that I think you should just name your formula what you want for the column label, and then write the formula to display the employee name, not the title, as in:

{@Project Sponsor}:

if {Employee.Rn_Descriptor} = "Project Sponsor" THEN {Employee.Name} ELSE ""

{@Lead Consultant}:
if {Employee.Rn_Descriptor} = "Lead Consultant" THEN {Employee.Name} ELSE ""

Dragging these onto the report canvas in the details section will result in this kind of layout:

Proj
ID Project Sponsor Lead Consultant
123 Janet Moen Teresa Sevals
345 Stuart Wahl Lynn Taylor

-LB

 

[lbass]

Actually, on second thought, I think you'd have to group on the Project ID, and insert a maximum (assuming one name per title) on each formula field to get the name in the group header or footer. Since the name field is being used for different titles, it will result in one record for each job title, and thus, the need for a group and a summary on each formula.

-LB

 

[brendanj68]

lbass

Thanx for the info, unfortunately Employee.Rn_Descriptor only returns 'Janet Moen'. Do you think the following will provide a solution - create another formula to link ID/ User/ Role and then use that within the structure as outlined above?

thanking u once again

 

[lbass]

Can you confirm that {Employee.Rn_Descriptor} returns alternatives including "Project Sponsor", "Lead Consultant", etc., and that there is one {Employee.Name} field which identifies the name related to each of the {Employee.Rn_Descriptor} results?

Did you place the formulas {@Project Sponsor} and {@Lead Consultant}, etc., in the detail sections? For each formula only one result will be returned per record, which is why you need to insert the summary on each formula so that the result appears at the group level, and then suppress the detail section.

-LB

 

[brendanj68]

Hey LB,

Have you ever used a CRM system called Pivotal? If yes - you'll know some of the issues wihtin it, if not then I'll try to explain further.
{Employee.Rn_Descriptor} only returns Stuart Wahl, little use their.
An alternative is {Opportunity_Team_Member.Rn_Descriptor} which returns the following: WIR-A, Resourcing Co-ordinator (Project Sponsor).

Did try your suggestion, and placed accordingly but need to find another field that can provide the users full name ie replace {Employee.Name}, bit this may be in another table, and I already have had to link 7 tables for this one report. Will continue on theme you suggested. Big thanx as ever

 

[lbass]

For further assistance, I think we would need to know the table fields that hold the roles of interest (your column heading values), since it now appears that there are multiple fields that identify roles, as well as the fields that hold the user/employee name, the tables involved, and the links between the tables.

-LB

 

[brendanj68]

Tried this as an alternative to '{Employee.Name}'

if {Employee.Rn_Descriptor} = "Project Sponsor" THEN
{@Names Concat} ELSE "" as well as replacing the Descriptor with another alternative - {Opportunity.Rn_Descriptor}

The {@Names Concat} consists of Employee.First_Name and .Last_Name!

Just returned blank. By nature of the report I have to include this in details. Also tried a crosstab (as a subreport), but that was even more of a disaster.

Links seem ok when run directly in SQL Analyzer. Though in general not all linx are direct! as I'm having to Inner Join a few tables at a time.

 

[brendanj68]

LB,

All expected data returning - good, but still having the following problem: Instead of each person/ role appearing on the same line (as there are three distinct @role params covering the roles required), an additinal line is shown in details/ GF (am supressing one while testing the other) for each additional new person/role. Have grouped each param (individually), is there a way to keep the data on one line and avoid this repetition? Thanx in advance

 

[lbass]

You should not be grouping on each role, but instead grouping on Project ID. For each formula that returns a name for a role, you should be inserting a maximum so that the name then appears in the ProjectID group footer or header. This way all names will be in one line.

It is difficult to help when you don't answer the questions that are asked and without examples of the results you are getting. If you need more help, please provide answers to all questions! Thanks.

-LB

 

[brendanj68]

LB, sincerest thanks for this (sorry 'bout the lack of info earlier!) Have now got my head around CR logic and all is working fine. Cheers for your patience.

B