mibuso.com
Home› NAV Three Tier
Options

Cross/span database query

joswaltjoswalt 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

  • Options
    David_SingletonDavid_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
  • Options
    joswaltjoswalt 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.
  • Options
    joswaltjoswalt Member Posts: 5
    Is it possible to create the report with the underlying query outside of Navision, just raw SSRS and do a link?
Sign In or Register to comment.