• Pass through variable "Last full month"

    What is the sql statement needed to get the last full month for a pass-through variable. Scenario is: On the 10th of the month, produce a GL detail report for all activity posted in the prior month. As always thanks in advance for your assistance…
  • Query data from IM_PeriodPostingHistory

    Hello, I'm trying to export the history tab of Item Inquiry window (see above) to excel sheet for reporting purpose. I created query which pulls data from the table of IM_PeriodPostingHistory. However, the beginning quantity of each month shows 0…
  • Restricting a column on reporting only a specific date instead of an entire period

    I'm hitting a wall. My client wants a column in Sage Intelligence to pull only total of net activity for a specific day instead of a period. It has to do with accruals. They don't want to enter accrual journal entries. They want to keep the expenses dated…
  • Sage 100 2019 Sage Intelligence. Company name or code for multi-company reports.

    Sage 100 2019 Sage Intelligence. How do I add company code from the SYO_Company table to my joins? In Crystal we would use "Not Equal" as a join for company name. In SI, Connector, there is only Right Join, Inner, and Outer. How do I get the company name…
  • Payroll Data Missing in Microsoft Query Sage Intelligence 100

    I am pulling from the table used in the PR_EarningsReport but not all fields are being populated in my query. For example, I can get Employee Number but not First and Last Name?
  • Is there a way to pull data from a specific source code, like AP or AR?

    We are looking for a way to separate our YE AJE's. It was suggested to us to use a source code named AJ. We'd like to create a set of financials that shows the unadjusted trial balance, then the AJE's, and then the final balances. So, my question is…
  • Reporting Tree Account Filter Rules

    I have created a Reporting Tree for a client where the nodes are cities. For one of the cities, the account filter rule needs to include three filters: ???-???-000 OR ???-???-100 OR ???-???-600 I can only get two of the rules to work. What am I…
  • UNION Query SAGE 100 (MAS90)

    Does the Connector support UNION Queries for Sage 100 Data? Have a simple UNION that just doesn't seem to work. See sample below: Select JT_Transaction.SalesOrderNo as 'SalesOrderNo' , JT_Transaction.TotalCost+JT_Transaction.TotalLaborCost…
  • SQL Query for Operating Income?

    Sage 100 Premium 2014 User - We are looking at building a dashboard with Tableau and one of the things we'd like to see is operating income at a monthly/weekly/daily level. Has anyone built SQL queries with the Sage database to show Operating Income…
  • How to Query the Month or Date an Invoice was paid in full.

    Hi, Maybe anyone has a good approach for this... I want to query the Sage 100 AR Tables to list me the Invoice with the month (or date) the Open Balance became "0" or went negative. Thanks for your help! Best, Bastian
  • Adding a "SQL Expression" to a container in Sage Intelligence

    The following SQL Query pulls the information exactly how I want it running it outside of Sage Intelligence: SELECT AR_SALESPERSON.SalesPersonNo, AR_SALESPERSON.SalesPersonName, AR_SALESPERSON.salesmanagerno AS manageNo, salesmanage.salespersonname…