duplicate values in OTBI Excel output

Sharmava

In the OTBI report results tab showing only one record where as in the export showing expected number of rows along with duplicate rows. Please advise any property settings missed in my case.

Nathan CCC

Sharmava,

Have you included all columns in your results view? Because you said "Results" tab say I assume you mean an analysis in OTBI not a report in OTBI (that is the object you have created in the catalog is a blue analysis not a yellow report).

When you export as data to CSV or XML you get ALL the columns with all rows in the criteria. This is no matter what views you have created in results. If you think you have duplicates then ask your bi administrator to use page Manage Privileges to grant your user or a role you have or inherit privilege Issue SQL Directly and Manage Sessions so you can View Log so you can understand why you are not getting the expected result from your query. Look at the joins and where clauses in the one or more physical SQL(s) on view objects.

In your results you will only get as many rows as you have asked for in the design of each view on each layout. For example, in results you have created a table view, your data has a child fact measure columns (ruler icon) with parent header and child line attribute columns in criteria, but you have excluded the child attribute columns in your table view, so your data is aggregated to 1 row per parent. Maybe post your logical sql from the advanced tab as text not attachment so we can see?

Cheers, Nathan

Sharmava

SELECT
"Job Requisition - Basic Information"."Requisition Number" saw_0,
"HR Position"."Position Code" saw_1,
"Job Requisition - Additional Details"."IRC_REQUISITIONS_DFF_NEW_ORG_STRUCTURE_BU_" saw_2,
"Job Requisition - Offer Information"."Business Unit Name" saw_3,
"Job Requisition - Dates"."Creation Date" saw_4,
"Job Requisition - Dates"."Approved Date" saw_5,
"Job Requisition - Basic Information"."Requisition Title" saw_6,
"Job Requisition - Offer Information"."Grade Name" saw_9,
CASE
WHEN "Job Requisition - Offer Information"."Grade Name" LIKE '%E%' THEN 'Eastern'
WHEN "Job Requisition - Offer Information"."Grade Name" LIKE '%W%' THEN 'Western'
WHEN "Job Requisition - Offer Information"."Grade Name" LIKE '%S%' THEN 'Saudi'
END saw_10,
"Job Requisition - Hiring Team"."Hiring Manager Full Name" saw_11,
"Job Requisition - Hiring Team"."Recruiter Full Name" saw_12,
CASE WHEN "Job Offer - Basic Information"."Last Accepted Date" IS NOT NULL THEN ROUND("Job Offer - Basic Information"."Last Accepted Date" - "Job Requisition - Dates"."Approved Date", 0) ELSE ROUND(CURRENT_DATE - "Job Requisition - Dates"."Approved Date", 0) END saw_13,
"Job Offer - Basic Information"."Creation Date" saw_14,
"Job Offer - Basic Information"."Last Accepted Date" saw_15,

FROM "Recruiting - Recruiting Events Real Time"

Sharmava

Posted the logical sql from advanced tab. There is no filters applied still we are getting duplicate records in the extracts

Nathan CCC

Hi,

Looks like you are query is mixing information from both 1) a job requisition and 2) the offers to candidates who made applications for a job. A job requisition may have many applications by many candidates (including multiple applications by same candidate). That application may or may not outcome with a job offer.

Add in the unique ID. It is likely that you have no "duplicates" just multiple job offers on multiple applications per job requisition. For example, add "Job Offer - Basic Information"."Offer ID".

Your query is missing a fact column. All query that include more than one dimension must have a measure column (yellow measure icon). For example, add "Facts - Job Offer"."Number of Job Offers".

Is this the expected number of rows

select all 0 s_0

, "Job Requisition - Basic Information"."Requisition Number" as requisition_number

, cast("Job Requisition - Basic Information"."Requisition Id" as character) as requisition_id

, "Job Requisition - Dates"."Approved Date" as requisition_approved

, cast("Job Offer - Basic Information"."Offer ID" as character) as offer_id

, "Job Offer - Basic Information"."Last Accepted Date" as offer_last_accepted
, "Recruiting - Recruiting Real Time"."Facts - Job Offer"."Number of Job Offers" as n

from "Recruiting - Recruiting Real Time"

order by 1 asc nulls last, 2 asc nulls last, 3 asc nulls last, 5 asc nulls last