Summary:

I am trying to build a report that provides information on intercompany invoicing with details of the time entries, comments and costs associated with it. I am using the "Project Billing - Invoices Real Time" for invoice related information and the two OTBI subject areas, , "Project Costing - Actual Costs Real Time" and "Project Billing - Revenue Real Time" for reporting costs, actual labor hours, comments etc. These two reports do have a common link, but that is limiting. The common link is the intercompany record which unfortunately does not have the labor hours, costs or comments.

Content (required):

I would like to create a simple report with one table being driven by "Project Billing - Invoices Real Time" and another table driven by "Project Costing - Actual Costs Real Time" and "Project Billing - Revenue Real Time". They need to be filtered by a date range but otherwise, they have their own filters to extract the right information such as 'Intercompany invoices' or employee type etc.

Combining these two reports results in values that are not the same as two separate reports. User wants to have a single report that can output to Excel as two tabs. Is this doable?

Version (include the version you are using, if applicable):

22B

Code Snippet (add any code snippets that support your topic, if applicable):

Query 1:

SELECT

   "Invoice Details"."Invoice Number" saw_0,

  "Invoice Details"."Invoice Status" saw_1,

  "Invoice Details"."Invoice Id" saw_2,

  "Invoice Details"."Project Billing Type Code" saw_3,

  "- Invoice Dates"."Invoice Date" saw_4,

  "Receivables Invoice Details"."Accounts Receivable Transaction Number" saw_5,

  "Receivables Invoice Details"."Transaction Date" saw_6,

  "Receiver Fiscal Calendar"."Receiver Fiscal Period" saw_7,

  "Bill-to Customer Account"."Bill To Customer Account Name" saw_8,

  "Project"."Project Number" saw_9,

  "Project"."Project Name" saw_10,

  "- Business Unit"."Business Unit Name" saw_11,

  "Task"."Task Name" saw_12,

  "Task"."Task Number" saw_13,

  "Ledger"."Ledger Currency" saw_14,

  "Invoice Transaction Measures"."Intercompany Invoice Amount in Invoice Currency" saw_15,

  DESCRIPTOR_IDOF("Project Billing - Invoices Real Time"."- Business Unit"."Business Unit Name") saw_16,

  DESCRIPTOR_IDOF("Project Billing - Invoices Real Time"."Task"."Task Name") saw_17,

  DESCRIPTOR_IDOF("Project Billing - Invoices Real Time"."Task"."Task Number") saw_18

FROM "Project Billing - Invoices Real Time"

WHERE

("Invoice Details"."Project Billing Type Code" = 'IC') AND ("Bill-to Customer Account"."Bill To Customer Account Name" = 'SAS Institute (Canada) Inc')

Query 2

SELECT

   "Project"."Project Business Unit" saw_0,

  "Project"."Organization Name" saw_1,

  "Project"."Project Number" saw_2,

  "Project"."Project Name" saw_3,

  "Project Contract Header Details"."Contract Type Name" saw_4,

  "Task"."Task Number" saw_5,

  "Task"."Task Name" saw_6,

  "Expenditure Item"."Expenditure Item" saw_7,

  "Expenditure Type"."Resource Type Name" saw_8,

  "Expenditure Item and Cost Distribution Details"."Expenditure Comment" saw_9,

  "- Expenditure Item and Cost Distribution Measures"."Cost in Transaction Currency" saw_10,

  "- Expenditure Item and Cost Distribution Measures"."Quantity" saw_11,

  "Expenditure Item and Cost Distribution Details"."Expenditure Item Date" saw_12,

  "Employee"."Employee Business Unit" saw_13,

  "Employee"."Employee Name" saw_14,

  "Employee"."Person Number" saw_15,

  "Project Billing - Revenue Real Time"."Revenue Transaction Measures"."Intercompany Revenue Amount in Bill Transaction Currency" saw_16,

  "Project Billing - Revenue Real Time"."Revenue Transaction Measures"."Intercompany Revenue Amount in Contract Currency" saw_17,

  "Project Billing - Revenue Real Time"."Revenue Transaction Measures"."Intercompany Revenue Amount in Revenue Currency" saw_18,

  "Project Billing - Revenue Real Time"."Project"."Project Number" saw_19,

  "Project Billing - Revenue Real Time"."Project"."Project Business Unit" saw_20,

  "Project Billing - Revenue Real Time"."Project"."Organization Name" saw_21,

  "Project Billing - Revenue Real Time"."Task"."Task Number" saw_22,

  DESCRIPTOR_IDOF("Project Costing - Actual Costs Real Time"."Task"."Task Number") saw_23,

  DESCRIPTOR_IDOF("Project Costing - Actual Costs Real Time"."Task"."Task Name") saw_24,

  DESCRIPTOR_IDOF("Project Billing - Revenue Real Time"."Task"."Task Number") saw_25

FROM "Project Costing - Actual Costs Real Time"

WHERE

("Project"."Project Business Unit" = '290_CA_CAD')

AND ("Expenditure Type"."Resource Type Name" IN ('Labor - MX Emp: Nonregular', 'Labor - MX Emp: Regular', 'Labor - US Emp: Nonregular', 'Labor - US Emp: Regular'))

AND ("Project Contract Header Details"."Contract Type Name" = 'Intercompany Contract')