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

 

1.png

2.png

*  *  *

Variables

Variables

ID412VarW.PNG

VariableW and VariableX are REQUIRED and we will set up for you.

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

1.png

VariableX: defaults to 'Equipment' - This is the Configuration name you use (from lists and codes: Configurations (custom properties)) on your equipment records:

2.png

1.png

ID412Var1.PNG

If you want the task to automatically 'send' the .csv file to the contract contact each time a new invoice is generated and you use custom properties for  the Cost Center, then you need to provide the Custom Property names on Variables1 thru 4. 

Optional: Variable1 - Variable4 - Specify a different Custom Property for .csv invoice to include. This is intended to be used as Cost Center(s) you customer needs on the invoice. These are set on Equipment Record Custom Properties tab:

**If you use the task to automatically 'send' the .csv file to the contract contact each time a new invoice is generated and you use custom properties for  the Cost Center, then you need to provide the Custom Property names on Variables1 thru 4. **

EqpCustProp.png  

However, the report, nor eAutomate can allocate the equipment$$ across multiple cost centers, so all we can do is link/concatenate all the values in the custom property fields you specify as a 'location' on the report.  So if you have more than one of those custom property cost centers populated, the combined string of those will appear on the report as a unique location.  For example, a machine that has cost center 123 and 456 will show as cost center 123, 456 on the report and would not appear under the individual cost centers 123 or 456. 

 

 

*  *  *

Alert Functionality

Alert Functionality

-This alert will install SSRS Report so you can create spreadsheet manually for your customer (see below) OR you can set on Contract Record to auto create .csv file each time it bills. 

- We will set this alert up in test mode first as it is a customer facing alert for you. 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:

1. Contact on Contract Record

2.png

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

1.png

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

-If you have custom invoices, this alert process will not work for you as we only have the ability to pull from eAuto background tables from within their standard invoice format.

-If using SSRS Report to create .csv file invoice, you must wait one day after invoice generated in eAuto as the alerts looks to your analytics tables to run and those update nightly.

-Base rates on Contract Record have to be set to be billed as 'Use equipment base rates' in order for the invoice to provide breakdown detail for each equipment:

1.png

Which in turn, enables Tax Code at equipment level on Contract Record, which also needs to be populated for tax to show in detailed breakdown for each equipment :

2.png

  

*  *  *

 

OnDemand Report Filters

OnDemand Report Filters

**Please note, if using SSRS Report to create .csv file invoice, you must wait one day after invoice generated in eAuto as the alerts looks to your background tables to run and those update nightly.** 

1.png

2.png

3.png

 Group By Level1 and Level2:

This will set .csv file to first group your selection set in Group By Level1 (i.e. Location). Then set Group by Level2 for secondary grouping.

CostCenter2 , 3 and 4 are option if you are using Custom Properties in Variables1 - 4 to show more details as needed for your customer.

 

*  *  *

 

Best Practices & Tips

Best Practices & Tips

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

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