I am trying my hand at the SQL Query Container type in the Connector Module.
I repeatedly get the error "The specified display field(s) [Jrnlhdr.TransactionDate, Jrnlhdr.JournalEx] do not exist in the data container." when I try running a report on the SQL Query Container
The data container has the 2 fields TransactionDate and JournalEx. When I run the Container's Sample Data, the fields are populated correctly. The problem is, I cannot get the report in the report manager to recognize these fields.
I simplified my query to try and isolate the issue:
When I right click the container, the expressions (TransactionDate and JournalEx) are available to add, which I did.
Over in Report Manager, I selected the container and the 2 available fields TransactionDate and JournalEx.
When I went to set a parameter on TransactionDate, there are no fields in the param tab to select. When I run report manager's sample data, I receive the above error. The other issue I have is a data type not available in Visual Basic error, however the data types are exactly the same as the Table Container that I have and that works fine.
Syntax? If anyone can assist with this, it would be greatly appreciated.
I've watched the webinar's on the connector and report manager + many Youtube videos and nothing addresses this. I compared my syntax to the post by Suzy Rabb on 8/20/2014 at 8:07pm and I am not sure syntax is the issue.
Data fields cannot be added as parameters in a SQL Query container that is why they are not available on your param tab.
1.You need to add a Pass through variable to your container, as per your example you want one for the date so ensure the data type is date
2. In the SQL Query code itself after your FROM statement you need to specify a WHERE clause to say something like WHERE "JrnlHdr"."TransactionDate" <=@TRDATE@
@TRDATE@ is just an example of a pass through variable name you could give it
3. On the Param Tab , add your Pass through variable expression as this would now be available to add
Thank you, Des.
Sorry for the delay, got pulled in another direction.
I established the 2 pass through variables and all is well.
There is a new issue, but I don't think it is Sage related if anyone can advise. I found this article regarding the error: " Variable uses an automation type not supported by Visual Basic" >>
I am trying to run this SQL Query report from the SI Report Manager when I get this error. Is there a way to apply code for the work around? It seems that I am missing something, since the other "Sage Standard" reports do not throw this error.
This is most likely a data type issue. Did you set your pass through variable with a data type of DBDate like this?
See attached screenshot
I think Sage is storing this variable as yyyymmdd and Excel does not recognize that format. I don't remember where I found that data type reference in Sage and I can't seem to recreate the steps to find the data types. It's not making sense, though, since the standard Sage reports export to Excel with no problem. I did a test lookup on the variables and it tests out mm/dd/yyyy.