0

Report running on different servers

Jet Reports Historic Posts

January 31, 2011 10:29

None

Follow

Is there a way I can run a Jet Report on various databases that belong to different servers (SQL Instances)?

Thanks

• Facebook
• Twitter
• LinkedIn

7 comments

• 0
  
  Jet Reports Historic Posts January 31, 2011 12:47

  Hi Jackie,
  
  In your report you may access as many data sources as you like. Jet Reports will use your default data source unless you tell your function to link to another data source.
  "Datasource=" or "Connection=" in the NL function is what you need.
  
  More info on these and other special NL functions: http://hansfousert.blogspot.com/2011/01/special-nl-options-part-1.html

• 0
  
  Jet Reports Historic Posts February 01, 2011 06:49

  Hi hansfousert,
  
  I already use datasource= function in my report to filter a range of databases. The problem derives from the fact that not all databases belong in the same server and thus when I run the report, I get results only for those that are in the server.
  
  Find attached a copy of the report I use. The last two databases in the filter belong to a different server (SQL instance).
  
  Thanks

  • Template GL_Count by partner.xlsx
• 0
  
  Jet Reports Historic Posts February 01, 2011 16:49

  Hi!
  
  You have to set up a separate data source in Jet Reports for each SQL database to which you want to connect. Then you could use NL("Rows",NP("DataSources")) to replicate the names of all your data sources (if you want all of them) or NL("Rows",{"DataSource1","DataSource2","DataSource3"}) if you only want to replicate the names of a few of them on your report. Does that make sense?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts February 02, 2011 06:55

  Hi fhilton :)
  
  I have already set up different datasources for each database. I also filter the specific databases I want to run the report.
  
  However, not all databases connect to the same server. If I select an active databsource that connects to a server, the report will generate the report for the databases that have the same server set. The rest will not run unless I run it again selecting another active datasource that has the same server as the remaining databases.

• 0
  
  Jet Reports Historic Posts February 02, 2011 16:06

  Aaah, I see your problem now. So I think what you need to do is actually add another replicator which will replicate the names of one data source on each of the SQL Servers. I have made this modification and am attaching the report. You will need to add one name of a data source from each of your SQL servers to cell C2 separated by |. Does that work for you?
  
  Regards,
  Hughes

  • Template GL_Count by partner Modified.xlsx
• 0
  
  Jet Reports Historic Posts February 03, 2011 08:18

  Thanks fhilton!!
  
  One question. Do I have to change the formula to the rest of the NL fields to filter database & datasource, like you did for company, or it's OK as it is. I didn't see any problems after 2-3 runs…

• 0
  
  Jet Reports Historic Posts February 03, 2011 16:06

  It looked like you already had filters for datasource= and company= in the rest of your NL formulas so I think it's ok as is. I'm glad that is working for you!
  
  Regards,
  Hughes