Open Forum

Expand all | Collapse all

Smartlist Builder Calculations

  • 1.  Smartlist Builder Calculations

    Posted Apr 24, 2020 12:11 PM
    ​​I'm trying to come up with a Smartlist that will sum all of our paycodes that make up our Regular wages, another column for OT wages, and a third for all our Bonus paycodes.  I am not well-versed in SQL, but I managed to pivot the data in the UPR30300 table to display one column per PayCode and create an SQL View for it.  I used Smartlist Builder and tried to use the Add Calculation to sum up multiple columns, but the result is always zero.  Any suggestions?
    Sum all
    Smartlist Result


    ------------------------------
    Gina Behee
    Director of Accounting
    Pharmacy First
    Shawnee Mission KS
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Smartlist Builder Calculations

    TOP CONTRIBUTOR
    Posted Apr 24, 2020 12:14 PM
    HI Gina,
    You smartlist is now based in a view?  is it possible to take a look at the view?

    ------------------------------
    Gerald Buenafe
    Owner/Consultant
    BTP Technologies LLC
    CHANTILLY VA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Smartlist Builder Calculations

    SILVER CONTRIBUTOR
    Posted Apr 25, 2020 11:05 AM
    Hi @Gina Behee,

    I've found that when I get zero results (or zero's for a particular column) in a SmartList based on a view, it's an issue with how my SQL is being executed by SmartList. I was just working on one yesterday that was giving me this issue. I ran a dexsql log and found that one of the column names was too long and was being truncated when SmartList attempted to call the view.


    If you can share your code with us, be happy to take a look.​

    ------------------------------
    Kelly Marinoff
    Keel & Company, LLC
    Virginia Beach VA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Smartlist Builder Calculations

    GPUG ALL STAR
    Posted Apr 27, 2020 11:08 AM
    I am not sure what you have in SQL for this, but based on what you said you are trying to get, here is a sample of how would do it in SmartList Builder.

    I would first add the Payroll Transaction History table (UPR30300) to a new list as shown below.


    Then I would create 3 calculations for the Regular, Overtime, and Bonus fields you want.  The calculation will decide which of the 3 columns the wage belongs in for each record.  I have included a sample here on how the Regular one might work.  Your codes will probably be different, but gives you an idea of how it should look.  The other two would be the same, just different codes.


    Once you have the data set at each record, then we can make the list a summary list.  I grouped it by the Employee and Year below, but you can group by whatever fields you are looking to do it by.  By grouping by Employee and Year, I get one record per employee per year with the calculated fields being sums of all the records that fall in that grouping.


    Here is what it gives me as a sample of Fabrikam data.

    Hopefully that helps, but let us know what you are doing in SQL and we can try to figure that one out too if you want.

    ------------------------------
    Nicole Albertson
    Product Manager
    eOne Solutions
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: Smartlist Builder Calculations

    Posted Apr 27, 2020 12:03 PM
    This is exactly what I needed!  Thank you so much!!

    ------------------------------
    Gina Behee
    Director of Accounting
    Pharmacy First
    Shawnee Mission KS
    ------------------------------

    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role