MISSING RESOURCE: Variable uses and Automation type not supported in Visual Basic

SUGGESTED

Hello, I am having trouble with a running a few reports in the Report Manager that I have created data containers for in the Connector Tool.  I keep getting a missing resource error like this one:

 https://imgur.com/a/PlNPghp

This occurs right at the very end of running a report out of the connector. 

We first ran into this issue when trying to make a simple item list.  We found that removing the Item Class value from our connector fixed the issue.  I am having this issue again when trying to create a list of all of the Price Levels for each item.  The report contains ItemID, PriceLevelXAmount, SalesPriceXAdjValue, SalesPriceXCalcBase, and SalesPriceXOPCode.  Where X is 1 through 10 for the 10 different price levels.

I’ve created two reports to test this.  The first report uses an SQL Data Container containing this code:

SELECT

LineItem.ItemID,

PriceLevel1Amount, SalesPrice1CalcBase, SalesPrice1OpCode, SalesPrice1AdjValue, 

PriceLevel2Amount, SalesPrice2CalcBase, SalesPrice2OpCode, SalesPrice2AdjValue,

PriceLevel3Amount, SalesPrice3CalcBase, SalesPrice3OpCode, SalesPrice3AdjValue,

PriceLevel4Amount, SalesPrice4CalcBase, SalesPrice4OpCode, SalesPrice4AdjValue,

PriceLevel5Amount, SalesPrice5CalcBase, SalesPrice5OpCode, SalesPrice5AdjValue,

PriceLevel6Amount, SalesPrice6CalcBase, SalesPrice6OpCode, SalesPrice6AdjValue,

PriceLevel7Amount, SalesPrice7CalcBase, SalesPrice7OpCode, SalesPrice7AdjValue,

PriceLevel8Amount, SalesPrice8CalcBase, SalesPrice8OpCode, SalesPrice8AdjValue,

PriceLevel9Amount, SalesPrice9CalcBase, SalesPrice9OpCode, SalesPrice9AdjValue,

PriceLevel10Amount, SalesPrice10CalcBase, SalesPrice10OpCode, SalesPrice10AdjValue

FROM LineItem

The second report uses a container built just from a Table using the LineItem table and the above variables as expressions.

 

The SQL report gives me an error, but the Table report works without error.  Do you see any thing that I could be doing wrong here?  I cannot figure out what that error means.  

  • 0
    SUGGESTED

    The one thing you may try is to begin using a SQL Join container. I find it a lot more flexible for getting to the data you want. Have a look at the canned Sales Analysis container to get a feel for how the Joins work for that style.

  • 0

    So, here I am, back with this same issue 2 years later and I still cannot figure it out.  Right now I am trying to pull information from the BOMItems table.  I can pull from the table without issue in the connector.  However, using the Report Manager fails with this error.  So far, I found that I can run:

    SELECT RecordNumber FROM BOMITEMS and that works without issue.

    But, if run

    SELECT RecordNumber, AssemblyRecordNo FROM BOMITEMS I get this error so it has something to do AssemblyRecordNo.

    I'll keep posting here until hopefully I figure it out.

  • 0 in reply to Ben Baum

    To add this, it only fails if I put that expression in the column of the report.  The container can pull whatever values it wants and the report does not fail as long as certain values are not placed in the report as a column. 

  • 0 in reply to Ben Baum

    I can only pull the RecordNumber.  Any other value results in the Variable uses Automation type not supported error.

  • 0 in reply to Ben Baum

    To add to the stupidity of this issue:

    SELECT LineItem.ItemRecordNumber, BOMItems.AssemblyRecordNo
    FROM BOMItems LEFT JOIN LineItem ON BOMItems.AssemblyRecordNo = LineItem.ItemRecordNumber

    This report selects the same exact data.  Either ItemRecordNumber from LineItem or AssemblyRecordNo from BOMItems.

    It's the exact same piece of data, however, the Report Manager fails if the BOMItems field is included in the report.  It works just fine with the LineItem field.

  • 0 in reply to Ben Baum

    Holy moly, I kind of got it working.

    SELECT
    B.RecordNumber As,
    B.AssemblyRecordNo,
    B.ComponentRecordNo,
    I.ItemID As ParentKey,
    SC.ItemID As ItemCode,
    B.QtyRequired As Quantity,
    CAST (B.RevisionNumber As INT) U_RevNum

    FROM (SELECT AssemblyRecordNo, MAX(RevisionNumber) AS rev FROM Bomitems GROUP BY AssemblyRecordNo) S
    LEFT JOIN Bomitems B ON S.AssemblyRecordNo = B.AssemblyRecordNo AND S.rev=B.RevisionNumber
    LEFT JOIN LineItem I ON B.AssemblyRecordNo = I.ItemRecordNumber
    LEFT JOIN LineItem SC ON B.ComponentRecordNo = SC.ItemRecordNumber

    I can pull everything into a report except for RecordNumber and AssemblyRecordNo.  I can only pull revision number now because I cast it as an integer.  Even though it says that variable is an integer in the documentation and even though I have the expression set as an integer it does not matter unless I cast it in the SQL code.  I tried casting the RecordNumber & AssemblyRecordNo as various types, but could not get them to work.