reporting_funcs documentation

reporting_funcs documentation#

class reporting_funcs.Report(report_functions, engine_settings, start_date, end_date, report_type='Queries', interval=None, group_by_interval=False, projections=False)#

Bases: object

__init__(report_functions, engine_settings, start_date, end_date, report_type='Queries', interval=None, group_by_interval=False, projections=False)#

Creates an object that runs a report and stores its outputs

Parameters:

  • report_functions (Dict) – A dictionary of functions to run. Value is

  • engine_settings (Dict) – A dictionary of preferences to initialize a suave_sql object

  • start_date (Str) – The start date of the report period, formatted as ‘YYYY-MM-DD’

  • end_date (Str) – The end date of the report period, formatted as ‘YYYY-MM-DD’

  • report_type – The sql_funcs object to use. Options include Audits, IDHS, ReferralAsks, Queries. Defaults to Queries

  • interval – (optional) The date intervals within the time period to run the report over. ‘MS’ for month, ‘3MS’ for quarter, ‘YS’ for year. Defaults to None

  • group_by_interval (Bool) – (optional) True groups a series of reports by time interval, False groups by each query. Defaults to False

  • projections (Bool) – whether to run projections for grants based on grant start/end. Defaults to False

Examples

Sample report_functions:

funcz = {"highest cases by team": ("highest_cases", (False,)),
        "highest cases by atty": ("highest_cases", (True,)),
        "all client neighborhoods":("dem_address",(False, 'region',)),
        "custody status counts":("custody_status",(True,)),
        "services missing staff":("service_lacks_staff",())
            }

Sample engine settings:

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'
}

Create a report for all clients between January and August:

r = Report(funcz, full_engine_settings, '2024-01-01', '2024-08-31', interval = None, report_type = 'Queries')

See reports for each month between January and August side-by-side:

r = Report(funcz, full_engine_settings, '2024-01-01', '2024-08-31', report_type = 'Queries', interval = 'MS')

See reports for each quarter between January and August one at a time:

r = Report(funcz, full_engine_settings, '2024-01-01', '2024-08-31', report_type = 'Queries', interval = '3MS', group_by_interval = True)
run_a_report()#

Runs the report using sql_funcs’ Tables.run_report() and saves it as a dictionary. Done automatically when using the Report object.

Example

To access the full dictionary of outputs:

r.report_outputs

To access a single query:

r.report_outputs["all client neighborhoods"]
report_to_excel(file_path, query_tabs=False, spacer_rows=1)#

Saves the report outputs to an excel file

Parameters:

  • file_path – The new file path for the excel document

  • query_tabs (Bool) – Whether each function should get its own tab in the file. Must be False if report_functions has multiple levels. Defaults to False

  • spacer_rows (Int) – The number of empty rows to put between each query if not on separate tabs. Defaults to 1

Examples

Save each query on separate tabs:

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

Put two spaces between each query on the same sheet:

r.report_to_excel(file_path="C:/Users/eli/Downloads/test.xlsx", spaces = True)
make_date_dict(interval)#

Makes a dictionary of dates to pass to an sql_funcs object

Parameters:

interval – the time interval to subset the dates. “MS” for month, “3MS” for quarter, “YS” for year

generate_reports()#

Generates reports for each time interval within the report and saves them to self.report_outputs.

consolidate_query_outputs(query_dict)#

Consolidates outputs for a given query over multiple intervals into one df

Parameters:

query_dict – the dictionary of report outputs

class reporting_funcs.ReportFromXlsxTemplate(excel_file_path, t1=None, t2=None, engine=None, include_grants=[], include_tabs=[])#

Bases: object

__init__(excel_file_path, t1=None, t2=None, engine=None, include_grants=[], include_tabs=[])#

Process a Report Template file and run reports for each grant represented in the file. t1, t2, and engine can be set to None for the purpose of reformatting an excel sheet exclusively. The formatted df is preserved as .base_df, the dictionary of outputs/queries is preserved as .query_dict

Parameters:

  • excel_file_path – path to your completed Report Template

  • t1 – start date of report period, formatted as ‘YYYY-MM-DD’.

  • t2 – end date of report period, formatted as ‘YYYY-MM-DD’

  • engine – mysql engine

  • include_grants (optional) – subset of grants within the excel file to include. Can include ‘all’ for non-grant requests

  • include_tabs (optional) – subset of tabs/report types to include (ie: PPR, audit).

filter_query_df(grant_list, tab_list)#

Subset the df to only include grants found in grant_list

Parameters:

grant_list – list of grants to include

format_df(df)#
parse_df(df)#

Reformat base_df to parse numbers in _row/_number cols, extract formatted methods from method col, and generate row_id for each row

Parameters:

df – dataframe of the excel file

format_dictionary()#
generate_output_dictionary(t1=None, t2=None, engine=None, function_dict=None, default_table=None, obj_parameters={}, existing_obj=None)#

when function_dict = None, self.formatted_report_dict is used

format_output_dictionary(query_dict)#
find_missing_outputs(flattened_key)#
add_missing_outputs(flattened_key, engine=None, t1=None, t2=None, default_table=None, obj_parameters={}, existing_obj=None)#
report_to_excel(file_path, spacer_rows=1, include_method=True, format_dict={'chart_title': {'align': 'center', 'bg_color': '#DAEFF5', 'bold': True, 'text_wrap': True, 'underline': True}, 'index': {'bg_color': '#D7D7D7', 'border': 1, 'italic': True}, 'narrative_title': {'align': 'center', 'bg_color': '#51B4CF', 'bold': False, 'text_wrap': True}, 'query_info': {'italic': True}, 'query_info_comments': {'text_wrap': False}, 'standard': {'text_wrap': True}})#