Difference between revisions of "Reports"

From SmartWiki
Jump to: navigation, search
 
(99 intermediate revisions by 3 users not shown)
Line 1: Line 1:
−
{{Banner-UnderConstruction}}
 
−
 
−
 
−
 
 
=Overview=
 
=Overview=
−
The [[SmartSimple]] reporting system is an integral of our business operating system.
+
The [[SmartSimple]] reporting system, an integral part of our business operating system, allows you to manipulate both the contents and the presentation of data contained in one or more of the SmartSimple tables. Unlike a table, reports present only the data that meets the criteria you define - they can also aggregate and display summarized data information. 
−
 
 
−
A report allows you to manipulate both the contents and the presentation of the data contained in one or more of the SmartSimple tables; unlike a table, reports present only the data that meets the criteria you define. They also have the ability to aggregate and display summary information. 
 
−
 
 
−
Reports provide the following features: 
 
  
−
* No additional buying or installation of software
+
You can utilize the functionality of reports in your [[SmartSimple]] [[instance]] without additionally buying or installing software. Reports are completely created from within the standard [[Interface|interface]] and are viewable from any [[Browser|browser]], as well as via '''Microsoft Word '''or '''Excel '''with the appropriate SmartSimple [[Exporting Reports|plug-in]]. 
−
* All creation functionality available within the standard [[:Category:Interface|interface]]
 
−
* Viewable from any [[Browser|browser]] 
 
−
* Accessible via '''Microsoft Word '''or '''Excel '''with the appropriate [[SmartSimple]] plug-in 
 
  
 
'''Benefits of Reports: '''
 
'''Benefits of Reports: '''
  
 
* Display data from one or more tables in one place
 
* Display data from one or more tables in one place
−
* Group-aggregate data from one or more table
+
* Group-aggregate data from one or more tables
 
* Display the grouped data in a variety of different types of graphs
 
* Display the grouped data in a variety of different types of graphs
 
* Export the data into any file format that you wish OR 
 
* Export the data into any file format that you wish OR 
−
* Designate [[Internet Enabled Reports|Internet-enabling]] so that the reports can be linked to public websites 
+
** Designate [[Internet Enabled Reports|Internet-enabling]] so that the reports can be linked to public websites 
 
* Group reports together as '''tabs''', allowing similar topic-based reports to be found together
 
* Group reports together as '''tabs''', allowing similar topic-based reports to be found together
 
* Copy and modify built-in reports as the basis of your customized reports 
 
* Copy and modify built-in reports as the basis of your customized reports 
  
−
[[User Role]]-based [[Role-Based Security|security]] defines who can view the reports that you create from your system.
+
[[User Role]]-based [[Role Based Security|security]] defines who can view the reports that you create from your system.
  
 
<pre> You must be a user with SysAdmin privileges in order to create reports from your SmartSimple system. </pre>
 
<pre> You must be a user with SysAdmin privileges in order to create reports from your SmartSimple system. </pre>
Line 31: Line 20:
 
You will encounter three major components in the reporting system: 
 
You will encounter three major components in the reporting system: 
  
−
* Reports that are created from underlying [[SmartSimple]] database tables - you can connect the required tables to generate a report
+
* '''Reports''' that are created from underlying [[SmartSimple]] database tables - you can connect the required tables to generate a report
−
* Graphs that you create yourself. These are based on existing reports and are used to display the report in a graphic format. 
+
* '''Graphs''' that you create yourself. These are based on existing reports and are used to display the report in a graphic format. 
−
* Dashboards that will display multiple charts on a single page
+
* '''Dashboards''' that will display multiple charts on a single page
  
 
The following diagram shows the relationship between these three types of [[Entity|entities]]:
 
The following diagram shows the relationship between these three types of [[Entity|entities]]:
  
−
:: [[Image:Reporting-componets-800w (2).jpg|link=]]
+
:: [[File:Reporting-componets-800w (2).jpg|600px|border]]
 
===Charts===
 
===Charts===
 
Charts are used to display the information from a report in an easy-to-read “graphic” format.
 
Charts are used to display the information from a report in an easy-to-read “graphic” format.
Line 45: Line 34:
 
* Generally, you will choose a “summary” type report as the basis for the chart, as that type of information is more meaningful when displayed in this manner.
 
* Generally, you will choose a “summary” type report as the basis for the chart, as that type of information is more meaningful when displayed in this manner.
  
−
A [wiki/User user] can only access the chart if they have permission to see the report the chart is based on.
+
<pre> A user can only access the chart if they have permission to see the report that the chart is based on. </pre>
 +
A [[User|user]] can only access the chart if they have permission to see the report the chart is based on.
  
 
===Dashboards===
 
===Dashboards===
Line 53: Line 43:
 
* You can create multiple dashboards to show different sets of key indicators.
 
* You can create multiple dashboards to show different sets of key indicators.
 
* A [[User|user]] can indicate that a specific dashboard should be their '''home page '''within [[SmartSimple]], giving them an immediate view of key information from the system when they first log in.
 
* A [[User|user]] can indicate that a specific dashboard should be their '''home page '''within [[SmartSimple]], giving them an immediate view of key information from the system when they first log in.
−
* Each dashboard needs to be permissioned to the [[User Role|roles]] you wish to be able to see the dashboard.
 
  
 +
<pre> Each dashboard needs to be permissioned for the roles that you wish to be able to see the dashboard. </pre>
 
===Tables===
 
===Tables===
 
In order to make the creation of reports a little simpler, some of the [[SmartSimple]] tables are presented in a [http://en.wikipedia.org/wiki/Denormalization denormalized] format when using the [[Report Builder (Neo)|Report Builder]].
 
In order to make the creation of reports a little simpler, some of the [[SmartSimple]] tables are presented in a [http://en.wikipedia.org/wiki/Denormalization denormalized] format when using the [[Report Builder (Neo)|Report Builder]].
Line 63: Line 53:
  
 
=Configuration - Essentials=
 
=Configuration - Essentials=
 +
==Permissioning Reports==
 +
Access to the '''report-creation''' features is controlled by the [[User Role|role]]-based manager permission. So you must be in a [[User Role|role]] associated with this feature in order to create reports.
 +
 +
<pre> Users who will only need to view reports do not need these manager permissions. </pre>
 +
You must be a member of a [[User Role|role]] with manager permission for '''Reports''' if you wish to do any of the following actions: 
 +
 +
* '''Create''' new reports.
 +
* '''Edit''' or copy existing reports.
 +
* '''Delete''' existing reports, either individual or as batch deletes.
 +
 +
To permission which roles will have the ability to create, edit, and delete reports in your system, follow these steps: 
 +
 +
1. Click on the 9-square menu icon on the top right of your page.
 +
 +
:: {{Icon-Menu}} 
 +
2. Under the heading '''Configuration, '''select '''Roles and Security. '''
 +
 +
:: [[File:Roles and security from menu.png|250px|border]]
 +
3. Under the subheading '''System Security Settings, '''select the hyperlink '''System Feature Permissions. '''
 +
 +
4. Click into the tab labelled '''Action Permission. '''
 +
 +
:: [[File:Action permission.png|400px|border]]
 +
5. Scroll down until you see the field labelled '''Reports - Manager. '''Click into the text box in order to select from a drop-down of [[User Role|user roles]] that will have manager permissions for reports. 
 +
 +
:: [[File:Reports manager permissions.png|350px|border]]
 +
:
 +
:* You can also specifically permission which roles will have the ability to '''Export Data '''from reports. 
 +
 +
6. Click '''Save '''at the bottom of the screen when you are done. 
 +
 
==Accessing Reports==
 
==Accessing Reports==
 
A [[User|user]] who is permissioned for report use can access the reports available to them. Follow these steps to access reports in your SmartSimple system: 
 
A [[User|user]] who is permissioned for report use can access the reports available to them. Follow these steps to access reports in your SmartSimple system: 
Line 68: Line 89:
 
1. Click the 9-square menu icon on the top right of your page.
 
1. Click the 9-square menu icon on the top right of your page.
  
−
:: {{Icon-Menu}} 
+
::: {{Icon-Menu}} 
 
2. Under the heading '''Tools, '''select '''Reports. '''
 
2. Under the heading '''Tools, '''select '''Reports. '''
  
−
:: [[File:Report.png|600px|border]]
+
::: [[File:Report.png|500px|border]]
 
The list view of available system reports will be displayed. 
 
The list view of available system reports will be displayed. 
 +
 +
::: '''[[File:Report list.png|1000px|border]]''' 
 +
The report window consists of the following sections along the top row of tabs:
 +
 +
:* '''Reports: '''This displays a [[List View Overview|list]] of your system's current reports that have been made available to you. 
 +
:* '''[[Global Report Template]]: '''This tab is used to define a customized HTML template for all reports.
 +
:* '''[[Creating a Dashboard to Display a Set of Charts|Dashboards]]: '''This tab is used to create a dashboard to display a set of charts.
 +
:* '''[[Web Access Statistics]]: '''This tab displays statistics on how often your web-enabled reports have been accessed.
 +
:* '''Deleted Reports: '''This tab displays a list of any deleted reports in your system. 
 +
You can filter the results of your report list by choosing a '''Category '''of reports - this can be found at the top right of your page. Any reports that have not been associated with a category will appear in the '''Uncategorized '''category option. Clicking into the '''Category '''field will allow a drop-down list of available report categories to appear: 
 +
:: [[File:Reports category.png|250px|border]]
 +
You can also search through reports manually by using the Search bar on the top right, just above the list view. If your results exceed the maximum amount of records displayed on a page, toggle between different pages by selecting the '''next '''or '''back '''arrow icons on the top right. The count of records that you are viewing out of the total will always be displayed next to these buttons.
 +
Any [[Internet Enabled Reports|reports that have been Internet-enabled]] will have a black dot under the '''Internet Enabled '''column to indicate so. 
 +
===Report List Columns===
 +
Each report on the main list of reports will have buttons to the left of it that have several different functions.
 +
 +
:: [[File:Buttons for reports.png|600px|border]]
 +
The functions are described in the table below: 
 +
 +
{| class="wikitable"
 +
|-
 +
||'''Run Report '''{{RunReportIcon}}
 +
||Clicking this icon will cause the system to run the report and display the results to you.
 +
|-
 +
||'''Custom Export '''{{CustomExportIcon}}</span>
 +
||Clicking this icon will allow you to configure the information that you wish to export from your SmartSimple report to external applications such as '''MS Word, MS Excel, '''or '''Quickbooks. '''
 +
|-
 +
||'''Export File '''{{FileExportIcon}}</span>
 +
||Clicking this icon will allow you to export your SmartSimple report to external applications in a standard file format such as '''MS Word, MS Excel, PDF, HTML '''or more. There is less configurability available with the '''Export File '''function than the '''Custom Export '''function. 
 +
|-
 +
||'''Edit Report '''{{EditIcon}}
 +
||Click this icon in order to open the edit options for this report. If the report is a built-in report, you will be able to edit the report to see how it is constructed, but any modifications you make will not be saved to the built-in report. In order to customize a report you will have to make a copy of the built-in report and make further edits with the copy. 
 +
|}
 +
* Any reports that are ''not ''enabled for certain features - for example, for '''File Export '''or '''Custom Export '''- will have the corresponding button icon greyed out next to the report name. 
 +
* In order to ''enable ''these features, simply click the '''pencil icon '''next to the report name, go to the appropriate tab of '''Custom '''or '''File Export, '''ensure that it is both enabled and set up to how you want it, then click '''Save. '''When you return to the list view of reports, the corresponding icon should now be clickable. 
 +
 +
The columns on the [[List View Overview|list view]] of reports are described in the following table: 
 +
 +
{| class="wikitable"
 +
|-
 +
||'''Report Number (#)'''
 +
||The index of the report on the page. Remember this page length, like all others, is set by your [[Personal Settings]] - '''Number of Records Per Page'''.
 +
|-
 +
||'''Report Name'''
 +
||The name assigned to the report. An asterisk (*) is displayed in front of the '''Name '''field of any built-in reports.The narrative description of the report.
 +
|-
 +
||'''Report Description'''
 +
||The narrative description for the purpose of the report. 
 +
|-
 +
||'''Roles'''
 +
||
 +
This will display a list of [[User Roles|roles]] that will be able to display the report. You must add the required roles to this list in order to make it available to other users (to learn how to, see [[Reports#Report Tabs|Report Tabs]] - the '''View Report '''field is under the '''Security '''tab.
 +
 +
* If you are in a role with [[Reports#Permissioning Reports|manager permissions for reports]], you are able to see all reports, even though the '''Roles '''field may be blank. 
 +
 +
|-
 +
||'''Last Updated'''
 +
||This will display a date and time stamp for when the report was last updated.
 +
|-
 +
||'''Modified By'''
 +
||This will display the name of who made the last modifications to this report.
 +
|-
 +
||'''Created By'''
 +
||This will display the name of who created the report initially.
 +
|-
 +
||'''Internet Enabled '''
 +
||If the report is not Internet Enabled, the column will be blank. If the report is [[Internet Enabled Reports|Internet Enabled]], the column will have a black dot.
 +
|-
 +
||'''Report ID'''
 +
||See [[Determining the reportid]]. 
 +
|}
 +
'''Remember: '''Any user will only be able to see the reports and functions that they have permissions to. If a user is permissioned to be able to create new reports, they will see a '''+ icon '''on the top left that allows them to create a new report. 
 +
 +
==Creating a Basic Report==
 +
Learn how to create a basic report by following the steps below. Prior to these steps, you must first be in the '''Reports '''list view (read the [[Reports#Accessing Reports|Accessing Reports]] section if you need help). 
 +
 +
* '''Note''': If you have many [[Custom Fields]], access the reports tab within the application you are creating the report from. If you are creating a report from a particular {{UTA}}, access the reports tab from that UTA.
 +
 +
1. Click the '''+ '''icon on the top left of the screen to '''Create a New Report.'''
 +
 +
2. The new report window is displayed: 
 +
 +
:: [[File:New report pag.png|800px|border]]
 +
The fields on the new report page are described below: 
 +
 +
{| class="wikitable"
 +
|-
 +
||'''Report ID'''
 +
||The unique ID assigned to the report, shown as a read-only field.
 +
|-
 +
||'''Builder Type'''
 +
||
 +
When creating a report, users will have the option to choose between the '''Neo '''or '''Classic Report-Builder. '''
 +
 +
'''Neo - '''See for now, [[Report Builder (Neo)]], a SmartSimple upgrade to the Reports tool that is meant to be more user-friendly and intuitive to use. 
 +
 +
'''Classic - '''Selecting the '''Classic '''builder type will enable a new tab called [[Recordsets]] to appear in the top row of tabs. This tab is used to select which tables are required in the report, the relationships between tables, and the primary table. The primary table determines how the data will be joined when multiple tables are selected. 
 +
 +
|-
 +
||'''Name'''
 +
||The name of the report will be displayed to any users who are accessing the report.
 +
|-
 +
||'''Description'''
 +
||The narrative description of the purpose of the report.
 +
|-
 +
||'''Category'''
 +
||
 +
The category you wish you associate with this report. Entering a unique category name will consequently create a new entry in the '''Category '''field in the main Reports list view. Entering an existing category name will display this report in the existing category.
 +
 +
When you view the main [[List View Overview|list view]] of reports, you will see a drop down at the top right with all of the system's available report categories.
 +
 +
|-
 +
||'''Lock'''
 +
||'''Do not build rebuild report - '''Toggling on this option will indicate that the report should not be rebuilt. In the case of a custom [[Report Template]], it will set off an alert when the '''Report Builder '''button is clicked. 
 +
|}
 +
The second tab following the '''General '''settings provides '''Advanced '''settings for how the report will be displayed, its external accesses, its caching options and more. For more information about the '''Advanced '''configuration, please see our [[Reports#Advanced Settings of Reports|Advanced Settings of Reports]] section. 
  
 
==Editing Reports==
 
==Editing Reports==
 +
After clicking the '''Save '''button, the page will reload and save your report into the system. You will now be in the position to edit this report with more detailed configuration. 
 +
 +
:: [[File:Editing reports.png|800px|border]]
 +
The '''Builder Type '''will no longer be modifiable once you have saved your new report. Furthermore, the main report information will now include the '''Created By, Last Modified, '''and '''Last Modified By '''auto-populated fields. 
 +
 +
To the right of the page will be the [[Notes Overview|Notes]] tab that allows you to make new notes or see any existing notes that have been made on your report. 
 +
 +
You will also be able to see the following new buttons at the bottom of the page: '''Save, Save As, Delete, '''and '''Preview. '''
 +
 +
* If you have not yet selected any table(s) nor fields from tables in your '''Report Builder, '''you will not be able to see a preview of the report.
 +
 +
===Copying a Report===
 +
* The '''Save As '''button at the bottom of the '''General Settings '''page when editing any individual report will enable a pop-up to appear that prompts you to enter the new name of your report. Once you insert a name and click '''OK, '''the system will make a copy of this report under the new name, and the list of reports accessed from '''Tools > Reports '''will be updated with this new copy. 
 +
 +
<pre style="white-space: -o-pre-wrap; word-wrap: break-word;">When a report is copied, the configurations made in the Report Builder and on the Advanced, Security, File Export, Custom Export and Report Template tabs will be copied over to the new report. Sub-Report settings and Charts will not, however, be copied.</pre>
 +
===Report Tabs===
 +
If you selected '''Classic Builder Type '''for this report, the following tabs will appear after the '''General '''settings tab. 
 +
 +
{| class="wikitable" style="height: 190px;" width="1154"
 +
|-
 +
||'''[[Recordsets]]'''
 +
||This tab allows you to attach recordsets to your reports. 
 +
|-
 +
||'''[[Edit Report Page: Advanced Settings Tab|Advanced]]'''
 +
||This tab allows you to configure the [[Reports#Advanced Settings of Reports|advanced settings]] of your report. 
 +
|-
 +
||'''Security'''
 +
||This tab allows you to permission the security of your report. A field labelled '''Allow View Report '''will enable you to select which [[User Role|roles]] have permission to see this report in your system. 
 +
|-
 +
||'''Sub-Reports'''
 +
||This tab allows you to build a "nested" report within the main report.
 +
|-
 +
||'''[[File Export]]'''
 +
||This tab allows you to the default export settings of the report.
 +
|-
 +
||'''[[Custom Export]]'''
 +
||This tab allows you to create custom export settings for the report.
 +
|-
 +
||'''Scheduled Export'''
 +
||This tab allows you to schedule an export for a later date, which can be set to either '''File Export '''or '''Custom Export.'''
 +
|-
 +
||'''[[Report Template]]'''
 +
||This tab allows you to control the layout and formatting of your report.
 +
|-
 +
||'''Charts'''
 +
||This tab allows you to create one or more charts to be associated with the report.
 +
|}
 +
Each '''Classic Report Builder '''tab will soon have its own subsection within this article for further explanation, if needed.
 +
 +
* '''Note: '''In '''Report Builder Neo, '''the only difference is that you will not see a [[Building Sub-Reports|Sub-Reports]] tab. 
 +
 +
==Adding a Table to a Report==
 +
The first step for constructing a report is always to add the tables required to the report on the '''Recordsets''' tab (right after the '''General '''tab). 
 +
 +
1. As as an example, in the '''Recordsets '''tab, scroll down and set the '''Primary table''' option for the '''Contacts''' table.
 +
 +
[[Image:Tble.png|link=]]
 +
 +
Selecting the primary table automatically selects that table for the report.
 +
 +
2. Click the '''Save '''button at the bottom of the page. 
 +
 +
* For more information about '''Inner '''and '''Outer Join, '''see [[Reports Based on Multiple Tables - Overview#Inner and Outer Joins|this article]] - however, the default when you add a table to a report is '''Inner Join, '''and the '''Outer Join '''functionality is not necessary until you want to add multiple tables to a report. 
 +
 +
==Launching the Report Builder==
 +
Once you have '''Saved '''your new report, a '''four square icon '''will appear at the top left of your page above the report name.
 +
 +
:: [[File:Report builder icon.png|120px|border]]
 +
Clicking on this icon will launch a modal window that displays the '''Report Builder, '''either in '''[[Reports#Report Builder Neo - Column Settings|Neo ]]'''or '''Classic.'''
 +
 +
<pre> It is highly recommended for all users to use the Report Builder Neo for easier and more intuitive functionality. </pre>
 +
* Ensure that your '''Builder Type '''is selected as '''Neo '''in order to launch the '''Report Builder Neo. '''
 +
 +
==Classic Report Builder - Column Settings==
 +
<!-- 109118 - Deprecate ability to create Classic Reports -->
 +
In the March 2020 upgrade, the ability to create classic style report was depricated. Existing reports will continue to function, but you will not have the ability to copy or create any new reports that use the classic report builder.
 +
<br />
 +
'''Report Builder Neo '''is recommended for all users, please see the [[Reports#Report Builder Neo - Column Settings|Report Builder Neo - Column Settings]] section of the page.<br />
 +
[[File:Classic report builder.png|800px|border]]<br />
 +
Each column will consist of the basic construction of the report by selecting the required fields and setting the way that the field should be used within the report.<br />
 +
Each report column consists of the following settings: 
 +
 +
{| class="wikitable"
 +
|-
 +
||'''Column Heading'''
 +
||The text that you wish to appear above each column in the report. Initially, this will be set to the same name as the field, but you can edit the text if required. If you wish to change the text back to the '''Field '''name, then click the button to the right of the column heading. [[Image:Reps6.png]]
 +
|-
 +
||'''Recordset'''
 +
||This refers to the recordset that has been selected in the [[Recordsets]] tab. If multiple recordsets have been selected to build your report, you can select which one you want to use as a table for your report from the drop-down when you click into the field box. 
 +
|-
 +
||'''Type'''
 +
||
 +
When a specific recordset has been selected, the '''Type '''field box will have a drop-down of corresponding types. Depending on what recordset it is, you can select the [[Client Categories|category]], [[User Role|role]], [[template]], or [[type]] from which your field will be derived.
 +
 +
'''Ex: '''If the Recordset you selected was '''Contacts, '''the corresponding '''Type '''would be from the available system [[User Role|roles]].
 +
 +
|-
 +
||'''Field'''
 +
||The field you wish to display in this column. All of the fields from all selected tables will be displayed in the field list; fields are sorted alphabetically by table name and by field name.
 +
|-
 +
||'''Date/Time Translation'''
 +
||Used to "extract" the day, month, week or year from a date field for comparative purposes.
 +
|-
 +
||'''Group By'''
 +
||Used in [[Creating Summary Reports|summary reports]] to group by, sum, count, or calculate the average of the field. Also provides [[Using Total Group Options in Report Builder|Total Group options]] for configuration of report groupings and easier retrieval of total rows. 
 +
|-
 +
||'''Sort By'''
 +
||Determines the [[Sort By|sort order]] of the report results, either '''Ascending '''or '''Descending.'''
 +
|-
 +
||'''Align'''
 +
||Determines the alignment of the column header and column values. Options are '''Left '''(default), '''Center '''and '''Right. '''
 +
|-
 +
||'''[[Calculated Field]]'''
 +
||This option allows you to build out further complexity - for example, mathematical operators and concatenation. This field also supports MySQL syntax.
 +
|-
 +
||'''Show'''
 +
||
 +
Checking this option will show the specific field data. An example of when you might not want to enable '''Show '''is if you wish to produce a list of salaries over $100,000 a year and use the salary as criteria, but not show the result. 
 +
 +
'''Left/Right Move Arrow Icons''' - These icons are displayed on the left and right of each '''Show '''check box and can be used to change the column order. 
 +
 +
|-
 +
||'''Show Total'''
 +
||Checking this option will display the total of all the data accumulated from this column. 
 +
|-
 +
||'''[[Criteria]] '''
 +
||Here you can set the criteria for which records to be displayed in a report. There are 5 columns available for setting criteria, each separated by the '''OR '''operator. 
 +
|}
 +
* '''Note: '''If you want to add more columns, click on the '''More Columns '''button at the bottom of the page to add 25 more columns. 
 +
* There is a fixed limit of '''50 columns '''for the '''Classic Report Builder. '''If more columns are required, you will need to use two reports linked together - see [[Merge Sub-Reports]]. 
 +
* '''Tip: '''The [[Browser|browser]] '''Mozilla Firefox '''will load the Report Builder more efficiently than '''Internet Explorer. '''
 +
 +
At the bottom of the page are a row of buttons: 
 +
 +
:: [[File:Report builder classic buttons.png|250px|border]]
 +
{| class="wikitable"
 +
|-
 +
||'''Build Query'''
 +
||
 +
This button is used to create your report based on the fields and criteria that you have selected in the Report Builder. When this button is clicked, the Report Builder will close and you will be brought back to the '''Edit Report '''page. 
 +
 +
* The [[Report Template]] will also be rebuilt when this button is clicked; if custom work has been done on the Report Template prior to building this query, make sure to save your changes elsewhere first. 
 +
 +
|-
 +
||'''Clear All'''
 +
||This button will clear all of the fields including calculated fields and specified criteria in the Report Builder. You will be able to start again from scratch.
 +
|-
 +
||'''More Columns'''
 +
||This button will add another 25 columns to the Report Builder to a maximum of 50 for any new '''Classic '''report. 
 +
|}
 +
===Classic Report Builder Help Panel===
 +
At the very bottom of the '''Classic Report Builder '''interface is the '''Help Panel '''with an upwards arrow that will open up help options when clicked. 
 +
 +
:: [[File:Report builder classic help.png|900px|border]] 
 +
The '''Help '''menu consists of 6 tabs, each with helpful information corresponding to that tab's topic. 
 +
 +
{| class="wikitable"
 +
|-
 +
||'''Overview'''
 +
||This tab provides descriptions for all the components of the top panel of the Report Builder, including column fields and their descriptions.
 +
|-
 +
||'''Comparison Operators'''
 +
||
 +
This tab will describe how to set the basic criteria on logical tests.
 +
 +
* See Also: [[Setting Text Criteria – Logical Operators in a Report]]
 +
 +
|-
 +
||'''Wildcard and Like Operators'''
 +
||
 +
This tab describes how to set '''partial criteria. '''
 +
 +
* See Also: [[Using Wildcard Criteria in Reports]], [[Prompting for a Criteria Using a Wildcard]]
 +
 +
|-
 +
||'''Special [[SmartSimple]] Criteria Values'''
 +
||This tab describes how to set criteria from user prompts and user attributes (such as user and company identifiers).
 +
|-
 +
||'''Date Comparison'''
 +
||
 +
This tab describes how to set date- and time-based data. 
 +
 +
* See Also: [[Using Date Criteria]], [[Accessing the Current Date in a Report]]
 +
 +
|-
 +
||'''[[Calculated Field]] '''
 +
||This tab describes valid mathematical expressions and functions, string functions, and logical statements for user in the [[Calculated Field]] section of the Report Builder.
 +
|}
 +
==Report Builder Neo - Column Settings==
 +
If your new report has been saved under '''Builder Type - Neo, '''then the Report Builder that is launched will be the '''Neo '''rather than the '''Classic '''version. 
 +
 +
The '''Report Builder Neo '''was introduced as an upgrade to our '''Classic '''Report Builder; it provides the following benefits: 
 +
 +
* A more intuitive interface with higher degree of configurability 
 +
* Saving time and effort in creating targeted, impactful reports
 +
* Uses your organization's language (the terminology related to your {{UTA}})
 +
 +
The primary upgrade in '''Neo '''is that it streamlines the process of '''finding, selecting, '''and '''setting up your information. '''
 +
 +
<pre>The Report Builder Neo can only be used to create new reports; any existing reports with the Classic Report Builder will not be able to switch to Neo. </pre>
 +
1. Click on the '''four-square '''icon on the top left of your page to launch the '''Report Builder Neo. '''
 +
 +
:: [[File:Report builder icon.png|100px|border]] 
 +
2. The '''Report Builder Neo '''window will open up as a modal window: 
 +
 +
:: [[File:Report builder new.png|800px|border]]
 +
It will have the following functions and fields: 
 +
 +
{| class="wikitable"
 +
|-
 +
||'''Number of Columns'''
 +
||You can add or subtract the number of columns you need for your report '''up to a maximum of 100. '''
 +
|-
 +
||'''Dataset'''
 +
||
 +
Click the '''+ icon '''on the top left of the Report Builder in order to add the primary dataset you will be using for your report. 
 +
 +
* The '''primary '''dataset will always be easily identifiable as once it appears on the left-hand dataset list in the Report Builder, it will appear with a '''star '''next to its name. 
 +
* You can create as many primary and secondary datasets as required to personalize each report you make. 
 +
 +
|-
 +
||'''Field'''
 +
||The field you wish to display in this column. 
 +
|-
 +
||'''Column Heading'''
 +
||The text that you wish to appear above each column in the report. 
 +
|-
 +
||'''Date/Time'''
 +
||
 +
Used to "extract" the day, month, week or year from a date field for comparative purposes. The options are shown in the screenshot below: 
 +
 +
:: [[File:Report neo date time.png|200px|border]]
 +
|-
 +
||'''Group Options'''
 +
||
 +
Used in [[Creating Summary Reports|summary reports]] to group by sum, count, or calculate the average of the field. Also provides [[Using Total Group Options in Report Builder|Total Group Options]] for configuration of report groupings and easier retrieval of total rows. The options are shown in the screenshot below: 
 +
 +
:: [[File:Report neo group by.png|220px|border]]
 +
|-
 +
||'''Sort By'''
 +
||Determines the [[Sort By|sort order]] of the reports, either '''Ascending '''or '''Descending.'''
 +
|-
 +
||'''Alignment'''
 +
||Determines the alignment of the column headings and the column values - '''Left '''(default), '''Center, '''and '''Right. '''
 +
|-
 +
||'''Formula'''
 +
||This field allows you to build out further complexity such as mathematical operators, concatenation, and supports MySQL syntax. 
 +
|-
 +
||'''Display'''
 +
||
 +
Checking this option will allow you to display the data from this column in your report; if you do not want it to be displayed, but rather only set as a criteria, you can uncheck this option and the column data will be hidden from the resulting report. 
 +
 +
'''Example:''' You may wish to produce a list of salaries over $100,00 a year and use the salary as a criteria, but do not want to show these results. 
 +
 +
|-
 +
||'''Show Total'''
 +
||Checking this option will display the total of all the data accumulated from that column. 
 +
|-
 +
||'''[[Criteria]]'''
 +
||Here, you can set the criteria for which records to be displayed in a report. There are 5 columns available for setting criteria, each separated by the '''OR '''operator.
 +
|}
 +
* The '''Report Builder Neo '''has the [[Display order#Drag and Drop Re-Order Fields|Drag and Drop]] functionality to re-order the display view of columns in a report and to input fields into the appropriate columns. 
 +
* The Report Builder will automatically note which base dataset your columns relate to. 
 +
 +
You can also use the '''Search '''functionality in the dataset field picker in order to narrow down your fieldset to those which include matches to a word search. Simply click on the '''Fields '''tab, and insert your search into the search box: 
 +
 +
:: [[File:Report builder neo search field box.png|200px|border]] 
 +
===Report Builder Neo Help Panel===
 +
See the [[Reports#Classic Report Builder Help Panel|Classic Report Builder Help Panel]]. 
 +
 +
==Deleting a Report==
 +
<pre>The user must be permissioned to be able to delete a report in order to do so. </pre>
 +
 +
===Batch Delete Reports===
 +
1. Click on the 9-square menu icon on the top right of your page.
 +
 +
:: {{Icon-Menu}} 
 +
2. Under the heading '''Tools, '''select '''Reports.'''
 +
3. Check the box on the left of all report names for the reports that you wish to delete. 
 +
4. A '''trashcan '''icon will appear in the top left row of buttons once at least one report has been selected. Click on this button to '''batch delete '''all of your selected reports.
 +
 +
:: [[File:Batch delete repeorts.png|250px|border]]
 +
5. A confirmation message will pop-up on your screen. Click '''Yes '''to confirm the deletion. 
 +
The reports will be deleted and your list view of reports will refresh to display these changes. 
 +
 +
===Individually Deleting a Report===
 +
1. Click on the 9-square menu icon on the top right of your page.
 +
 +
:: {{Icon-Menu}} 
 +
2. Under the heading '''Tools, '''select '''Reports.'''
 +
3. Click on the '''pencil icon '''next to the name of the report that you want to delete to go into '''Edit '''mode for that report. 
 +
4. The report details will be displayed, and at the bottom will be a '''Delete '''button among a row of other buttons. Click on the '''Delete '''button.
 +
 +
:: [[File:Delete report.png|800px|border]]
 +
5. A confirmation message will pop-up on your screen. Click '''Yes '''to confirm the deletion.
 +
The report will be deleted. Your page will refresh and you will be brought back to the list of reports, now updated to reflect your deletion.
 +
 +
===View Deleted Reports===
 +
In order to see which reports have been deleted from your system, either by you or another user, you can always click onto the last tab in the '''Reports '''section of the system, titled '''Deleted Reports.'''
 +
 +
:::* '''Note: '''Only a user with [[Global System Administrator|System Administrator]] privileges can view the '''Deleted Reports '''tab. 
 +
:::*: [[File:Deleted reports section.png|900px|border]]
 +
 +
'''<strong> '''</strong>
 +
 +
The list of deleted records will have columns that include '''who '''deleted the report ('''Deleted By '''column) and '''when''' ('''Deleted Date '''column, presented as a date and time stamp).
 +
 +
===Restore or Purge Deleted Reports===
 +
<pre> You must have SysAdmin privileges in order to view or modify already deleted reports. </pre>
 +
 1. Click on the 9-square menu icon on the top right of your page.
 +
 +
::::: {{Icon-Menu}} 
 +
2. Under the heading '''Tools, '''select the hyperlink '''Reports.'''
 +
 +
3. The list view of reports will be displayed. Click into the last tab, labelled '''Deleted Reports. '''
 +
 +
4. The list of deleted reports in your system will be displayed.
 +
 +
5. Select the checkbox on the left of the report name of the report that you would like to either '''Restore '''or '''Purge.'''
 +
 +
Once you click this checkbox, two buttons will appear on the top left of your page underneath the tab labels. The '''reload '''button will allow you to restore the deleted report so that it will once more be accessible, viewable, and modifiable in your system. The '''trashcan icon '''will allow you to permanently delete this report, so that it is not accessible even from the '''Deleted Reports '''tab. 
 +
 +
::::: [[File:Restore or purge deleted reports.png|150px|border]] 
 +
:::
 +
:::* '''Note: '''If you choose to permanently delete a report, a pop-up confirmation message will appear. Click '''Yes '''to go through with this purge. 
 +
 +
<pre> Any reports that have been deleted for over one year will be automatically purged. </pre>
 +
=Configuration - Advanced=
 +
==Advanced Settings of Reports==
 +
The second tab on the '''New Report '''page is labelled '''Advanced. '''This tab will display all the advanced settings that can be configured for the report.
 +
 +
::::: [[File:Advanced report functions.png|400px|border]]
 +
The options for the '''Advanced''' tab are described in the table below:
 +
 +
{| class="wikitable"
 +
|-
 +
||'''Criteria Text'''
 +
||Text used to prompt the [[user]] when they are [[Prompting for Criteria in Reports|required to enter]] some [[Criteria|criteria]] values. This setting will also specify the introduction text, as well as labels for [[Custom Fields]]. Labels are defined by a hyphen (-) preceding the label ('''ex: '''"-First Name") and are applied to the criteria fields in the other that they are entered.
 +
|-
 +
||'''View Mode'''
 +
||
 +
'''[[List View Overview|List View]] - '''Selecting this option will allow all the records to appear in a list.
 +
 +
'''Single Record View - '''Selecting this option means that each report will be displayed one at a time. 
 +
 +
|-
 +
||'''External Access'''
 +
||'''[[Internet Enabled Reports|Internet Enabled]] - '''Toggling on this function will generate a [[URL]] that allows the report to be accessed over the internet without the need for a user to log into the system. This setting is useful for creating '''public '''reports. 
 +
|-
 +
||'''[[Odata Overview|OData Connector]]'''
 +
||
 +
Toggling on this option will use Open Data Protocol to simplify data-sharing across disparate applications in enterprise, Cloud, and mobile devices.
 +
 +
:::* This feature will only appear for '''Neo Reports'''; it is not available for new '''Classic Reports. '''
 +
 +
|-
 +
||'''Disable Global Template'''
 +
||Toggling on this option will disable the [[Global Report Template]] (a personalization of HTML header/footer for all system-based reports) for this specific report. 
 +
|-
 +
||'''Interactive View Display'''
 +
||
 +
'''None '''
 +
 +
'''Display Link to Interactive View - '''
 +
 +
'''Open Report Directly in Interactive View -'''
 +
 +
|-
 +
||'''Total Label'''
 +
||Provide a name for the '''Total '''column. See [[Using Total Group Options in Report Builder]] for more./ 
 +
|-
 +
||'''Enable Translation'''
 +
||
 +
Allows report headers and associated charts to use translation options in the system's [[Language Library]].
 +
 +
* See [[Using Language Translation in Reports]] for more. 
 +
 +
|-
 +
||'''Enable Straight Join'''
 +
||See [[Straight Join]].
 +
|-
 +
||'''Enable Pivot View'''
 +
||
 +
 Enables pivot functionality; this means that when grouping by two different columns, you now have the option to perform an aggregate function and pivot the grouped columns across the x-/y-axis.
 +
 +
[[File:2019-05-ticket-85865-1.png|thumb|none|500px|This example report is using "Pivot View" with the data grouped by "type" and "status". The user can choose which group options they wish to view via the drop down.]]
 +
 +
|-
 +
||'''Merge Sub-Reports'''
 +
||Merging of two [[Classic Report Builder - Column Settings|Classic builder type reports ]]
 +
|-
 +
||'''No Name URL Parameter'''
 +
||See [[Enable URL Parameter Encryption]]. 
 +
|-
 +
||'''Hide Custom Export'''
 +
||This will hide the ability for a user to [[Custom Export]] this report by hiding the custom export icon {{ CustomExportIcon }}.
 +
|-
 +
||'''Hide File Export'''
 +
||This will hide the ability for a user to [[File Export]] this report by hiding the export icon {{ FileExportIcon }}
 +
|}
 +
===Report and Field Caching===
 +
Scrolling further down the '''Advanced Settings '''tab will allow you to modify the functionality of caching for reports and field. 
 +
 +
::::: [[File:Report more advanced.png|500px|border]]
 +
{| class="wikitable"
 +
|-
 +
||
 +
'''Report Caching'''
 +
 +
||
 +
'''Caching Options: Enable - '''Enabling caching is a useful function that can help improve loading time and reduce the processing load on the server. This function is best for complex reports and reports that are accessed regularly. 
 +
 +
:::* '''Do not '''enable general caching for reports that [[Prompting for Criteria in Reports|prompt the user for criteria]]. 
 +
 +
'''Caching Options: Cache By User - '''Caching by user'' must be used for'' when a report includes [[:Category:Criteria|criteria]] to filter based on the user that opens the report (i.e. =@userid@). 
 +
 +
'''Cache Life Span - '''Enter an appropriate cache life span in minutes. Therefore, rather than re-creating the report each time it is run, caching stores the results for the duration specified in this field. The first time a report is viewed, it will be created by querying the database and live data will be displayed. Anyone viewing the report after this and ''within ''the life span will see a snapshot of the report from the cache. 
 +
 +
:::* Changes to underlying data will not be reflected in the report during the '''Cache Life Span. '''
 +
:::* The report will be updated only once the life span has expired. 
 +
:::* In general, [[Building Sub-Reports|Sub-Reports]] should not be cached, though the parent report can be cached if appropriate. 
 +
 +
|-
 +
||
 +
'''[[Field Caching]]'''
 +
 +
 +
||
 +
'''Frequency - '''The frequency of field caching can be set as follows:
 +
 +
:::* '''''Disabled: '''''Default setting; indicates a report will not run on an automated schedule
 +
:::* '''''Daily: '''''The report will run every morning. 
 +
:::* '''''Weekly: '''''The report will run every Sunday.
 +
:::* '''''Monthly: '''''The report will run on the first of every month. 
 +
'''Identifier Column - '''Select a report column from those available in the [[Report Builder]] that will uniquely identify the record to update. This is the ''match field ''as it indicates the field that allows the system to know which record to update. 
 +
 +
'''Column Field Value Column - '''Select a report column from those available in the [[Report Builder]] for the data that you wish to store; this acts as a ''source field ''as the setting represents the values that the system will use to update the '''Custom Field '''specified in the next field. 
 +
 +
'''Custom Field - '''Select a dataset-specific [[Custom Field]] to where the dataset will be stored. This is the ''destination field ''as the custom field will be updated each time that the report is run. 
 +
 +
'''Last Run - '''This will display a date and time stamp of the last time that the field caching/scheduled report was run. 
 +
 +
|}
 +
==Internet-Enabling Reports==
 +
The ability to '''Internet Enable '''a report is useful because it will make that report public, presenting real-time reports on the Internet - it generates a [[URL]] relating to that report that can then be linked to your website, or sent as a link in an [[Email|email]] or [[Overview of the Email Broadcast Application|email broadcast]] or other communication method. 
 +
 +
:::* The person who clicks the link does ''not ''need to have login access to your [[SmartSimple]] system. 
 +
:::* Once the link is clicked, the report is processed and the results are displayed accordingly. 
 +
 +
<pre>The following steps are for reports made with Report Builder Neo, although the Classic Report Builder undergoes a very similar process for Internet Enabling reports. </pre>
 +
1. Once you have the '''Reports '''section of your system open (see [[Reports#Accessing Reports|Accessing Reports]] if needed), click on the '''pencil icon '''next to a specific report name to '''Edit '''it. 
 +
 +
2. Click on the second tab labelled [[Reports#Advanced Settings of Reports|'''Advanced''']]. 
 +
 +
3. Look for the field '''External Access. '''Toggle ''on ''the '''Internet Enabled '''feature. 
 +
 +
::::: [[File:Report internet enable.png|800px|border]]
 +
{| class="wikitable"
 +
|-
 +
||'''Access Key'''
 +
||
 +
If you want to password-protect your Internet-enabled report, write in a customized access key that the user must have in order to see the results of the report, even if they have access to the URL. 
 +
 +
:::* The access key will append itself to the URL of the report. 
 +
:::* By allowing people to view an Internet-enabled report specifically with an access key, you can remove access easily by simply changing the access key. 
 +
 +
|-
 +
||'''URL'''
 +
||This is the conventional URL that will display the results of the report on the [[Browser|browser]] that the user is using, as a web page.
 +
|-
 +
||'''Export URL'''
 +
||This URL, when inputted into a [[Browser|browser]], will enable the report to be downloaded onto the receiver's computer, and can be opened in a number of different file formats that they can select from.
 +
|-
 +
||'''Export URL-XML'''
 +
||This URL, when inputted into a [[Browser|browser]], will enable the report to be downloaded onto the receiver's computer in an [http://en.wikipedia.org/wiki/XML XML]''' '''format. 
 +
|}
 +
:::* The [[URL]]s for reports that are Internet-enabled are automatically generated. Simply copy and paste them to use it.
 +
 +
4. Click '''Save. '''
 +
 +
:::* '''Note: '''You can always tell whether a report is properly Internet-enabled if it has a '''black dot '''in the '''Internet-Enabled '''column on the main reports list view, found from '''Tools > Reports. '''See screenshot below. 
 +
:::*: [[File:Internet enabled report.png|900px|border]]
 +
 +
===Language Translation for Internet-Enabled Reports===
 +
Users can now add a parameter to the Internet Enabled URL of all reports so the results and column headers can be made language-specific, if corresponding translations have been configured for the fields.
 +
 +
The parameter used is ''lang'' followed by the ID of the [[Language|language]] that the report should be translated into.
 +
 +
In the example below the parameter '''lang=2''' is used to translate the report into French.
 +
 +
<pre>https://test.smartsimpleqa.com/ex/ex_viewreport.jsp?key=&token=%40GgUNSxoaeFxbSxRdSRxRRVdQZFB3H3Q~&lang=2</pre>
 +
==Reports vs List Views==
 +
In terms of displaying data, [[SmartSimple]] offers numerous methods to do so. In particular, '''[[List View Overview|list views]] '''and '''Reports '''are two of the most popular and highly configurable functions. A critical question is therefore ''when ''is it appropriate to use a list view, and when is it appropriate to build a report? For more in-depth information, see our [[List View and Reports - Why?]] page. 
 +
 +
{| class="wikitable"
 +
|-
 +
||'''Benefits '''
 +
||'''Things to Note'''
 +
|-
 +
||
 +
A report is needed if your situation includes ''at least one ''of the following criteria: 
 +
 +
* You need more than 30 columns
 +
* You need formulae or calculations 
 +
* You need more complex aggregations than a simple total (e.g. count distinct, maximum, sum, group by, total by) 
 +
* You need an [[Odata Overview|OData]] connection or you need to link multiple datasets that are not automatically connected (e.g. multiple {{UTAs}}s) 
 +
 +
||
 +
Reports are more sensitive because of the following: 
 +
 +
* Only 5 criteria are permitted compared to the limitless criteria on a [[List View Overview|list view]] 
 +
* Requires more knowledge of SQL and reporting - although if a user is familiar with '''MS Access '''or '''Tableau, '''this is definitely sufficient, as the programs use the same principles 
 +
* Less dynamic visibility 
 +
 +
|}
 +
==See Also==
 +
* [[List View and Reports - Why?]] 
 +
 +
[[Category:Reports]]

Latest revision as of 08:40, 18 October 2023

Overview

The SmartSimple reporting system, an integral part of our business operating system, allows you to manipulate both the contents and the presentation of data contained in one or more of the SmartSimple tables. Unlike a table, reports present only the data that meets the criteria you define - they can also aggregate and display summarized data information. 

You can utilize the functionality of reports in your SmartSimple instance without additionally buying or installing software. Reports are completely created from within the standard interface and are viewable from any browser, as well as via Microsoft Word or Excel with the appropriate SmartSimple plug-in. 

Benefits of Reports: 

  • Display data from one or more tables in one place
  • Group-aggregate data from one or more tables
  • Display the grouped data in a variety of different types of graphs
  • Export the data into any file format that you wish OR 
  • Group reports together as tabs, allowing similar topic-based reports to be found together
  • Copy and modify built-in reports as the basis of your customized reports 

User Role-based security defines who can view the reports that you create from your system. 

 You must be a user with SysAdmin privileges in order to create reports from your SmartSimple system.

Report Components

You will encounter three major components in the reporting system: 

  • Reports that are created from underlying SmartSimple database tables - you can connect the required tables to generate a report
  • Graphs that you create yourself. These are based on existing reports and are used to display the report in a graphic format. 
  • Dashboards that will display multiple charts on a single page

The following diagram shows the relationship between these three types of entities:

Reporting-componets-800w (2).jpg

Charts

Charts are used to display the information from a report in an easy-to-read “graphic” format.

  • You can create pie charts, bar charts, line charts, and table-type charts.
  • You can create more than one chart type of each report and when displaying a report, choose from any of the charts associated with the report.
  • Generally, you will choose a “summary” type report as the basis for the chart, as that type of information is more meaningful when displayed in this manner.
 A user can only access the chart if they have permission to see the report that the chart is based on.

A user can only access the chart if they have permission to see the report the chart is based on.

Dashboards

A dashboard is a single page view of a set of charts.

  • When the user displays the dashboard, each report referenced by each chart is processed, and the current data is displayed in charts that are displayed on the dashboard.
  • You can create multiple dashboards to show different sets of key indicators.
  • A user can indicate that a specific dashboard should be their home page within SmartSimple, giving them an immediate view of key information from the system when they first log in.
 Each dashboard needs to be permissioned for the roles that you wish to be able to see the dashboard.

Tables

In order to make the creation of reports a little simpler, some of the SmartSimple tables are presented in a denormalized format when using the Report Builder.

For example, the contact record does not contain the company name; these two tables are linked at the database level through the CompanyID field. So contact table does not contain the company name field but rather a key field that links to the company table.

But when you use the contact table within the Report Builder it will display a field Company Name. This means that you can use this field without needing to link the two tables together.

Configuration - Essentials

Permissioning Reports

Access to the report-creation features is controlled by the role-based manager permission. So you must be in a role associated with this feature in order to create reports. 

 Users who will only need to view reports do not need these manager permissions.

You must be a member of a role with manager permission for Reports if you wish to do any of the following actions: 

  • Create new reports.
  • Edit or copy existing reports.
  • Delete existing reports, either individual or as batch deletes.

To permission which roles will have the ability to create, edit, and delete reports in your system, follow these steps: 

1. Click on the 9-square menu icon on the top right of your page.

052919 MenuIcon.png 

2. Under the heading Configuration, select Roles and Security. 

Roles and security from menu.png

3. Under the subheading System Security Settings, select the hyperlink System Feature Permissions. 

4. Click into the tab labelled Action Permission. 

Action permission.png

5. Scroll down until you see the field labelled Reports - Manager. Click into the text box in order to select from a drop-down of user roles that will have manager permissions for reports. 

Reports manager permissions.png
  • You can also specifically permission which roles will have the ability to Export Data from reports. 

6. Click Save at the bottom of the screen when you are done. 

Accessing Reports

A user who is permissioned for report use can access the reports available to them. Follow these steps to access reports in your SmartSimple system: 

1. Click the 9-square menu icon on the top right of your page.

052919 MenuIcon.png 

2. Under the heading Tools, select Reports. 

Report.png

The list view of available system reports will be displayed. 

Report list.png 

The report window consists of the following sections along the top row of tabs:

  • Reports: This displays a list of your system's current reports that have been made available to you. 
  • Global Report Template: This tab is used to define a customized HTML template for all reports.
  • Dashboards: This tab is used to create a dashboard to display a set of charts.
  • Web Access Statistics: This tab displays statistics on how often your web-enabled reports have been accessed.
  • Deleted Reports: This tab displays a list of any deleted reports in your system. 

You can filter the results of your report list by choosing a Category of reports - this can be found at the top right of your page. Any reports that have not been associated with a category will appear in the Uncategorized category option. Clicking into the Category field will allow a drop-down list of available report categories to appear: 

Reports category.png

You can also search through reports manually by using the Search bar on the top right, just above the list view. If your results exceed the maximum amount of records displayed on a page, toggle between different pages by selecting the next or back arrow icons on the top right. The count of records that you are viewing out of the total will always be displayed next to these buttons. Any reports that have been Internet-enabled will have a black dot under the Internet Enabled column to indicate so. 

Report List Columns

Each report on the main list of reports will have buttons to the left of it that have several different functions.

Buttons for reports.png

The functions are described in the table below: 

Run Report NewRunReportIcon2.png Clicking this icon will cause the system to run the report and display the results to you.
Custom Export ReportCustomExportIcon.png</span> Clicking this icon will allow you to configure the information that you wish to export from your SmartSimple report to external applications such as MS Word, MS Excel, or Quickbooks. 
Export File ReportFileExportIcon.png</span> Clicking this icon will allow you to export your SmartSimple report to external applications in a standard file format such as MS Word, MS Excel, PDF, HTML or more. There is less configurability available with the Export File function than the Custom Export function. 
Edit Report ReportEditIcon2.png Click this icon in order to open the edit options for this report. If the report is a built-in report, you will be able to edit the report to see how it is constructed, but any modifications you make will not be saved to the built-in report. In order to customize a report you will have to make a copy of the built-in report and make further edits with the copy. 
  • Any reports that are not enabled for certain features - for example, for File Export or Custom Export - will have the corresponding button icon greyed out next to the report name. 
  • In order to enable these features, simply click the pencil icon next to the report name, go to the appropriate tab of Custom or File Export, ensure that it is both enabled and set up to how you want it, then click Save. When you return to the list view of reports, the corresponding icon should now be clickable. 

The columns on the list view of reports are described in the following table: 

Report Number (#) The index of the report on the page. Remember this page length, like all others, is set by your Personal Settings - Number of Records Per Page.
Report Name The name assigned to the report. An asterisk (*) is displayed in front of the Name field of any built-in reports.The narrative description of the report.
Report Description The narrative description for the purpose of the report. 
Roles

This will display a list of roles that will be able to display the report. You must add the required roles to this list in order to make it available to other users (to learn how to, see Report Tabs - the View Report field is under the Security tab.

Last Updated This will display a date and time stamp for when the report was last updated.
Modified By This will display the name of who made the last modifications to this report.
Created By This will display the name of who created the report initially.
Internet Enabled  If the report is not Internet Enabled, the column will be blank. If the report is Internet Enabled, the column will have a black dot.
Report ID See Determining the reportid. 

Remember: Any user will only be able to see the reports and functions that they have permissions to. If a user is permissioned to be able to create new reports, they will see a + icon on the top left that allows them to create a new report. 

Creating a Basic Report

Learn how to create a basic report by following the steps below. Prior to these steps, you must first be in the Reports list view (read the Accessing Reports section if you need help). 

  • Note: If you have many Custom Fields, access the reports tab within the application you are creating the report from. If you are creating a report from a particular , access the reports tab from that UTA.

1. Click the + icon on the top left of the screen to Create a New Report.

2. The new report window is displayed: 

New report pag.png

The fields on the new report page are described below: 

Report ID The unique ID assigned to the report, shown as a read-only field.
Builder Type

When creating a report, users will have the option to choose between the Neo or Classic Report-Builder. 

Neo - See for now, Report Builder (Neo), a SmartSimple upgrade to the Reports tool that is meant to be more user-friendly and intuitive to use. 

Classic - Selecting the Classic builder type will enable a new tab called Recordsets to appear in the top row of tabs. This tab is used to select which tables are required in the report, the relationships between tables, and the primary table. The primary table determines how the data will be joined when multiple tables are selected. 

Name The name of the report will be displayed to any users who are accessing the report.
Description The narrative description of the purpose of the report.
Category

The category you wish you associate with this report. Entering a unique category name will consequently create a new entry in the Category field in the main Reports list view. Entering an existing category name will display this report in the existing category.

When you view the main list view of reports, you will see a drop down at the top right with all of the system's available report categories.

Lock Do not build rebuild report - Toggling on this option will indicate that the report should not be rebuilt. In the case of a custom Report Template, it will set off an alert when the Report Builder button is clicked. 

The second tab following the General settings provides Advanced settings for how the report will be displayed, its external accesses, its caching options and more. For more information about the Advanced configuration, please see our Advanced Settings of Reports section. 

Editing Reports

After clicking the Save button, the page will reload and save your report into the system. You will now be in the position to edit this report with more detailed configuration. 

Editing reports.png

The Builder Type will no longer be modifiable once you have saved your new report. Furthermore, the main report information will now include the Created By, Last Modified, and Last Modified By auto-populated fields. 

To the right of the page will be the Notes tab that allows you to make new notes or see any existing notes that have been made on your report. 

You will also be able to see the following new buttons at the bottom of the page: Save, Save As, Delete, and Preview. 

  • If you have not yet selected any table(s) nor fields from tables in your Report Builder, you will not be able to see a preview of the report.

Copying a Report

  • The Save As button at the bottom of the General Settings page when editing any individual report will enable a pop-up to appear that prompts you to enter the new name of your report. Once you insert a name and click OK, the system will make a copy of this report under the new name, and the list of reports accessed from Tools > Reports will be updated with this new copy. 
When a report is copied, the configurations made in the Report Builder and on the Advanced, Security, File Export, Custom Export and Report Template tabs will be copied over to the new report. Sub-Report settings and Charts will not, however, be copied.

Report Tabs

If you selected Classic Builder Type for this report, the following tabs will appear after the General settings tab. 

Recordsets This tab allows you to attach recordsets to your reports. 
Advanced This tab allows you to configure the advanced settings of your report. 
Security This tab allows you to permission the security of your report. A field labelled Allow View Report will enable you to select which roles have permission to see this report in your system. 
Sub-Reports This tab allows you to build a "nested" report within the main report.
File Export This tab allows you to the default export settings of the report.
Custom Export This tab allows you to create custom export settings for the report.
Scheduled Export This tab allows you to schedule an export for a later date, which can be set to either File Export or Custom Export.
Report Template This tab allows you to control the layout and formatting of your report.
Charts This tab allows you to create one or more charts to be associated with the report.

Each Classic Report Builder tab will soon have its own subsection within this article for further explanation, if needed.

  • Note: In Report Builder Neo, the only difference is that you will not see a Sub-Reports tab. 

Adding a Table to a Report

The first step for constructing a report is always to add the tables required to the report on the Recordsets tab (right after the General tab). 

1. As as an example, in the Recordsets tab, scroll down and set the Primary table option for the Contacts table.

Tble.png

Selecting the primary table automatically selects that table for the report.

2. Click the Save button at the bottom of the page. 

  • For more information about Inner and Outer Join, see this article - however, the default when you add a table to a report is Inner Join, and the Outer Join functionality is not necessary until you want to add multiple tables to a report. 

Launching the Report Builder

Once you have Saved your new report, a four square icon will appear at the top left of your page above the report name.

Report builder icon.png

Clicking on this icon will launch a modal window that displays the Report Builder, either in Neo or Classic. 

 It is highly recommended for all users to use the Report Builder Neo for easier and more intuitive functionality.
  • Ensure that your Builder Type is selected as Neo in order to launch the Report Builder Neo. 

Classic Report Builder - Column Settings

In the March 2020 upgrade, the ability to create classic style report was depricated. Existing reports will continue to function, but you will not have the ability to copy or create any new reports that use the classic report builder.
Report Builder Neo is recommended for all users, please see the Report Builder Neo - Column Settings section of the page.
Classic report builder.png
Each column will consist of the basic construction of the report by selecting the required fields and setting the way that the field should be used within the report.
Each report column consists of the following settings: 

Column Heading The text that you wish to appear above each column in the report. Initially, this will be set to the same name as the field, but you can edit the text if required. If you wish to change the text back to the Field name, then click the button to the right of the column heading. Reps6.png
Recordset This refers to the recordset that has been selected in the Recordsets tab. If multiple recordsets have been selected to build your report, you can select which one you want to use as a table for your report from the drop-down when you click into the field box. 
Type

When a specific recordset has been selected, the Type field box will have a drop-down of corresponding types. Depending on what recordset it is, you can select the category, role, template, or type from which your field will be derived.

Ex: If the Recordset you selected was Contacts, the corresponding Type would be from the available system roles.

Field The field you wish to display in this column. All of the fields from all selected tables will be displayed in the field list; fields are sorted alphabetically by table name and by field name.
Date/Time Translation Used to "extract" the day, month, week or year from a date field for comparative purposes.
Group By Used in summary reports to group by, sum, count, or calculate the average of the field. Also provides Total Group options for configuration of report groupings and easier retrieval of total rows. 
Sort By Determines the sort order of the report results, either Ascending or Descending.
Align Determines the alignment of the column header and column values. Options are Left (default), Center and Right. 
Calculated Field This option allows you to build out further complexity - for example, mathematical operators and concatenation. This field also supports MySQL syntax.
Show

Checking this option will show the specific field data. An example of when you might not want to enable Show is if you wish to produce a list of salaries over $100,000 a year and use the salary as criteria, but not show the result. 

Left/Right Move Arrow Icons - These icons are displayed on the left and right of each Show check box and can be used to change the column order. 

Show Total Checking this option will display the total of all the data accumulated from this column. 
Criteria  Here you can set the criteria for which records to be displayed in a report. There are 5 columns available for setting criteria, each separated by the OR operator. 
  • Note: If you want to add more columns, click on the More Columns button at the bottom of the page to add 25 more columns. 
  • There is a fixed limit of 50 columns for the Classic Report Builder. If more columns are required, you will need to use two reports linked together - see Merge Sub-Reports. 
  • Tip: The browser Mozilla Firefox will load the Report Builder more efficiently than Internet Explorer. 

At the bottom of the page are a row of buttons: 

Report builder classic buttons.png
Build Query

This button is used to create your report based on the fields and criteria that you have selected in the Report Builder. When this button is clicked, the Report Builder will close and you will be brought back to the Edit Report page. 

  • The Report Template will also be rebuilt when this button is clicked; if custom work has been done on the Report Template prior to building this query, make sure to save your changes elsewhere first. 
Clear All This button will clear all of the fields including calculated fields and specified criteria in the Report Builder. You will be able to start again from scratch.
More Columns This button will add another 25 columns to the Report Builder to a maximum of 50 for any new Classic report. 

Classic Report Builder Help Panel

At the very bottom of the Classic Report Builder interface is the Help Panel with an upwards arrow that will open up help options when clicked. 

Report builder classic help.png 

The Help menu consists of 6 tabs, each with helpful information corresponding to that tab's topic. 

Overview This tab provides descriptions for all the components of the top panel of the Report Builder, including column fields and their descriptions.
Comparison Operators

This tab will describe how to set the basic criteria on logical tests.

Wildcard and Like Operators

This tab describes how to set partial criteria. 

Special SmartSimple Criteria Values This tab describes how to set criteria from user prompts and user attributes (such as user and company identifiers).
Date Comparison

This tab describes how to set date- and time-based data. 

Calculated Field  This tab describes valid mathematical expressions and functions, string functions, and logical statements for user in the Calculated Field section of the Report Builder.

Report Builder Neo - Column Settings

If your new report has been saved under Builder Type - Neo, then the Report Builder that is launched will be the Neo rather than the Classic version. 

The Report Builder Neo was introduced as an upgrade to our Classic Report Builder; it provides the following benefits: 

  • A more intuitive interface with higher degree of configurability 
  • Saving time and effort in creating targeted, impactful reports
  • Uses your organization's language (the terminology related to your )

The primary upgrade in Neo is that it streamlines the process of finding, selecting, and setting up your information.  

The Report Builder Neo can only be used to create new reports; any existing reports with the Classic Report Builder will not be able to switch to Neo.

1. Click on the four-square icon on the top left of your page to launch the Report Builder Neo. 

Report builder icon.png 

2. The Report Builder Neo window will open up as a modal window: 

Report builder new.png

It will have the following functions and fields: 

Number of Columns You can add or subtract the number of columns you need for your report up to a maximum of 100. 
Dataset

Click the + icon on the top left of the Report Builder in order to add the primary dataset you will be using for your report. 

  • The primary dataset will always be easily identifiable as once it appears on the left-hand dataset list in the Report Builder, it will appear with a star next to its name. 
  • You can create as many primary and secondary datasets as required to personalize each report you make. 
Field The field you wish to display in this column. 
Column Heading The text that you wish to appear above each column in the report. 
Date/Time

Used to "extract" the day, month, week or year from a date field for comparative purposes. The options are shown in the screenshot below: 

Report neo date time.png
Group Options

Used in summary reports to group by sum, count, or calculate the average of the field. Also provides Total Group Options for configuration of report groupings and easier retrieval of total rows. The options are shown in the screenshot below: 

Report neo group by.png
Sort By Determines the sort order of the reports, either Ascending or Descending.
Alignment Determines the alignment of the column headings and the column values - Left (default), Center, and Right. 
Formula This field allows you to build out further complexity such as mathematical operators, concatenation, and supports MySQL syntax. 
Display

Checking this option will allow you to display the data from this column in your report; if you do not want it to be displayed, but rather only set as a criteria, you can uncheck this option and the column data will be hidden from the resulting report. 

Example: You may wish to produce a list of salaries over $100,00 a year and use the salary as a criteria, but do not want to show these results. 

Show Total Checking this option will display the total of all the data accumulated from that column. 
Criteria Here, you can set the criteria for which records to be displayed in a report. There are 5 columns available for setting criteria, each separated by the OR operator.
  • The Report Builder Neo has the Drag and Drop functionality to re-order the display view of columns in a report and to input fields into the appropriate columns. 
  • The Report Builder will automatically note which base dataset your columns relate to. 

You can also use the Search functionality in the dataset field picker in order to narrow down your fieldset to those which include matches to a word search. Simply click on the Fields tab, and insert your search into the search box: 

Report builder neo search field box.png 

Report Builder Neo Help Panel

See the Classic Report Builder Help Panel. 

Deleting a Report

The user must be permissioned to be able to delete a report in order to do so.

Batch Delete Reports

1. Click on the 9-square menu icon on the top right of your page.

052919 MenuIcon.png 

2. Under the heading Tools, select Reports. 3. Check the box on the left of all report names for the reports that you wish to delete.  4. A trashcan icon will appear in the top left row of buttons once at least one report has been selected. Click on this button to batch delete all of your selected reports.

Batch delete repeorts.png

5. A confirmation message will pop-up on your screen. Click Yes to confirm the deletion.  The reports will be deleted and your list view of reports will refresh to display these changes. 

Individually Deleting a Report

1. Click on the 9-square menu icon on the top right of your page.

052919 MenuIcon.png 

2. Under the heading Tools, select Reports. 3. Click on the pencil icon next to the name of the report that you want to delete to go into Edit mode for that report.  4. The report details will be displayed, and at the bottom will be a Delete button among a row of other buttons. Click on the Delete button.

Delete report.png

5. A confirmation message will pop-up on your screen. Click Yes to confirm the deletion. The report will be deleted. Your page will refresh and you will be brought back to the list of reports, now updated to reflect your deletion.

View Deleted Reports

In order to see which reports have been deleted from your system, either by you or another user, you can always click onto the last tab in the Reports section of the system, titled Deleted Reports.

 

The list of deleted records will have columns that include who deleted the report (Deleted By column) and when (Deleted Date column, presented as a date and time stamp).

Restore or Purge Deleted Reports

 You must have SysAdmin privileges in order to view or modify already deleted reports.

 1. Click on the 9-square menu icon on the top right of your page.

052919 MenuIcon.png 

2. Under the heading Tools, select the hyperlink Reports.

3. The list view of reports will be displayed. Click into the last tab, labelled Deleted Reports. 

4. The list of deleted reports in your system will be displayed.

5. Select the checkbox on the left of the report name of the report that you would like to either Restore or Purge.

Once you click this checkbox, two buttons will appear on the top left of your page underneath the tab labels. The reload button will allow you to restore the deleted report so that it will once more be accessible, viewable, and modifiable in your system. The trashcan icon will allow you to permanently delete this report, so that it is not accessible even from the Deleted Reports tab. 

Restore or purge deleted reports.png 
  • Note: If you choose to permanently delete a report, a pop-up confirmation message will appear. Click Yes to go through with this purge. 
 Any reports that have been deleted for over one year will be automatically purged.

Configuration - Advanced

Advanced Settings of Reports

The second tab on the New Report page is labelled Advanced. This tab will display all the advanced settings that can be configured for the report.

Advanced report functions.png

The options for the Advanced tab are described in the table below:

Criteria Text Text used to prompt the user when they are required to enter some criteria values. This setting will also specify the introduction text, as well as labels for Custom Fields. Labels are defined by a hyphen (-) preceding the label (ex: "-First Name") and are applied to the criteria fields in the other that they are entered.
View Mode

List View - Selecting this option will allow all the records to appear in a list.

Single Record View - Selecting this option means that each report will be displayed one at a time. 

External Access Internet Enabled - Toggling on this function will generate a URL that allows the report to be accessed over the internet without the need for a user to log into the system. This setting is useful for creating public reports. 
OData Connector

Toggling on this option will use Open Data Protocol to simplify data-sharing across disparate applications in enterprise, Cloud, and mobile devices.

  • This feature will only appear for Neo Reports; it is not available for new Classic Reports. 
Disable Global Template Toggling on this option will disable the Global Report Template (a personalization of HTML header/footer for all system-based reports) for this specific report. 
Interactive View Display

None 

Display Link to Interactive View - 

Open Report Directly in Interactive View -

Total Label Provide a name for the Total column. See Using Total Group Options in Report Builder for more./ 
Enable Translation

Allows report headers and associated charts to use translation options in the system's Language Library.

Enable Straight Join See Straight Join.
Enable Pivot View

 Enables pivot functionality; this means that when grouping by two different columns, you now have the option to perform an aggregate function and pivot the grouped columns across the x-/y-axis.

This example report is using "Pivot View" with the data grouped by "type" and "status". The user can choose which group options they wish to view via the drop down.
Merge Sub-Reports Merging of two Classic builder type reports 
No Name URL Parameter See Enable URL Parameter Encryption. 
Hide Custom Export This will hide the ability for a user to Custom Export this report by hiding the custom export icon ReportCustomExportIcon.png.
Hide File Export This will hide the ability for a user to File Export this report by hiding the export icon ReportFileExportIcon.png

Report and Field Caching

Scrolling further down the Advanced Settings tab will allow you to modify the functionality of caching for reports and field. 

Report more advanced.png

Report Caching

Caching Options: Enable - Enabling caching is a useful function that can help improve loading time and reduce the processing load on the server. This function is best for complex reports and reports that are accessed regularly. 

Caching Options: Cache By User - Caching by user must be used for when a report includes criteria to filter based on the user that opens the report (i.e. =@userid@). 

Cache Life Span - Enter an appropriate cache life span in minutes. Therefore, rather than re-creating the report each time it is run, caching stores the results for the duration specified in this field. The first time a report is viewed, it will be created by querying the database and live data will be displayed. Anyone viewing the report after this and within the life span will see a snapshot of the report from the cache. 

  • Changes to underlying data will not be reflected in the report during the Cache Life Span. 
  • The report will be updated only once the life span has expired. 
  • In general, Sub-Reports should not be cached, though the parent report can be cached if appropriate. 

Field Caching


Frequency - The frequency of field caching can be set as follows:

  • Disabled: Default setting; indicates a report will not run on an automated schedule
  • Daily: The report will run every morning. 
  • Weekly: The report will run every Sunday.
  • Monthly: The report will run on the first of every month. 

Identifier Column - Select a report column from those available in the Report Builder that will uniquely identify the record to update. This is the match field as it indicates the field that allows the system to know which record to update. 

Column Field Value Column - Select a report column from those available in the Report Builder for the data that you wish to store; this acts as a source field as the setting represents the values that the system will use to update the Custom Field specified in the next field. 

Custom Field - Select a dataset-specific Custom Field to where the dataset will be stored. This is the destination field as the custom field will be updated each time that the report is run. 

Last Run - This will display a date and time stamp of the last time that the field caching/scheduled report was run. 

Internet-Enabling Reports

The ability to Internet Enable a report is useful because it will make that report public, presenting real-time reports on the Internet - it generates a URL relating to that report that can then be linked to your website, or sent as a link in an email or email broadcast or other communication method. 

  • The person who clicks the link does not need to have login access to your SmartSimple system. 
  • Once the link is clicked, the report is processed and the results are displayed accordingly. 
The following steps are for reports made with Report Builder Neo, although the Classic Report Builder undergoes a very similar process for Internet Enabling reports.

1. Once you have the Reports section of your system open (see Accessing Reports if needed), click on the pencil icon next to a specific report name to Edit it. 

2. Click on the second tab labelled Advanced. 

3. Look for the field External Access. Toggle on the Internet Enabled feature. 

Report internet enable.png
Access Key

If you want to password-protect your Internet-enabled report, write in a customized access key that the user must have in order to see the results of the report, even if they have access to the URL. 

  • The access key will append itself to the URL of the report. 
  • By allowing people to view an Internet-enabled report specifically with an access key, you can remove access easily by simply changing the access key. 
URL This is the conventional URL that will display the results of the report on the browser that the user is using, as a web page.
Export URL This URL, when inputted into a browser, will enable the report to be downloaded onto the receiver's computer, and can be opened in a number of different file formats that they can select from.
Export URL-XML This URL, when inputted into a browser, will enable the report to be downloaded onto the receiver's computer in an XML format. 
  • The URLs for reports that are Internet-enabled are automatically generated. Simply copy and paste them to use it.

4. Click Save. 

  • Note: You can always tell whether a report is properly Internet-enabled if it has a black dot in the Internet-Enabled column on the main reports list view, found from Tools > Reports. See screenshot below. 
    Internet enabled report.png

Language Translation for Internet-Enabled Reports

Users can now add a parameter to the Internet Enabled URL of all reports so the results and column headers can be made language-specific, if corresponding translations have been configured for the fields.

The parameter used is lang followed by the ID of the language that the report should be translated into.

In the example below the parameter lang=2 is used to translate the report into French. 

https://test.smartsimpleqa.com/ex/ex_viewreport.jsp?key=&token=%40GgUNSxoaeFxbSxRdSRxRRVdQZFB3H3Q~&lang=2

Reports vs List Views

In terms of displaying data, SmartSimple offers numerous methods to do so. In particular, list views and Reports are two of the most popular and highly configurable functions. A critical question is therefore when is it appropriate to use a list view, and when is it appropriate to build a report? For more in-depth information, see our List View and Reports - Why? page. 

Benefits  Things to Note

A report is needed if your situation includes at least one of the following criteria: 

  • You need more than 30 columns
  • You need formulae or calculations 
  • You need more complex aggregations than a simple total (e.g. count distinct, maximum, sum, group by, total by) 
  • You need an OData connection or you need to link multiple datasets that are not automatically connected (e.g. multiple Universal Tracking Applications™s) 

Reports are more sensitive because of the following: 

  • Only 5 criteria are permitted compared to the limitless criteria on a list view 
  • Requires more knowledge of SQL and reporting - although if a user is familiar with MS Access or Tableau, this is definitely sufficient, as the programs use the same principles 
  • Less dynamic visibility 

See Also

Retrieved from ‘https://wiki.smartsimple.com/index.php?title=Reports&oldid=41879’