Introduction
To access State Data Warehouse using excel or other tools, create a DTS helpdesk ticket, they need to install and configure your ODBC settings to access State Data Warehouse. (StateDW) For ODBC connection, use the DW login (DW1234) DTS will notify you when ODBC is configured.
Instructions
1. In Excel, select the Data menu ribbon. From the left side, select the Get Data menu selection tab. From that drop-down, select From Other Sources, then from the secondary drop-down click From Microsoft Query.
2. The screen will appear for you to select the data source, select StateDW to continue, click OK. (If StateDW does not appear, your machine has not been configured properly) Please contact your local desktop support.
3. You will be prompted for Login ID andPassword, then click OK.
4. Query Wizard - Choose Columns will display. The left side will display the table name, and the right side will display the columns you select. Click the = symbol to expand the tables to view columns. We have expanded to view the columns in the Accounting Journal.
5. Click on the column name, and then select the center arrow icon to move it into the cell Columns in your query. (The linked document has an example)
After you have selected your columns, click Next.
6. Click on the Department column, then on the right-side select the empty cells drop down. Choose your criteria, in this example choose equals.
7. Next, the cell to the right of your criteria will appear white/editable (not grayed out). Enter your criteria, enter a department number. Stay on this window, be sure not to click next yet.
8. You can see that Department is now BOLD. This indicates you have criteria using the Department Column and now your next step is to add additional criteria for Account Type.
9. Filtering data with Account Type, you can select the drop down on the right-side column, and it will display available values. Choose equals, then click the drop down and select 22.
10. Let’s add one more filter, using Fiscal Year. Create a filter where Fiscal Year is greater than 2023. Notice that Account Type, Department and Fiscal Year columns. This indicates that you have created a
filter for multiple items. If you need to revisit any of this criteria, simply click on the bold column name. Once you have completed your criteria and filters, click Next.
11. The Sort Order window will appear. We will sort the results by Document Code.
12. The Finish window appears. You can run your query in Excel and save the query for future re-use. Or if you are savvy using the Query Editor, you can customize your results further. Click Return Data to Microsoft Excel then select Finish.
13. You will then see an Import Data screen. Select how you want to see your data, as a Pivot table, or as a Table of data. Click OK.
14. We have selected to view the data as a PivotTable report.