Hi,

I'm trying to pass the following through the Saleslogix OLEDB connection to generate a rank column, however it fails everytime:

"row_number() over(Partition by "acc_code" order by Sum("Sales") DESC )"

If I run it through ODBC, it works fine - but I can't then distribute the report via Saleslogix.

Does anyone know why this won't work or how I can fix it?

Thanks

The obvious answer to why this doesn't work through SalesLogix is that the SLX OLEDB Provider doesn't like it.

As far as a workaround, you could try wrapping the Query on an SQL View (if possible) and then enable that view within SLX. That would allow you to use the View on the Report, and have the view invoke your code.

Now, I mentioned "if possible" on using a View. As is, views don't support the ORDER statement (on most cases). Never the less, this is a typical workaround to try and bypass issues with the Provider.

Another method is to execute a Script before Opening a Report. This would allow you to execute the Query, write the Data to a "Reporting Table" which would be what you use within the Report to display the data.

Off course, since the issue is the provider, you may still need to execute your query via a direct SQL Connection to bypass the SLX OLEDB provider.