External Data Access: Difference between revisions

From WCS
VisualWikitext
(New page: = Summary = The Calidus 3''pl''-Mobile database can be accessed for client reporting purposes using a Microsoft-standard ODBC data connection. The suggested mechanism for accessing the ...)
 
No edit summary
Line 22: Line 22:
Create a new Access database.  
Create a new Access database.  


[[Image:]]
[[Image:DataAccess1]]


This database can be created locally (or your PC) if this is only being used by you, or can be created on any network machine (for example, the WCS Server) if used by many people.
This database can be created locally (or your PC) if this is only being used by you, or can be created on any network machine (for example, the WCS Server) if used by many people.


[[Image:]]
[[Image:DataAccess2]]


In this case this has been called WCS-Reporting.mdb, created in My Documents
In this case this has been called WCS-Reporting.mdb, created in My Documents




[[Image:]]
[[Image:DataAccess3]]


Choose Get External Data/Link Tables from the File menu.
Choose Get External Data/Link Tables from the File menu.


[[Image:]]
[[Image:DataAccess4]]


Link to your Calidus 3''pl''-Mobile logging database. This is normally in X:\Log.mdb or X:\Log1.mdb.
Link to your Calidus 3''pl''-Mobile logging database. This is normally in X:\Log.mdb or X:\Log1.mdb.


[[Image:]]
[[Image:DataAccess5]]


Link the table ‘RDT Activity’
Link the table ‘RDT Activity’


[[Image:]]
[[Image:DataAccess6]]


Save your new database by closing Access.
Save your new database by closing Access.
Line 53: Line 53:
If this option is unavailable on your PC, you must have an Administrator user do this for you.
If this option is unavailable on your PC, you must have an Administrator user do this for you.


[[Image:]]
[[Image:DataAccess7]]


Create a new System DSN
Create a new System DSN


[[Image:]]
[[Image:DataAccess8]]


Choose ‘Microsoft Access Driver (*.mdb)’.
Choose ‘Microsoft Access Driver (*.mdb)’.
Line 63: Line 63:
If this option is unavailable on your PC, the Microsoft Data Access Components (MDAC) must be installed on your PC.
If this option is unavailable on your PC, the Microsoft Data Access Components (MDAC) must be installed on your PC.


[[Image:]]
[[Image:DataAccess9]]


Ensure the Data Source Name is 8 characters or less.
Ensure the Data Source Name is 8 characters or less.
Line 69: Line 69:
Select your data source using the ‘Select…’ button.
Select your data source using the ‘Select…’ button.


[[Image:]]
[[Image:DataAccess10]]


Click ‘OK’ to save the new data source
Click ‘OK’ to save the new data source


[[Image:]]
[[Image:DataAccess11]]




Line 82: Line 82:
Open Excel and create a new workbook.  
Open Excel and create a new workbook.  


[[Image:]]
[[Image:DataAccess12]]


Choose Import External Data/New Database Query from the Data menu.
Choose Import External Data/New Database Query from the Data menu.
Line 88: Line 88:
At this point, you may be prompted to install Microsoft Query for Excel. This may have to be completed by an Administrator.
At this point, you may be prompted to install Microsoft Query for Excel. This may have to be completed by an Administrator.


[[Image:]]
[[Image:DataAccess13]]


Choose the WCS Reporting Data Source we set up earlier.
Choose the WCS Reporting Data Source we set up earlier.


[[Image:]]
[[Image:DataAccess14]]


Choose the table to report. In this case, you will want the RDT Activity table.
Choose the table to report. In this case, you will want the RDT Activity table.


[[Image:]]
[[Image:DataAccess15]]


Choose the data you want to filter.
Choose the data you want to filter.


[[Image:]]
[[Image:DataAccess16]]


Choose the Sort Order
Choose the Sort Order


[[Image:]]
[[Image:DataAccess17]]


Return the data back into Excel.
Return the data back into Excel.


[[Image:]]
[[Image:DataAccess18]]


Choose the cell into which you want the data to be loaded.
Choose the cell into which you want the data to be loaded.


[[Image:]]
[[Image:DataAccess19]]


Once the data is loaded in the form, you can filter and format the data in any way you want.
Once the data is loaded in the form, you can filter and format the data in any way you want.


[[Image:]]
[[Image:DataAccess20]]




You can refresh the data by choosing Refresh Data from Data menu or the menu shown when right-clicking on the cells.
You can refresh the data by choosing Refresh Data from Data menu or the menu shown when right-clicking on the cells.


[[Image:]]
[[Image:DataAccess21]]


The sheet will retain its formatting.
The sheet will retain its formatting.
Line 137: Line 137:
Keep hitting Next until you get to the end of the query, then click on the ‘View Data in Microsoft Query’ option, before clicking Finish.
Keep hitting Next until you get to the end of the query, then click on the ‘View Data in Microsoft Query’ option, before clicking Finish.


[[Image:]]
[[Image:DataAccess22]]


You will be taken into the Microsoft Query application.
You will be taken into the Microsoft Query application.


[[Image:]]
[[Image:DataAccess23]]


Make sure the Auto-Query button (!) is not pressed in.
Make sure the Auto-Query button (!) is not pressed in.


[[Image:]]
[[Image:DataAccess24]]


Choose the item you wish to parameterise.  
Choose the item you wish to parameterise.  


[[Image:]]
[[Image:DataAccess25]]


Enter a prompt for the value.
Enter a prompt for the value.


[[Image:]]
[[Image:DataAccess26]]


Hit Enter. You will be prompted for an employee code in a popup box. Enter one.
Hit Enter. You will be prompted for an employee code in a popup box. Enter one.


[[Image:]]
[[Image:DataAccess27]]


You can now exit Microsoft Query and return the data to the Excel spreadsheet by clicking the X in the top corner.
You can now exit Microsoft Query and return the data to the Excel spreadsheet by clicking the X in the top corner.


[[Image:]]
[[Image:DataAccess28]]


The data is now selecting only the tasks relevant to employee IJ. Whenever you refresh the data, the Employee will be prompted:
The data is now selecting only the tasks relevant to employee IJ. Whenever you refresh the data, the Employee will be prompted:


[[Image:]]
[[Image:DataAccess29]]


You can add multiple parameters to a query. Each will be prompted for in turn.
You can add multiple parameters to a query. Each will be prompted for in turn.
Line 172: Line 172:
'''Note:''' You have full help on how Microsoft Query works, available using the Help button on the Query forms.
'''Note:''' You have full help on how Microsoft Query works, available using the Help button on the Query forms.


[[Image:]]
[[Image:DataAccess30]]




Line 187: Line 187:
'''RDT Activity table'''
'''RDT Activity table'''


[[Image:]]
[[Image:DataAccess31]]

Revision as of 10:04, 16 May 2011

Summary

The Calidus 3pl-Mobile database can be accessed for client reporting purposes using a Microsoft-standard ODBC data connection.


The suggested mechanism for accessing the data is:


  1. Create a link database
  2. Create an ODBC Data Source
  3. Get the data from the ODBC data source with Microsoft Excel

Details

Set-up Requirements

The Calidus 3pl-Mobile system must be set up for your data.

You must have access to the logging database. This document assumes that this has been created under a network drive X:


Create a Link Database

In order to minimise the impact of the query on the Calidus 3pl-Mobile system, this should be performed on a link database. It is possible to access the database directly, but this is not recommended by OBS.

Create a new Access database.

File:DataAccess1

This database can be created locally (or your PC) if this is only being used by you, or can be created on any network machine (for example, the WCS Server) if used by many people.

File:DataAccess2

In this case this has been called WCS-Reporting.mdb, created in My Documents


File:DataAccess3

Choose Get External Data/Link Tables from the File menu.

File:DataAccess4

Link to your Calidus 3pl-Mobile logging database. This is normally in X:\Log.mdb or X:\Log1.mdb.

File:DataAccess5

Link the table ‘RDT Activity’

File:DataAccess6

Save your new database by closing Access.


Create an ODBC Data Source

Choose Start/Settings/Control Panel/Administrative Tools/Data Sources (ODBC) from the Start menu.

If this option is unavailable on your PC, you must have an Administrator user do this for you.

File:DataAccess7

Create a new System DSN

File:DataAccess8

Choose ‘Microsoft Access Driver (*.mdb)’.

If this option is unavailable on your PC, the Microsoft Data Access Components (MDAC) must be installed on your PC.

File:DataAccess9

Ensure the Data Source Name is 8 characters or less.

Select your data source using the ‘Select…’ button.

File:DataAccess10

Click ‘OK’ to save the new data source

File:DataAccess11


Access the Data in Microsoft Excel

Now that this is set up, you can access the Calidus 3pl-Mobile logging data from external sources. In this example, this is from Microsoft Excel, but could just as easily be from any ODBC-compliant reporting tool, such as Crystal Reports or Cognos Impromtu.


Open Excel and create a new workbook.

File:DataAccess12

Choose Import External Data/New Database Query from the Data menu.

At this point, you may be prompted to install Microsoft Query for Excel. This may have to be completed by an Administrator.

File:DataAccess13

Choose the WCS Reporting Data Source we set up earlier.

File:DataAccess14

Choose the table to report. In this case, you will want the RDT Activity table.

File:DataAccess15

Choose the data you want to filter.

File:DataAccess16

Choose the Sort Order

File:DataAccess17

Return the data back into Excel.

File:DataAccess18

Choose the cell into which you want the data to be loaded.

File:DataAccess19

Once the data is loaded in the form, you can filter and format the data in any way you want.

File:DataAccess20


You can refresh the data by choosing Refresh Data from Data menu or the menu shown when right-clicking on the cells.

File:DataAccess21

The sheet will retain its formatting.


You can then save this worksheet for running in the future.


Adding Parameters

Parameters allow you to specify the data to be returned ‘on the fly’ by saying what you want it to match.


Choose Import External Data/Edit Query from the Data menu.

Keep hitting Next until you get to the end of the query, then click on the ‘View Data in Microsoft Query’ option, before clicking Finish.

File:DataAccess22

You will be taken into the Microsoft Query application.

File:DataAccess23

Make sure the Auto-Query button (!) is not pressed in.

File:DataAccess24

Choose the item you wish to parameterise.

File:DataAccess25

Enter a prompt for the value.

File:DataAccess26

Hit Enter. You will be prompted for an employee code in a popup box. Enter one.

File:DataAccess27

You can now exit Microsoft Query and return the data to the Excel spreadsheet by clicking the X in the top corner.

File:DataAccess28

The data is now selecting only the tasks relevant to employee IJ. Whenever you refresh the data, the Employee will be prompted:

File:DataAccess29

You can add multiple parameters to a query. Each will be prompted for in turn.


Note: You have full help on how Microsoft Query works, available using the Help button on the Query forms.

File:DataAccess30


    1. WCS File Layout

RDT Activity table

File:DataAccess31

Retrieved from ‘https://calidusassist.adcservices.apteancloud.com/calidus-assist/WCS/index.php?title=External_Data_Access&oldid=170’