myDBR uses by default a separate database for reporting objects. This includes the internal tables of myDBR and the created reports (stored procedures). The reason for endorsing the separate database is that with this setup, there is no danger that objects created conflict with the user database objects. Also, in many cases reporting is done on existing (perhaps a 3rd party) database which the vendors tends like to keep to themselves.
Having said that, there is no reason why myDBR objects could not be installed directly to user database. myDBR works just fine with this setup. myDBR tables and procedures are named with 'mydbr' in them and, by default, the reports start with 'sp_DBR' so the database objects are not likely to conflict.
Fetching data from different database differs slightly on database servers:
- On MySQL the syntax is "database.table". In order to make life bit simpler, you can use aliases (like t1 in example) in query to make reference to the tables bit shorter:
select t1.Name, sum(t2.value)
from database1.table1 d1, database2.table2 t2
where t1.id=t2.id
group by t1.Name;
In order to select data in MySQL from another server, you can utilize the Federated storage engine. This is an extra option, so you might want to take a look at the documentation first:
http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html
- On MS SQL Server the full table syntax is "server.database.owner.table". Here too, you can use aliases in query. You also often omit the owner-part in table reference:
select t1.Name, sum(t2.value)
from server1.database1..table1 d1, server2.database2..table2 t2
where t1.id=t2.id
group by t1.Name;
Hope this helps,
--
myDBR Team
