Categories
Logic to show Manager and Employee Comments of Overall Section in Peformance in single row
Currently using Workforce Performan - Performance Rating Real Time Subject area to pull Manager and Employee Comments from Overall Section , its showing in two rows, one row manager comments and another row employee comments. Any hint on the Logic to show Manager and Employee Comments of Overall Section in Peformance in single row
Answers
-
One approach to achieve this is by using pivoting or joining techniques:
Pivot Table Method: You can pivot the comments column based on the role (Manager vs. Employee) so that both appear in a single row.
Union or Case Statements: If you're working with SQL, you might need to use a CASE statement or self-join to align the comments into a single row based on the employee ID.
0 -
Hello - either use pivot table - put comments as a measure and set the aggregation to Max or create separate case statements
Data:
Via Pivot Table:
Via Separate Case statement columns using table
Manager:
max(case when "Performance Document Participant"."Role Definition" = 'Manager' then cast("Document and Rating Details"."Rating Comments" as char(1000)) end)
Colleague:
max(case when "Performance Document Participant"."Role Definition" = 'Colleague' then cast("Document and Rating Details"."Rating Comments" as char(1000)) end)
0 -
Pivot Table Method:
If OTBI allows pivoting, you can structure the data using comments as a measure and set aggregation to MAX:
a)Drag the Comments field to the Measures section
b)Set aggregation to MAX
c)Use the Role Definition field as the pivot column
d)Ensure each participant's role is properly mappedThis will allow Manager and Employee comments to display in separate columns within a single row.
Using CASE Statements:If you need a structured column format, use a CASE statement in OTBI:
sql:
MAX(CASE
WHEN "Performance Document Participant"."Role Definition" = 'Manager'
THEN CAST("Document and Rating Details"."Rating Comments" AS CHAR(1000))
END) AS Manager_Comments,MAX(CASE
WHEN "Performance Document Participant"."Role Definition" = 'Employee'
THEN CAST("Document and Rating Details"."Rating Comments" AS CHAR(1000))
END) AS Employee_CommentsThe CAST function ensures compatibility with text formatting.
Using MAX ensures that comments are grouped properly into a single row.0 -
@Riyaz Ali-Oracle isn't that what I said above?
0 -
I just expanded on your approach a bit by providing additional details and formatting for clarity. Your method—either using a Pivot Table or CASE statements—is the right way to go for structuring Manager and Employee comments in a single row.
Try the below steps:
- Navigate to Your Analysis in OTBI
Open Oracle OTBI and go to the Analysis Editor.
Select the Performance Rating Real Time subject area.2. Apply the Case Statement
In the Criteria tab, click the fx (Formula) button for the column where you want to display Manager and Employee Comments.
Paste the following formula into separate calculated columns:MAX(CASE
WHEN "Performance Document Participant"."Role Definition" = 'Manager'
THEN CAST("Document and Rating Details"."Rating Comments" AS CHAR(1000))
END) AS Manager_Comments,MAX(CASE
WHEN "Performance Document Participant"."Role Definition" = 'Employee'
THEN CAST("Document and Rating Details"."Rating Comments" AS CHAR(1000))
END) AS Employee_CommentsThis ensures that comments appear in one row instead of separate ones.
3. Run the Query
Execute the analysis and verify if Manager and Employee Comments are now appearing in a single row.
If the report still shows two rows instead of merging:
Ensure Grouping is Correct – Try adding Employee ID to the grouping to maintain alignment.
Check Aggregation Settings – Make sure MAX is set correctly to group comments.
Verify the Role Definition Field – Double-check that role labels ('Manager' and 'Employee') match exactly.1 -
- Ensure Aggregation Is Set to MAX
In the Column Properties, confirm that the aggregation is set to MAX for both Manager and Employee Comments.
If OTBI isn't grouping them correctly, try SUM instead.2. Verify Grouping Fields in OTBI
In the Results tab, ensure that Employee ID or Document ID is included to force grouping of comments into a single row.
If Manager and Employee Comments still appear in separate rows, try using Ranking or Row Number functions.3. Try a Different Method: String Concatenation
If OTBI does not support MAX properly, you might need to concatenate comments into a single field using.
sql
LISTAGG("Document and Rating Details"."Rating Comments", ', ') WITHIN GROUP (ORDER BY "Performance Document Participant"."Role Definition")
This method will merge multiple comments into a single field, separated by commas.4. Check for Unexpected Filters
Make sure your report filters aren’t excluding the necessary records.
If there’s a date filter or department restriction, it might be preventing some comments from displaying.0 -
HI Riyaz,gclampitt,
Thank you for your responses. Tried with the options, but Getting below error
Error
View Display Error
Error during query processing (SQLExecDirectW).
Error DetailsError Codes: OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65PState: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000)State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS. (HY000)State: HY000. Code: 43119. [nQSError: 43119] Query Failed: (HY000)State: HY000. Code: 17001. [nQSError: 17001] Oracle Error code: 910, message: ORA-00910: specified length too long for its datatype at OCI call OCIStmtExecute. (HY000)State: HY000. Code: 17010. [nQSError: 17010] SQL statement preparation failed. (HY000)SQL Issued: SET VARIABLE PREFERRED_CURRENCY='User Preferred Currency 1';SELECT 0 s_0, "Workforce Performance - Performance Rating Real Time"."- Performance Rating"."Overall Manager Rating" s_1, "Workforce Performance - Performance Rating Real Time"."- Rating Level"."Rating Description" s_2, "Workforce Performance - Performance Rating Real Time"."Business Unit"."Business Unit Name" s_3, "Workforce Performance - Performance Rating Real Time"."Department"."Department Name" s_4, "Workforce Performance - Performance Rating Real Time"."Job"."Job Manager Level" s_5, "Workforce Performance - Performance Rating Real Time"."Job"."Job Name" s_6, "Workforce Performance - Performance Rating Real Time"."Job"."PER_JOBS_DFF_MBO_BONUS_TARGET_" s_7, "Workforce Performance - Performance Rating Real Time"."Legal Employer"."Name" s_8, "Workforce Performance - Performance Rating Real Time"."Location"."Worker Location Name" s_9, "Workforce Performance - Performance Rating Real Time"."Manager"."E-Mail Address" s_10, "Workforce Performance - Performance Rating Real Time"."Manager"."Name" s_11, "Workforce Performance - Performance Rating Real Time"."Performance Document Details"."Performance Document Manager" s_12, "Workforce Performance - Performance Rating Real Time"."Performance Document Details"."Performance Document Name" s_13, "Workforce Performance - Performance Rating Real Time"."Performance Document Details"."Performance Document Status" s_14, "Workforce Performance - Performance Rating Real Time"."Review Period"."Review Period Name" s_15, "Workforce Performance - Performance Rating Real Time"."Worker"."Employee Email Address" s_16, "Workforce Performance - Performance Rating Real Time"."Worker"."Employee First Name" s_17, "Workforce Performance - Performance Rating Real Time"."Worker"."Employee Last Name" s_18, "Workforce Performance - Performance Rating Real Time"."Worker"."Employee Mailing County Code" s_19, "Workforce Performance - Performance Rating Real Time"."Worker"."Person Number" s_20, CASE WHEN "Workforce Performance - Performance Rating Real Time"."Parent Managers"."Parent Manager 09 Name" IS NULL AND "Workforce Performance - Performance Rating Real Time"."Parent Managers"."Parent Manager 08 Name" IS NOT NULL THEN "Workforce Performance - Performance Rating Real Time"."Parent Managers"."Parent Manager 07 Name" WHEN "Workforce Performance - Performance Rating Real Time"."Parent Managers"."Parent Manager 08 Name" IS NULL AND "Workforce Performance - Performance Rating Real Time"."Parent Managers"."Parent Manager 07 Name" IS NOT NULL THEN "Workforce Performance - Performance Rating Real Time"."Parent Managers"."Parent Manager 06 Name" WHEN "Workforce Performance - Performance Rating Real Time"."Parent Managers"."Parent Manager 07 Name" IS NULL AND "Workforce Performance - Performance Rating Real Time"."Parent Managers"."Parent Manager 06 Name" IS NOT NULL THEN "Workforce Performance - Performance Rating Real Time"."Parent Managers"."Parent Manager 05 Name" WHEN "Workforce Performance - Performance Rating Real Time"."Parent Managers"."Parent Manager 06 Name" IS NULL AND "Workforce Performance - Performance Rating Real Time"."Parent Managers"."Parent Manager 05 Name" IS NOT NULL THEN "Workforce Performance - Performance Rating Real Time"."Parent Managers"."Parent Manager 04 Name" WHEN "Workforce Performance - Performance Rating Real Time"."Parent Managers"."Parent Manager 05 Name" IS NULL AND "Workforce Performance - Performance Rating Real Time"."Parent Managers"."Parent Manager 04 Name" IS NOT NULL THEN "Workforce Performance - Performance Rating Real Time"."Parent Managers"."Parent Manager 03 Name" WHEN "Workforce Performance - Performance Rating Real Time"."Parent Managers"."Parent Manager 04 Name" IS NULL AND "Workforce Performance - Performance Rating Real Time"."Parent Managers"."Parent Manager 03 Name" IS NOT NULL THEN "Workforce Performance - Performance Rating Real Time"."Parent Managers"."Parent Manager 02 Name" WHEN "Workforce Performance - Performance Rating Real Time"."Parent Managers"."Parent Manager 03 Name" IS NULL AND "Workforce Performance - Performance Rating Real Time"."Parent Managers"."Parent Manager 02 Name" IS NOT NULL THEN "Workforce Performance - Performance Rating Real Time"."Parent Managers"."Parent Manager 01 Name" WHEN "Workforce Performance - Performance Rating Real Time"."Parent Managers"."Parent Manager 02 Name" IS NULL AND "Workforce Performance - Performance Rating Real Time"."Parent Managers"."Parent Manager 01 Name" IS NOT NULL THEN "Workforce Performance - Performance Rating Real Time"."Parent Managers"."Parent Manager 01 Name" ELSE NULL END s_21, CAST(NULL AS VARCHAR(1)) s_22, DESCRIPTOR_IDOF("Workforce Performance - Performance Rating Real Time"."Business Unit"."Business Unit Name") s_23, DESCRIPTOR_IDOF("Workforce Performance - Performance Rating Real Time"."Job"."Job Manager Level") s_24, DESCRIPTOR_IDOF("Workforce Performance - Performance Rating Real Time"."Job"."Job Name") s_25, MAX(CASE WHEN "Workforce Performance - Performance Rating Real Time"."Performance Document Participant"."Role Type" ='Manager' THEN cast("Workforce Performance - Performance Rating Real Time"."Document and Rating Details"."Rating Comments" as char(10000))END) s_26, MAX(CASE WHEN "Workforce Performance - Performance Rating Real Time"."Performance Document Participant"."Role Type" ='Worker' THEN CAST("Workforce Performance - Performance Rating Real Time"."Document and Rating Details"."Rating Comments" as char(10000))END) s_27 FROM "Workforce Performance - Performance Rating Real Time" WHERE (("Performance Document Details"."Performance Document Status" = 'Completed') AND ("Worker"."Assignment Status" NOT IN ('Inactive - No Payroll', 'Inactive - Payroll Eligible', 'Suspended - No Payroll', 'Suspended - Payroll Eligible'))) FETCH FIRST 75001 ROWS ONLY
After doing as expected.
Regards,
Hanuman
0 -
Hi Hanuman,
The ORA-00910: specified length too long for its datatype error typically occurs when a column's defined length exceeds the allowed limit for its datatype. In your case, it seems that a VARCHAR2 or CHAR field is being assigned a length that exceeds the maximum allowed.
Try the below:1. Check Column Lengths
Review the CAST() or CHAR() functions in your query, especially where CAST("Document and Rating Details"."Rating Comments" AS CHAR(10000)) is used.
The maximum length for VARCHAR2 in Oracle is 4000 characters.2. Use CLOB Instead of VARCHAR2
If you need to store more than 4000 characters, consider using CLOB instead of VARCHAR2.
3. Modify the Query
Try reducing the length in the CAST() function:
sql:
CAST("Document and Rating Details"."Rating Comments" AS CHAR(4000))1 -
Hi Riyaz,
Thank you, even though we change that to CHAR(4000) its throwing error as the length was 4018. . Can you please suggest any other alternative.
Regards,
Hanuman
0 -
It looks like the issue persists even after reducing the length to CHAR(4000), likely because the actual data exceeds the allowed limit.
Try the below:
- Use CLOB Instead of VARCHAR2/CHAR
Example:
sql
CAST("Document and Rating Details"."Rating Comments" AS CLOB)
CLOB allows storing large text data beyond the 4000-character limit.2. Split the Data into Multiple Columns
If CLOB is not an option, consider splitting the long text into multiple columns.
Example:
sql
SUBSTR("Document and Rating Details"."Rating Comments", 1, 4000) AS Part1,
SUBSTR("Document and Rating Details"."Rating Comments", 4001, 4000) AS Part23. Use BI Publisher Instead of OTBI
BI Publisher supports handling large text fields better than OTBI.
If possible, generate the report using BI Publisher instead of OTBI.Check the below as it might be helpful:
Error when having a string concatenation of two columns over 4000 characters data — Oracle Analytics
OTBI: Getting Error: exceeded configured maximum number of allowed output rows — Oracle Analytics
1
