Spreadsheet (.csv) contract invoice showing details by equipment
Overview | Samples | Variables | Alert Functionality | OnDemand Report Filters | Best Practices & Tips | Related Alerts
Overview
Overview
Some of your customers have a need to receive a spreadsheet breakdown of each contract invoice that shows the location/address/city/state/model of the equipment with the clicks/charges for each machine so they can charge back their internal departments accordingly.
Run Schedule: Daily
Type of Output: Email and On Demand SSRS Report
* * *
Sample
Sample
* * *
Variables
Variables
VariableW: Custom Property on Contract Record that is set to 'Enabled" in order to trigger an invoice in .xls format
VariableX: NO LONGER USED used as of 2021.07.08 revision - This is the Configuration name you use on your equipment records containing the Custom Properties with Cost Center info, defaults to 'Equipment
Variable1 - Variable4: - Optional - Custom Property on Equipment Record containing Cost Center Number as reference for your customer. This is intended to be used as Cost Center(s) you customer needs on the invoice.
*Please note, if a machine has more than one Cost Center/Custom Property value, all will show together in same cell on excel spreadsheet. They will not appear under 'each' cost center individually. For example, a machine that has Cost Centers /Custom Properties 123 and 456, will displace as Cost Center 123, 456 on the report and would not appear under the individual cost centers 123 or 456 (The report, nor eAutomate can allocate the equipment$$ across multiple cost centers.)
* * *
Alert Functionality
Alert Functionality
1. This alert will automatically install a SSRS Report so you can create spreadsheet manually for your customer (see below) OR you can set on Contract Record to auto create .xls file each time it bills.
2. This alert will not work if you bill multiple BW or Color meters on the same machine.
3. Sends a single csv file (to the contact on the contract record) that can be imported into excel and reads information from the contract invoice billing tables. It uses the same distributed check box that the invoice print routine provides to allocate the base/overage charges across the equipment by color according to the % of clicks each machine ran against the meter group totals. Since we are using that same logic this report should match the eAutomate invoice printout.
4. The auto create functionality cannot be used for contracts that bill Base to Leasing company and Overages to Customer. The spreadsheet for the Overage billing will need to be manually created via the SSRS Report and sent to the customer. If contract set to auto create spreadsheet for these types of contracts, it will create and send two files to the contact on the contract record, one for the base charges and one for overage charges.
5. Our process does not prevent the eAuto invoice from sending. We suggest to either change the send method of invoices for that customer to print or change the AR contact to the in house person who handles the contracts. Most of our client send the eAuto Invoice along with the spreadsheet.
6. Emailed version of invoice (not On Demand SSRS Report) does not provide control of grouping. It will automatically group by the Custom Properties on your subscription. If no Custom Properties, then Cost Center will be blank and there will be no grouping.
7. We will set this alert up in test mode first as it is a customer facing alert. While in test mode, alert will only send to any email(s) listed on the to/cc/bcc of your alert subscription. You can (and need to) safely enter actual Contact on Contract Record as alert not wired to consider their email while in Test Mode. While in test mode, however; you MUST still have a Contact listed on the Contract Record in order for alert to send samples.
Once you've reviewed sample emails and feel comfortable with the alert's functionality, then you can let us know at help@ceojuice.com you're ready to go live. At that time, we suggest removing emails from to/cc as their email will show on emails sent to your customers and those emails will likely be bombarded with emails.
8. Two areas you MUST populate on Contract Record in order for alert to automatically generate the .csv file (even while in Test Mode):
a. Contact on Contract Record
b. Custom Property ZCJAlert412SendBillingDetail (per VaraibleW) set to Enabled:
Custom Property Use
Cost Center/Custom Property Use
You have up to four Custom Properties via your Variables 1, 2, 3, and 4 to provide Cost Center info on the spreadsheet invoice. You MUST populate those Custom Properties in your variables in order for these to show on the automated email sending the invoice:
**Custom Properties are NOT a grouping or filter function.**
-If you use more than one Custom Property, then the Cost Center Column combines ALL content (separated with comma). For example:
-Emailed version of invoice (not On Demand SSRS Report) does not provide control of grouping. It will automatically group by the Custom Properties on your subscription. If no Custom Properties, then Cost Center will be blank and there will be no grouping.
Columns
Column Explanation
Location Remarks/Location Name/Address/City/State:
All from Equipment Record:
Contact Name/Phone/Email:
Contact is pulled from Equipment Record and will populate Meter reading contact, if that is blank, then will populate the Equipment contact, if neither present, then spreadsheet invoice will be blank.
Orig Date / Maturity Date:
Start /End Date per Contract Record
Install Date:
Per Equipment Record
Coverage From / To Date:
Coverage period per Contract Invoice
BW Base Clicks AND Color Base Clicks:
This is a calculation done by eAuto (not CEO Juice). Reflects clicks by device as % proportionately distibuted as part of entire allowance.
BW Overage Clicks AND Color Overage Clicks:
Actual overage clicks, if any, per contract invoice.
Beg / End Meter Display (BW and Color):
Per Contract Invoice
Overage Rate (BW and Color):
Per Contract Invoice
Tax Code / Tax Rate:
Shows code and rate based per Tax code listed at Equipment Level, if none at Equipment Level,
then shows from Bill To tab:
Equipment Level:
Contract Bill To Level:
Lease Number: / Contract Lease Amount:
Per Service Lease Record
Contract Misc Charge:
Found on Contract Record / Contract Equipment Detail / Miscellaneous
OR
Contract Record / Miscellaneous
Contract Amount:
Total amount by device, before tax. Excludes lease amounts.
* * *
OnDemand Report Filters
OnDemand Report Filters
Group By Level1 and Level2:
This will set .csv file to first group your selection set in Group By Level1 (i.e. LocationAddress). Then set Group by Level2 for secondary grouping (LocationRemarks).
CostCenter1, 2 , 3 and 4 are option if you are using Custom Properties in Variables1 - 4 to show more details as needed for your customer.
Cost Center field will ALWAYS be Custom Property, regardless of other filters. This is just a column on the spreadsheet, not a filter. If no Cost Center / Custom Property exists, then Cost Center column populates with Equipment Record Location Address, if none, then will be Location Remarks:
When using On Demand Report, please be sure to export to Excel (not other formats) for best printing and/or editing:
* * *
Best Practices & Tips
Best Practices & Tips
-Need your invoice to send to all emails listed on the Contact record, and not just the main email? Email help@ceojuice.com and ask us to adjust your ID412 subscription to send the invoice to the main email address as well as those listed in the 'Other Emails' section of the Contact record.
-Need your invoice customized? Your IT support will need to adjust/customize via SSMS Report Builder (can remove columns/change font, coloring, etc.). This will then change the emailed version of your invoice for ALL as well as your On Demand SSRS Report version. If your IT doesn't know how, we can help (email help@cejuice.com with exact details of what is needed please).
-For customizations by customer, you will need to use On Demand SSRS Report and export to excel for needed changes (we can't customize by customer).
-Location Info Missing?
Check that the Pencil Edit feature is not in use on the Equipment Record. This info is not stored in eAuto tables, so we can't pick it up for display:
**Use our alert ID891 to audit these
Send from
Send from Email Setting:
-This alert is considered a customer facing alert, so we highly recommend setting the from email address on your subscription to an email address your staff manages should your customer reply to the email received (i.e. ar@ or support@)
HOWEVER, be sure your IT Dept has set needed send as (not on behalf of) permissions; otherwise, emails will be generated but not actually sent once they reach your email server to send out. Please see THIS LINK on needed permissions set up for you IT. Once completed, please email us help@ceojuice.com so we can test (before you change your subscription)
Custom Property
Custom Properties:
(1) To set up Custom Propery ZCJAlert412SendBillingDetail for Contract Record:
Under Tools / Lists and Codes, search for Attributes and select New:
Attribute name: ZCJAlert412SendBillingDetail
Description: Enable Alert 412 - Send Billing Detail
Date type: User-Defined Lookup
Look up list: Enabled/Disabled
Then go to Configurations (custom properties) and find the Configuration set with Group of Contract Properties:
Search for ZCJAlert412SendBillingDetail, set default to Disabled, use QuickAdd, then be sure to hit OK to save:
(2) To set up Custom Properties for Variable1 - 4 (Cost Centers):
Under Tools / Lists and Codes, search for Attributes and select New:
Name new attribute and provide description, set Data type to Text Box (this will allow users to enter any variation of letters and numbers in the Custom Property):
Then go to Configurations (custom properties) and find the Configuration set with Group of Equipment Properties:
Search for the Attribute you created above and use QuickAdd to add this Attribute to the Equipment Configuration:
* * *
Related Alerts
Related Alerts
ID426 - Send Equipment Contact table by Equipment Location with BW / Color Meter Charges (ID412 sends one .csv invoice for all equipment to contact on Contract Record)
* * *
0 Comments