x Want to participate? Join the group to interact
Options
  • State Suggested Answer
  • Replies 3 replies
  • Answers 1 answer
  • Subscribers 105 subscribers
  • Views 2965 views
  • Users 0 members are here
Browse Forums
Announcements
293
topics
370
replies
Business Object Interface
1k
topics
7k
replies
Core Financial Modules
2k
topics
8k
replies
Customization and Productivity
3k
topics
12k
replies
Distribution, Manufacturing, and Applications
2k
topics
7k
replies
General Discussion
1k
topics
3k
replies
Partner Cloud
45
topics
92
replies
Payroll, Time Tracking, and Job Cost
777
topics
1k
replies
Reporting and Analytics
962
topics
3k
replies
Sage CRM for Sage 100
164
topics
357
replies
Technical and Installation
2k
topics
9k
replies
Year-end Forum
89
topics
237
replies
wakeet

Previous Month - Rolling, to be used for automated report delivery

SUGGESTED
Posted By

Currently using Sage 100 2013 and Sage Intelligence 5.0.

We have a need to pull previous month transactions from AR_InvoiceHistoryHeader.  Would like to filter this data based on the ShipDate.  We would also like these report so be distributed automatically on a monthly basis.  Therefore, our approach has mostly been geared towards filtering the container by previous month data so only previous month transactions are pulled into the report.

-Using SQL Query Source Container Types with WHERE clauses to filter by previous month

-Adding SQL Expression Fields to identify Ship Dates in the previous month

-many variants of the above to approaches

I can't seem to find a solution anywhere in the documentation or forums for previous month transactions.  The System Variables don't include Previous Month.  What am I missing?!?

Thank you very much for your help.

  • 0

    This would be a snap with Sage Alerts and Workflow aka Knowledge Sync.

  • 0 in reply to BigLouie

    We are heavily invested in KnowledgeSync and love it's power.  One thing i failed to mention is we are using SI for its ability to consolidate data from multiple company codes into one container.  This specific report in question needs to be run for multiple company codes so we are using the Consolidation Container of BI.

  • 0 in reply to wakeet
    SUGGESTED

    This appears to work when used in the WHERE statement of SQL Query container type:

    (DATEPART(mm, "AR_InvoiceHistoryHeader"."ShipDate") = datepart(mm,dateadd(mm,-1,getdate()))) AND (DATEPART(yy, "AR_InvoiceHistoryHeader"."ShipDate") = datepart(yy,dateadd(mm,-1,getdate())))

    Essentially, this query looks at the current date and breaks it into two parts:

    Previous Month (Integer)

    -Takes the current date and uses the DateAdd function to subtract one month. returns the month number. i.e. current date = 1/1/2015 returns 12. current date = 3/30/2015 returns 2.

    Previous Month Year (Integer)

    -Takes the current date and uses the DateAdd function to subtract one month. returns the year number. i.e. current date = 1/1/2015 returns 2014. current date = 3/30/2015 returns 2015.

    The query only selects the records where the ShipDate month and year equal the Previous Month and Previous Month Year.

*Community Hub is the new name for Sage City