Reporting

Using SmartViews to improve user productivity

 

More on SmartViews

Schedule your demo in a click!

How to leverage SmartViews in your Material Planning

Here's an example on how you can take advantage of SmartViews to drive VISUAL in your Material Planning efforts.

More on SmartViews

Schedule your demo in a click!

SmartViews: Self Service Reporting Tool for VISUAL

Infor VISUAL ERP - SmartViews Reporting
Being in the VISUAL space, I'm sure you've heard talk over the years on Synergy's SmartViews.  And you’re likely already aware of the features and benefits – among them being:

  • Dynamic and Self-Service Reporting Tool
  • Access to Real-Time Information
  • 25+ Pre-Built “Best-in-Class” Strategic and Tactical Analytics to drive growth
  • Intuitive Interface
  • Pivot Tables, Slice and Dice, Drag ‘n Drop
  • Trend analysis
  • Dashboards upon dashboards
  • Charts and more charts
  • Database expertise NOT required
  • Automated Report Delivery system

A reporting tool....on overdrive....with features so easy that anyone can report, analyze and act on the data.

There are specifically 2 things I like best with SmartViews:

  1. It’s Self-Serving

    SmartViews was built for self-service with its’ intuitive interface and easy drag and drop analytics creation.  You can hand over the keys to your end users – and let them have at it.  Slice, Dice, Drag, Drop, Filter, Sort, Chart – whatever the combination.

    And when they’re all done – they can save it and share it.

    Think about it…

    With users creating and modifying their own reports, you will significantly reduce the burden placed on your in-house (or external) reporting expert.  No waiting for reports to be created or modified. 

    People should be empowered to interact with data with a user-friendly application, creating reports that they need and not what someone else understood them to be.    
  2. VISUAL Hooks

    Obviously, SmartViews is plugged into the VISUAL database to provide the analytics, but it can also be configured to dynamically interact with the system.  Meaning – you can automatically open VISUAL and retrieve relating information depending on your selection in SmartViews.

    For example, suppose you have a Bill of Material for a fabricated item, you can set it up so that when you select a material line item of the BOM in SmartViews – it will call up the Part ID in VISUAL’s Material Planning Window to determine whether there’s sufficient supply compared to demand.  You can easily click through your exploded BOM for supply and demand info in the Material Planning Window.  This is a good example on ways to streamline and reduce time you spend in and out of the Material Planning Window – your planners know exactly what I mean here.

    This is just a simple example of the many (many) ways you can leverage VISUAL Hooks in SmartViews.


Are you interested in "seeing" it and not so much "reading" it? 
Hit me up for a demo

You can also find additional information on SmartViews here.

 

Removing an extended query from a QRP

You'd think that you would be able to remove your extended query from the same window where you originally added it -> the SYSTEM REPORT FORMAT OVERRIDES window

You won't have much luck there!

You actually have to open your QRP file from the Report Builder application and delete it from there.  It's a pain that you have to do it outside the VISUAL interface, but it's not complicated to do.

  1. Using your favorite reporting application, Unify Report Builder, open your QRP file with the extended query that you want to remove.
     
  2. From the OBJECT tree panel, expand the FORMULAS folder and find the item "SQLQUERY" (most likely near the bottom of the list).
      Infor ERP VISUAL - Report Builder
     
  3. Right click on SQLQUERY and select DELETE from the popup menu.
      Infor ERP VISUAL - Report Builder - SQLQUERY 
  4. Save the QRP

 

Enabling the User-Defined Print Forms

I'm sure you've noticed that VISUAL allows you to print customer facing forms (and vendor facing forms) in 5 different formats - Standard, Print Form and 3 User-Defined formats

Infor ERP VISUAL Express - Print Form
The STANDARD format points to the QRP named "xxxxxFRM.QRP" and the PRINT FORM to "xxxxxFM1.QRP".  The "xxxxx" would change depending on the the module that you're in.  For example, the customer order acknowledgement QRPs are VMORDFRM.QRP and VMORDFM1.QRP, respectively.

If you're like most VISUAL users, chances are the 3 remaining formats, the User-Defined ones, are greyed out and disabled.

These options only become enabled when their respective QRP physically exist in the VISUAL executable directory.  If they don't, the option is not available.

So how do we get our hands on these USER-DEFINED QRPs if they don't exist?

Easy.  We just copy either the STANDARD or PRINT FORM qrp file and rename it using the following naming convention:

  • "xxxxxUD1.QRP" = for User Defined 1
  • "xxxxxUD2.QRP" = for User Defined 2
  • "xxxxxUD3.QRP" = for User Defined 3

So using the same customer acknowledgement example, making a copy of the VMORDFRM.QRP and renaming it to VMORDUD1.QRP would enable the USER DEFINED 1 option.

Now before you go and start formatting these USER-DEFINED qrps --> best practice dictates that you make use of the REPORT FORMAT FILE OVERRIDES feature and copy these user defined files to your custom report directory and make your changes there.

The elusive Gross Profit Report

Infor ERP Express Visual - Gross Profit ReportEvery few months this report becomes an active discussion in the VISUAL forums.  It usually starts off with a user requesting some guidance as they attempt to recreate the VISUAL gross profit report found under the Post Manufacturing Journals. 

They soon come to realize that this is no easy feat as it involves numerous tables and complex queries.

Why are users wanting to recreate this report when they could just use the one in VISUAL?  Well I'm sure they have good reasons....

  • better access and distribution of report;
  • format/layout of report; 
  • additional fields (data) to include;
  • facilitate export of data to excel;
  • they have nothing else better to do!  :-)

In any case, they've taken on the task. 

So what makes this report so challenging?  It's largely because it ties to the financials for the period.  For instance, you cannot just take the costs from the INVENORY_TRANS table as it only contains the current costs (regardless if they are posted or not).  You actually need to pull the costs from the distribution tables which may contain multiple entries for the one shipment (that account for cost changes through the periods).

So if you are wanting to recreate this report, you'll need to look at the following tables:

  • RECEIVABLE_LINE
  • SHIPMENT_DETAIL
  • SHIPMENT_DIST
  • SHIPPER
  • SHIPPER_LINE 
  • INVENTORY_TRANS

Information_greenWhat's that?  You have better things to do with your valuable time than recreate this report?  Contact me.

Considering SSRS? The first step is the hardest

If you're like me, I'm always a bit apprehensive when venturing into projects involving new platforms or applications.  And that's because I know there's going to be a learning curve - with my luck it'll be a steep one! 

ChallengeAcceptedThat's not to say that I'm not up for the challenge.  But when there's so many projects and with so little time  - you want to make the most of your valuable time.  For me, when I take on something new, I want to make sure it's worth the time and effort.

Learning SSRS (SQL Server Reporting Services) was definitely time well spent.  And I now use it as my primary reporting application.

I'm sure you know all the benefits of SSRS.  If you don't, just google it.  And in case you don't know - SSRS is free if you own SQL Server (2005 or greater).  There's no shortage of resources on the internet that can get you started.

Here are 2 posts in particular to help you with your 1st step:

How to Install SQL Server 2008 Reporting Services
This post shows you (with screenshots) how to quickly get SSRS up and running in your environment.  It also has additional links to guide through additional settings.

SQL Server Reporting Services 2008 Tutorial in 5 Minutes or "How to send my boss a report from a view quickly"
Once you have SSRS up and running, refer to this post to see how easy it is to create a report and distribute it.

 

Gupta Report Builder: Are you a glutton for punishment?

Frustration_a I've always said that you lose brain cells when working with Gupta Report Builder in VISUAL.  It's hard to retain anything you learn in this application - you basically forget it as soon as you're finished with it.  This platform has got to be the most un-user friendly reporting application I've ever worked with. 

But hey that's me - maybe I'm a little slow.

To minimize my pain I refer to these resources - so I can get in and get out with the least amount of brain damage.

 

Using Excel and VBA to generate your VISUAL reports

If you find yourself always needing to export and import your VISUAL data into Excel - why not just retrieve the data straight from Excel?  You can do it with VBA.  Visual Basic for Applications (VBA) is a programming language built into most of Microsoft Office products.  You can do many things with VBA to control Excel - like connecting and querying a database and returning the results to the spreadsheet.

There's tons of VBA resources online - just google "VBA Resources"

With VBA, there's no reason why you cannot have Excel be your primary reporting application.  Check out Sales & Gross Margins Analysis Reporting in Excel with Drill Down Capabilities.  This was all done with VBA.

 
The example below generates a sales report for a given date range.  This mini project involves creating a dialog box prompting the user to enter a start and end date for the report.
 

To use VBA, you need to first make sure you have access to the Developer tab in the Menu Ribbon.

Infor ERP Visual - Excel integration - Developer Ribbon 

If it's not there, click the Office Button and click Excel Options. In the Excel Options dialog box, place a checkmark next to the Show Developer tab in the Ribbon and then click OK.  

Infor ERP Visual - Excel Integration - Developer tab option 

The Developer tab should now be appear in the ribbon.

Infor ERP Visual - Excel Integration - Developer tab enabled


  
Now you're all set.  So let's get crackin'.

  1. Under the Developer tab, click on Visual Basicto launch the VBA interface.

    Infor ERP Visual - Excel Integration - Visual Basic menu 
     
  2. Select UserForm from the INSERT menu.

    Infor ERP Visual - Excel Integration - UserForm 
     
  3. Select the Label icon from the TOOLBOX and place it on the form (as shown).
     
    Infor ERP Visual - Excel Integration - Label 
     
  4. Right click on the label and select Propertiesfrom the popup menu.  Enter "Starting Invoice Date" in the Caption property (as shown).
     
    Infor ERP Visual - Excel Integration - Label Caption 
     
  5. Select the Textbox icon from the TOOLBOX and place it next to the "Starting Invoice Date" label on the form (as shown).
     
    Infor ERP Visual - Excel Integration - Textbox 
     
  6. Right click in the textbox and select Properties from the popup menu.  Enter "txtStartDate" in the Name property (as shown).
     
    Infor ERP Visual - Excel Integration - Textbox caption 
     
  7. Repeat steps 3 through 6 to enter another label and textbox with the following properties:

        Label Caption:     "Ending Invoice Date"
        Textbox Name:    "txtEndDate"
     
     
  8. Select the CommandButton from the TOOLBOX and place it on the form (as shown).
     
    Infor ERP Visual - Excel Integration - Command Button 
     
  9. Right click on the CommandButton and select Propertiesfrom the popup menu.  Enter "Generate Report" in the Caption property (as shown).
     
    Infor ERP Visual - Excel Integration - Command caption 
     
     
  10. OK...we just completed the user-interface where a date range can be entered to base the report on.  Now we need to enter the code that will query the VISUAL database to generate the report.  To open the Code window, double click on the CommandButton "Generate Report".
     
     
  11. Before we start inserting code we need to set one reference in order to access the VISUAL database.  Select References... from the TOOLS menu. 
     
    Infor ERP Visual - Excel Integration - References 
     
  12. Place a checkmark next to Microsoft ActiveX Data Objects 2.6 Library.  Click OK.
     
    Infor ERP Visual - Excel Integration - ActiveX data Objects 
     
  13. In the Code window, place the code below right after the "Private Sub CommandButton1_Click()" (as shown).  You can DOWNLOAD the code (in case you're trouble copying the code)

    Infor ERP Visual - Excel Integration - Code window 
     
    Dim SQL As String
    Dim dbConn As New ADODB.Connection
    Dim rs As New ADODB.Recordset

    Dim iCols As Integer

    Dim strServerName As String
    Dim strDatabase As String
    Dim strUserName As String
    Dim strPassword As String

    '***********************************************************************************************
    'Be sure to modify the following 4 variables to include your database connection information
    '***********************************************************************************************
    strServerName = "E N T E R   N A M E   O F   S Q L   S E R V E R"
    strDatabase = "E N T E R   D A T A B A S E   N A M E"
    strUserName = "E N T E R   U S E R N A M E"
    strPassword = "E N T E R   P A S S W O R D"

    'Clear Spreadsheet
    Application.Sheets("Sheet1").Range("A1:Z60000").Cells.Clear

    'Validate Starting Invoice Date entered
    If Not IsDate(txtStartDate.Text) Then
     MsgBox "Invalid Starting Invoice Date.  Please verify", vbOKOnly, "Invalid Date"
     txtStartDate.SetFocus
     Exit Sub
    End If

    'Validate Ending Invoice Date entered
    If Not IsDate(txtEndDate.Text) Then
     MsgBox "Invalid Ending Invoice Date.  Please verify", vbOKOnly, "Invalid Date"
     txtEndDate.SetFocus
     Exit Sub
    End If

    'Setup connection to VISUAL database
    Set dbConn = CreateObject("ADODB.Connection")
    dbConn.Open "Driver={SQL Server};Server=" & strServerName & ";Database=" & strDatabase & ";Uid=" & strUserName & ";Pwd=" & strPassword

    'Setup recordset to query for invoices for specified date range
    Set rs = CreateObject("ADODB.Recordset")
    SQL = "SELECT REL.INVOICE_ID as [Invoice ID],RE.INVOICE_DATE as [Invoice Date], RE.CUSTOMER_ID as [Customer ID], C.NAME as [Customer], Sum(REL.AMOUNT*RE.SELL_RATE) as Revenue " & _
    "FROM ((RECEIVABLE RE INNER JOIN RECEIVABLE_LINE REL ON RE.INVOICE_ID = REL.INVOICE_ID) INNER JOIN CUSTOMER C ON RE.CUSTOMER_ID = C.ID) INNER JOIN ACCOUNT A ON REL.GL_ACCOUNT_ID = A.ID " & _
    "WHERE (((A.TYPE)='R')) " & _
    "GROUP BY REL.INVOICE_ID, RE.CUSTOMER_ID, C.NAME, RE.INVOICE_DATE " & _
    "HAVING RE.INVOICE_DATE>='" & SQLDate(txtStartDate.Text) & "' and RE.INVOICE_DATE<='" & SQLDate(txtEndDate.Text) & "' " & _
    "ORDER BY REL.INVOICE_ID, RE.INVOICE_DATE"
    rs.Open SQL, dbConn, adOpenStatic

    If rs.State = 1 Then

        'Enter Column Headings onto spreadsheet from the recordset
        For iCols = 0 To rs.Fields.Count - 1
          Application.Sheets("Sheet1").Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
        Next

       'Set column headings to BOLD
        Application.Sheets("Sheet1").Range(Application.Sheets("Sheet1").Cells(1, 1), Application.Sheets("Sheet1").Cells(1, rs.Fields.Count)).Font.Bold = True

       'Paste recordset to spreadsheet
        Application.Sheets("Sheet1").Range("A2").CopyFromRecordset rs

       'Auto-fit columns widths
        Application.Sheets("Sheet1").Range("A1:Z60000").Columns.AutoFit

    End If

    rs.Close

    Set rs = Nothing
    dbConn.Close
  14. me.Hide
     
     
  15. You also need to add the following 2 functions to the Code window.  These functions are used to format the dates for queries on a SQL Server database.  Be sure to place them outside of the CommandButton1_Click() procedure.  You can download these functions.

    Infor ERP Visual - Excel Integration - Code Window 
    Private Function SQLDate(dt)
       SQLDate = Year(dt) & pd(Month(dt), 2) & pd(Day(dt), 2)
    End Function

    Private Function pd(n, totalDigits)
       If totalDigits > Len(n) Then
          pd = String(totalDigits - Len(n), "0") & n
       Else
          pd = n
       End If
    End Function
     
     
  16. OK...We're almost done...I promise.  Now we need a macro that will prompt the user to enter the date range for the report.  Under the Developer tab, click on Macros menu item.
     
     Infor ERP Visual - Excel Integration - Macros menu 
     
  17. Enter SalesReport for the Macro name (as shown). Click Create. 
     
    Infor ERP Visual - Excel Integration - Macro name 
     
  18. Enter the code "UserForm1.Show" right after the "Sub SalesReport()" (as shown)
     
    Infor ERP Visual - Excel Integration - Macro code 

And we're done!  The report is ready to be used (and distributed) just like any other excel file.

I know it may have seemed complicated but it really isn't.  Once you get the hang of it...it's actually quite simple and easy to put in place.

To run the report: 

  1. Click on the Macros Menu

     Infor ERP Visual - Excel Integration - Macros menu 
     
  2. Select "SalesReport" from the Macro list. Click on Run

    Infor ERP Visual - Excel Integration - Macro list   
      
  3. Enter the starting and ending dates for the report.  Click Generate Report 

    Infor ERP Visual - Excel Integration - Data entry 
     
  4. And there you go...the report based on your date range.
     
    Infor ERP Visual - Excel Integration - Results

 

 

 

Is this a Credit Memo or an Invoice?

Well if the total amount is negative than it's an credit memo, otherwise it's an Invoice....no?  Well of course, but wouldn't it be nice to have it clearly state whether it's a credit or invoice on top of the document?

In VISUAL, invoices and credits are printed using the same QRP template files.  With conditional formatting, you can clearly state whether the document is an INVOICE or CREDIT MEMO.  Conditional formatting allows you to dynamically change the appearance of the document based on the result of a formula.  In this case, the formula will look at the GRAND TOTAL.  And if it's negative it will print CREDIT MEMO otherwise it will print INVOICE.

  1. Using Report Builder, open the invoice QRP (ie: VMINVFM1.QRP)
     
  2. Add a new field and place it at the top of the form
     
  3. Right click on the field and select PROPERTIES from the popup menu
      
    Infor ERP Visual - QRP Field Properties
      
  4. Click on the Edit Field Formula button
      
    Infor ERP Visual - QRP Edit Field Button
     
  5. Enter the following formula in the space provided:

    StrIFF( StrScan( CUR_GRANDTOTAL,'(' ) , 'INVOICE', 'CREDIT MEMO', 'CREDIT MEMO' )

    Negative values are displayed with brackets.  So this formula searches the CUR_GRANDTOTAL field for an open bracket '('.  If it finds one it will print "CREDIT MEMO" otherwise it will print "INVOICE".
      
    Infor ERP Visual - QRP Field Formula  
  6. Click OK
     
  7. Change the field's font style and font size (if desired)
      
    Infor ERP Visual - QRP Template
      
  8. Save the QRP

So the next time you're printing invoices and you happen to come across a shipment return - you should see something similar to the following:

Infor ERP Visual - QRP Invoice 
 

WHO IS VISUALNUGGETS?

Rich Ferlatte
Results Driven VISUAL Specialist

CONSULTING SERVICES

Of course, I am available for hire!

ERP Implementations & Upgrades
Analytics & Reporting
Integrations & Macros
General VISUAL Support

ONLINE VISUAL WORKSHOPS

Taking it to the next level and designing a program around "Hands On VISUAL Training".

Check them out - there's sure to be one to interest you!

Operational Workshops
Reporting Workshops
Financial Workshops
Inventory Workshops
Quality Workshops

VISUAL EXTENSIONS

BOM Xploder
Drastically improve your BOM visibility and discover shortages at ANY level of your bill of material no matter how deep.
SmartViews
See, analyze, and track information that helps you better support individual, departmental, and your company’s performance metrics and goals.
eBoard - Financial Power App
Developed for accounting professionals looking for powerful and easy-to-use GL reporting software. Access live financial data, drill down to support details and export financial statements to Excel in one click.
Inventory App
This Inventory Reconciliation App provides a systematic approach to control and manage your Visual inventory effectively and efficiently
Credit Card Integration
Easily accept and process credit card payments right from within VISUAL.
ACH Document Processing
Leveraging VISUAL's payment scheduling and batch payment functions, with the added convenience, performance, and security of Electronic Funds Transfer payment processing.
Latitude Manifest & Shipping System
A seamless multi-carrier shipping solution that integrates to Infor VISUAL ERP. See how it can simplify your small parcel and LTL shipping and package tracking.
Sales Tax Automation with AvaTax
Easily automate sales and use tax in VISUAL with AvaTax. No more manually entering data or uploading rate tables and tax information.
Workforce Management with Kebrite Solutions
Streamlines the collection of Job, Labor, Payroll, Time & Attendance, and a vast array of HR related data-- with live VISUAL integration.