Reporting

Written by Richard 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 Richard 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 Richard 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:

  • 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.

Written by Richard 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 Richard 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 Richard 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
    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

 

 

 

Written by Richard 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 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 
 

Written by Richard 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)

Totals


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
     
     
    PlasticBagCharge
     
     
  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. 
     
     
    NewTotals


     
  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
     
    NewTotals2

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


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


Written by Richard on March 3, 2009

Setting up SQL Views to access binary fields

In a previous post, I mentioned that Visual stores specifications and notations in a binary format.  In order for this data to be readable, they must first be converted using one of these formulas

What I've done to facilitate this process was create SQL Server Views for most of the "_BINARY" tables.  These views process the binary fields into a readable format and make it available as such.  By doing this there is no need to convert the data on the report (or query) itself.

To create a view for line specifications for customer orders:

  1. Open SQL Server Management Studio and drill down to the Views of the database.
     
  2. Right click on the Views folder and select "New View...".
     
    View1

     
  3. Select the CUST_LINE_BINARY table from the Add Table window and then click the close button.
     
    View2

  4. Place a check mark for ROWID, CUST_ORDER_ID, CUST_ORDER_LINE_NO and TYPE (as indicated in image below). Then add the following in the last row (of the grid) that converts the binary field to readable text:

    CAST(CAST(BITS AS varbinary(128)) AS VARCHAR(128))
     
  5. View3

  6. Click on the Save button to save the View to the database


You can now access this view from any data connection and report application (including centura reports).

Follow these simple steps above to create additional views for the remaining binary fields in Visual. 

To get a jump start, you can download this file.  It contains a script that will create 16 views that converts the various binary fields to readable text.

As always, please run any scripts on a copy of your database and validate it before implementing on your production database

Download the file and run it in the query window of the SQL Management Studio console (for the proper database):

File: SetupBinaryViews (zipped)

Script will create the following views:

Customer Order Specifications
zv_CUST_ORDER_SPEC (header)
zv_CUST_LINE_SPEC (line)

Packlist specifications
zv_PACK_SPEC (header)
zv_PACK_LINE_SPEC (line)

InterBranch Transfer specifications
zv_IBT_SPEC (header)
zv_IBT_LINE_SPEC (line)

NOTATION table
zv_VIEW_NOTATION

PART table
zv_PART_SPEC

Purchase Order specifications
zv_PURC_ORDER_SPEC (header)
zv_PURC_LINE_SPEC (line)

Invoice Specifications
zv_RECEIVABLE_SPEC (header)
zv_RECV_LINE_SPEC (line)

Work Order Specifications
zv_WORKORDER_SPEC (header)
zv_OPERATION_SPEC
zv_OPER_TYPE_SPEC
zv_REQUIREMENT_SPEC

WHO IS VISUALNUGGETS?

Richard Ferlatte
Results Driven VISUAL Specialist

VISUAL WEBINARS - Updated!

Check out our 50+ Training Webinars focusing on the various VISUAL modules and concepts. There's sure to be one to interest you!
24 Manufacturing Webinars
15 Quality Webinars
13 Financial Webinars

VISUAL EXTENSIONS

Extend the functionality of your VISUAL ERP system with these proven and supported VISUAL apps
Go Paperless App
Integration of the Go Paperless App solution and VISUAL results in Paperless ERP that provides a way for VISUAL users to increase efficiencies and thereby, the cash conversion cycle.
Inventory Management Tool
Provides you the ultimate freedom to quickly filter data, drill-down and track inventory/work in process discrepancies, easily reconcile actual inventory/wip to financial records for current or past periods.
eBoard – Financial Dashboard
A powerful Financial Dashboard allowing you to access live Financial data, drill down to supporting details and export the financial statements to Microsoft Excel in one click.
VE Mobile
The premier mobile extension to the VISUAL, adding flexibility and productivity improvements through the introduction of mobile scanning technology to the warehouse.
Logicity™ Automatic Invoicing
Automatically generate e-mailed or printed invoices out of VISUAL
EDI Integrator
A powerful alternative to the VMDI interface for EDI transactions (inbound and outbound).
Web Store Integration
e-Commerce solution integrating the web to your VISUAL system.