PANTHEON™ Help

 Toc
 PANTHEON Help - Welcome
[Collapse]PANTHEON
 [Collapse]Guides for PANTHEON
  [Collapse]Guide for PANTHEON
   [Collapse]Settings
    [Expand]Subjects
    [Expand]Items
    [Expand]POS
    [Expand]Manufacturing
    [Collapse]Personnel
     [Expand]Employment
     [Expand]Time Terminal
     [Expand]Leave
     [Expand]Daily Allowances and Mileage Rates
     [Collapse]Payroll
      [Expand]Average Salaries
      [Expand]Contributions
      [Expand]Salary Grades
       Income Sources
      [Expand]Income and Payroll Tax Rates
      [Expand]Payment Order Codes
      [Expand]Work Types
      [Collapse]Earnings Types
        Earning Types by Department
       [Expand]Taskbar
        Transfer Registers – Earnings Types
        Edit several earnings types with related criteria
        Formulas for Predefined Earnings Types
        Predefined Earnings Types
       [Collapse]Formula Builder
         Program Parameters
         Average Salaries
         Administration panel
         Payroll Preparation
         Variables for Contributions
         Single Employee
         Employment Record
         Position
         Grade
         Periodic Rating
         Factor Net
         Cost Centers
         GE Variables
         Subjects
         Rounding
         Finished Work Analysis
         Fixed Assets
         Date variables
         Using SQL Statement in Formula
       Additional General Relief
     [Expand]Health and Safety at Work
     [Expand]Messages in Personnel Settings
      Work Processes
     Calendar
    [Expand]Financials
    [Expand]Customs
    [Expand]Program
    [Expand]Documentation
    [Expand]Change User
   [Expand]Orders
   [Expand]Goods
   [Expand]Manufacturing
   [Expand]POS
   [Expand]Service
   [Expand]Financials
   [Expand]Personnel
   [Expand]Analytics
   [Expand]Desktop
   [Expand]Help
   [Expand]Messages and Warnings
   [Expand]Additional programs
   [Expand]Old products
  [Expand]Guide for PANTHEON Retail
  [Expand]Guide for PANTHEON Vet
  [Expand]Guide for PANTHEON Farming
 [Collapse]User Manuals for PANTHEON
  [Expand]User Manual for PANTHEON
  [Expand]User Manual for PANTHEON Retail
  [Expand]User manual for PANTHEON Vet
  [Expand]User Manual for PANTHEON Farming
[Collapse]PANTHEON Web
 [Collapse]Guides for PANTHEON Web
  [Expand]Guide for PANTHEON Web Light
  [Expand]Guide for PANTHEON Web Terminal
  [Expand]Guide for PANTHEON Web Legal
  [Expand]Old products Archive
 [Collapse]User Manuals for PANTHEON Web
  [Expand]Getting started PANTHEON Web
  [Expand]User Manual for PANTHEON Web Light
  [Expand]User Manual for PANTHEON Web Terminal
  [Expand]User Manual for PANTHEON Web Legal
  [Expand]Old products Archive
[Collapse]PANTHEON Granules
 [Collapse]Guides for PANTHEON Granules
  [Expand]Personnel Granule
  [Expand]Travel Orders Granule
  [Expand]Documents and Tasks Granule
  [Expand]Dashboard Granule
  [Expand]B2B Orders Granule
  [Expand]Field Service Granule
  [Expand]Fixed Assets Inventory Granule
  [Expand]Warehouse Inventory Granule
 [Collapse]User Manuals for PANTHEON Granules
  [Expand]Getting started
  [Expand]Personnel Granule
  [Expand]Travel Orders Granule
  [Expand]Documents and Tasks Granule
  [Expand]B2B Orders Granule
  [Expand]Dashboard Granule
  [Expand]Field Service Granule
  [Expand]Fixed Assets Inventory Granule
  [Expand]Warehouse Inventory Granule
  [Expand]Archive
[Expand]User Site

Load Time: 685.7456 ms
"
   9151 | 9699 | 340190 | Updated
Label

Using SQL Statement in Formula

Using SQL expression in Formula

010381.gif010411.gif010382.gif010383.gif

Use an SQL expression to create a formula. See below on how to do this:

  • an SQL expression begins and ends with a delimiter ~S~
  • a formula can contain any number of SQL expressions
  • an SQL expression cannot contain logical conditions ([..:..]..)
  • an SQL expression cannot contain temp tables with with #
  • an SQL expression can contain variables (# ... #)
  • Logical conditions can contain SQL expressions.

 

You can use any mathematical function available in your version of SQL Server when creating an SQL expression. For example:

  • SIN( float_expression )
    Returns the trigonometric sine of the given angle (in radians) in an approximate numeric (float) expression,

  • COS ( float_expression )
    A mathematic function that returns the trigonometric cosine of the given angle (in radians) in the given expression,

  • TAN ( float_expression )
    Returns the tangent of the input expression,

  • SQRT ( float_expression )
    Returns the square root of the given expression,

  • For all other mathematical functions visit: http://msdn.microsoft.com/en-us/library/ms174077.aspx.

 


 

Formula including a condition such as for example: [#KOLICINA#>20:#Cena#*0,9] [#KOLICINA#>10:#Cena#*0,95] #Cena#

can be created using the following SQL expression:

~S~
case
  when #KOLICINA# > 20 then #Cena# * 0.9
  when #KOLICINA# > 10 then #Cena# * 0,95
  else #Cena#
end
~S~

or


~S~ dbo.MojaFunkcija(#KOLICINA#, #Cena#) ~S~

In this case it is required that you have a customized function dbo.MojaFunkcija().

 

79176.gif For more information on how to create a commute formula using an SQL expression see:Create Formula Using an SQL Expression
000001.gif You can test formulas which do not include SQL expression in the Test panel, since SQL expressions cannot be tested in formula builder.
 

 

 

 


 

Rate this topic
Was this topic usefull?
Comments
Comment will also bo visible in forum!