Multi-Level Reports - DMS
Line Item Level Reports
In addition to other basic reports you can create in Analysis, you can create line item level reports and export the data to Excel for additional manipulation. Below is an example of how you can view and export line item details for more in-depth reports.
Creating and Exporting Line Item Level Reports
Scenario: You have been asked to generate a report showing the Purchase Order spend data for your agency. You want to show all PUIs for your agency and be able to export line item level detail.
[pic]
|Step |Action |
| |Analysis reporting does have some limitations. Reports are generally 48 hours behind the current date. In addition, the following|
| |items CANNOT be included in Analysis reports: CMBE code (description is available), Org Code, Expansion Option, Certified Forward |
| |Indicator, Transaction History, and Object Code. |
|1. |Click on My Dashboard to begin. |
[pic]
|Step |Action |
|2. |Click “Create Analytical Report”. |
[pic]
|Step |Action |
|3. |Enter an appropriate Title. (No confidential information should be included in the title.) |
|4. |Enter a Detailed Description. (No confidential information should be included in the description.) |
|5. |Select the appropriate Source Data. In this Example, we will select “Purchase Order”. |
[pic]
|Step |Action |
|6. |Select items for Data Field. For our example, choose “P.O. Count and Amount”. |
[pic]
|Step |Action |
|7. |Select items for the row and column fields. For our example, select Agency Name and PUI for the Row and Ordered Date for the |
| |Column. |
|8. |Once you have selected all of the fields you wish to have in your report, click Next to continue. |
|9. |This brings you to the Refine Data page. |
[pic]
|Step |Action |
|10. |You can change the data to be retrieved for your report from years to months, or specify a date range. You can also click |
| |Customize to show more filters for the rows and columns that can be changed. |
[pic]
|Step |Action |
|11. |Click the Show button for all of the fields listed that you would like to appear on your report. For our example, we want all |
| |three fields to show. Click the Done button when finished. |
[pic]
|Step |Action |
|12. |Click the Required box if you want to always have to enter this information. (For example, if you wanted to run a report for a |
| |specific PUI, you could make this field required so that your report only provides data for the PUI you enter.) The “up arrow” will|
| |move a field up and the “down arrow” moves the field down. The “X” removes that field. Click Done when finished. |
[pic]
|Step |Action |
|13. |The year field can be listed by year, quarter, or month. Selecting “Year” will still allow you to break down data later on by |
| |quarter or month. |
[pic]
|Step |Action |
|14. |You can search for an agency by name from the drop down list or by entering all or part of the name. Entering a partial |
| |name will bring up all agencies with that word in the name. Select the correct name clicking on the name in the drop down |
| |list or by clicking Select Others and then checking the box for the appropriate agency(s). Click OK when finished. |
[pic]
This is an example of searching for an agency by typing in the Agency name and clicking Search. The search results will display all agency names with any part of the name you typed. In the above example, we typed in “health” and two agency names were retrieved.
[pic]
|Step |Action |
|15. |To select additional options, click Advanced options. |
[pic]
|Step |Action |
|16. |If you wish to refine your data further, you can select the drop-down box for Ordered Date and choose another date type from the |
| |list. |
[pic]
|Step |Action |
|17. |You can also change the date type from Fiscal to Calendar, if you wish. Once finished, click Run Report. |
[pic]
|Step |Action |
|18. |View report to determine if it provides the information you desired. Notice on this screen only 8 lines of data are displayed. To|
| |change this, click Options and select “All” to display all of the PUIs on the page. |
[pic]
|Step |Action |
|19. |Change the Show maximum to All and click OK. In our example, we have decided to view multiple agency PUI data so we will need to |
| |show more rows of information on the screen. |
[pic]
|Step |Action |
|20. |You can also view your information by quarter by clicking FY2007 and selecting Fiscal Quarter. From there you can click|
| |on FYQ1 and select Month to view information by month. Each time you drill down, the previous selection moves up on the|
| |page. |
[pic]
|Step |Action |
|21. |Notice on this screen, the data is displayed by quarter and the year has moved up to the top of the screen. |
|22. |To set up the information you want to export, click Configure Export. |
[pic]
|Step |Action |
|23. |Select Custom under Data Set to define how you will be exporting the information. Also choose whether you will download into Excel|
| |or CSV format. Downloading into Excel will provide you with charts and graphs as well as your data. Downloading into CSV format |
| |will only provide you with the data. If downloading in CSV format, be sure when saving the file that you use your title name as |
| |follows: “SampleTitle.csv”. If you don’t have the .csv at the end, your data will not download properly. With either format (CSV|
| |or Excel), you can easily move data around later. Once the information is in Excel, you can sort the information, add subtotals, |
| |or move columns around in any order that you wish. |
[pic]
|Step |Action |
|24. |Select the fields of information you want to export. To define line item detail, select the Export Line Level Detail |
| |radio button. |
[pic]
|Step |Action |
|25. |Select the items you want displayed for your line item details. |
[pic]
|Step |Action |
|26. |Hierarchies help you select the levels of information and how they are displayed on the spreadsheet. The drop-down |
| |menus provide you with the list of items you can choose from. You can choose not to export any of the items in the |
| |hierarchies list if that information is not needed. For instance, if you are printing a report only for your agency, |
| |you may choose not to export the agency name. When selecting the date to export, if you want to display the actual |
| |order date, you will want to select DATE from the drop down menu. When the information is exported, it will display, |
| |the fiscal year, the fiscal quarter, the month and the date in separate columns. You can then delete the columns you do|
| |not want to see. |
|27. |Click OK when finished. |
[pic]
|Step |Action |
|28. |Click YES to activate the Active X Controls for downloading into Excel. |
[pic]
|Step |Action |
|29. |Run the Report. The system will display the downloading progress as shown above. |
[pic]
|Step |Action |
|30. |The Download Results page shows you the total number of rows that were downloaded into Excel. Click Done when finished and|
| |Open Excel to view the data that was downloaded. |
[pic]
|Step |Action |
|31. |Click the Pivot Tab to view information by PUI. The drop down box will allow you to view by agency name and by |
| |individual PUI for more detailed information. |
[pic]
[pic]
|Step |Action |
|32. |Click BAR Chart to view Agency PO Count by FY. You can choose the fields that you wish to show on your chart by |
| |selecting them from the Pivot Table Field List on the right side of the page. The fields selected are listed at the |
| |bottom of the page and can be removed by right-clicking the field and selecting Delete. |
[pic]
|Step |Action |
|33. |Click Pie Chart to view PO Count by Agency. |
[pic]
|Step |Action |
|34. |Click the Chart-Table to view Agency data totals by the selected Fiscal Quarter. |
[pic]
|Step |Action |
|35. |Click the Data Tab to view line item information if you downloaded into Excel Format. If you downloaded in CSV format, |
| |the Excel screen will appear as it does above (there will not be any chart or graph tabs). The data displayed on this |
| |screen is what you selected to be displayed. This information can now be sorted and manipulated as you desire in Excel. |
| |To filter your data, click Data, Filter, and AutoFilter. This will then allow you to sort columns as you desire. |
[pic]
|Step |Action |
|36. |Notice that there may be more than one item per DO. Generally, the DO lines will show up together as is noted above. |
[pic]
|Step |Action |
|37. |You can also add subtotals to your data by highlighting the entire section of data, then selecting Data from the menu |
| |bar, and then Subtotals as shown above. |
| |[pic] |
|Step |Action |
|38. |The Subtotal box pops up and allows you to choose which items you wish to have subtotaled. Select the items you want by |
| |clicking the appropriate check boxes and then click OK. |
[pic]
|Step |Action |
|39. |Subtotals are now displayed under each set of DOs in your list with the actual subtotal amount under the Amount column. |
[pic]
|Step |Action |
|40. |This is the Analysis screen view, PO Spend by Agency. Click the Save button to save the report for future reference. |
[pic]
|Step |Action |
|41. |Click the Save button to save the report as originally titled or click the Save As radio button to save under a different |
| |title. |
[pic]
|Step |Action |
|42. |Click the Add to Dashboard button to add a chart or table to your dashboard for easy viewing. |
[pic]
|Step |Action |
|43. |Click Explore Reports to view all saved reports on your dashboard. Any saved charts or tables previously saved for |
| |display on the dashboard will also be visible on this screen. |
[pic]
|Step |Action |
|44. |Click the Title Name (underlined) for the report that you want to use. You can also click the check box next to the title|
| |name to copy, delete, or rename the report. |
[pic]
|Step |Action |
|45. |To move a report to the Public Folder so that others may also use the report, click the check box next to the title you |
| |want to move. Next, select where you want the report to be moved from the drop down list. |
|46. |Click the Move to folder. |
[pic]
|Step |Action |
|47. |Notice that the report no longer shows in the folder items list but the Public folder now shows one item. Click the |
| |Public title to view a list of reports in that folder. |
[pic]
|Step |Action |
|48. |The report that we copied to the Public folder now appears and is ready for other users to view or copy to their |
| |dashboards. |
[pic]
|Step |Action |
|49. |To view reports in the Public Folder, click the Public Folders link. |
[pic]
|Step |Action |
|50. |Type the name of the person whose public folder you wish to view and click Search. If the name is displayed, click on |
| |the name of that person to open up their public folder. |
[pic]
|Step |Action |
|51. |Once the name is displayed, click on the name to open the public folder. |
[pic]
|Step |Action |
|52. |Notice the report title now says that it is a copy. This is because other users are not permitted to change the |
| |original report created. They can, however, copy the report and then change any information they need for their own |
| |reports. |
[pic]
|Step |Action |
|53. |The report may be copied to a viewer’s Personal Folder or their Favorites Folder for quicker accessibility. |
|54. |Click Copy to Personal Folder. |
|55. |Save the report. |
|56. |End Procedure. |
-----------------------
Up and down arrows move field order of the items selected. Clicking the X box will remove that item as a field.
When selecting a PUI to view, click on the PUI number(s) you want to display. Any title with a drop down arrow can be changed depending upon the information you want to review.
Choose between Excel and CSV format for downloading information.
Select Custom to choose which information will be downloaded, including line-item data.
Excel will flash on your tool bar when the information has downloaded and is ready to open. Click on Excel to open the file.
-----------------------
[pic]
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.