Alchemex offers the facility to consolidate data across more than 1 company or database.
To enable the report to prompt for connection information at run time and therefore give us a choice which database to run from, we need to modify the Alchemex Administrator module to be dynamic. The connection properties need to be modified to use PICKLIST functionality.
The following PICKLIST functionality exists:
- PICKLIST – allows you to choose ONE database at time of running the report
- PICKLISTM – allows you to choose MULTIPLE databases at time of running the report
- PICKLISTA – automatically runs the report for ALL connections
- PICKLISTDSN – Presents you with a list of all available DSN connections but only allows you to select ONE.
Add a PICKLIST Connection
- Rightclick the Driver and click Add Connection.
- Complete the Connection Info window by adding a Connection Name.
- In the Access Database (mdb) field enter your PICKLIST Information.
PICKLISTM=Select your DB;RKLA::C:Alchemex60RKL Trading A.mdb;RKLB::C:Alchemex60RKL Trading B.mdb;RKLC::C:Alchemex60RKL Trading C.mdb
Select your DB; – This is the text that will appear in the popup box. Remember to always end in ”;”
RKLA :: – Is your descriptive name so anything goes – remember to end in ::
C:Alchemex60RKL Trading A.mdb; – Defines your database location and name, end in “;”
NOTE – There is no ; after the last database entry in the list.
- Click Add
The Connection will be added in the Object window.
Test that the connection works
- Rightclick the connection and click Check/Test
- The following window will appear if the PICKLIST is correct
- Select a DB and click OK.
- The following Alchemex message window will appear.
This is a facility that offers to cache the information for the remainder of the session. Then the user will not be asked for the information again and the cached information will be assumed for all subsequent references to the connection.
- Click Yes.
- Click OK on the Success window that appears
When a dynamic connection has been created, the reports need to be imported on this connection to be able to prompt for connection information at run time.
When running the report that was imported under the PICKLIST connection in the Report Manager module, the Choose Datasources window will appear.
Select which data source to run the report from, or Select All, and then click OK.
All the data from the selected data sources will appear in MS Excel on Sheet1, underneath one another.
The data is now consolidated and ready for reporting.