Oracle Fusion Data Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Fusion Data Intelligence› Oracle Fusion Data Intelligence

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Filter from one table are empty when selecting a value from another table

Received Response
41
Views
3
Comments
Anders W. Langholm
Anders W. Langholm Rank 5 - Community Champion
in Oracle Fusion Data Intelligence

Hi all

I have created a data diagram that looks like this:

image.png

I have created a table with columns from both dataset 1 and 2, and it works perfectly.

Then I add columns from both datasets to a dashboard filter, and then my troubles begin… 😅

When I filter on a column from dataset 1, the filters from dataset 2 turns blank, even though there are still values from dataset 2 present in the table…?

In the example below, I filter on the line description (Beskrivelse in danish, sorry 😋). Then the column "Medarbejdernummer" from dataset 2 is present in the table, but dissappears in the filter… WHY? 😄

image.png

It's really confusing, if there is no relation between then tables in this way…

Anyone who knows anything and can explain and perhaps come up wuith a solution?

Kind regards
Anders

Tagged:

Answers

  • JohnW-Oracle
    JohnW-Oracle Rank 7 - Analytics Coach

    Hello @Anders W. Langholm,

    I see you never received a response on this question. We would probably need to see the SQL to offer more.

    In general, filtering on a Description can be problematic because they vary. It looks like your "Medarbejdernummer" column and the description column are the same thing. Do you have multiple descriptions to choose from in the filter if you select the "Medarbejdernummer" first?

    The physical sql will show the join so if the problem is not with the description, that would be a good starting point.

    Regards,

    John

  • Anders W. Langholm
    Anders W. Langholm Rank 5 - Community Champion

    Hi @JohnW-Oracle

    Thank you for replying 😊 I have attached the session query - I'm usually okay with reading them, but this is a bit to advanced for me… 😆

    Sessions_and_Query Log_lonbogsdetaljer.txt

    You are right, the description ("Beskrivelse" in the picture below) and "Medarbejdernavn" are the same and I would also always prefer to filter on a name instead of a description… But right here it makes okay sense, as it is GL transactions of Salaries for employees and therefore the descriptions are only the employee name and nothing else…

    image.png

    I have tried to filter on the column "Medarbejdernavn" from the table "lonbog_medarbejder_info" and then all the filters from the local subject area "Financials - GL Transactions" are empty, even though there is a clear connection and data from both tables are connected and present in the data object as shown in my first post.

    When I filter on "Medarbejdernavn" from "lonbog_medarbejder_info", nothing happens - the data table just keeps loading, but the filters from "Financials - GL Transactions" turns empty…

    So there is a relation between the tables in the data diagram, I just only get data, when filtering on columns from "Financials - GL Transactions" and no matter what I do, once I have chosen a filter from one of the tables, I can only choose to filter on columns from that table, the rest are empty…

    Any thoughts on why and what to do would be greatly appreciated 🤗

    //Anders

  • Anders W. Langholm
    Anders W. Langholm Rank 5 - Community Champion

    I have just had the patience to actually wait for the query to finish, when filtering on "Medarbejdernavn"… 😅 I have filtered on my name, so there should only be around 600 records, but I end up with a "Max Row Limit Exceeded" error message, which clearly indicates, that the connection is not there, regardless of what I would expect based on my assumptions from earlier comments:

    Error during query processing (SQLExecDirectW).
    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.
    (HY000)
    State: HY000. Code: 43275. [nQSError: 43275] Message returned from OBIS [ecid:faa6c244-b2ac-48df-aaf6-bbd4083b540c-000159e3,0:1:2:6 ts:2025-08-05T07:09:43.001+00:00].
    (HY000)
    State: HY000. Code: 43121. [nQSError: 43121] Max Row Limit Exceeded
    (HY000)
    State: HY000. Code: 43121. [nQSError: 43121] Max Row Limit Exceeded
    (HY000)
    State: HY000. Code: 43121. [nQSError: 43121] Max Row Limit Exceeded
    (HY000)
    State: HY000. Code: 43119. [nQSError: 43119] Query Failed:
    (HY000)
    State: HY000. Code: 60008. [nQSError: 60008] The query for user 'andaj' exceeded the maximum query governing rows 2000000 from the database 'Oracle_Data_Warehouse'. (HY000)
    SQL Issued: SET VARIABLE ENABLE_DIMENSIONALITY=1;SELECT
    0 s_0,
    "Financials - GL Detail Transactions"."Cost Center Hierarchy"."Cost Center Level 30 Description" s_1,
    "Financials - GL Detail Transactions"."Cost Center Hierarchy"."Cost Center Level 30 Name" s_2,
    "Financials - GL Detail Transactions"."Cost Center"."Cost Center Description" s_3,
    "Financials - GL Detail Transactions"."Document Details"."Line Description" s_4,
    "Financials - GL Detail Transactions"."GL Accounting Date"."GL Accounting Period" s_5,
    "Financials - GL Detail Transactions"."GL Segment1"."Segment Code" s_6,
    "Financials - GL Detail Transactions"."GL Segment10"."Segment Code" s_7,
    "Financials - GL Detail Transactions"."GL Segment10"."Segment Description" s_8,
    "Financials - GL Detail Transactions"."GL Segment6"."Segment Name" s_9,
    "Financials - GL Detail Transactions"."GL Segment7"."Segment Description" s_10,
    "Financials - GL Detail Transactions"."GL Segment8"."Segment Description" s_11,
    "Financials - GL Detail Transactions"."Natural Account"."Natural Account Code" s_12,
    "Financials - GL Detail Transactions"."Natural Account"."Natural Account Description" s_13,
    "Financials - GL Detail Transactions"."Time"."Fiscal Date" s_14,
    DESCRIPTOR_IDOF("Financials - GL Detail Transactions"."Cost Center Hierarchy"."Cost Center Level 30 Description") s_15,
    DESCRIPTOR_IDOF("Financials - GL Detail Transactions"."Cost Center"."Cost Center Description") s_16,
    DESCRIPTOR_IDOF("Financials - GL Detail Transactions"."GL Segment10"."Segment Description") s_17,
    DESCRIPTOR_IDOF("Financials - GL Detail Transactions"."GL Segment6"."Segment Name") s_18,
    DESCRIPTOR_IDOF("Financials - GL Detail Transactions"."GL Segment7"."Segment Description") s_19,
    DESCRIPTOR_IDOF("Financials - GL Detail Transactions"."GL Segment8"."Segment Description") s_20,
    DESCRIPTOR_IDOF("Financials - GL Detail Transactions"."Natural Account"."Natural Account Description") s_21,
    SORTKEY("Financials - GL Detail Transactions"."GL Accounting Date"."GL Accounting Period") s_22,
    XSA('andaj'.'Lonbog_medarbejder_info')."DW_BIP_X_LONBOG3"."ANSAT_FRA" s_23,
    XSA('andaj'.'Lonbog_medarbejder_info')."DW_BIP_X_LONBOG3"."ANSAT_TIL" s_24,
    XSA('andaj'.'Lonbog_medarbejder_info')."DW_BIP_X_LONBOG3"."CPR" s_25,
    XSA('andaj'.'Lonbog_medarbejder_info')."DW_BIP_X_LONBOG3"."JOB_NAVN" s_26,
    XSA('andaj'.'Lonbog_medarbejder_info')."DW_BIP_X_LONBOG3"."MEDARBEJDERNAVN" s_27,
    XSA('andaj'.'Lonbog_medarbejder_info')."DW_BIP_X_LONBOG3"."MEDARBEJDERNUMMER" s_28,
    XSA('andaj'.'Lonbog_satsgrundlag')."DW_BIP_X_LONBOG_SATSGRUNDLAG"."SATS_GRUNDLAG" s_29,
    "Financials - GL Detail Transactions"."GL Detail Transactions (LC)"."Net Amount" s_30,
    REPORT_SUM("Financials - GL Detail Transactions"."GL Detail Transactions (LC)"."Net Amount" BY ) s_31,
    REPORT_SUM(XSA('andaj'.'Lonbog_timer')."DW_BIP_X_LONBOG_TIMER"."TIMER" BY ) s_32,
    XSA('andaj'.'Lonbog_timer')."DW_BIP_X_LONBOG_TIMER"."TIMER" s_33
    FROM "Financials - GL Detail Transactions"
    WHERE
    (XSA('andaj'.'Lonbog_medarbejder_info')."DW_BIP_X_LONBOG3"."MEDARBEJDERNAVN" = 'Anders Worm Langholm')
    FETCH FIRST 500001 ROWS ONLY