Jump to content
Changes to the Jaspersoft community edition download ×

Multiple databases/schemas in a report but select which at run time


Recommended Posts

Use the free/cheapee community edition, but I assume this might be a tad easy.
What I have been asked to see if it is possible is this (will try to keep it really simple).
MariaDB or MySQL data source.
Have about 100 databases (really I see them as different schemas) on the reporting server. Every one has just two tables in it, lets call them A and B to keep it simple. B has a foreign key to A so B is a child of A. There are 12 columns in B, 8 in A. The link column is a simple integer called 'ref'. The tables are identical in all but data content.
What I have been asked to find out and so far stumped...
Need to create a table report and at run time need to select which database/schema is being used.
The detail band will be basically from B and each 'section' will group by A... make sense?
Anyway is this possible? I know with a single data source this is easy.... using one of the simple table reports.
I 'think' either need to mess about with attributes in some way, or input parameters but cannot find examples about switching the database/schema just selecting based on some column or other. The docs talk about server attributes but don't tell you how to create a report that might select at runtime. If I use a virtual data source not sure this gives me what I want either as I need to create JDBC data sources to each of the mysql databases anyway and they will get added to....  anyway I create a virtual data source spanning them all.

In studio create a report based on one of the JDBC data sources, you cannot seem to create a virtual data source... publish the report and change to the virtual one....
Now I get stuck as doesn't seem to run, change the data source to a normal one and it works but just for that schema.
Never need more than one schemas data on any report.
Need to 'populate' the schema name at report runtime based on the schema names that exist.
If it makes it easier to understand imagine this is a multi application reporting db and the A and B are the access logs.
The actual data is on other servers but I have pulled it all into this one using federatedx storage engine.... I can run all the SQL to get all the data on the reporting server prefixing with a databse/schema name and it is runs fine. If I test using a direct JDBC data source it works but don't want to create 100's of reports, one for each schema and the schemas get added to and taken away (the whole back end creation of the database and tables using federatedX bit is fine) ... anyone?
Link to comment
Share on other sites

  • Replies 0
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...