Building Combined Reports

Available for use by itself, or in conjunction with BI Pages or Dashboards, are Combined Reports. Combined Reports allow for multiple grid reports to be tied together by a common variable, bringing analysis previously done using multiple reports into a single view. Combined Reports, while powerful on their own, reach their full potential when they incorporate the use of pre-filters and Grid Printouts. The Combined Report serves as a dataset that pulls together information from multiple reports, pre-filters allow for a refinement of that data while Grid Printouts allow the use of formatting, filtering, and grouping that refines the raw dataset into a presentable report. These formatted reports allow for a more rapid absorption of information than presenting data in a grid. Combined Reports allow a user to specify multiple reports with desired data, such as Sales > Order Reporting, Inventory Reporting, and Production > Job Reporting reports, as well as how to join these reports. This could be especially helpful for planners to provide a quick view of open Sales Orders, the Jobs to which they are linked, and current inventory levels to complete the Jobs and orders. More information is contained in the "Adding Combined Reports" section below.

Configuration

Before a BI Page can be created and published, one or more BI Datasets must be configured, which can be based on any published data including Combined Reports. If using Combined Reports in BI Datasets and BI Pages, one or more Combined Reports must be added to a User's record via the "Published Data" button. Otherwise, no Combined Reports will be available to select from when creating the BI Datasets. The steps to create Combined Reports are included in the Process section below. Steps for BI Datasets and BI Pages are available on Building BI Pages

Process

Adding Combined Reports

A new Publish Data option, Combined Reports, is available to allow a user to specify multiple reports with desired data, as well as how to join these reports. When a Combined Report is created, users can publish reports to it to be combined based on columns available to the Grid Layout. The Name of a Combined Report is controlled through the Captions table, it will be common that a new Caption will have to be added for each Combined Report you create. To setup a new Combined Report:

Navigate to Tools > Management Reporting.
Set the Report Type to "Combined Reports" and hit View.
Click "Add" and enter a name.
When a Combined Report is created, users can publish reports to it to be combined based on columns available to the grid layout.
The combined report will appear on the user's Favorites bar.
For Reports to be combined, a report published to the Combined Report must be selected as Master. Other reports will then have to either be joined to the master or be children of reports joined to master or children of those, etc, joining on selected columns, Join to and Join from.
Setting a report as Master will unset any previously-set master report in that particular Combined Report.
Reports that are not joined properly (orphans) will be listed on creation of the Combined Report.
Users can only view/modify Combined Reports they have access to via their User profile or the User Group to which they belong.

Note: Join alias must not be a number only and can only contain letters, numbers, and underscores.

Example

As an example, here are two reports and how they would join under this scenario. Each table consists of two simple columns, and they join on the "Color" column they share.

Table 1 (Master)		Table 2 (Joins to Table 1 / Master)		Resulting joined table
Number	Color	Letter	Color	Number	Color	Letter
1	Green	A	Green	1	Green	A
2	Green	B	Blue	1	Green	D
3	Blue	C	Purple	2	Green	A
4	Yellow	D	Green	2	Green	D
5	Green	E	Red	3	Blue	B
6	Red	F	Red	5	Green	A
				5	Green	D
				6	Red	E
				6	Red	F

Join logic

Blue:

1-to-1
Blue exists once on Table 1 and once on Table 2.
Therefore, it appears once on the resulting table with a value for Num, Color, and Letter

Yellow:

1-to-0
Yellow exists once on Table 1 and not on Table 2.
Therefore, it does not appear on the resulting table

Purple:

0-to-1
Purple exists once on Table 2 and not on Table 1.
Therefore, it does not appear on the resulting table

Red:

1-to-Many
Blue exists once on Table 1 and multiple times on Table 2.
Therefore, it appears on the resulting table as many times as it does on table 2 (twice), with repeated data from Table 1 (number) and unique data (letter) from Table 2 each time.

Green:

Many-to-Many
Green exists multiple times on Table 1 and multiple times on Table 2.
Therefore, it appears on the resulting table as many times as the product of its appearances on each table (3 lines * 2 lines = 6 resulting lines).
Each line in the results has one of the 6 unique combinations of the joined data.

Copying an existing Combined Report

The ‘Same As’ button will allow you to copy an existing Combined Report as the starting point for a new combined report. This would be useful in the scenario where you wanted a Monthly and Yearly view of the same reports. Each Combined Report would have the same Grid Reports published to it, but the Date Based On parameters would change from the Monthly to Yearly version. This is discussed in more detail in the Managing Grid Reports within a Combined Report section of this document.

Accessing the Combined Report

Combined Reports can be made available by User or User Group, managed through the ‘Published Data’ button found on the Edit User or Edit User Group form. Those forms are accessed by modifying any of the Users or User Groups found within System > System Maintenance > Users/User Groups. Remember that this will initially be done for only the users responsible for building and validating the report. Additional users can be given access to the report once the design and validation is complete.

Within the Published Data form, a list of the Combined Reports shows on the left side, double clicking them will move them to the right side. Clicking ‘Continue’ will make them available to the User or User Group that you accessed the Published Data form from.

Publishing additional grid reports to a Combined Report

Additional Grid Reports can be published to a Combined Report at any time, those additional reports will be joined to either the Master report, or any of the other reports that have been added to the Combined Report. The scenario below shows the second Grid Report being published into the Combined Report, so this report is being joined to the Master report from the prior section.

All the same fields from Publishing Grid Reports to a Combined Report are relevant here, however, there is an addition of the ‘Joint To’, ‘Join To Column’ and ‘Join From Column’ this time. Selecting a value in the ‘Join To’ field will enable the ‘Join To Column’ and ‘Join From Column’. The ‘Join To’ field identifies which of the reports in the Combined Report this Grid Report should be joined with, these reports must share a common variable to join on. The reports in the Combined Report will show in the ‘Join To’ field based on their ‘Join Alias’. The ‘Join To Column’ specifies the field to join on from the ‘Join To’ report. The ‘Join From Column’ specifies the field to join on from the report being published.

Reports can also be joined using User Defined Columns, this allows for custom variables to be generated within Grid Reports that can then be used in the join between two reports on a Combined Report.

Combined Reports operate using an INNER JOIN, this means that records will only show in your Combined Report if they exist in both reports that are joined together. In the example of joining Sales Order Detail to Inventory On Hand, the Inventory On Hand report was configured to show zero quantity items, this ensured that all stocked items were present on both reports. If zero items were left off the on hand report, the combined report would have dropped the sales order lines where no inventory was on hand.

Managing grid reports within a Combined Report

Anytime after a Grid Report has been published into a Combined Report, changes can be made to the Description, Start Date Type, End Date Type, Join To, Join Alias, Join To Column, and Join From Column. Grid Reports can also be deleted from a Combined Report. The Edit Combined Report form is accessed by modifying any of the Combined Reports found within System > System Maintenance > Combined Reports.

Using the modify button will open the same form referenced in the sections above, regarding publishing Grid Reports to a Combined Report. Using the delete button will present a Yes/No prompt that confirms if you wish to delete the highlighted Grid Report from the Combined Report.

Managing the Combined Report grid layout

While the bulk of grid management will occur in the base reports, prior to publishing them into the Combined Report, there are still instances where grid changes will need to occur directly within the Combined Report. Examples of this would be when needing to perform math using a variable in the Sales Order Detail grid as well as the On Hand Inventory grid. Or in a case where an expression needs to check if something is true in a variable from both the Sales Order Detail and On Hand Inventory grids.

Modifying a Combined Report Grid Layout is much like modifying any other Grid Layout. First you must run the Combined Report from your Favorites list. Once the Combined Report is open, click the More button and select Grid Layout. From within the Edit Grid Layout form you will be able to add new variables as well as modify or delete the existing ones. Adding new variables will be limited to User-Defined Columns, modifying the base grids will require the base grid be updated and republished to the Combined Report.

Generating a Combined Report

Once a Combined Report is ready for review, it can be generated from the Favorites list of all Users that have been given access at the User or User Group level. The Favorites list is accessed through the star icon in the top right corner of DEACOM.

Building a Report Layout for a Combined Report

Report Layouts can now be associated with grid layouts, this includes Combined Reports. The Combined Report has a grid layout of its own where the Report Layout must be attached and designed. Unlike the stock reports in DEACOM, these Grid Report Layouts have no starting template. The report editor will open with a blank Report Layout that can be designed to work with the data in the Combined Report. This functionality is accessed from within the Grid Layout button under the More button menu from within the Combined Report grid.

Once inside the Edit Grid Layout form you click the Add button in the Print Outs section. This opens the Edit Print Outs form, on that form click the magnifying glass icon to open the Report Layout selection. Once in the Select a Report Layout form, click the ‘Add New’ button.
Clicking the ‘Add New’ button will open a blank Report Layout editor. From there click the ‘Report Properties’ button.
Clicking ‘Report Properties’ will open the Edit Report form, here you must assign the report a name and click ‘Save’. Save the Report Layout back on the main form and close the Report Layout designer.
Back on the Select a Report Layout form, you can now search for your Report Layout name and select the report to be saved to the Combined Report grid. Double click to select.
This will bring you back to the Edit Print Out form, add a ‘Name’, this is what will show in the print selection screen when printing the report. Check the ‘Default’ checkbox if this is the only Report Layout that will be attached to the Combined Report. The ‘Default’ checkbox controls which Print Out comes up when printing; if no default exists, a specific report will have to be selected during printing, otherwise DEACOM will print the Combined Report grid layout.
After saving the Report Layout to the Combined Report grid the Report Layout can be updated at any time from the Edit Grid Layout form within the Combined Report.

Printing a Report Layout from a Combined Report

Print Outs that are attached to the Combined Report Grid Layout can be easily printed by clicking on the printer icon from within the Combined Report results grid.

Building a pre-filter for a Combined Report

New to DEACOM, the ability to build a pre-filter against a Combined Report, this functionality allows to for the larger dataset within the Combined Report to be limited to just certain values. The options available to the pre-filter is based on the columns that are on the Combined Report.

The pre-filter functionality is accessed from the ‘Pre-Filter’ button on the Edit Combined Report form, found within System > System Maintenance > Combined Reports.
After clicking the ‘Pre-Filter’ button the Layout form is displayed. Double click on the “Combined Report (CombinedReportPrefilter)” menu option to open the Edit Element form.
From within the Edit Element form click the ‘Tables’ button. This will open a mover form that allows for the selection of tables to include in your pre-filter. Double click on the necessary tables to move them from the left to the right and click the ‘Continue’ button
After clicking the ‘Continue’ button, save and close the Edit Element form. Back on the Layout form, expand the ‘SectionFrame (SectionFrame)’ menu option, highlight the ‘Section (Section)’ within and click the ‘Add New’ button.
Clicking the ‘Add New’ button opens the Edit Element form where the pre-filter field is fully identified. The ‘Type’ field specifies whether the pre-filter field is a Search Box, Date Field, Text Field, etc. ‘Control Source’ is used to identify what piece of information will be fed into the prefilter field. Different ‘Type’ selections will result in different fields being available on the form.
After the required pre-filter fields have been added, save the Edit Element form and save the Layout form.

Using Combined Report pre-filters

The Combined Report pre-filter is presented after running the Combined Report from the Favorites list. Instead of the Combined Report going straight to the grid results, the user is presented with a pre-filter that can be populated. If none of the pre-filter fields are marked as required, the report can be run by clicking the ‘View’ button. Adding any data into the pre-filter fields will put a restriction on the report results returned.

Attaching documents to a Combined Report

Documents can be attached to a Combined Report using the ‘View Docs’ button on the Edit Combined Report form, accessed by modifying any combine report in the list found within System > System Maintenance > Combined Reports.

FAQ & Diagnostic Tips

I am getting an LSE when I attempt to modify an existing BI Dataset or BI Page that contains a Combined Report.

Ensure that you have permission to view and use the selected Combined Report. This can be verified by a System Administrator performing the following:

Navigate to System > System Maintenance > Users.
Select the appropriate user then click "Modify" then "Published Data".
The Combined Report used in the BI Dataset/BI Page must be displayed on the right-hand side of the mover form, which means that the user has permission to view the report.

Tip: Within combined report pre-filters, for every date type field, there are three date fields that can be used as control sources:

datename_start (When using the _start date, it is treated as anything greater than or equal to that date.)
datename_end (When using the _end date, it is treated as anything less than or equal to that date.)
datename_exact (When using the _exact date, it is treated as anything that equals that date.)