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.
Related Articles
QRG - Using Access to query data warehouse
(A copy of the QRG with screenshots can be found here: https://drive.google.com/file/d/1jcxI35q1Rpz_gLCbOonQaWSgO51duFq7/view ) Introduction Open Database Connectivity (ODBC) database. Similar to the state data warehouse, you’ll need to install an ...
Excel Querying Data Warehouse (Microsoft Query)
It appears recent Office 365 Updates (2024) have impacted the menu choices for those of you who use the Microsoft Query to access State Data Warehouse. If you no longer see the Microsoft Query in the 'Other' query option, please see below for ...
Data Warehouse - Data Dictionary
Information Data Warehouse has published a Data Dictionary, sharing all of the State Datawarehouse Tables and Fields. Some tables have details and joins to use, where other less commonly used tables simply share the field names. This should be ...
Labor Plus Query
Labor Plus is a link within the Online Accounting Journal query. If you have accesss to the online accounting journal, then you have access to see Labor Plus. For privacy reasons, State Data Warehouse hides many vendor names. If you were to add the ...
QRG - Table Export All Available Columns
Description and Purpose When working with pages that have grids, sometimes it is easier to export the data to a spreadsheet which allows easy manipulation of the data to get the desired results. When selecting Export All. FINET allows you to either ...