Verified Answers

If an answer to your question is correct, click on "Verify Answer" under the "More" button. The answer will now appear with a checkmark. Please be sure to always mark answers that resolve your issue as verified. Your fellow Community members will appreciate it!  Learn more

  • State Not Answered
  • Replies replies
    7
  • Subscribers subscribers
    169
  • Views views
    316
  • Users 0 members are here
Related
Options

Report Joins in Content section is not working . Getting check with your administrator

adilpata

The table joins that are usually available in the 'Content' tab of Reports cause the report to error out.

The report that I'm creating utilizes the 'Use Advanced SQL techniques' settings in the 'Search' tab. 

I can create reports with joins in the Content tab and SQL within other process apps. But I'm getting an error in the above scenario on one particular process app.

Reason for using Join on the 'Content' tab: Add fields from other related tables.

Here is the error

An error occurred while processing the last report

Please contact your administrator.

The server was processing this URL when this message was created

HTTP:// URL details

Tags:

  • 0  

    Are you trying to setup a JOIN report that also uses Advanced SQL in the same report? I don't know if I have tried that before.

    If you create a JOIN report in this process app without the Advanced SQL, does it work correctly? If you create an Advance SQL report without the JOIN in the process app, does that work correctly? I know you tried both in other process apps. I just want to confirm that each works individually in this process app.

  • 0

    In regards to your question:

    1) 'Are you trying to setup a JOIN report that also uses Advanced SQL in the same report? I don't know if I have tried that before'  Yes, that's correct. This is where I'm having an issue

    2) "If you create a JOIN report in this process app without the Advanced SQL, does it work correctly?" Yes, they work

    3) "If you create an Advance SQL report without the JOIN in the process app, does that work correctly?" Yes, that works also. 

  • 0   in reply to 

    If it's not working, my first thought is that it must be a defect. I suggest creating a case so we can research more and submit the defect for you.

    Thanks,

    Vickie

  • 0

    On the SBM server, view the error in the Windows Application Event log.  This should give you the full "SELECT" statement that SBM is using along with the DB server's error.  This provides much more information than you will see in the SBM GUI.  The syntax SBM uses with JOINED reports can be confusing.  Also note that you can include SQL comments in the Advanced SQL filter clause.  This can be helpful for whoever has to debug the report 5 years from now after you have been promoted to VP :-)

    A trick I occasionally use when debugging complex reports that don't return the correct results, especially reports using Advanced SQL ... I'll deliberately insert an error into my Advanced SQL then check the server's App Event Log to see what SBM is actually doing.

  • 0  

    Use Paul T's method to get the SQL that is being used. Maybe it is possible that the Join is causing the WHERE of the advanced SQL condition to use an ambiguous column name.

  • 0   in reply to 

    I LOVE this tip! What a great idea. Heart eyes

  • 0   in reply to   

    Agreed. Good idea.

Resources

Support
Documentation
Learning Services
Partner Programs
Privacy
Compliance
Help
Company
Privacy Policy
Terms of Use
Cookies Preferences
Accessibility
Anti-Slavery Statement
Support
Contact Us
Careers
Code of Business Conduct and Ethics
The opinions expressed above are the personal opinions of the authors, not of OpenText. By using this site, you accept the Terms of Use. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.), Hewlett Packard Enterprise Company, or Micro Focus. As of January 31, 2023, the Material is now offered by OpenText, a separately owned and operated company. Any reference to the HP, Hewlett Packard Enterprise/HPE, and Micro Focus marks is historical in nature and the HP, Hewlett Packard Enterprise/HPE, and Micro Focus marks are the property of their respective owners.