What to use for reporting financials in S3

Below is what is pretty typical for clients:

Internal Finance Inquiries = Adhoc queries Excel Add-Ins,
Internal Reporting within Finance = RW/Report Writer
External Reporting for Board/Departments = Crystal Reports

As you alluded to, the difficulty with developing the financial reports in Crystal is understanding the data structure and having the necessary sql skills. One solution to this a 3rd party solution (e.g. Dashboard Gear's Financial Tookit, that delivers reporting tables that are easier to develop reports against, as well as OLAP cubes for analytics) to simplify the report development process. Typically though, power users in Finance use the delivered reports/programs in Lawson Portal, Excel Add-Ins, and RW, while reporting outside of the department is handled by IT using their reporting tool of choice (e.g. Crystal Reports, BIDS for SSRS, etc).

We use Excel Add-ins for departmental inquiries, Basic reports are done in Crystal by the super users in each department (HR, FIN, MM). This has been a slow move to this method over the years since we first implemented. FOr complex reports the IT staff here writes them and publishes them to DEV. Super users create upload and test in DEV then IT uploads to PROD after some validation.

GLCONSOL has units and budget but does not include the posting level, however it is great for consolidated reports.
I sometimes use sub reports for budget when the relationship with other tables is an issue. GLAMOUNTS and FBDETAIL can be difficult to work with. If you use GLCHARTDTL or GLMASTER and connect to both GLAMOUNTS and FBDETAIL you may need to use a sub report for the FBDETAIL budget data.
GLTRANS includes amounts and units but it is a very large table because it has all the JE detail transactions. It can be useful if you are creating a trial balance with detail.

I'm curious as to if anyone is using LBI (Lawson Business Intelligence) environment and what reporting tools (Crystal, SRSS, Qlikview, other) that they are using in the LBI environment.

We are using LBI pretty heavily to distribute reports. In the finance area we are using RW's and crystal reprots and some back office reports that we crystlaized. They are put on a "manager" dashboard and when a manager logs into lawson and clicks on a report they only see what departments they are responsible for.

Thank you all for your replies on what you use for reporting.

Have you tried running a GL 298 report (without totals) and exporting the report data to excel by creating a CSV file? When it is in excel you can sort and or sub-total as desired.
GL298 report includes budget, actual, encumbrances and commitments.

Hello, we are a K-12 school district, live since 2008 with financials, procurement, AC, payroll and now using Global HR (LTM).

The Accounting Services manager uses both RW reports and Lawson form reports to make sure everything is tidy and balanced before we close the period. We then have a suite of Crystal Reports which go into a monthly bound “Board Report” book that show particular slices and dices of financials for our governing board. These reports are available on the Lawson LBI dashboard.


Our schools and some departments use two different “one size fits all” Crystal Reports (one for summary balances and one for the transactional detail) which allows them to monitor their expenditure budgets. The two reports are burst by BRC (Budget Responsibility Center) so that schools only see their information. The reports pull in actual expenditures, budgets and commitment/encumbrances. These reports refresh daily overnight and during the noon hour. Each one is actually present three times on the dashboard with different runtime options for sorting and subtotaling and different choices as to whether all Expenditure accounts or only Discretionary accounts are shown. The accounting services manager trains all the department and school office managers so everyone know which version of the SUMMARY and DETAIL reports are appropriate for them to use.


At period close we create a frozen exported image of the summary and detail reports as of the close of the period. The current year and prior year are present on the Lawson dashboard.


We also have created a wonderful “Departmental Download” Excel Pivot workbook. There are two Crystal reports (one for summary and one for detail) that use an AU LIST to pull the user’s pie slice (the criteria in an AU List can be as complex as needed to identify the exact accounting strings they own). The power users LOVE using this vehicle as it puts information into Excel and allows them to manipulate the template workbook so they see it exactly as they wish to see it. It can be run either for one period or in a YTD mode. The detail gives them full Source-system information so that they can find exactly what they are looking for. There are run from the user’s desktop.


We have variants on these Crystal Report Downloads/Excel Pivots which are used by the Budget department and the Director of Finance which all them to pull in multiple years for analysis and forecasting. Forecasting is an extremely hot item here.


We have tons of other Crystal Reports and Excel Pivots which support Payroll, Assets, AC Project Accounting, Purchasing, Accounts Payable, and Warehouse.


We are happy these days with the reporting we have but users always want more so I have a neverending backlog of new things to develop and to roll out.

We just purchased an Excel add-in called Spreadsheet Server. It brings back data directly from Lawson into Excel enabling us to do easier reporting. We have 80 companies we consolidate and RW was not an option for that many entities. We also use Crystal Reports and LBI.

MORE THOUGHTS

Melissa, I think you must use a UNION join and insert the SQL as a COMMAND into a Crystal Report because of the Actuals w/o Units and Units w/o Actuals. We had the same problems with Budgets w/o Actuals and Actuals w/o Budgets. FYI we Budget Edit Check at a “summary” level so we do allow ACTUALS in an individual AU/Account that the user did not Budget.

We have also created five USER DEFINED AU ATTRIBUTES to assist with summary financial reporting. For example, we have created DIVISION and DEPARTMENT which are roll ups of our LEVEL FOUR AU segment BRC (Budget Responsibility Center). This allows us to produce a report for a DIVISION head showing how his/her direct reports (Department Heads) are performing. A department head can then report to monitor his/her direct reports or individual BRC’s which make up their department.

Two other USER DEFINED AU ATTRIBUTES we created are PROGRAM CATEGORY and PROGRAM SUB-CATEGORY which are rollups of AU level one “Program” value. Programs are the source of funds in the General Ledger. The Budget Department and the Forecasting guru are the main users of these two attributes.

The final USER DEFINED AU ATTRIBUTE we created is STATE ACTIVITY CODE which is a rollup up of AU level two “Activity” value. We have a state report we must produce that uses this attribute.

We have some financial download/Excel pivots which include all five of these user defined attributes so that power end users can really model and manipulate the data to get the exact slice and dice view of data that they need.

We have a dashboard report that opens up in excel listing new AU’s that are missing these user defined values. These AU’s are plugged into a master workbook from which the five GL20.7 ADDIN Uploads are performed to populate the appropriate user defined attribute values.

DIVISION and DEPARTMENT is usually only mass changed (i.e. ORG chart changes) as one fiscal year ends and the new one begins. At that time we do a total refresh of all AU’s so that they reflect the current year’s ORG chart. That way we can see CURENT YEAR vs. PRIOR YEAR information as if the prior year had been using the current ORG chart. For example, PRINTING & GRAPHICS used to be under the Director of Finance (a DEPARTMENT under DIVISION BUSINESS SERVICES) but in 2013-14 (starts in September for us) it moves to DEPARTMENT PURCHASING (also a part of DIVISION BUSINESS SERVICES). As soon as we put 2012-13 into limited, we will do a mass update of the DIVISION and DEPARTMENT attribute values to catch this, and all other ORG CHART changes effective as of the new fiscal year.

Again, this Lawson capability works well for us for performing this sort of dynamic grouping w/o having to hard code any logic in Crystal Reports.

We use Lawson RW100 reports to get the basic "dump" of information needed for a report and then used Crystal to do the roll-ups. This enabled us to utilize Lawson's linking capabilities along with the flexibility in Crystal reporting. The reports are published on dashboards using LBI with LBI security. So, one report could be used for the entire organization but by using LBI security, each user only gets to see their own information. Crystal allows us to create reports to enable users to drill down into the details. I hope this helps.

There isn't really an inexpensive product out there which, upon having purchased, you will never have to think or work again. The best tool is the one that you will actually use. For the more complex reports you will end up doing some kind of SQL (whether Crystal or SSRS) because the database is configured for transactions; not for online analytic queries. If you have the budget the Dashboard Gear solution is wonderful. If not, determine whether or not you have suitable Crystal licenses, or, there is often a SQL Server hanging around somewhere that you can get some space on and SSRS is free. Then work with the user groups (etc) to get some basic queries setup: perhaps a view or two of the data that you need.

In the end you may find that purchasing one or two reports will serve you well (getting the 80% of the functionality you require and allowing you to study how the report was created) as opposed to a deluxe solution that you will be struggling to support in 2 - 4 years.

The "cost" usually isn't the product itself. For instance, whether you use Lawson with SQL Server nor not you can still use SQL Server products [e.g. SQL Reporting Services (SSRS) and SQL Analysis Services (SSAS)] as long as you have SQL Server as part of your Microsoft licensing. Also, I offer a "poor-man's LBI" alternative that supports on-demand / scheduled Crystal and SSRS reports as well as various pivot table/grid viewers over Lawson data sources. Where clients typically expend is on the content development itself (how are accounting units defined, attributes used as dimensions, etc.).

Hello, Our facility is new to Infor and we are in the process of implementing the latest version of Infor for financial management, supply chain and HR. There has been alot of discussion about whether or not to implement LBI. We've heard it takes a full time FTE to manage this part of the system. Can you share with me what staffing levels you have in place just for LBI?

Additionally, we purchased Analytics for Financial Management. Is anyone using this technology? How does it compare to LBI?

Reading all the posts have me a bit concerned that I will need a dedicated FTE for reporting from Lawson.

Nicole

Hi Nicole,

If you are utilizing all of the components of LBI, including Smart Notification and Viewpoint, in addition to the Analytics package, a full-time FTE for day-to-day administration sounds about right. This will vary depending on the number of users and amount of content implemented (e.g. dashboards, reports, notifications, Viewpoint OLAP views, BPW models).

I want to stress that this is independent of content development. For development, especially during a new implementation of EFM/SCM/HCM, there is typically a team dedicated to this. The size of the team varies depending on a number of variables: project timelines, amount and complexity of the content, etc. Many organizations will augment the project team with Business Intelligence consultants with Lawson experience for this reason, as the good ones can expedite the development/implementation process while establishing development standards and advising on best practices.

Adoption of Healthcare Analytics (HCA) for Financial Management is starting to pick, but this is complementary to LBI. It provides pre-defined datamart tables and an OLAP cube, along with a number of delivered GL reports and OLAP views. The datamart/OLAP can reduce development time of additional reports/notifications/views, as it simplifies the complex Lawson Financials data model.

If you have any other questions, feel free to email me offline as well.

Chris Martin
Principal/Lead Architect
Datalyst Solutions
p. 314.807.0169
e. chris@datalystsolutions.com
w. datalystsolutions.com