FL Custom Excel Reports

1.What are Custom Excel Reports? #

The custom Excel Report feature in PACS allows user to build custom reports in PACS.  The user is allowed to write SQL statements to pull the data and is given the ability to create parameters so that the end user has a simple user interface to gather the data.  The results can be exported directly into an Excel spreadsheet.   Note:  This feature was introduced in Release 10.0.18.  For more information, please review the release notes for 10.0.18.
Last updated on May 13, 2021

2.User Rights #

Purpose

In order to modify administer Custom Excel Reports in PACS, the appropriate user right must be granted using the Security Manager Application. 

Procedure

  1. Open the Role Editor
  2. Select each role that should be granted the right to administer creation and editing of report configuration in PACS
  3. Navigate to Administrator > Custom Excel Report Configuration, and ensure that the “Modify” right has been assigned. 
  4. Save the role to ensure the right is persisted to the security database.

Last updated on May 13, 2021

3.Building a Custom Report #

Purpose

Build a custom report in PACS

Procedure:

  • Click Green Plus under General
    • This section lists all custom reports existing in the system
  • Enter Data for the following:
    • Category – Defines the functional area within PACS where the report will be added to the report menu.
    • Name – A unique name within the Category used to identify the report.
    • Assign Users – A button to assign which PACS users may access the report
      • This is optional
    • Sub-menu – A user-specified sub-menu under which to place the report menu item within the contextual area defined by the category. 
      • This is optional
      • For example, the menu will appear on the Custom Excel Reports View and can be invoked on the view’s command bar using Reports > Condo & Timeshare > Condo/Timeshare Damage.
  • Enter Query Parameters – these are prompts that the user will choose in order to run the report.  
    • Parameter – this must always begin with @.  You will reference the parameter in the SQL query below
    • Type – type of parameter the user will have in the report
      • Year (Year Combo) – This option will cause the parameter dialog displayed with running the report to include a combo box with all years listed from the pacs_year table + the “FUTURE” option.
      • Year Combo with As Of – This option will cause the parameter dialog displayed when running the report to include a paragraph containing both a year combo box and radio button selections for year state, including Roll Correction (with additional RC # combo selection), Preliminary Certification, Certification, and Final Certification
      • Number (whole #) – This option will cause the parameter dialog displayed when running the report to include a text box with a mask accepting up to 14 digits.
      • Currency (2 decimals) – This option will cause the parameter dialog displayed when running the report to include a text box with a mask accepting up to 12 digits, followed by a decimal and 2 more digits.
      • Percent (3 digits+6 decimals) – This option will cause the parameter dialog displayed when running the report to include a text box with a mask accepting up to 3 digits, followed by a decimal and up to 6 more digits.
      • Code Selector – This option will cause the parameter dialog displayed when running the report to include a combo box listing the codes for the selected Code Type.
      • Code Chooser – This option will cause the parameter dialog displayed when running the report to include a code chooser combo box allowing the user to list multiple comma-separated code values or, using the ellipsis button, invoke the code chooser dialog to list the available codes for the selected Code Type.
      • Date – This option will cause the parameter dialog displayed when running the report to include a Date Picker control.
      • String – This option will cause the parameter dialog displayed when running the report to include a text box accepting any input as a string.
      • Checkbox – This option will cause the parameter dialog displayed when running the report to include a checkbox, which will translate as a bit (0 or 1) parameter value.
    • Label – The label to display for the parameter when prompting the user for the parameter values to run the report.
    • Code Type –  When the user selects either the “Code Selector” or the “Code Chooser” parameter type, they must choose a Code Type from the combo box listing the available code lists to choose from.
      • Example:  Imprv_type will choose the imprv_type_cd in PACS
    • Optional – A flag indicating whether the parameter is optional or required when prompting the user for the parameter values to run the report. 
  • Enter SQL Query – This field is required and is used to specify the SQL that will be run to generate the data for the report
    • Note:  The validity of the SQL is the responsibility of the user. If invalid, then attempts to execute the report may result in an error.
    • User must reference the parameters indicated in the parameters created
      • Example:  If parameter is @year, then the SQL must indicate the year fields with the appropriate PACS field.  
        • ….and prop_val_yr = @year

 

Examples:

 

Last updated on November 6, 2020

3.1.Special Notes about SQL Statements #

Note #1

If the SQL query performs multiple operations that would result in insert, update, delete, or data selection operations prior to executing the final select statement to return query results, then those additional operations must be encapsulated with set nocount on … set nocount off commands, or attempts to execute the report will result in an error. For example:

set nocount on

…perform SQL to insert data into #temp table…

set nocount off

select * from #temp

Harris Govern does not recommend the client update, delete or insert data in the production database using the SQL Statements.  Any actions taken and data affected if user moves forward with any data manipulation are at the sole discretion and responsibility of the user.  If data is mistakenly changed and correction to the data is needed, it does not fall under the support contract and is billable to the client.  Additionally, if data changes are made and there are not appropriate backup tables to recover, Harris Govern does not guarantee data is recoverable.

Note #2

If the parameter if of the “Code Chooser” type, a SQL function built into the PACS database may aid in parsing the parameter value like so:

where tbl.value_cd in (select str from dbo.fn_ParseTable(@codes, ‘,’))

However that it is possible that the user will have selected all codes available for a given parameter.  In such a case, the parameter value will contain the distinct value ‘<All>’.  As such, when using a Code Chooser as an input parameter, the SQL will typically need to be written with this in mind.  For example:

if @imprvTypeCd = ‘<All>’

begin

       select

              imprv_type_cd as [Type],

              primary_use_cd as [Primary Use],

              imprv_desc as [Description],

              imprv_val as [Value],

              convert(bit, case when imprv_homesite = ‘T’ then 1 else 0 end)

as [Is Homesite],

              hs_pct as [Homestead %]

       from imprv with (nolock)

       where prop_val_yr = @year and sup_num = @sup_num and prop_id = @prop_id

       and sale_id = 0

       — Note that @imprvTypeCd is not used to filter the results in this case…

end

else

begin

       select

              imprv_type_cd as [Type],

              primary_use_cd as [Primary Use],

              imprv_desc as [Description],

              imprv_val as [Value],

              convert(bit, case when imprv_homesite = ‘T’ then 1 else 0 end)

as [Is Homesite],

              hs_pct as [Homestead %]

       from imprv with (nolock)

       where prop_val_yr = @year and sup_num = @sup_num and prop_id = @prop_id

       and sale_id = 0

       — But in this case, it is being used…

       and imprv_type_cd in (select str from dbo.fn_ParseTable(@imprvTypeCd, ‘,’))

end

Last updated on November 6, 2020

4.Running a Custom Report #

Purpose

If a report configuration has made a report available for the current user, then that report will be listed under the Reports command button on the Commands bar of the view for the specified functional area.  If no reports are available to the user, then the Reports command button will not be visible at all.

Procedure

  • Browse to Reports>Custom Excel Reports
  • Click the Reports drop down
    • Any reports that have been added to the Reports Menu category and been made available to the current user will be listed under the Reports Command button.
  • User makes selection based on parameters available>Click OK

    • Data is reflected in panel
      • User may click the header to sort and have filter options
      • User may right click the column to render “Group By this Column” options.
  • Export – allows user to save in Excel format
  • Reset View – removes filters and sorts

 

 

Last updated on November 6, 2020

4.1.Report Customization Features #

The grid has the capability of being customized to the extend that the user may, at their discretion choose to perform the following actions:

  • Column Sorting is supported. Click a column header to sort by its values.  Click the column header again to reverse sort.
  • Column Filtering is supported. Click the filter button on the right-hand side of a column to filter rows according to selected column values.
  • Grid columns may be moved and resisted as needed.
  • Column Grouping is supported. The user may do this in either of two ways:
    • Right-click the desired column header and select “Group By This Column” from the context menu that appears:
    • Right-click any column header and select “Show Group By Box”. This causes the grid to display a field grouping box above the grid results where the user may group by several columns.  Columns may then be dragged into the grouping box:
  • Group Summaries Report Summaries are supported.
    • To utilize group summaries, the user must right-click in the Group By Box and select “Show Group Footer” from the context menu that appears:
  • To utilize report summaries, the user must right-click any column header and select “Show Footer” from the context menu that appears

 

Last updated on November 6, 2020
Suggest Edit

%d bloggers like this: