AugForums.com

An Acumatica User Group

Acumatica Forums

By using this website, you agree to our Terms of Use (click here)

Forums
AUG Forums
Acumatica Analytica...
Gross Sales by Stat...
 
Notifications
Clear all

Questions Gross Sales by State

 
Acumatica Analytical Report Manager (Financial Report Writer)
Last Post by Tim Rodman 4 years ago
6 Posts
3 Users
0 Reactions
2,947 Views
RSS
johnaslipp
Posts: 4
 johnaslipp
Topic starter
February 22, 2018 6:28 pm
(@johnaslipp)
Active Member
Joined: 8 years ago

I need some assistance creating a report that puts Gross Sales (by user determined time period) into buckets by State please.


5 Replies
Tim Rodman
Posts: 3193
 Tim Rodman
Admin
February 22, 2018 11:06 pm
(@timrodman)
Famed Member
Joined: 10 years ago

Can you mock it up with some sample data in Excel and post a screenshot of what it should look like with different user determined time periods?


Reply
johnaslipp
Posts: 4
 johnaslipp
Topic starter
February 23, 2018 11:29 am
(@johnaslipp)
Active Member
Joined: 8 years ago

Hi Tim,

 

The first thing I would need to be sure of is that the total of the report matches the P&L:

ABC Company      
Profit & Loss      
As of   February 28, 2018        
  YTD      
         
Sales      
  Product Sales  $                                                         200.00      
  Fastback Machine Rental Income  $                                                            50.00      
  Technical Service Labor  $                                                            45.00      
  Maintenance Agreement Sales  $                                                         100.00      
  Warranty Sales  $                                                            50.00      
  Rebate / Commissions / Refunds  $                                                            10.00      
           
Total Sales  $                                                         455.00      
           
           
ABC Company
Sales by State
As of   February 28, 2018        
  State Zip      
  AK 9xxxx  $           25.00    
    9xxxx  $           35.00    
    9xxxx  $           40.00  $        100.00  
  WA 9xxxx  $           25.00    
    9xxxx  $           25.00    
    9xxxx  $           50.00    
    9xxxx  $           25.00    
    9xxxx  $           75.00  $        200.00  
  CA 9xxxx  $           25.00    
    9xxxx  $           25.00    
    9xxxx  $           25.00    
    9xxxx  $           25.00  $        100.00  
  Other (not USA States)      $           55.00  
         $        455.00  

I would then like to be able to enter a user-defined time period, Start Date and End Date, to extract the data.  We are located in Oregon and we have to be able to report on sales for various districts for Tax purposes at year-end.

 

John


Reply
Tim Rodman
Posts: 3193
 Tim Rodman
Admin
February 23, 2018 11:24 pm
(@timrodman)
Famed Member
Joined: 10 years ago

Ah, I see. The Analytical Report Manager allows you to report on General Ledger or Project Ledger data only. And you can only report in Financial Period buckets, not down to individual days.

I would suggest building a Generic Inquiry based on the ARTran table. That would allow you to join up to the information on the Invoice to get the State, Zip, etc. It would also allow you to grab the date from the invoice.

This should agree to the General Ledger as long as the invoice dates all fall in their default financial period and as long as all of the entries to the sales GL Accounts are flowing through the Accounts Receivable module.


Reply
busyfritz
Posts: 16
 busyfritz
May 13, 2021 5:55 pm
(@busyfritz)
Eminent Member
Joined: 5 years ago

I had the same need as the original poster, so I created a GI for it. It works well with one exception: To change the date you have to edit the GI.

I couldn't figure out how to set up a parameter or filter that filters the underlying query using a BETWEEN clause. I came across this article but it didn't help much: https://www.augforums.com/forums/acumatica-generic-inquiries/using-parameters-to-filter-aggregate-results/

@timrodman if you can figure out how to do dynamic date filtering it would be much appreciated.

So anyway-here's the GI-Transactions by Region(I avoided using state because we are in CA as well):

It supports grouping by any account class
It's in the Finance workspace.
It is based on GLTran joined to AR Tran so that it will match GL perfectly.
Sorted first by Branch, Country, Region, then by account
Shows total debits and credits and sums them both ways

Things that could be improved:
Default to the branch country and state if no country or state

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<data-set>
  <relations format-version="3" relations-version="20201014" main-table="GIDesign" stable-sharing="True" file-name="(Name)">
    <link from="GIFilter (DesignID)" to="GIDesign (DesignID)" />
    <link from="GIGroupBy (DesignID)" to="GIDesign (DesignID)" />
    <link from="GIMassAction (DesignID)" to="GIDesign (DesignID)" />
    <link from="GIMassUpdateField (DesignID)" to="GIDesign (DesignID)" />
    <link from="GINavigationScreen (DesignID)" to="GIDesign (DesignID)" />
    <link from="GINavigationParameter (DesignID, NavigationScreenLineNbr)" to="GINavigationScreen (DesignID, LineNbr)" />
    <link from="GIOn (DesignID, RelationNbr)" to="GIRelation (DesignID, LineNbr)" />
    <link from="GIRecordDefault (DesignID)" to="GIDesign (DesignID)" />
    <link from="GIRelation (DesignID, ParentTable)" to="GITable (DesignID, Alias)" />
    <link from="GIRelation (DesignID, ChildTable)" to="GITable (DesignID, Alias)" />
    <link from="GIResult (DesignID)" to="GIDesign (DesignID)" />
    <link from="GIResult (ObjectName, DesignID)" to="GITable (Alias, DesignID)" />
    <link from="GISort (DesignID)" to="GIDesign (DesignID)" />
    <link from="GITable (DesignID)" to="GIDesign (DesignID)" />
    <link from="GIWhere (DesignID)" to="GIDesign (DesignID)" />
    <link from="SiteMap (Url)" to="GIDesign (DesignID)" type="WeakByUrl" linkname="toDesignById" baseurl="~/GenericInquiry/GenericInquiry.aspx" paramnames="id" />
    <link from="SiteMap (Url)" to="GIDesign (Name)" type="WeakByUrl" linkname="toDesignByName" baseurl="~/GenericInquiry/GenericInquiry.aspx" />
    <link from="ListEntryPoint (ListScreenID)" to="SiteMap (ScreenID)" />
    <link from="SiteMap (ScreenID)" to="GIDesign (PrimaryScreenIDNew)" linkname="to1Screen" />
    <link from="FilterHeader (ScreenID)" to="SiteMap (ScreenID)" />
    <link from="FilterRow (FilterID)" to="FilterHeader (FilterID)" />
    <link from="PivotTable (NoteID)" to="FilterHeader (RefNoteID)" />
    <link from="PivotField (ScreenID, PivotTableID)" to="PivotTable (ScreenID, PivotTableID)" />
    <link from="MUIScreen (NodeID)" to="SiteMap (NodeID)" />
    <link from="MUIWorkspace (WorkspaceID)" to="MUIScreen (WorkspaceID)" type="FromMaster" linkname="workspaceToScreen" split-location="yes" updateable="True" />
    <link from="MUISubcategory (SubcategoryID)" to="MUIScreen (SubcategoryID)" type="FromMaster" updateable="True" />
    <link from="MUITile (ScreenID)" to="SiteMap (ScreenID)" />
    <link from="MUIWorkspace (WorkspaceID)" to="MUITile (WorkspaceID)" type="FromMaster" linkname="workspaceToTile" split-location="yes" updateable="True" />
    <link from="MUIArea (AreaID)" to="MUIWorkspace (AreaID)" type="FromMaster" updateable="True" />
    <link from="MUIPinnedScreen (NodeID, WorkspaceID)" to="MUIScreen (NodeID, WorkspaceID)" type="WeakIfEmpty" isEmpty="Username" />
    <link from="MUIFavoriteWorkspace (WorkspaceID)" to="MUIWorkspace (WorkspaceID)" type="WeakIfEmpty" isEmpty="Username" />
    <link from="GIDesign (NoteID)" to="Note (NoteID)" type="Note" />
    <link from="GIFilter (NoteID)" to="Note (NoteID)" type="Note" />
    <link from="GIFilter (NoteID)" to="GIFilterKvExt (RecordID)" type="RowKvExt" />
    <link from="GIGroupBy (NoteID)" to="Note (NoteID)" type="Note" />
    <link from="GIOn (NoteID)" to="Note (NoteID)" type="Note" />
    <link from="GIRelation (NoteID)" to="Note (NoteID)" type="Note" />
    <link from="GIResult (NoteID)" to="Note (NoteID)" type="Note" />
    <link from="GIResult (NoteID)" to="GIResultKvExt (RecordID)" type="RowKvExt" />
    <link from="GISort (NoteID)" to="Note (NoteID)" type="Note" />
    <link from="GITable (NoteID)" to="Note (NoteID)" type="Note" />
    <link from="GIWhere (NoteID)" to="Note (NoteID)" type="Note" />
    <link from="FilterHeader (NoteID)" to="Note (NoteID)" type="Note" />
  </relations>
  <layout>
    <table name="GIDesign">
      <table name="GIFilter" uplink="(DesignID) = (DesignID)">
        <table name="Note" uplink="(NoteID) = (NoteID)" />
        <table name="GIFilterKvExt" uplink="(NoteID) = (RecordID)" />
      </table>
      <table name="GIGroupBy" uplink="(DesignID) = (DesignID)">
        <table name="Note" uplink="(NoteID) = (NoteID)" />
      </table>
      <table name="GIMassAction" uplink="(DesignID) = (DesignID)" />
      <table name="GIMassUpdateField" uplink="(DesignID) = (DesignID)" />
      <table name="GINavigationScreen" uplink="(DesignID) = (DesignID)">
        <table name="GINavigationParameter" uplink="(DesignID, LineNbr) = (DesignID, NavigationScreenLineNbr)" />
      </table>
      <table name="GIRecordDefault" uplink="(DesignID) = (DesignID)" />
      <table name="GISort" uplink="(DesignID) = (DesignID)">
        <table name="Note" uplink="(NoteID) = (NoteID)" />
      </table>
      <table name="GITable" uplink="(DesignID) = (DesignID)">
        <table name="GIRelation" uplink="(DesignID, Alias) = (DesignID, ParentTable)">
          <table name="GIOn" uplink="(DesignID, LineNbr) = (DesignID, RelationNbr)">
            <table name="Note" uplink="(NoteID) = (NoteID)" />
          </table>
          <table name="Note" uplink="(NoteID) = (NoteID)" />
        </table>
        <table name="GIResult" uplink="(Alias, DesignID) = (ObjectName, DesignID)">
          <table name="Note" uplink="(NoteID) = (NoteID)" />
          <table name="GIResultKvExt" uplink="(NoteID) = (RecordID)" />
        </table>
        <table name="Note" uplink="(NoteID) = (NoteID)" />
      </table>
      <table name="GIWhere" uplink="(DesignID) = (DesignID)">
        <table name="Note" uplink="(NoteID) = (NoteID)" />
      </table>
      <table name="SiteMap" uplink="(DesignID) = (Url)" linkname="toDesignById">
        <table name="ListEntryPoint" uplink="(ScreenID) = (ListScreenID)" />
        <table name="FilterHeader" uplink="(ScreenID) = (ScreenID)">
          <table name="FilterRow" uplink="(FilterID) = (FilterID)" />
          <table name="PivotTable" uplink="(RefNoteID) = (NoteID)">
            <table name="PivotField" uplink="(ScreenID, PivotTableID) = (ScreenID, PivotTableID)" />
          </table>
          <table name="Note" uplink="(NoteID) = (NoteID)" />
        </table>
        <table name="MUIScreen" uplink="(NodeID) = (NodeID)">
          <table name="MUIPinnedScreen" uplink="(NodeID, WorkspaceID) = (NodeID, WorkspaceID)" />
        </table>
        <table name="MUITile" uplink="(ScreenID) = (ScreenID)" />
      </table>
      <table name="SiteMap" uplink="(Name) = (Url)" linkname="toDesignByName">
        <table name="ListEntryPoint" uplink="(ScreenID) = (ListScreenID)" />
        <table name="FilterHeader" uplink="(ScreenID) = (ScreenID)">
          <table name="FilterRow" uplink="(FilterID) = (FilterID)" />
          <table name="PivotTable" uplink="(RefNoteID) = (NoteID)">
            <table name="PivotField" uplink="(ScreenID, PivotTableID) = (ScreenID, PivotTableID)" />
          </table>
          <table name="Note" uplink="(NoteID) = (NoteID)" />
        </table>
        <table name="MUIScreen" uplink="(NodeID) = (NodeID)">
          <table name="MUIPinnedScreen" uplink="(NodeID, WorkspaceID) = (NodeID, WorkspaceID)" />
        </table>
        <table name="MUITile" uplink="(ScreenID) = (ScreenID)" />
      </table>
      <table name="SiteMap" uplink="(PrimaryScreenIDNew) = (ScreenID)" linkname="to1Screen">
        <table name="ListEntryPoint" uplink="(ScreenID) = (ListScreenID)" />
        <table name="FilterHeader" uplink="(ScreenID) = (ScreenID)">
          <table name="FilterRow" uplink="(FilterID) = (FilterID)" />
          <table name="PivotTable" uplink="(RefNoteID) = (NoteID)">
            <table name="PivotField" uplink="(ScreenID, PivotTableID) = (ScreenID, PivotTableID)" />
          </table>
          <table name="Note" uplink="(NoteID) = (NoteID)" />
        </table>
        <table name="MUIScreen" uplink="(NodeID) = (NodeID)">
          <table name="MUIPinnedScreen" uplink="(NodeID, WorkspaceID) = (NodeID, WorkspaceID)" />
        </table>
        <table name="MUITile" uplink="(ScreenID) = (ScreenID)" />
      </table>
      <table name="Note" uplink="(NoteID) = (NoteID)" />
    </table>
    <table name="MUIWorkspace">
      <table name="MUIFavoriteWorkspace" uplink="(WorkspaceID) = (WorkspaceID)" />
    </table>
    <table name="MUISubcategory" />
    <table name="MUIArea" />
  </layout>
  <data>
    <GIDesign>
      <row DesignID="0d511f1b-4177-4417-ac85-ef3fc12b027b" Name="Transactions by Region" FilterColCount="3" PageSize="0" ExportTop="0" NewRecordCreationEnabled="0" MassDeleteEnabled="0" AutoConfirmDelete="0" MassRecordsUpdateEnabled="0" MassActionsOnRecordsEnabled="0" ExposeViaOData="0" ExposeViaMobile="0" ShowDeletedRecords="0">
        <GIGroupBy LineNbr="4" IsActive="1" DataFieldName="ARAddress.state" />
        <GIGroupBy LineNbr="3" IsActive="1" DataFieldName="Account.accountID" />
        <GIGroupBy LineNbr="2" IsActive="1" DataFieldName="GLTran.branchID" />
        <GISort LineNbr="4" IsActive="1" DataFieldName="Account.accountID" SortOrder="A" />
        <GISort LineNbr="3" IsActive="1" DataFieldName="ARAddress.state" SortOrder="A" />
        <GISort LineNbr="2" IsActive="1" DataFieldName="ARAddress.countryID" SortOrder="A" />
        <GISort LineNbr="1" IsActive="1" DataFieldName="GLTran.branchID" SortOrder="A" />
        <GITable Alias="GLTran" Name="PX.Objects.GL.GLTran">
          <GIRelation LineNbr="3" ChildTable="ARInvoice" IsActive="1" JoinType="L">
            <GIOn LineNbr="4" ParentField="refNbr" Condition="E " ChildField="refNbr" Operation="A" />
          </GIRelation>
          <GIResult LineNbr="13" SortOrder="1" IsActive="1" Field="branchID" Caption="Branch" IsVisible="1" DefaultNav="1" QuickFilter="0" FastFilter="1" RowID="fe7e8a3f-60e3-4405-afa9-01c1998e3c20" />
          <GIResult LineNbr="12" SortOrder="9" IsActive="1" Field="=(([GLTran.DebitAmt]*-1)+[GLTran.CreditAmt])" SchemaField="ARInvoice.CuryDocBal" Caption="Total" IsVisible="1" DefaultNav="1" AggregateFunction="SUM" TotalAggregateFunction="SUM" QuickFilter="0" FastFilter="1" RowID="59904a2b-9304-4cce-adfa-4a6ff74d17b5" />
          <GIResult LineNbr="5" SortOrder="4" IsActive="1" Field="accountID_description" Caption="Account Desc" IsVisible="1" DefaultNav="1" QuickFilter="0" FastFilter="1" RowID="708e3708-0e02-4532-9ea7-a42ec2f506fd" />
          <GIResult LineNbr="3" SortOrder="3" IsActive="1" Field="accountID" Caption="Account" IsVisible="1" DefaultNav="1" QuickFilter="0" FastFilter="1" RowID="abf1fd45-2b6e-4262-a1ec-3db43736c76e">
            <Note NoteText="" GraphType="PX.Data.Maintenance.GI.GenericInquiryDesigner" EntityType="PX.Data.Maintenance.GI.GIResult" />
          </GIResult>
          <GIResult LineNbr="2" SortOrder="6" IsActive="1" Field="creditAmt" SchemaField="GLTran.CreditAmt" Caption="Credit Amount" IsVisible="1" DefaultNav="1" QuickFilter="0" FastFilter="1" RowID="7bad761f-4368-40aa-91ef-79374aba8693" />
          <GIResult LineNbr="1" SortOrder="5" IsActive="1" Field="debitAmt" SchemaField="GLTran.DebitAmt" Caption="Debit Amount" IsVisible="1" DefaultNav="1" QuickFilter="0" FastFilter="1" RowID="8b7b9405-1324-4d14-a73d-610c63f9aad8" />
        </GITable>
        <GITable Alias="ARInvoice" Name="PX.Objects.AR.ARInvoice">
          <GIRelation LineNbr="4" ChildTable="ARAddress" IsActive="1" JoinType="L">
            <GIOn LineNbr="3" ParentField="shipAddressID" Condition="E " ChildField="addressID" Operation="A" />
          </GIRelation>
        </GITable>
        <GITable Alias="ARAddress" Name="PX.Objects.AR.ARAddress">
          <GIResult LineNbr="16" SortOrder="8" IsActive="1" Field="state" Caption="AR State" IsVisible="1" DefaultNav="1" QuickFilter="0" FastFilter="1" RowID="b7d5adfc-02bf-463f-96bf-e47d5a746d1e" />
          <GIResult LineNbr="10" SortOrder="7" IsActive="1" Field="countryID" Caption="Country" IsVisible="1" DefaultNav="1" QuickFilter="0" FastFilter="1" RowID="7f240561-2db2-40f6-9b3e-ef2938a4a43b" />
        </GITable>
        <GITable Alias="Account" Name="PX.Objects.GL.Account">
          <GIRelation LineNbr="1" ChildTable="GLTran" IsActive="1" JoinType="I">
            <GIOn LineNbr="1" ParentField="accountID" Condition="E " ChildField="accountID" Operation="A" />
          </GIRelation>
          <GIResult LineNbr="14" SortOrder="2" IsActive="1" Field="accountClassID" Caption="Account Class" IsVisible="1" DefaultNav="1" QuickFilter="0" FastFilter="1" RowID="cbbd3578-dfed-462b-8609-ec8ab80a28bd" />
        </GITable>
        <GIWhere LineNbr="8" IsActive="1" DataFieldName="GLTran.finPeriodID" Condition="LE" IsExpression="1" Value1="122020" Operation="A" />
        <GIWhere LineNbr="7" IsActive="1" DataFieldName="GLTran.finPeriodID" Condition="GE" IsExpression="1" Value1="012020" Operation="A" />
        <SiteMap linkname="toDesignById">
          <row Title="Transactions by Region" Url="~/genericinquiry/genericinquiry.aspx?id=0d511f1b-4177-4417-ac85-ef3fc12b027b" ScreenID="GI000095" NodeID="0913d2f5-397a-4db4-b7c5-ca26760908a6" ParentID="00000000-0000-0000-0000-000000000000">
            <MUIScreen IsPortal="0" WorkspaceID="b5ec7b62-d2e5-4234-999d-0c92a0b0b74d" Order="60" SubcategoryID="98e86774-69e3-41ea-b94f-eb2c7a8426d4">
              <MUIPinnedScreen IsPortal="0" Username="" IsPinned="1" />
            </MUIScreen>
          </row>
        </SiteMap>
      </row>
    </GIDesign>
    <MUIWorkspace>
      <row IsPortal="0" WorkspaceID="b5ec7b62-d2e5-4234-999d-0c92a0b0b74d" Order="104" Title="Finance" Icon="balance-scale" AreaID="338edf81-5456-4735-8601-b213458e93fe" ScreenID="WSGL0000" IsSystem="0">
        <MUIFavoriteWorkspace IsPortal="0" Username="" IsFavorite="1" />
      </row>
    </MUIWorkspace>
    <MUISubcategory>
      <row IsPortal="1" SubcategoryID="98e86774-69e3-41ea-b94f-eb2c7a8426d4" Order="896" Name="Inquiries" Icon="" IsSystem="1" />
      <row IsPortal="0" SubcategoryID="98e86774-69e3-41ea-b94f-eb2c7a8426d4" Order="896" Name="Inquiries" Icon="" IsSystem="1" />
    </MUISubcategory>
    <MUIArea>
      <row IsPortal="0" AreaID="338edf81-5456-4735-8601-b213458e93fe" Order="10" Name="Financials" />
    </MUIArea>
  </data>
</data-set>

Reply
Tim Rodman
Posts: 3193
 Tim Rodman
Admin
August 12, 2021 9:49 pm
(@timrodman)
Famed Member
Joined: 10 years ago

Does this get you what you're looking for?

image
image

Reply
Forum Jump:
  Previous Topic
Next Topic  

Online Members

 No online members at the moment

Acumatica Forums

Terms of Use & Disclaimers :: Privacy Policy

Copyright © 2025 · AUG Forums, LLC. All rights reserved. This website is not owned, affiliated with, or endorsed by Acumatica, Inc.