I am newly introduced to OTBI due to work (about 2 months ago). I have some SQL knowledge, but I am at a loss on how to join to datasets I have.
I want to list my budget and expenses (see screenshot) but just place the YTD revenue also on the report without it being impacted by the calculations of my first analysis. In other words, I just want to place the revenue in my data model underneath each listed fund.
The first image on the screenshot is an old report I’m wanting to recreate. Second image is what I was able to piece together with the two analysis by creating a dashboard. Currently, that middle image gives me all the data I’m wanting. The third image is the Data Model I’ve put together so far. However if I try to pull in the YTD revenue ( “General Ledger – Transactional Balances Real Time”.”Base”.”Year to Date” s_6,) then
it aggregates the data and doesn’t reflect correctly.
I attached some screenshots that I hope provide a little more context. Be easy on me, my department has no dedicated tech lead and the consultants the company hired to assist in this transition have been less than helpful.
I appreciate any help truly.
I created two analyses in OTBI. One analysis uses the Budgetary Control subject area and pulls Budget data, Commitments, Expenditures and what’s remaining for particular funds (accounts). The other analysis looks at the General Ledger subject area and pulls revenue (what all has come in) and then lists the revenue by the same particular funds. **I attached a screengrab of an old report I am attempting to recreate. I redacted out potential sensitive information.
Here is my SQL code I pulled from my analysis for the Revenue portion:
<sub>SET VARIABLE PREFERRED_CURRENCY=’User Preferred Currency 1′;SELECT
0 s_0,
DESCRIPTOR_IDOF(“General Ledger – Transactional Balances Real Time”.”Balancing Segment”.”Balancing Segment Description”) s_1,
“General Ledger – Transactional Balances Real Time”.”Balancing Segment”.”Balancing Segment Description” s_2,
DESCRIPTOR_IDOF(“General Ledger – Transactional Balances Real Time”.”Natural Account Segment”.”Natural Account Segment Description”) s_3,
“General Ledger – Transactional Balances Real Time”.”Natural Account Segment”.”Natural Account Segment Description” s_4,
“General Ledger – Transactional Balances Real Time”.”Time”.”Fiscal Year Number” s_5,
“General Ledger – Transactional Balances Real Time”.”Base”.”Year to Date” s_6,
REPORT_AGGREGATE(“General Ledger – Transactional Balances Real Time”.”Base”.”Year to Date” BY “General Ledger – Transactional Balances Real Time”.”Time”.”Fiscal Year Number”) s_7
FROM “Budgetary Control – Balances Real Time”
WHERE
(((“General Ledger – Transactional Balances Real Time”.”Natural Account Segment”.”Natural Account Segment Code” LIKE ‘3%’ OR “General Ledger – Transactional Balances Real Time”.”Natural Account Segment”.”Natural Account Segment Code” LIKE ‘4%’)) AND (“Ledger”.”Ledger Name” = ‘ACTUALS’) AND (“General Ledger – Transactional Balances Real Time”.”Time”.”Fiscal Year Number” = 2024) AND ((“General Ledger – Transactional Balances Real Time”.”Balancing Segment”.”Balancing Segment Code” LIKE ‘%5000%’ OR “General Ledger – Transactional Balances Real Time”.”Balancing Segment”.”Balancing Segment Code” LIKE ‘%5001%’ OR “General Ledger – Transactional Balances Real Time”.”Balancing Segment”.”Balancing Segment Code” LIKE ‘%5002%’ OR “General Ledger – Transactional Balances Real Time”.”Balancing Segment”.”Balancing Segment Code” LIKE ‘%5003%’ OR “General Ledger – Transactional Balances Real Time”.”Balancing Segment”.”Balancing Segment Code” LIKE ‘%5010%’ OR “General Ledger – Transactional Balances Real Time”.”Balancing Segment”.”Balancing Segment Code” LIKE ‘%5022%’ OR “General Ledger – Transactional Balances Real Time”.”Balancing Segment”.”Balancing Segment Code” LIKE ‘%5035%’ OR “General Ledger – Transactional Balances Real Time”.”Balancing Segment”.”Balancing Segment Code” LIKE ‘%5040%’ OR “General Ledger – Transactional Balances Real Time”.”Balancing Segment”.”Balancing Segment Code” LIKE ‘%5041%’ OR “General Ledger – Transactional Balances Real Time”.”Balancing Segment”.”Balancing Segment Code” LIKE ‘%5050%’ OR “General Ledger – Transactional Balances Real Time”.”Balancing Segment”.”Balancing Segment Code” LIKE ‘%5060%’ OR “General Ledger – Transactional Balances Real Time”.”Balancing Segment”.”Balancing Segment Code” LIKE ‘%5075%’ OR “General Ledger – Transactional Balances Real Time”.”Balancing Segment”.”Balancing Segment Code” LIKE ‘%5099%’ OR “General Ledger – Transactional Balances Real Time”.”Balancing Segment”.”Balancing Segment Code” LIKE ‘%5100%’ OR “General Ledger – Transactional Balances Real Time”.”Balancing Segment”.”Balancing Segment Code” LIKE ‘%5101%’ OR “General Ledger – Transactional Balances Real Time”.”Balancing Segment”.”Balancing Segment Code” LIKE ‘%5102%’ OR “General Ledger – Transactional Balances Real Time”.”Balancing Segment”.”Balancing Segment Code” LIKE ‘%5103%’ OR “General Ledger – Transactional Balances Real Time”.”Balancing Segment”.”Balancing Segment Code” LIKE ‘%5104%’ OR “General Ledger – Transactional Balances Real Time”.”Balancing Segment”.”Balancing Segment Code” LIKE ‘%5106%’ OR “General Ledger – Transactional Balances Real Time”.”Balancing Segment”.”Balancing Segment Code” LIKE ‘%5107%’ OR “General Ledger – Transactional Balances Real Time”.”Balancing Segment”.”Balancing Segment Code” LIKE ‘%5108%’ OR “General Ledger – Transactional Balances Real Time”.”Balancing Segment”.”Balancing Segment Code” LIKE ‘%5109%’ OR “General Ledger – Transactional Balances Real Time”.”Balancing Segment”.”Balancing Segment Code” LIKE ‘%5150%’ OR “General Ledger – Transactional Balances Real Time”.”Balancing Segment”.”Balancing Segment Code” LIKE ‘%5151%’ OR “General Ledger – Transactional Balances Real Time”.”Balancing Segment”.”Balancing Segment Code” LIKE ‘%5159%’ OR “General Ledger – Transactional Balances Real Time”.”Balancing Segment”.”Balancing Segment Code” LIKE ‘%5160%’ OR “General Ledger – Transactional Balances Real Time”.”Balancing Segment”.”Balancing Segment Code” LIKE ‘%9121%’)))
ORDER BY 6 ASC NULLS LAST, 2 ASC NULLS LAST, 3 ASC NULLS LAST, 4 ASC NULLS LAST, 5 ASC NULLS LAST
FETCH FIRST 75001 ROWS ONLY</sub>
Here is my SQL code I pulled from my anaylsis for the Budget portion:
SET VARIABLE PREFERRED_CURRENCY=’User Preferred Currency 1′,ENABLE_DIMENSIONALITY=1;SELECT
0 s_0,
“Budgetary Control – Balances Real Time”.”Time”.”Budget Year” s_1,
“Budgetary Control – Balances Real Time”.”XCC_DEPARTMENT_VI”.”Segment Code” s_2,
“Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Code” s_3,
“Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Description” s_4,
LEFT(RIGHT(“Budgetary Control – Balances Real Time”.”Control Budget Account”.”Concatenated Segements”,21),2) s_5,
SUBSTRING(“Budgetary Control – Balances Real Time”.”XCC_DEPARTMENT_VI”.”Segment Code” FROM 1 FOR 3) s_6,
“Budgetary Control – Balances Real Time”.”Budget”.”Total Budget” s_7,
“Budgetary Control – Balances Real Time”.”Consumption”.”Commitments” s_8,
“Budgetary Control – Balances Real Time”.”Consumption”.”Expenditures” s_9,
“Budgetary Control – Balances Real Time”.”Consumption”.”Obligations” s_10,
“Budgetary Control – Balances Real Time”.”Funds Available”.”Funds Available Amount” s_11,
“General Ledger – Transactional Balances Real Time”.”Base”.”Year to Date” s_12,
REPORT_AGGREGATE(“General Ledger – Transactional Balances Real Time”.”Base”.”Year to Date” BY “Budgetary Control – Balances Real Time”.”Time”.”Budget Year”,”Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Code”) s_13,
REPORT_AGGREGATE(“General Ledger – Transactional Balances Real Time”.”Base”.”Year to Date” BY “Budgetary Control – Balances Real Time”.”Time”.”Budget Year”,”Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Description”,”Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Code”,SUBSTRING(“Budgetary Control – Balances Real Time”.”XCC_DEPARTMENT_VI”.”Segment Code” FROM 1 FOR 3)) s_14,
REPORT_AGGREGATE(“General Ledger – Transactional Balances Real Time”.”Base”.”Year to Date” BY “Budgetary Control – Balances Real Time”.”Time”.”Budget Year”,”Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Description”,”Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Code”,SUBSTRING(“Budgetary Control – Balances Real Time”.”XCC_DEPARTMENT_VI”.”Segment Code” FROM 1 FOR 3),LEFT(RIGHT(“Budgetary Control – Balances Real Time”.”Control Budget Account”.”Concatenated Segements”,21),2)) s_15,
REPORT_SUM(“Budgetary Control – Balances Real Time”.”Budget”.”Total Budget” BY “Budgetary Control – Balances Real Time”.”Time”.”Budget Year”,”Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Code”) s_16,
REPORT_SUM(“Budgetary Control – Balances Real Time”.”Budget”.”Total Budget” BY “Budgetary Control – Balances Real Time”.”Time”.”Budget Year”,”Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Description”,”Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Code”,SUBSTRING(“Budgetary Control – Balances Real Time”.”XCC_DEPARTMENT_VI”.”Segment Code” FROM 1 FOR 3)) s_17,
REPORT_SUM(“Budgetary Control – Balances Real Time”.”Budget”.”Total Budget” BY “Budgetary Control – Balances Real Time”.”Time”.”Budget Year”,”Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Description”,”Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Code”,SUBSTRING(“Budgetary Control – Balances Real Time”.”XCC_DEPARTMENT_VI”.”Segment Code” FROM 1 FOR 3),LEFT(RIGHT(“Budgetary Control – Balances Real Time”.”Control Budget Account”.”Concatenated Segements”,21),2)) s_18,
REPORT_SUM(“Budgetary Control – Balances Real Time”.”Consumption”.”Commitments” BY “Budgetary Control – Balances Real Time”.”Time”.”Budget Year”,”Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Code”) s_19,
REPORT_SUM(“Budgetary Control – Balances Real Time”.”Consumption”.”Commitments” BY “Budgetary Control – Balances Real Time”.”Time”.”Budget Year”,”Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Description”,”Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Code”,SUBSTRING(“Budgetary Control – Balances Real Time”.”XCC_DEPARTMENT_VI”.”Segment Code” FROM 1 FOR 3)) s_20,
REPORT_SUM(“Budgetary Control – Balances Real Time”.”Consumption”.”Commitments” BY “Budgetary Control – Balances Real Time”.”Time”.”Budget Year”,”Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Description”,”Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Code”,SUBSTRING(“Budgetary Control – Balances Real Time”.”XCC_DEPARTMENT_VI”.”Segment Code” FROM 1 FOR 3),LEFT(RIGHT(“Budgetary Control – Balances Real Time”.”Control Budget Account”.”Concatenated Segements”,21),2)) s_21,
REPORT_SUM(“Budgetary Control – Balances Real Time”.”Consumption”.”Expenditures” BY “Budgetary Control – Balances Real Time”.”Time”.”Budget Year”,”Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Code”) s_22,
REPORT_SUM(“Budgetary Control – Balances Real Time”.”Consumption”.”Expenditures” BY “Budgetary Control – Balances Real Time”.”Time”.”Budget Year”,”Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Description”,”Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Code”,SUBSTRING(“Budgetary Control – Balances Real Time”.”XCC_DEPARTMENT_VI”.”Segment Code” FROM 1 FOR 3)) s_23,
REPORT_SUM(“Budgetary Control – Balances Real Time”.”Consumption”.”Expenditures” BY “Budgetary Control – Balances Real Time”.”Time”.”Budget Year”,”Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Description”,”Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Code”,SUBSTRING(“Budgetary Control – Balances Real Time”.”XCC_DEPARTMENT_VI”.”Segment Code” FROM 1 FOR 3),LEFT(RIGHT(“Budgetary Control – Balances Real Time”.”Control Budget Account”.”Concatenated Segements”,21),2)) s_24,
REPORT_SUM(“Budgetary Control – Balances Real Time”.”Consumption”.”Obligations” BY “Budgetary Control – Balances Real Time”.”Time”.”Budget Year”,”Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Code”) s_25,
REPORT_SUM(“Budgetary Control – Balances Real Time”.”Consumption”.”Obligations” BY “Budgetary Control – Balances Real Time”.”Time”.”Budget Year”,”Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Description”,”Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Code”,SUBSTRING(“Budgetary Control – Balances Real Time”.”XCC_DEPARTMENT_VI”.”Segment Code” FROM 1 FOR 3)) s_26,
REPORT_SUM(“Budgetary Control – Balances Real Time”.”Consumption”.”Obligations” BY “Budgetary Control – Balances Real Time”.”Time”.”Budget Year”,”Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Description”,”Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Code”,SUBSTRING(“Budgetary Control – Balances Real Time”.”XCC_DEPARTMENT_VI”.”Segment Code” FROM 1 FOR 3),LEFT(RIGHT(“Budgetary Control – Balances Real Time”.”Control Budget Account”.”Concatenated Segements”,21),2)) s_27,
REPORT_SUM(“Budgetary Control – Balances Real Time”.”Funds Available”.”Funds Available Amount” BY “Budgetary Control – Balances Real Time”.”Time”.”Budget Year”,”Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Code”) s_28,
REPORT_SUM(“Budgetary Control – Balances Real Time”.”Funds Available”.”Funds Available Amount” BY “Budgetary Control – Balances Real Time”.”Time”.”Budget Year”,”Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Description”,”Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Code”,SUBSTRING(“Budgetary Control – Balances Real Time”.”XCC_DEPARTMENT_VI”.”Segment Code” FROM 1 FOR 3)) s_29,
REPORT_SUM(“Budgetary Control – Balances Real Time”.”Funds Available”.”Funds Available Amount” BY “Budgetary Control – Balances Real Time”.”Time”.”Budget Year”,”Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Description”,”Budgetary Control – Balances Real Time”.”XCC_FUND_VI”.”Segment Code”,SUBSTRING(“Budgetary Control – Balances Real Time”.”XCC_DEPARTMENT_VI”.”Segment Code” FROM 1 FOR 3),LEFT(RIGHT(“Budgetary Control – Balances Real Time”.”Control Budget Account”.”Concatenated Segements”,21),2)) s_30
FROM “Budgetary Control – Balances Real Time”
WHERE
((“Time”.”Budget Year” = 2024) AND (LEFT(RIGHT(“Control Budget Account”.”Concatenated Segements”,21),2) LIKE ‘5%’) AND ((“XCC_FUND_VI”.”Segment Code” LIKE ‘%5000%’ OR “XCC_FUND_VI”.”Segment Code” LIKE ‘%5001%’ OR “XCC_FUND_VI”.”Segment Code” LIKE ‘%5002%’ OR “XCC_FUND_VI”.”Segment Code” LIKE ‘%5003%’ OR “XCC_FUND_VI”.”Segment Code” LIKE ‘%5010%’ OR “XCC_FUND_VI”.”Segment Code” LIKE ‘%5022%’ OR “XCC_FUND_VI”.”Segment Code” LIKE ‘%5035%’ OR “XCC_FUND_VI”.”Segment Code” LIKE ‘%5040%’ OR “XCC_FUND_VI”.”Segment Code” LIKE ‘%5041%’ OR “XCC_FUND_VI”.”Segment Code” LIKE ‘%5050%’ OR “XCC_FUND_VI”.”Segment Code” LIKE ‘%5060%’ OR “XCC_FUND_VI”.”Segment Code” LIKE ‘%5075%’ OR “XCC_FUND_VI”.”Segment Code” LIKE ‘%5099%’ OR “XCC_FUND_VI”.”Segment Code” LIKE ‘%5100%’ OR “XCC_FUND_VI”.”Segment Code” LIKE ‘%5101%’ OR “XCC_FUND_VI”.”Segment Code” LIKE ‘%5102%’ OR “XCC_FUND_VI”.”Segment Code” LIKE ‘%5103%’ OR “XCC_FUND_VI”.”Segment Code” LIKE ‘%5104%’ OR “XCC_FUND_VI”.”Segment Code” LIKE ‘%5106%’ OR “XCC_FUND_VI”.”Segment Code” LIKE ‘%5107%’ OR “XCC_FUND_VI”.”Segment Code” LIKE ‘%5108%’ OR “XCC_FUND_VI”.”Segment Code” LIKE ‘%5109%’ OR “XCC_FUND_VI”.”Segment Code” LIKE ‘%5150%’ OR “XCC_FUND_VI”.”Segment Code” LIKE ‘%5151%’ OR “XCC_FUND_VI”.”Segment Code” LIKE ‘%5159%’ OR “XCC_FUND_VI”.”Segment Code” LIKE ‘%5160%’)))
ORDER BY 2 ASC NULLS LAST, 4 ASC NULLS LAST, 5 ASC NULLS LAST, 7 ASC NULLS LAST, 6 ASC NULLS LAST
FETCH FIRST 75001 ROWS ONLY