Written by Rich on April 10, 2018

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.


Written by Rich on July 18, 2012

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


Written by Rich on May 21, 2012

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.

Written by Rich on January 16, 2012

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:


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

Written by Rich on January 14, 2012

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.


Written by Rich on February 22, 2011

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.

Written by Rich on November 3, 2010

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

    'Validate Starting Invoice Date entered
    If Not IsDate(txtStartDate.Text) Then
     MsgBox "Invalid Starting Invoice Date.  Please verify", vbOKOnly, "Invalid Date"
     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"
     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 " & _
    "WHERE (((A.TYPE)='R')) " & _
    "HAVING RE.INVOICE_DATE>='" & SQLDate(txtStartDate.Text) & "' and RE.INVOICE_DATE<='" & SQLDate(txtEndDate.Text) & "' " & _
    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

       '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

    End If


    Set rs = Nothing
  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
          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'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




Written by Rich on September 29, 2010

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  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:


    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 

Written by Rich on July 15, 2010

Sum function in a Visual QRP

So you created a line item formula that does some sort of numeric calculation on the customer order form.  But now you want to have a total of this formula and display it in the footer section of the form. 

No problem.

Report Builder allows you to do this through the TOTALs section found under the Report Objects (in the Object Tree pane)


Here's how it's done

Let's say that you want to show your customers how much they will save if they supply their own shipping material (boxes, skids, PLASTIC BAGS,etc) to ship their order to them.  Let's assume that you charge a 5 cent surcharge (for each unit ordered - quantity) when they don't supply their own shipping material.

  1. Open the customer order QRP form using Report Builder
  2. Create a formula that will calculate the line item plastic surcharge based in the quantity (0.05*LN_ORDER_QTY). In this example, I named the formula Plastic_Bag_Charge
  3. Insert the formula Plastic_Bag_Chargein the details section of the QRP (you can choose to hide this value)
  4. To calculate the total of the Plastic_Bag_Charge, right click on the TOTALS folder (found in the Report Objects panel) and select NEW. 

  5. Click on the NEW button in the Format Totals window.
  6. At the Define Total window, enter the following: 
    Name = enter name for the new total. In this case, Total_Plastic_Bag_Charge 
    Formula = enter the formula name (Plastic_Bag_Charge) you created in Step 2 
    Statistics = Sum 
    Restart Event = First Fetch

  7. Click OK and then close the remaining windows
  8. Your new total should now appear under the Totals section.

  9.  You should now be able to insert this total in the footer section of the form.

"By providing your own boxes, skids, peanuts, plastic, tape and labels your savings amounts to 40 cents...Thank you for your business"


Written by Rich on June 3, 2009

FACT: Extended query field variables in QRPs cannot be exported

This is not so much a tip as it is a known fact.  QRP fields variables created by extended queries will not be exported when you select "Print to File".  When printing to file, Visual does not execute the extended query because the exporting process is done through the executable and not the QRP (where the extended query is attached to)

A work-around (if you want to call it that) is to set up a Generic/Text only printer that will print to a file when selected as the printer destination.  Then you could open up this new "PRN" file with excel or application of choice. 

Warning: you might (and probably will) have to play around with the layout of the QRP so that the output is usable when opening the file in excel

You might be better off just creating a new report in MSAccess, Crystal or Web. 

Here's a thought...if your planning on exporting to excel...why not skip the whole exporting process and retrieve the Visual data straight from excel!

...hmmm sounds like an interesting project


Rich Ferlatte
Results Driven VISUAL Specialist


Of course, I am available for hire!

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


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


BOM Xploder
Drastically improve your BOM visibility and discover shortages at ANY level of your bill of material no matter how deep.
See, analyze, and track information that helps you better support individual, departmental, and your company’s performance metrics and goals.
Credit Card Integration
Easily accept and process credit card payments right from within VISUAL.
CAD Integration with CADLink
Reduce rework, minimize scrap, and work more efficiently with CADLink - a fully automated, two-way connection between VISUAL and your CAD system.
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.
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.