2,299
edits
Changes
no edit summary
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|interface]] and are viewable from any [[Browser|browser]], as well as via '''Microsoft Word '''or '''Excel '''with the appropriate SmartSimple [[Exporting Reports|plug-in]].
'''Benefits of Reports: '''
* Display data from one or more tables in one place
* Group-aggregate data from one or more tabletables
* Display the grouped data in a variety of different types of graphs
* 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
* 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
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:
:: [[File:Reports manager permissions.png|350px|border]]
:
:* You can also specifically permission which roles will have the ability to '''Export ''''''Export Data '''from reports.
6. Click '''Save '''at the bottom of the screen when you are done.
|-
||'''Roles'''
||This will display a list of [[User Roles|roles]] that will be able to display the report. You must [[Sharing Reports with People - Overview|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'''
|-
||'''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.
:: [[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.
<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.
==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.
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.
:: [[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=='''Note: '''Since the '''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. This section will only focus on the '''Classic Report Builder. ''' [[File:Classic report builder.png|800px|border]]
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.
|-
||'''Sort By'''
||Determines the [[Sort OrderBy|sort order]] of the report results, either '''Ascending '''or '''Descending.'''
|-
||'''Align'''
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.
'''[[Changing the Column Display Order in a Report|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.
|-
:: [[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.
||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|500px400px|border]]The options for the '''Advanced''' tab are described in the table below:{| class="wikitable"'''Criteria Text'''Text used to prompt the [[User|user]] when they are [[Prompting_for_Criteria_in_Reports|required to enter]] some [[:Category:Criteria|criteria]] values. Use this setting to specify the introduction text as well as labels for custom fields. Labels are defined by a hyphen (-) preceding the label and are applied to the criteria fields in the order that they are entered.'''View Mode'''
{| 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 ]] - '''Displays a Toggling on this function will generate a [[URL]] that that allows the report to be [[Creating_a_Public_–_Internet_Enabled_Report|be accessed over the internet]] without the need for a user to log into the system. This setting is useful for creating public creating '''public '''reports. |-||'''[[OData 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 '''Enabled Version 2,3,4 -Neo Reports'''; it is not available for new '''Classic Reports. '''
'''Disable Global Template'''Disables the [[Global Report Template]] for this specific report.'''Record Limit'''Determines the maximum number of records returned in the report.'''Display Link to Interactive View Display- '''
'''None 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./ |-||'''Other Options'''||'''Display Link to Interactive View Enable Translation - '''Allows report headers and associated charts to use translation options in the system's [[Language Library]].
'''Total Label'''<br />'''Other OptionsEnable Straight Join - '''See [[Straight Join]].
'''Enable TranslationPivot View - ''' - Allows report headersEnables pivot functionality; this means that when grouping by two different columns, you now have the option to perform an aggregate function and associated charts, to use translation options in [[Language Library]]pivot the grouped columns across the x-/y-axis.
'''Enable Pivot View Merge Sub-Reports - ''' Enables pivot functionality. 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 axisSee [[Merge Sub-Reports]].
'''Merge SubNo Name URL Parameter -Reports ''' - See [[Merge Sub-ReportsEnable URL Parameter Encryption]].
'''No Name URL ParameterHide Custom Export - ''' - See This will hide the ability for a user to [[Enable URL Parameter EncryptionCustom Export]]this report.
'''Hide Custom File Export''' - This will hide the ability for [[Custom Export]] for this report. '''Hide File Export''' - This will hide the ability for a user to [[File Export]] for this report.
|}
===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]]''' '''(Scheduled Reports)''' ||'''Frequency - '''The frequency of field caching (what is also referred to as '''[[Scheduled Reports]]''') 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|'''Caching Options: Cache By User -Advanced''']].
3. Look for the field '''Cache Life Span -External Access. '''Toggle ''on ''the '''Internet Enabled '''feature.
::::: [[File:Report internet enable.png|800px|border]]
{| class="wikitable"
|-
||'''Field CachingAccess 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]'Frequency '' '''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 ''Identifier Column '||'''Things to Note'''|- ||A report is needed if your situation includes ''at least one ''of the following criteria:
* 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 '''Last Run -Tableau, '''this is definitely sufficient, as the programs use the same principles * Less dynamic visibility
|}
==See Also==
* [[List View and Reports - Why?]]
[[Category:Reports]]
