Cross/span database query

joswalt
joswalt Member Posts: 5
edited 2009-12-02 in NAV Three Tier
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.

Comments

  • David_Singleton
    David_Singleton Member Posts: 5,479
    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.
    David Singleton
  • joswalt
    joswalt Member Posts: 5
    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
    joswalt Member Posts: 5
    Is it possible to create the report with the underlying query outside of Navision, just raw SSRS and do a link?

Categories