This scenario is for controllers or accountants who work with financial data and spreadsheets. In this walk-through, we'll open a Profit and Loss (PL) Trend report and drill into the supporting detail from the general ledger detail. The Journal Entry (JE) Query report will provide us account level detail and will allow us to drill further into each journal entry.
We can go directly to any section of this walk-through by clicking one of the links below.
Navigating to the PLTrend Report
Step 1: Navigate to the PL Trend report page inside the INTERJECT Financials folder of the Report Library . The Report Library is a central place for publishing reporting templates. It is versioned and easily located.
Step 2: With the report template open, type the location number, year, and the month of the report to view. Let's use 7002 for Location and 2002-05 (May 2002) for the year and month.
Now we're ready to pull the data. Use the "Pull Data" button as shown below, or use the short key Ctrl-Shift-J.
It will just take a moment for our report to populate. The illustration below shows the amounts for March, April, and May of 2002.
Modifying the Report
Step 1: Now we're going to quickly add a few more columns to the report for June, July, and August. Let's first clear the data so we have an empty template. Clear the data by selecting the "Pull Data" button again, but this time let's choose the "Clear" button .
Step 2: Extend the trend out a few more months by highlighting column N, clicking and holding the small square anchored on the bottom right of the header, and dragging it three columns to the right. Once released, the columns automatically expand. The below animated GIF illustrates this series of steps.
Step 3: Re-pull the data with the "Pull Data" menu button as we did earlier.
Once the update is completed, all six months of data will be shown and populated.
Grouped Detail in the Report
INTERJECT allows for spreadsheet groupings within financial reports, so additional detail can be included and expanded when needed. The steps below will demonstrate how to expand grouped areas to view the account level detail included in this report.
Step 1: Groupings are managed from the top and left margins of the spreadsheet. In this report, we are only using grouped rows so there are only +/- buttons on the left. Click the plus sign to expand a group and see the account detail.
The previous example shows two groups expanded individually, but you can also expand all groups at one time. On the top left there is a sidebar with the numbers 1 and 2 on the top of the left margin.
Clicking 2 will expand all the groups at that level.
Clicking 1 will collapse all the groupings.
Drill to JE-Transaction
After expanding the groupings of modified the reports, we may still want more detailed information about a specific account. The "Drill on Data" feature allows us to pass information from one report to another for additional details.
For example, drilling on any "Trend Data" value in account 32000 will take us to the "JE Query" report template which is a brand new workbook rather than another tab and will provide the line item detail. Try drilling on account 32000 for March 2003.
Step 2: A new report will open and automatically pull the underlying detail for the drill. The total will agree with the amount that was drilled on in L34 from the previous report. Notice the filters for the report were automatically populated from the report that you drilled from. The date range, account, and location all match.
Drill Further into JE-Lookup
Lets go one step further. Now that we see the journal entry detail summing the account balance, we'll pick a specific line and drill to view the complete journal entry JRNLWA00032760 .
Step 1: Click on the first line that has a negative 20,900 value, on row 23. This line is from the journal entry JRNLWA00032760 .
This drill goes directly to a second tab in the report called JE Lookup, and it will show the complete detail for journal entry JRNLWA00032760. As expected, it sums to zero like all journal entries must.
Report Example with Data Cells
The above examples illustrate the INTERJECT Data List reports. These reports are structured by specifying what financial data goes into which column and row subtotals setup to show a specific group of accounts. INTERJECT provides an alternate method to setting up reports so each worksheet cell can contain any value you need.
Step 1 : The first thing we are going to do is delete the rows below 44 and completely clear them out.
The report now looks like this.
Now we are going to use Data Cells to replicate some of the present data. By having both data generated by tabular reports and data cells next to each other, we can confirm that they are yielding the same results.
Step 2: To see how it works, we'll use a Data Cell equation.
The example below illustrates how the jCell() function can be used to show totals for Actual in March 2002.
To open the function window, select the cell, click on " jCellN " in the formula bar above the columns, then click the fx button to the left.
We use the jAcct() function inside the Full Account section to include filters for both the Accounts and Location segments of the chart of accounts.
To get the Function window for jAcct(), just click inside of its function in the formula bar.
In this function we are using accounts 31000, 32000, and 33000 in Segment 1, and the location 7002 in Segment 2.
Notice that these are the same accounts that makeup "Revenue - Sales" in the original PL Trend section and the same location we filtered on earlier.
Step 3: Now we can use the "Pull Data" function.
As we can see, the totals for "Revenue - Sales" match for both the data cell and data list sections.
Step 4: Listing out each account for a financial group can take time if the account is large enough. Next let's try to use data cells again, but this time we are going to use groupings to make the function easier.
Go back to the jAcct() function window and change the accounts to be "Revenue - Sales" instead of the numbered list. Be sure to keep everything else the same.
After the switch, we can see that the values still are the same. This is because the accounts 31000, 32000, and 33000 are all under the preset grouping "Revenue - Sales."
Step 5: More complex filter text can be used to request precise balances. The following example uses a combination of ranges, exclusions, wildcards, and financial groups to return specific data. Even Net Income or EBITDA can be presented in a single worksheet cell.
For more information on the Data Cell Filter options, click here .
In the below example, the groupings to the left of the figures represent the grouping filter in the jAcct Function to the cell beside it. This is so we can see what account groupings are being used for each of the numbers.
As we can see, the numbers in the data cells tie perfectly with the numbers in the data list above.
Step 6: To compare to the earlier PL Trend report, open up the report "PL Trend - w/ DataCells."
Enter the same period and location, 2002-05 and 7002, like we did earlier in this walk-through. Use the "Pull Data" operation to pull data.
As we can see, the results will be the same as the earlier PL Trend above, but there are no groups to show account level detail.
A Quick Summary of the Disadvantages of Data Cells vs Tabular Reports .
Although using Data Cells (the jCell() function) to construct a report is very flexible and easy to learn, it has drawbacks that create extra work. The ideal applications for Data Cells are special reports that do not have consistent row and column structures and do not present low level details that change.
The main disadvantages of Data Cells are:
- The report will typically run slower, but especially for larger reports. Tabular Reports reports, in comparison, are much faster and more efficient for reporting servers.
- The report won't allow detail lines to be added or removed automatically based on what data exists for the filters. Data Cells create fixed reports that require more maintenance.
- Data Cells are not portable to non-INTERJECT users unless using the export feature to remove the special formulas like jCell().
We find that 90% of a company's needs are handled efficiently by Data List reports as first illustrated in this walk-through, and in many cases Data Cells are not needed at all. But for certain reporting requirements, Data Cells can be very handy.
This completes the initial walk-through detailing interactive reporting. Later in this wiki, we'll learn how to create reports and drills once INTERJECT has been integrated with ERP software.