Ran into an issue today with creating a calculated column in one BIE and it is causing an ambiguous column error in another BIE. For example, to duplicate the issue, add a calculated column called "My Transaction" to the Invoice BIE and use the Transaction Column. The BIE screen will place TranID in the calculation field. Save this and test the Invoice BIE and it will work fine. However, open the Sales Order BIE and select an order with Invoices Preview selected and you will receive the ambiguous column error.

This is because only the column name was saved for the calculated column and not vdvInvoice.TranID. Since TranID is a common column name in Sage 500, it conflicted with the join to vdvSalesOrder in the query Sage 500 created. Calculated Column calculation values should include the view name as well to avoid this issue as most users do not have access to SQL Server tools to troubleshoot the issue or would be familiar with the view name.

by: Louis D. | over a year ago | 4 - Reports / BI