Follow

ID412 - SSRS Spreadsheet Contract Invoice Overview & Sample

Spreadsheet (.csv) contract invoice showing details by equipment

Overview | Samples | Variables | Alert Functionality | OnDemand Report FiltersBest Practices & Tips |  Related Alerts

Click to Subscribe 

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

ID412Sample1.PNG

ID412Sample2.PNG

ID412Sample3.PNG

*  *  *

Variables

Variables

ID412Variables.PNG

VariableW: Custom Property on Contract Record that is set to 'Enabled" in order to trigger an invoice in .xls format

ID412ContractCP.PNG

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

1.png

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.

ID412EquipRecordCP.PNG 

*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.)

ID412TwoCostCenters.PNG

 

 

*  *  *

Alert Functionality

Alert Functionality

-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. 

-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.

-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.

- 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.

ID412SendFrom.PNG

-Two areas you MUST populate on Contract Record in order for alert to automatically generate the .csv file (even while in Test Mode):

1. Contact on Contract Record

2.png

2. Custom Property ZCJAlert412SendBillingDetail (per VaraibleW) set to Enabled:

ID412ContractContact.PNG

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.**

ID412CPVariables.PNG

-If you use more than one Custom Property, then the Cost Center Column combines ALL content (separated with comma). For example:

ID412TwoCostCenters.PNG

-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

ID412Explanation1.PNG

Location Remarks/Location Name/Address/City/State:

All from Equipment Record:

ID412EqupRecor.PNG

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.

ID412Contact.PNG

Orig Date / Maturity Date:

Start /End Date per Contract Record

ID412OrigMatDate.PNG

Install Date:

Per Equipment Record

ID412InstDate.PNG

ID412Explanation2.PNG

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

ID412Explanation3.PNG

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:

2.png

Contract Bill To Level:

ID412TaxContract.PNG

Lease Number: / Contract Lease Amount:

Per Service Lease Record

ID412Leases.PNG

Contract Misc Charge:

Found on Contract Record / Contract Equipment Detail / Miscellaneous

ID412Misc1.PNG

OR

Contract Record / Miscellaneous

ID412Misc.PNG

 

Contract Amount:

Total amount by device, before tax. Excludes lease amounts.

*  *  *

 

OnDemand Report Filters

OnDemand Report Filters

ID412SSRS1.PNG

ID412SSRSGrpBy.PNG

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).

ID412SSRSCostCtr.PNG

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:

ID412EqupRecor.PNG

 

 

 

*  *  *

 

Best Practices & Tips

Best Practices & Tips

-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 you 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

IS412PencilEdit.PNG

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)

ID412fromAR.PNG

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

ID412CP1.PNG

Then go to Configurations (custom properties) and find the Configuration set with Group of Contract Properties:

ID412CP2.PNG

Search for ZCJAlert412SendBillingDetail, set default to Disabled, use QuickAdd, then be sure to hit OK to save:

ID412CP3.PNG

(2) To set up Custom Properties for Variable1 - 4 (Cost Centers):

Under Tools / Lists and Codes, search for Attributes and select New:

 C1.png

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):

C2.png

Then go to Configurations (custom properties) and find the Configuration set with Group of Equipment Properties:

C3.png

Search for the Attribute you created above and use QuickAdd to add this Attribute to the Equipment Configuration:

C4.png

 

 

*  *  *

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)

 

*  *  * 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk