# # # build_raw_standard() # # include "templates.statistics.util.table_utilities"; include "templates.statistics.raw_report_element.standard.add_raw_display_header"; include "templates.statistics.raw_report_element.standard.add_raw_header"; include "templates.statistics.raw_report_element.standard.add_raw_data"; # Note, column values are not formatted in the raw report element because # a.) Strings are usually zoom fields, so the raw report element column value # is required to match the database column value # b.) Strings may be formatted differently, depending on the output format. # (I.e., breaking a line in HTML is different than in PDF) # c.) Numerical column output format may change as well, such as thousand divider. subroutine(build_raw_standard( string session_id, string profile_name, string database_last_modification_time, node query_fields, string report_element_type, bool is_log_detail, bool is_extended_log_detail, node report_element, node raw_report_element, node base_query_header, string root_table_id, int total_rows, bool is_pivot_table, bool is_hierarchical_table, string combined_filter_expression_id, string combined_filter_expression, bool is_table_filter_expression, int number_of_days, node progress_substep_info, node collected_whitelist_objects), ( debug_message("\n\n#### build_raw_standard() START \n\n"); node final_base_query_header; string final_root_table_id; # the ID of the active outer modified root table string final_sorted_table_id; # the ID of the active outer table (sorted or unsorted) string qs; # # Set alpha_root_table_id # # We set a alpha_root_table_id, this is required for set_sum_and_total_in_query_header() # because the outer_table gets a different root_table_id here and we would not anymore # be able to refer to any row_visibility_cutoff_filter_expression from the outer_root_table_id. # So the alpha_root_table_id is only different to the root_table_id for Pivot Tables, in this # case the alpha_root_table_id refers to the table which contains the main report field # and the drill down report_field, this is the root table the expression handling code works # for row visiblity. # # string alpha_root_table_id = root_table_id; # # Set number of rows # # debug_message("report_element: " . node_as_string(report_element) . "\n"); int starting_row = if (report_element?{"starting_row"}) then (@report_element{"starting_row"}) else (1); int ending_row = if (report_element?{"ending_row"}) then (@report_element{"ending_row"}) else (10); if (ending_row > total_rows) then ( ending_row = total_rows; ); if (starting_row > ending_row) then ( starting_row = ending_row; ); # # Set basic variables # string main_report_field_name = @report_element{"report_field"}; set_subnode_value(raw_report_element, "report_field", main_report_field_name); set_subnode_value(raw_report_element, "is_pivot_table", is_pivot_table); if (is_pivot_table) then ( set_subnode_value(raw_report_element, "pivot_report_field", @(report_element . ".pivot_table.report_field")); set_subnode_value(raw_report_element, "number_of_non_aggregating_fields_in_outer_table", ""); # Set total_rows_of_flat_table which is required for pivot table export # where we need the unmodified total number of rows for export! set_subnode_value(raw_report_element, "total_rows_of_flat_table", total_rows); ); set_subnode_value(raw_report_element, "is_hierarchical_table", is_hierarchical_table); set_subnode_value(raw_report_element, "starting_row", starting_row); set_subnode_value(raw_report_element, "ending_row", ending_row); set_subnode_value(raw_report_element, "total_rows", total_rows); set_subnode_value(raw_report_element, "sort_by", ""); set_subnode_value(raw_report_element, "sort_direction", ""); # # Set breakdown values # if (is_hierarchical_table and ?(report_element . ".breakdown.item_value")) then ( node breakdown = report_element{"breakdown"}; string breakdown_item_value = @breakdown{"item_value"}; if (breakdown_item_value ne "") then ( raw_report_element . ".breakdown.field_label" = @(query_fields . "." . main_report_field_name . ".column_label"); raw_report_element . ".breakdown.item_value" = breakdown_item_value; raw_report_element . ".breakdown.additional_filter_item_values" = @breakdown{"additional_filter_item_values"}; ); ); set_subnode_value(raw_report_element, "display_header", ""); set_subnode_value(raw_report_element, "header", ""); if (is_pivot_table) then ( set_subnode_value(raw_report_element, "inner_header", ""); ); set_subnode_value(raw_report_element, "rows", ""); set_subnode_value(raw_report_element, "aggregation_rows", ""); node raw_header = raw_report_element{"header"}; node raw_rows = raw_report_element{"rows"}; node raw_aggregation_rows = raw_report_element{"aggregation_rows"}; # # Get general report element parameters # string sort_by = if (report_element?{"sort_by"}) then (@report_element{"sort_by"}) else (""); string sort_direction = if (report_element?{"sort_direction"}) then (@report_element{"sort_direction"}) else ("descending"); bool show_remainder_row = if (report_element?{"show_remainder_row"}) then (@report_element{"show_remainder_row"}) else (false); bool show_averages_row = if (report_element?{"show_averages_row"}) then (@report_element{"show_averages_row"}) else (false); bool show_min_row = if (report_element?{"show_min_row"}) then (@report_element{"show_min_row"}) else (false); bool show_max_row = if (report_element?{"show_max_row"}) then (@report_element{"show_max_row"}) else (false); bool show_totals_row = if (report_element?{"show_totals_row"}) then (@report_element{"show_totals_row"}) else (false); node pivot_table; string pivot_report_field_name; string pivot_column_label; if (is_pivot_table) then ( pivot_table = report_element{"pivot_table"}; pivot_report_field_name = @pivot_table{"report_field"}; pivot_column_label = @query_fields{pivot_report_field_name}{"column_label"}; ); # # # Setup sort paramaters # # # We don't sort here, because if we drill down (Pivot Table) we can combine sorting # at a lower level! # # # # Handle sort and drill down # # # # # # Set sort parameters for the outer table # # bool is_sort_by = false; string sort_by_report_field_name = ""; string sort_by_table_field_name = ""; string sort_direction = ""; bool is_sort_by_report_field_in_query_header; sort_by_report_field_name = if (report_element?{"sort_by"}) then (@report_element{"sort_by"}) else (""); if (sort_by_report_field_name ne "") then ( is_sort_by_report_field_in_query_header = get_is_sort_by_report_field_in_query_header(base_query_header, sort_by_report_field_name); if (is_sort_by_report_field_in_query_header) then ( # Make sure that we don't sort on the drill down report field if (!is_pivot_table or (sort_by_report_field_name ne pivot_report_field_name)) then ( is_sort_by = true; sort_by_table_field_name = @(query_fields . "." . sort_by_report_field_name . ".table_field_name"); sort_direction = if (report_element?{"sort_direction"}) then (@report_element{"sort_direction"}) else ("descending"); set_subnode_value(raw_report_element, "sort_by", sort_by_report_field_name); set_subnode_value(raw_report_element, "sort_direction", sort_direction); ); ); ); # # # # Normal Table / Pivot Table handling # # # if (!is_pivot_table) then ( # # # Handle normal table # # # Allow to sort log detail reports if is_extended_log_detail if (is_sort_by and (!is_log_detail or is_extended_log_detail)) then ( final_sorted_table_id = get_sorted_table_id(root_table_id, sort_by_report_field_name, sort_direction); # # Check if the table exists, else do the query now # if (!get_table_exists(profile_name, final_sorted_table_id, collected_whitelist_objects)) then ( debug_message("\#### final_sorted_table_id: " . final_sorted_table_id . "\n"); qs = "CREATE TABLE " . final_sorted_table_id . " "; qs .= "SELECT * "; qs .= "FROM " . root_table_id . " "; qs .= "ORDER BY " . sort_by_table_field_name; if (sort_direction eq "descending") then ( qs .= " DESC"; ); ssql_query(qs); # Add new table to collected_whitelist_objects @collected_whitelist_objects{"tables"}{final_sorted_table_id} = true; ); ) else ( # No sorted table or sorted log detail table which is always sorted # via query_db_for_report() but not via ssql. final_sorted_table_id = root_table_id; ); final_base_query_header = base_query_header; final_root_table_id = root_table_id; ) else ( # # # Handle pivot table # # # We need an outer_base_query_header which does not contain # the drill down field (remove drill down field and renumber columns) # debug_message("\n\n Handle Pivot Table START \n\n"); node outer_base_query_header = get_outer_base_query_header(base_query_header, pivot_report_field_name); # debug_message("\n\n" . node_as_string(outer_base_query_header) . "\n\n"); string outer_root_table_id = get_outer_root_table_id(root_table_id); # debug_message("\n\n" . node_as_string(outer_base_query_header) . "\n\n"); # # # Get outer_sorted_table_id # # string outer_sorted_table_id; if (is_sort_by) then ( outer_sorted_table_id = get_sorted_table_id(outer_root_table_id, sort_by_table_field_name, sort_direction); ) else ( outer_sorted_table_id = outer_root_table_id; ); if (!get_table_exists(profile_name, outer_sorted_table_id, collected_whitelist_objects)) then ( # # No sorted outer table exists, check if a an outer root table exists # bool outer_root_table_exists = if (is_sort_by) then (get_table_exists(profile_name, outer_root_table_id, collected_whitelist_objects)) else (false); if (!outer_root_table_exists) then ( # # # Create outer_root_table from root_table # # # Note, we need to fix the sum values in the outer root table # before doing any sorting! # string outer_table_qs = create_outer_table_query_string(outer_base_query_header, root_table_id, outer_root_table_id); # debug_message("\n\n#### outer_table_qs:\n" . outer_table_qs . "\n\n"); debug_message("\n\n Handle Pivot Table 4 \n\n"); ssql_query(outer_table_qs); # Add new table to collected_whitelist_objects @collected_whitelist_objects{"tables"}{outer_root_table_id} = true; debug_message("\n\n Handle Pivot Table 5 \n\n"); # # TEMP - show the table in debug output # # debug_ssql_table(outer_root_table_id); # # # # Fix the sum in the outer_root_table # # # # KHP-RC, fix the sum in the outer_root_table for any field where aggregation_method == unique # before we sort the outer_root_table. In this case the sums are not fixed by overview reports but # by a single query_db_for_report() of the outer table from which we update the sum in the table # we generated by GROUP BY by using ssql only! # For details see sm8_pivot_table_ssql_table_handling.doc in internal_docs. # We also fix all rows of the table because the outer_root_table becomes cached and # will be re-used here and in the graphing code! # # ); # # # Create the sorted outer_root_table # # if (is_sort_by) then ( qs = "CREATE TABLE " . outer_sorted_table_id . " "; qs .= "SELECT * "; qs .= "FROM " . outer_root_table_id . " "; qs .= "ORDER BY " . sort_by_table_field_name; if (sort_direction eq "descending") then ( qs .= " DESC"; ); ssql_query(qs); # Add new table to collected_whitelist_objects @collected_whitelist_objects{"tables"}{outer_sorted_table_id} = true; ); debug_message("\n\n Handle Pivot Table 7 \n\n"); ); final_base_query_header = outer_base_query_header; final_root_table_id = outer_root_table_id; final_sorted_table_id = outer_sorted_table_id; # # # Get final total_rows and ending_row for the outer sorted table # # table check_outer_row_numbers_table = load_table(outer_sorted_table_id); total_rows = table_get_num_rows(check_outer_row_numbers_table); unload_table(check_outer_row_numbers_table); if (ending_row > total_rows) then ( ending_row = total_rows; ); set_subnode_value(raw_report_element, "ending_row", ending_row); set_subnode_value(raw_report_element, "total_rows", total_rows); # debug_message("\n\n#### number of rows in outer root table: " . ending_row . "\n\n"); # # # # Write all drill down data to a global node so that they can be easily accessed # within the follow-up subroutine # # # delete_node("v.pivot_table_info"); v.pivot_table_info = ""; node pivot_table_info = "v.pivot_table_info"; # # # # Set inner table sort parameters # # # bool is_specific_inner_sort_by = false; bool is_inner_sort_by = false; string inner_sort_by_report_field_name = ""; string inner_sort_by_table_field_name = ""; string inner_sort_direction = ""; # # If specific pivot table sort_by is specified # if (pivot_table?{"sort_by"} and (@pivot_table{"sort_by"} ne "")) then ( inner_sort_by_report_field_name = @pivot_table{"sort_by"}; bool is_inner_sort_by_report_field_in_query_header = get_is_sort_by_report_field_in_query_header(base_query_header, sort_by_report_field_name); if ((inner_sort_by_report_field_name ne main_report_field_name) and is_inner_sort_by_report_field_in_query_header) then ( is_specific_inner_sort_by = true; is_inner_sort_by = true; inner_sort_direction = if (pivot_table?{"sort_direction"} and (@pivot_table{"sort_direction"} eq "ascending")) then ("ascending") else ("descending"); ); ); # # Else use sort_by of outer table # if (!is_specific_inner_sort_by and is_sort_by) then ( is_inner_sort_by = true; if (sort_by_report_field_name ne main_report_field_name) then ( inner_sort_by_report_field_name = sort_by_report_field_name; ) else ( inner_sort_by_report_field_name = pivot_report_field_name; ); inner_sort_direction = sort_direction; ); if (is_inner_sort_by) then ( inner_sort_by_table_field_name = @(query_fields . "." . inner_sort_by_report_field_name . ".table_field_name"); ); # # # # Check if the outer_base_query_header contains more than one non-aggregating field # (We need this information when we execute the query for the inner table) # # int number_of_non_aggregating_fields_in_outer_table = get_number_of_non_aggregating_fields(outer_base_query_header); set_subnode_value(pivot_table_info, "number_of_non_aggregating_fields_in_outer_table", number_of_non_aggregating_fields_in_outer_table); # We need number_of_non_aggregating_fields_in_outer_table also in the raw report element to determine any colspan for the inner table # non-aggregating field. set_subnode_value(raw_report_element, "number_of_non_aggregating_fields_in_outer_table", number_of_non_aggregating_fields_in_outer_table); # # # # # Create the query string for the inner table # # # The query string contains a place holder for the "WHERE" clause which becomes # replaced with the actual WHERE data for each specific row. Due the possibility # of multiple string fields in the outer table we create a node in pivot_table_info # to generate the WHERE clause # # # # string inner_table_id = root_table_id . "_inner_table"; string inner_table_qs = "CREATE TABLE " . inner_table_id . " "; inner_table_qs .= "SELECT * "; inner_table_qs .= "FROM " . root_table_id . " "; inner_table_qs .= "__WHERE__CLAUSE__PLACEHOLDER__"; if (is_inner_sort_by) then ( inner_table_qs .= " ORDER BY " . inner_sort_by_table_field_name; if (inner_sort_direction eq "descending") then ( inner_table_qs .= " DESC"; ); ); set_subnode_value(pivot_table_info, "inner_table_id", inner_table_id); set_subnode_value(pivot_table_info, "qs", inner_table_qs); # # Create the node for the inner_table_qs WHERE clause in pivot_table_info.qs_where_structure node # set_subnode_value(pivot_table_info, "qs_where_structure", ""); create_inner_table_query_string_where_structure(pivot_table_info{"qs_where_structure"}, base_query_header, pivot_report_field_name); # debug_message("\n\n#### inner table query string:\n" . inner_table_qs . "\n\n"); # # # # Create the inner_base_query_header # # # # # # The inner_base_query_header is equal the base_query_header! # # IMPORTANT, the inner_base_query_header contains the main_report_field # as the first column or it even contains multiple non-aggregating fields! # We need and keep main the report field and any other non-aggregating field to handle # the inner root table though we need to ignore this non-aggregating fields # when generating the raw rows in sub-sequent subroutines. # clone_node(base_query_header, pivot_table_info . ".base_query_header"); # # # Set inner table aggregation rows # # # Note, remainder_row and totals_rows is always shown in drill down data. # we don't make them an option! bool show_inner_remainder_row = true; bool show_inner_averages_row = if (pivot_table?{"show_averages_row"} and @pivot_table{"show_averages_row"}) then (true) else (false); bool show_inner_min_row = if (pivot_table?{"show_min_row"} and @pivot_table{"show_min_row"}) then (true) else (false); bool show_inner_max_row = if (pivot_table?{"show_max_row"} and @pivot_table{"show_max_row"}) then (true) else (false); bool show_inner_totals_row = true; set_subnode_value(pivot_table_info, "show_remainder_row", show_inner_remainder_row); set_subnode_value(pivot_table_info, "show_averages_row", show_inner_averages_row); set_subnode_value(pivot_table_info, "show_min_row", show_inner_min_row); set_subnode_value(pivot_table_info, "show_max_row", show_inner_max_row); set_subnode_value(pivot_table_info, "show_totals_row", show_inner_totals_row); # # # Create the inner aggregation query headers (only required for remainder, min and max) # # if (show_inner_remainder_row) then ( node inner_remainder_query_header = get_inner_aggregation_query_header("remainder", "inner_remainder_query_header", base_query_header); if (num_subnodes(inner_remainder_query_header) > 0) then ( # Create the ssql query string for the inner remainder cutoff sum table string inner_cutoff_table_id = inner_table_id . "_cutoff"; string inner_cutoff_sum_table_id = inner_table_id . "_cutoff_sum"; string inner_cutoff_sum_qs = get_aggregation_query_string("sum", inner_remainder_query_header, inner_cutoff_table_id, inner_cutoff_sum_table_id); set_subnode_value(pivot_table_info, "inner_cutoff_table_id", inner_cutoff_table_id); set_subnode_value(pivot_table_info, "inner_cutoff_sum_table_id", inner_cutoff_sum_table_id); set_subnode_value(pivot_table_info, "inner_cutoff_sum_qs", inner_cutoff_sum_qs); clone_node(inner_remainder_query_header, pivot_table_info . ".inner_remainder_query_header"); ); ); if (show_inner_min_row) then ( node inner_min_query_header = get_inner_aggregation_query_header("min", "inner_min_query_header", base_query_header); if (num_subnodes(inner_min_query_header) > 0) then ( string inner_min_table_id = inner_table_id . "_min"; string inner_min_qs = get_aggregation_query_string("min", inner_min_query_header, inner_table_id, inner_min_table_id); set_subnode_value(pivot_table_info, "inner_min_table_id", inner_min_table_id); set_subnode_value(pivot_table_info, "inner_min_qs", inner_min_qs); clone_node(inner_min_query_header, pivot_table_info . ".inner_min_query_header"); ); ); if (show_inner_max_row) then ( node inner_max_query_header = get_inner_aggregation_query_header("max", "inner_max_query_header", base_query_header); if (num_subnodes(inner_max_query_header) > 0) then ( string inner_max_table_id = inner_table_id . "_max"; string inner_max_qs = get_aggregation_query_string("max", inner_max_query_header, inner_table_id, inner_max_table_id); set_subnode_value(pivot_table_info, "inner_max_table_id", inner_max_table_id); set_subnode_value(pivot_table_info, "inner_max_qs", inner_max_qs); clone_node(inner_max_query_header, pivot_table_info . ".inner_max_query_header"); ); ); # # # Create the rows_data_header for the inner table (is required to build the aggregation rows, remainder, average, min, ...) # # node inner_header = raw_report_element{"inner_header"}; add_raw_header( base_query_header, inner_header, "", pivot_report_field_name, false, true ); # # # Set inner table number of rows # # int inner_table_number_of_rows = if (pivot_table?{"number_of_rows"}) then (@pivot_table{"number_of_rows"}) else (10); set_subnode_value(pivot_table_info, "number_of_rows", inner_table_number_of_rows); # debug_message("#### pivot_table_info:\n" . node_as_string(pivot_table_info) . "\n\n"); ); # if pivot table # # # Create the display_header # # node display_header = raw_report_element{"display_header"}; add_raw_display_header( final_base_query_header, display_header, is_pivot_table, main_report_field_name, pivot_column_label ); # # # Create the rows data header # # add_raw_header( final_base_query_header, raw_header, main_report_field_name, pivot_report_field_name, is_hierarchical_table, false ); # # # Start building the rows with columns # # if (total_rows > 0) then ( bool use_overview_for_totals = @report_element{"use_overview_for_totals"}; add_raw_data( session_id, profile_name, database_last_modification_time, raw_header, raw_rows, raw_aggregation_rows, alpha_root_table_id, final_root_table_id, final_sorted_table_id, final_base_query_header, starting_row, ending_row, show_remainder_row, show_averages_row, show_min_row, show_max_row, show_totals_row, is_log_detail, is_extended_log_detail, is_pivot_table, is_hierarchical_table, main_report_field_name, pivot_report_field_name, combined_filter_expression, is_table_filter_expression, use_overview_for_totals, number_of_days, progress_substep_info, collected_whitelist_objects ); ); # debug_message("\n\n" . node_as_string(raw_report_element) . "\n\n"); ));