Since v2012, Sage 300 Crystal Runtime has had issues running queries that include items not in the data dictionary. This does include views. The work around I typically implement to get around this is to implicitly reference the view using a sql command, "Select * from <viewname>". In crystal designer you can go to Database>Set Datasource Location in order to update your view properties to be a command.

Hello Angel G, I have found the spot to add command under ODBC.  Is there a trick to setting parameters?  First I get "Failed to retrieve data from database" then "Invalid argument provided".  Tx, G

hmmm....You should be able to just use parameters as normal if your command syntax is "select * from <viewname>"

Here in Set Datasource Location you can update a data element like a table or view to be a command. First click on your view name in the top portion of the screen, open your connection in the bottom portion of the screen, click Add Command then Update.

Another option we have gotten to work is to save your report to use a Trusted Connection. This option is not my favorite because then you have to create a sql user with read access for whatever windows user(s) that will run the report.

Hi, I've set up command as datasource.  I was able to add the SQL successfully and swap out all references to the original view.  When I attempt to view the report in Crystal I get the following error:

Any idea how to get around this?

Thanks!

Hi, I've set up command as datasource.  I was able to add the SQL successfully and swap out all references to the original view.  When I attempt to view the report in Crystal I get the following error:

Any idea how to get around this?

Thanks!