report examples#

initializing a Report object#

A whole bunch of parameters go into creation of the Report object, so let’s take some time to break a few down

report_functions#

the base case report_functions dictionary takes the structure

{‘dataframe title’: (‘function_name’,(function_parameters))}

where:

dataframe title is the key in the eventual report_output dictionary (and the heading/tab of an excel file)
function_name is the name of the suave_sql function to run
function_parameters are the ordered parameters of the suave_sql function (unless all values are default)

example_functions = {'unique participants': ('enrollment',()),
        'program enrollment': ('enrollment',(True, False, False,)),
        'participant races':('dem_race_gender',('race',)),
        'participant genders': ('dem_race_gender',('gender',)),
        }

report_functions can also take a nested dictionary in order to place specific functions on specific sheets in excel

example_nested_functions = {
    'demographics': {
        'unique participants': ('enrollment',()),
        'program enrollment': ('enrollment',(True, False, False,)),
        'participant races':('dem_race_gender',('race',)),
        'participant genders': ('dem_race_gender',('gender',)),
        },
    'legal information':  {
        "rearrested clients": ("legal_rearrested", ()),
        'violent case types':('legal_bonanza',(True, None, None,  'case_type','violent',)),
        'violent case outcomes':('legal_bonanza',(True, None, None,'case_outcomes','violent',))
    }
}

engine_settings#

engine_settings takes all the information one typically uses when initializing a suave_sql object (except for dates) and stores it in a dictionary.

This way, if you’re running a report for each month over a longer period of time, it’s easy to re-initialize a suave_sql object. For instance, if you want to continually use the stints.neon_chd table, your setup would look something like this:

engine = create_engine('mysql+pymysql://eli:password@LCLCN001/neon', isolation_level="AUTOCOMMIT")
full_engine_settings = {
    'engine': engine,
    'print_SQL': True,
    'clipboard': False,
    'mycase': True,
    'default_table': 'stints.neon_chd'
}

Actual Setup of the Report Object#

A full initialization looks something like this:

from report_funcs import Report

example_functions = {'unique participants': ('enrollment',()),
        'program enrollment': ('enrollment',(True, False, False,)),
        'participant races':('dem_race_gender',('race',)),
        'participant genders': ('dem_race_gender',('gender',)),
        }

engine = create_engine('mysql+pymysql://eli:password@LCLCN001/neon', isolation_level="AUTOCOMMIT")
full_engine_settings = {
    'engine': engine,
    'print_SQL': True,
    'clipboard': False,
    'mycase': True,
    'default_table': 'stints.neon_chd'
}

# for one report in a timeframe
r = Report(report_functions = example_functions, engine_settings = full_engine_settings, start_date = '2024-01-01',
end_date = '2024-08-31', report_type = 'Queries', interval = None)

# for monthly reports in the timeframe
r = Report(report_functions = example_functions, engine_settings = full_engine_settings, start_date = '2024-01-01',
end_date = '2024-08-31', report_type = 'Queries', interval = 'MS')

Using the Report Object#

Inspecting Report Outputs#

Upon initialization of the Report object, a dictionary of outputs for each function is created. This can be accessed with:

r.report_outputs

a specific output can also be examined by entering the key of a report_function entry. For instance:

r.report_outputs['unique participants']

# if you used a nested dictionary, you'll need to include that as well

r.report_outputs['demographics']['unique participants']

# the value of each entry is a dataframe, so you can mess around with it in python further if desired
# you could also just copy it to your clipboard

r.report_outputs['demographics']['unique participants'].to_clipboard()

creating an excel file#

it’s real easy to do this, all you need to do is make up a file path for your future excel file.

r.report_to_excel(file_path=r"C:\Users\eli\Downloads\test.xlsx")

If you have a single-level reporting_funcs dictionary, you can set the query_tabs parameter to True to give each item in the dictionary its own sheet in the excel file.

r.report_to_excel(file_path=r"C:\Users\eli\Downloads\test.xlsx", query_tabs = True)

Otherwise, each output dataframe will be placed one after another, with N spacer_rows in between. To create a file with these stacked dataframes and two spaces between each row:

r.report_to_excel(file_path=r"C:\Users\eli\Downloads\test.xlsx", query_tabs = False, spacer_rows = 2)

A nested dictionary will automatically place each subdictionary on a separate tab, so its query_tabs parameter should always be set to False

Example Reports#

IDHS#

engine = create_engine('mysql+pymysql://eli:password@LCLCN001/neon', isolation_level="AUTOCOMMIT")

standard_inputs = {
'engine': engine,
'print_SQL': True,
'clipboard': False,
'mycase': True,
'default_table': 'stints.neon'
}

funcz ={'cvi': {'unique participants': ('idhs_enrollment',()),
        'participant ages': ('idhs_age',(False,)),
        'participant races':('idhs_race_gender',('race',)),
        'participant genders': ('idhs_race_gender',('gender',)),
        'participant languages': ('idhs_language',()),
        'detail-level services': ('idhs_linkages',(False,)),
        'internal, external linkages':('idhs_linkages',(True,)), 
        'CPIC prompting incidents': ('idhs_incidents',(True,)),
        'CPIC prompting incidents': ('idhs_incidents',(False,))
        },
'ppr': {
        'unique participants': ('idhs_enrollment',()),
        'participant races':('idhs_race_gender',('race',)),
        'participant ages': ('idhs_age',(False,)),
        'participant genders': ('idhs_race_gender',('gender',)),
        'outreach eligibility': ('outreach_elig_tally',(True,)),
        'linkages': ('idhs_linkages',(False,)),
        'discharged case plans':('isp_discharged',()),
        'incidents':('incident_tally', ()),
        'incident responses':('incident_response',())
}}
r = Report(funcz, standard_inputs, '2024-07-01', '2024-09-30',interval = None, report_type = 'IDHS')
r.report_to_excel(file_path=r"C:\Users\eli\Downloads\test_idhs.xlsx")

Monthly Referral Meeting#

standard_inputs = {
    'engine': engine,
    'print_SQL': True,
    'clipboard': False,
    'mycase': True,
    'default_table': 'stints.neon'
}

funcz ={'monthly metrics': {"highest cases by team": ("highest_cases", (False,)),
"highest cases by atty": ("highest_cases", (True,)),
'cm_closures':('cm_closures',()),
'neighborhoods':('dem_address',(False, 'community',)),
'custody_statuses':('custody_status',(True,))
    },
'ongoing flags':{'clients missing staff':('service_lacks_staff',()),
    'clients missing ISP':('missing_isp',()),
    'missing outreach assessments':('assess_missing_outreach',()),
    'no contact last month': ('last_30_days',(True,)),
    'cpic notifications':('cpic_summary',())
}  
    }
r = Report(funcz, standard_inputs, '2024-08-01', '2024-08-31',interval = None, report_type = 'ReferralAsks')
r.report_to_excel(file_path=r"C:\Users\eli\Downloads\referral_meeting_1024.xlsx")

Data Digests#

standard_inputs = {
    'engine': engine,
    'print_SQL': True,
    'clipboard': False,
    'mycase': True,
    'default_table': 'stints.neon'
}

funcz ={
    'client programs': ('enrollment_bundles',()),
    'service enrollment':('enrollment',(False, True, False,)),
    'client flow': ('enrollment_flow',()),
    'felony cases':('legal_bonanza',(True, None, None, ['case_type'], 'class_type',)),
    'violent cases': ('legal_bonanza',(True, None, None, ['case_type'], 'violent',)),
    'clients with CM session': ('session_tally',('Case Management',True,)),
    'CM session frequency':('session_frequency',('Case Management',)),
    'CM session total':('session_tally',('Case Management',False,)),
    'clients with outreach session': ('session_tally',('Outreach',True,)),
    'outreach session frequency':('session_frequency',('Outreach',)),
    'outreach session total':('session_tally',('Outreach',False,)),
    'client linkage statuses':('linkages_monthly',(True, True,)),
    'linkage totals': ('linkages_tally',()),
    'linkage education/employment':('linkages_edu_employ',(True, None, False, 19, True,)),
    'isp tracker': ('isp_tracker',(True,True, 45,)),
    'isp goal status': ('isp_goal_tracker',()),
    'assm scores': ('assess_assm',(2, 'min',)),
    'case outcomes': ('legal_bonanza',(True, 'ended', 'highest_outcome', 'case_outcomes', None,)),
    'new cases': ('legal_rearrested', (True,))
    }
r = Report(funcz, standard_inputs, '2024-08-01', '2024-08-31',interval = None, report_type = 'ReferralAsks')
r.report_to_excel(file_path=r"C:\Users\eli\Downloads\data_digest_1024.xlsx")

report examples

Contents