No Respond Excel File Generation

Good morning,

my QC domain has 5 directories under “Subject” in the test plan. Archive, BSK, FI, FI-SP, SPK. For each of these folders I use the same SQL query to generate an Excel report. The SQL code is attached.

Generation works for all folders except FI-SP. When generating reports in FI-SP, the application hangs. There are fewer test cases in the folder itself than in the BSK folder, which can be generated error-free in just a few seconds. The query still works perfectly in the SQL editor, but I can't copy anything there.

I also look into the Excel log, but I can't find any errors there. The data center informed me that the DB connection always worked without errors during my attempts.

In addition, I tested some subfolders under FI-SP and was able to generate an Excel report, but only for a maximum of around 100 test cases. There are over 6000 in the BSK folder and that's it.

SELECT TEST.TS_TEST_ID AS "Test ID", TEST.TS_NAME AS "Testfall Name", TEST.TS_DESCRIPTION AS "Testfall Beschreibung", TEST.TS_RESPONSIBLE AS "Verantwortlicher", DESSTEPS.DS_STEP_NAME AS "Testschritt",  DESSTEPS.DS_DESCRIPTION AS "Testschritt Beschreibung", DESSTEPS.DS_EXPECTED AS "Erwartetes Ergebnis Testschritt", TEST.TS_STATUS AS "Status", TEST.TS_DEV_COMMENTS, TEST.TS_USER_03 AS "Testobjekt", TEST.TS_USER_02 AS "Kategorie", TEST.TS_TYPE, TEST.TS_CREATION_DATE AS Datum, TEST.TS_EXEC_STATUS, ALL_LISTS.AL_DESCRIPTION as "Name",

PTH as "Path"

FROM ALL_LISTS

INNER JOIN Test ON TEST.TS_SUBJECT = ALL_LISTS.AL_ITEM_ID

FULL OUTER JOIN DESSTEPS On DESSTEPS.DS_TEST_ID = TEST.TS_TEST_ID,

(SELECT sys_connect_by_path ( AL_DESCRIPTION, '\\') PTH, AL_ITEM_ID TID

FROM ALL_LISTS CONNECT BY prior AL_ITEM_ID = AL_FATHER_ID

start WITH AL_ITEM_ID = 2) /*2 is Subject folder*/

WHERE TID = AL_ITEM_ID AND PTH LIKE '%Subject\\FI-SP\\AT%'

  • Suggested Answer

    0  

    Hello, 

    This issue sounds like it could be caused by either performance limitations in ALM’s Excel report execution or a specific problem with the FI-SP folder. Given that the SQL query works fine in an external SQL editor but not in ALM, have you tried to get your data using Business View Excel reports Business Views Microsoft Excel Add-In | AppDelivery Marketplace ? This Add-in is the best recommendation for customers working with reports.

     

    About your issue, The FULL OUTER JOIN with DESSTEPS could be creating a large dataset, causing ALM to struggle with processing. Even though BSK has more test cases, FI-SP might have more steps per test case, leading to a much larger dataset than anticipated. The outer join with DESSTEPS means that for each test case, multiple rows are generated per test step. As initial recommendation run query without DESSTEPS (Test Step Data), If this runs successfully, the issue is likely related to the number of test steps. Another recommendation is to run reports in smaller batches, If this works, then ALM is timing out due to too much data. A workaround could be to generate multiple reports for different date ranges (e.g., using TS_CREATION_DATE).

    Best regards, 


    Bernal Salzar

    OpenText Customer Support Agent | ALM

    Although I am an OpenText employee, I am speaking for myself and not for OpenText.
    If you found this post useful, give it a “Like” or click on "Verify Answer" under the "More" button.