Cross/span database query

joswalt

We have need to run a report across 2 separate databases. I know we can do it using raw SQL and SSRS, but I'm trying to make it work in Nav 2009 RTC. I have the query working, but am now ready to work on the report. I'm not sure adding a view will work, because they have to do some funky transposing of the data.

David_Singleton

A view should work fine. Just attach the view to a table in Navision. The only thing you need to be careful of is that Navision can not apply filters on views, so it forces SQL to build the filtered view in the TempDB which can be very slow. So make sure that the view is already filtered. You can easily do that with some T-SQL.

joswalt

I'll try that, though my query is using a table variable. The original report was dumping everything into a termporary Navision table, which was rather slow.

joswalt

Is it possible to create the report with the underlying query outside of Navision, just raw SSRS and do a link?