Create Custom Reports Using Excel
By Ted Henson on Oct 14, 2010
It has come to my attention that several users are cutting data for the standard reports in Knowledge Pathways and pasting the data into Excel to create custom reports. This article is going to describe how to streamline and automate the process using Excel and a connection to the Knowledge Pathways database.
First ensure that you can make a connection to the database. To make the connection, there are two options: Windows authentication or database server authentication. Which one you use will depend on how the server was set up by the database administrator. Contact your database administrator for this information. You need to the following information:
- Name of the database server.
- Have a login to access the database server. The user name must have the KPREPORT role assigned to it. This role allows the login to access the database, but restricts the access to only retrieving data.
- The password for the login.
Next, get a copy of the KNOWLEDGE PATHWAYS CUSTOM REPORTS documentation
Once you have access to the database and documentation, you are ready to get started.
1. Start Excel.
2. On the Data tab, in the Get External Data group, click From Other Sources, and then click From Microsoft Query. (If Microsoft Query has not been installed you will be prompted to install.) The Chose Database Source dialog box opens.
3. Click the Databases tab.
The following steps may be different, depending on your environment. The process only needs to be done once and will then appear in the list to be selected.
4. Double-click <Create New Data Source>
In step 1, type a name to identify the data source, such as KP Database
In step 2, click a driver for the type of database that you are using as your data source.
Select Oracle for Oracle databases and SQL Server for MS SQL databases.
In step 3, click Connect
5. If connecting to an Oracle database you will be prompted to enter the Service Name, User Name and Password. If connecting to a MS SQL database you will be prompted to enter the Server, and possibly Login ID and Password.
6. Click OK
7. In the Database tab, select the database source that you just created.
8. Click OK. The Microsoft Query window will open with the Add Tables dialog on top.
This is where you need to refer to the KNOWLEDGE PATHWAYS CUSTOMREPORTS documentation to refer to to determine which KPVIEWS you need to add to make your query.
Note: Make sure that neither of the "!" buttons directly below the Help menu item are selected. If the one to the right (Auto Query) is selected it will greatly slow down the creation process.
This will be a very simple query. The query will provide a report that will show all users that have the last name Henson, the knowledge paths they have taken, and the precent complete. To do this you need to add three tables. Those tables are KP_ACTIVITY and KPVIEW_SUBJECT_AU_SUMMARY.
To add a table, select the table from the list and click the required tables. Once they have been added you can close the Add Tables dialog box. Resize the tables to make the contents easier to view the contents. If you add a table by mistake you can delete it from the design area by selecting the * and the top of the list and pressing the Delete key.
I the documenation you will notice that each view has one or more primary keys. If the tables selected have the same primay key you need to "join" these fields. For example the KPVIEW_ACTIVITY and KPVIEW_SUBJECT_AU_SUMMARY tables both contain the SUBJECT_AU_ID primary field. To join them, select one of the SUBJECT_AU_ID fields in a table and drag it to the SUBJECT_AU_ID field in other table. This has to be done for all primary key fields.
Once you have your joins made to can start to create your querty. Simply drag the items that you want into the lower area. For example, FIRST_NAME, LAST_NAME, PATH_NAME and COMPLETE_PERCENT. Now is a good time to test your query. Click the "!" (Query Now)button to see the results. To add criteria using Microsoft Query, select the lower section field that you want to added criteria, such as LAST_NAME. Then select Criteria from the menu, then Add Criteria ... and the Add Criteria dialog box opens. Select the criteria, select Add, then Close. Click the "!" (Query Now) button to see the results.
For this example the Microsoft Query looks like this