DARTS : ITS Financial Summary Report

Search Tags: Budget

JIRA Reference:

Report Purpose

This report provides financial summary for IT department.

Report Location

Power BI App- ITS Leadership Reports workspace (The access is limited. )

Report Name

ITS Financial Summary Report

Report Type

Power BI

Data Refresh

Weekly on Sundays at 9am and the day monthly allocation is ready

Table Used

UM_GLQ_REPRT_VW - Ledger Data

SQL code for ITS_CATEGORY- Alias A = UM_GLQ_REPRTNG

CASE
           WHEN A.ACCOUNT >= '40000' AND A.ACCOUNT <= '49999' THEN 'External Revenue'
           WHEN A.ACCOUNT >= '80000' AND A.ACCOUNT <= '89999' THEN 'External Revenue'
           WHEN A.ACCOUNT >= '65800' AND A.ACCOUNT <= '65899' AND A.DEPTID = '8450100' THEN  'Campus Allocation'
           WHEN A.ACCOUNT >= '65800' AND A.ACCOUNT <= '65899' THEN  'Internal Revenue - Interdepartmental Credits'
           WHEN A.ACCOUNT = '64012' AND A.DEPTID = '8750102' THEN  'Internal Revenue - Network Access Charges'
           WHEN A.ACCOUNT = '60802' AND A.DEPTID = '8750101' THEN 'Internal Revenue - Disk and Tape Rental Charges'
           WHEN A.ACCOUNT = '64000' AND A.DEPTID = '8750101' THEN 'Internal Revenue - Telephone Charges'
           WHEN A.ACCOUNT >= '60000' AND A.ACCOUNT <= '60102' THEN 'Consultants, Professional Services'
           WHEN A.ACCOUNT = '60103' THEN  'Supplies, Printing, Catering'
           WHEN A.ACCOUNT >= '60400' AND A.ACCOUNT <= '60499' THEN 'Supplies, Printing, Catering'
           WHEN A.ACCOUNT >= '60600' AND A.ACCOUNT <= '60699' THEN 'Supplies, Printing, Catering'
           WHEN A.ACCOUNT >= '61000' AND A.ACCOUNT <= '61005' THEN 'Supplies, Printing, Catering'
           WHEN A.ACCOUNT >= '61015' AND A.ACCOUNT <= '61099' THEN 'Supplies, Printing, Catering'
           WHEN A.ACCOUNT = '60109' THEN 'Professional Development, Training, Subscriptions'
           WHEN A.ACCOUNT >= '60300' AND A.ACCOUNT <= '60305' THEN  'Professional Development, Training, Subscriptions'
           WHEN A.ACCOUNT >= '60900' AND A.ACCOUNT <= '60999' THEN 'Professional Development, Training, Subscriptions'
           WHEN A.ACCOUNT >= '64000' AND A.ACCOUNT <= '64099' THEN 'Telecom, Computing, Television Services'
           WHEN A.ACCOUNT = '65000' THEN 'Telecom, Computing, Television Services'
           WHEN A.ACCOUNT >= '60800' AND A.ACCOUNT <= '60899' THEN  'Rentals, Leases'
           WHEN A.ACCOUNT = '64700' AND A.DEPTID = '8450100' THEN  'Maintenance, Software'
           WHEN A.ACCOUNT = '64700' AND A.DEPTID = '8750101' AND A.CLASS_FLD = '03' THEN  'Maintenance, Software'
           WHEN A.ACCOUNT = '64700' THEN 'Maintenance, Hardware'
           WHEN A.ACCOUNT = '64750' THEN  'Maintenance, Software'
           WHEN A.ACCOUNT >= '62000' AND A.ACCOUNT <= '62013' THEN 'Equipment, <$5,000'
           WHEN A.ACCOUNT = '62200' THEN 'Equipment, <$5,000'
           WHEN A.ACCOUNT = '62204' THEN 'Equipment, <$5,000'
           WHEN A.ACCOUNT >= '62500' AND A.ACCOUNT <= '62504' THEN  'Equipment, >$5,000 (capital)'
           WHEN A.ACCOUNT = '62700' THEN 'Equipment, >$5,000 (capital)'
           WHEN A.ACCOUNT = '62704' THEN 'Equipment, >$5,000 (capital)'          
           WHEN A.ACCOUNT = '62016' THEN 'Software Licenses, <$5,000'
           WHEN A.ACCOUNT = '62216' THEN  'Software Licenses, <$5,000'
           WHEN A.ACCOUNT = '62516' THEN   'Software Licenses, >$5,000 (capital)'
           WHEN A.ACCOUNT >= '61400' AND A.ACCOUNT <= '61499' THEN 'Travel'
           WHEN A.ACCOUNT >= '61500' AND A.ACCOUNT <= '61599' THEN 'Travel'
           WHEN A.ACCOUNT >= '64100' AND A.ACCOUNT <= '64300' THEN  'Facilities Maintenance, Electricity, Renovation, Moving'
           WHEN A.ACCOUNT = '64409' THEN  'Facilities Maintenance, Electricity, Renovation, Moving'
           WHEN A.ACCOUNT = '64800' THEN  'Facilities Maintenance, Electricity, Renovation, Moving'
           WHEN A.ACCOUNT = '64900' THEN   'Facilities Maintenance, Electricity, Renovation, Moving'
           WHEN A.ACCOUNT = '60701' THEN   'Vehicles'
           WHEN A.ACCOUNT = '62715' THEN   'Vehicles'
           WHEN A.ACCOUNT = '64703' THEN    'Vehicles'
           WHEN A.ACCOUNT >= '64600' AND A.ACCOUNT <= '64699' THEN  'Vehicles'
           WHEN A.ACCOUNT = '66100' THEN   'Depreciation, Debt Service'
           WHEN A.ACCOUNT >= '65900' AND A.ACCOUNT <= '65999' THEN  'Depreciation, Debt Service'
           WHEN A.ACCOUNT >= '71900' AND A.ACCOUNT <= '71999' THEN  'Depreciation, Debt Service'
           WHEN A.ACCOUNT = '61008' THEN 'Transfers'
           WHEN A.ACCOUNT >= '70000' AND A.ACCOUNT <= '79999' THEN  'Transfers'
           WHEN A.ACCOUNT >= '67000' AND A.ACCOUNT <= '67999' THEN  'ERP Capital'
           WHEN A.ACCOUNT >= '50000' AND A.ACCOUNT <= '54999' THEN   'Compensation'
           ELSE  'Other'
         END

Criteria Used

FUND_CODE <> '90'
UM_DEPT_CR_1 = '8ITS'
ACCOUNT between 30000 and 89999
ACCOUNT_PERIOD between 1 and 12
FISCAL_YEAR > 2015

DEPT_AUDIT_ADJUSTMENT = 'N' AND PRIOR_PERIOD_ADJUSTMENT = 'N' AND
YEAR_END_CLOSEOUT = 'N' AND Statistics_Code = ' '

CASE
          WHEN (CASE
                  WHEN A.DEPTID >= '1000010' AND A.DEPTID <= '1000100' THEN 'Y'
                  WHEN A.DEPTID >= '2000010' AND A.DEPTID <= '2000099' THEN 'Y'
                  WHEN A.DEPTID >= '3000010' AND A.DEPTID <= '3000100' THEN 'Y'
                  WHEN A.DEPTID >= '4000010' AND A.DEPTID <= '4000099' THEN 'Y'
                  WHEN A.DEPTID >= '5000010' AND A.DEPTID <= '5000099' THEN 'Y'
                  WHEN A.DEPTID >= '6000010' AND A.DEPTID <= '6000099' THEN 'Y'
                  WHEN A.DEPTID >= '7000010' AND A.DEPTID <= '7000100' THEN 'Y'
                  WHEN A.DEPTID >= '8000010' AND A.DEPTID <= '8000099' THEN 'Y'
                  ELSE 'N'
                END) = 'N' AND 
               (CASE
                  WHEN A.ACCOUNT = '46999' THEN 'Y'
                  WHEN A.ACCOUNT = '66999' THEN 'Y'
                  ELSE 'N'
                END) = 'N' AND 
               (CASE
                  WHEN A.ACCOUNT = '80099' THEN 'Y'
                  WHEN A.ACCOUNT = '80399' THEN 'Y'
                  WHEN A.ACCOUNT = '81599' THEN 'Y'
                  WHEN A.ACCOUNT = '81699' THEN 'Y'
                  WHEN A.ACCOUNT = '81299' THEN 'Y'
                  WHEN A.ACCOUNT = '82299' THEN 'Y'
                  WHEN A.ACCOUNT = '84899' THEN 'Y'
                  WHEN A.ACCOUNT = '70099' THEN 'Y'
                  WHEN A.ACCOUNT = '70399' THEN 'Y'
                  WHEN A.ACCOUNT = '71299' THEN 'Y'
                  WHEN A.ACCOUNT = '71599' THEN 'Y'
                  WHEN A.ACCOUNT = '71699' THEN 'Y'
                  WHEN A.ACCOUNT = '72299' THEN 'Y'
                  ELSE 'N'
                END) = 'N' 
               AND A.STATISTICS_CODE = ' ' THEN 'Y'
          ELSE 'N'
         END) = 'Y')

Need help running a report?

Click on Viewing Power BI Reports for viewing reports. Access to Power BI App workspace is limited.

HELP IS AT HAND!

Round, red help button. Click to email DARTS.

Question, comment, critique? We are here to help and we strive to make our site a comprehensive, user friendly experience. Contact us at DARTS@maine.edu.