Multiple field values in a single group

[chillam]

Hi,
I am having trouble grouping multiple field data in a single group.
I have a table as below:
Name1 Cost1 Name2 Cost2 Name3 Cost3
Joe $5.00 Ben $4.50 Bob $6.75
Ben $8.00 Jane $3.00 Joe $11.15
Mike $32.00 Bob $9.32 Joe $5.95
Jane $9.45 Ben $8.55 Mike $8.50


The result on the report should be as:

Group Total Cost
Joe $22.10
Ben $21.05
Bob $16.07
Jane $12.45
Mike $40.50

Any help is greatly appreciated.

Navin

 

[Turkbear]

Hi,
Does each record have 3 name fields and 3 cost fields?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[chillam]

Yes. Each record have multiple Names.

Thanks,

 

[lbass]

The simplest solution would be to use a command as your datasource, where you combine the fields into one field:

select table.name1,table.cost1
from table
union all
select table.name2,table.cost2
from table
union all
select table.name3,table.cost3
from table

Then you can insert a group on name1 and insert a sum on cost1, and they will return the values based on all names/costs.

-LB

 

[chillam]

LB,
Thanks for your reply.

When i use the above mentioned process in performing the union in the command, am getting error "lexical element not found".

There are other fields in the table too, that have to be displayed, which are like start date, end date...these fields are not multiples as name & cost.

Thanks.

 

[lbass]

What type of database are you using?

You should use the command as your sole datasource, so you should add in the other tables and fields.

You have to add the punctuation appropriate to your datasource, so you might want to check another report->database->'show SQL query' to see how you should be creating the command.

I could try to troubleshoot it if you copy the command into the thread.

-LB

 

[chillam]

Hello LB,
Thnks again for your reply....

Here is the SQL Query. We are connecting to Oracle database using 'AR System ODBC Connector'.

SELECT "WorkOrder"."Work_Order_ID_", "WorkOrder"."Total_Cost", 
"WorkOrder"."Create_Date", "WorkOrder"."Category", 
"WorkOrder"."Request_Type", "WorkOrder"."Request_Completed_Time", 
"WorkOrder"."Status", 
 "WorkOrder"."Engineer1", "WorkOrder"."Engineer2", 
"WorkOrder"."Engineer3", "WorkOrder"."Engineer4", 
"WorkOrder"."Total_Work_Hours1", "WorkOrder"."Total_Work_Hours2", 
"WorkOrder"."Total_Work_Hours3", "WorkOrder"."Total_Work_Hours4"
 FROM   "WorkOrder" "WorkOrder"
 WHERE  ("WorkOrder"."Create_Date">={ts '2010-01-01 00:00:00'}) AND 
("WorkOrder"."Request_Type"='Emergency' OR 
"WorkOrder"."Request_Type"='Routine') AND 
("WorkOrder"."Assignment_Event"='Building Engineer' OR 
"WorkOrder"."Assignment_Event"='Engineer')

As mentioned above... it is the sum of total work hours for each engineer, has to be populated in the report.

An engineer info. can be anywhere in the record (like it can be under 'Engineer1' or 'Engineer2' or 'Engineer3' or 'Engineer4')

-Navin

 

[Turkbear]

Hi,
May I ask why you are using an ODBC connection instead
of the native Oracle Server connection in CR?

Usually the native drivers will insure that all Oracle functions are supported.

Also,
Are all Engineer1s the same person, or does it matter - it seems that to sum by a particular engineer, there could be an issue if the data is inconsistant about a particular engineers' name field.



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[chillam]

Hi,
I will be using this report in 'Remedy Applications', where the connection to the Oracle DB is done using 'AR System ODBC Connector'.

As mentioned initially, these engineer (like Joe, Ben...) can be under 'Engineer1/ Engineer2/ Engineer3/ Engineer4'. They are not restricted to one field.

Thanks.

 

[lbass]

Here is how the command should look, if "Add command" is an option with your particular driver:

SELECT "WorkOrder"."Work_Order_ID_", "WorkOrder"."Total_Cost",
"WorkOrder"."Create_Date", "WorkOrder"."Category",
"WorkOrder"."Request_Type", "WorkOrder"."Request_Completed_Time",
"WorkOrder"."Status", "WorkOrder"."Engineer1" "Engineer",
"WorkOrder"."Total_Work_Hours1" "Total Work Hours"
FROM "WorkOrder" "WorkOrder"
WHERE
("WorkOrder"."Create_Date">={ts '2010-01-01 00:00:00'}) AND
("WorkOrder"."Request_Type"='Emergency' OR
"WorkOrder"."Request_Type"='Routine') AND
("WorkOrder"."Assignment_Event"='Building Engineer' OR
"WorkOrder"."Assignment_Event"='Engineer')

Union All

SELECT "WorkOrder"."Work_Order_ID_", "WorkOrder"."Total_Cost",
"WorkOrder"."Create_Date", "WorkOrder"."Category",
"WorkOrder"."Request_Type", "WorkOrder"."Request_Completed_Time",
"WorkOrder"."Status", "WorkOrder"."Engineer2" "Engineer",
"WorkOrder"."Total_Work_Hours2" "Total Work Hours"
FROM "WorkOrder" "WorkOrder"
WHERE
("WorkOrder"."Create_Date">={ts '2010-01-01 00:00:00'}) AND
("WorkOrder"."Request_Type"='Emergency' OR
"WorkOrder"."Request_Type"='Routine') AND
("WorkOrder"."Assignment_Event"='Building Engineer' OR
"WorkOrder"."Assignment_Event"='Engineer')

Union all

SELECT "WorkOrder"."Work_Order_ID_", "WorkOrder"."Total_Cost",
"WorkOrder"."Create_Date", "WorkOrder"."Category",
"WorkOrder"."Request_Type", "WorkOrder"."Request_Completed_Time",
"WorkOrder"."Status", "WorkOrder"."Engineer3" "Engineer",
"WorkOrder"."Total_Work_Hours3" "Total Work Hours"
FROM "WorkOrder" "WorkOrder"
WHERE
("WorkOrder"."Create_Date">={ts '2010-01-01 00:00:00'}) AND
("WorkOrder"."Request_Type"='Emergency' OR
"WorkOrder"."Request_Type"='Routine') AND
("WorkOrder"."Assignment_Event"='Building Engineer' OR
"WorkOrder"."Assignment_Event"='Engineer')

Union all

SELECT "WorkOrder"."Work_Order_ID_", "WorkOrder"."Total_Cost",
"WorkOrder"."Create_Date", "WorkOrder"."Category",
"WorkOrder"."Request_Type", "WorkOrder"."Request_Completed_Time",
"WorkOrder"."Status", "WorkOrder"."Engineer4" "Engineer",
"WorkOrder"."Total_Work_Hours4" "Total Work Hours"
FROM "WorkOrder" "WorkOrder"
WHERE
("WorkOrder"."Create_Date">={ts '2010-01-01 00:00:00'}) AND
("WorkOrder"."Request_Type"='Emergency' OR
"WorkOrder"."Request_Type"='Routine') AND
("WorkOrder"."Assignment_Event"='Building Engineer' OR
"WorkOrder"."Assignment_Event"='Engineer')

-LB

 

[chillam]

LB, Thanks for your response.

Unfortunately, Union is not permitted using AR System ODBC Driver.