Purpose In order to modify administer Custom Excel Reports in PACS, the appropriate user right must be granted using the Security Manager Application. Procedure Purpose Build a custom report in PACS Procedure: Examples: 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 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 The grid has the capability of being customized to the extend that the user may, at their discretion choose to perform the following actions: 1.What are Custom Excel Reports?
2.User Rights
3.Building a Custom Report
3.1.Special Notes about SQL Statements
4.Running a Custom Report
4.1.Report Customization Features
FL 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.
