Exports

1             Defining exports

In this chapter, you will find the explanation of how to create an export from a stored procedure or a query defined in query builder.

1.1     Exports overview

You can find the export overview in the menu under “Export -> Export Overview”.

In this overview, you can select your defined campaigns and add or modify exports bound exports for that campaign.

The first item ‘General’ can be used to define exports not bound to an export.

1.2     Defining a new/existing export

You can define a new export by pressing the button “New Export” in the left corner of the toolbar.

To open an existing export definition, just click the name of the export.

In both cases you get the same screen and same functions.

 

1.2.1    Common

Every export has some common information.

This information consist of :

• Export name, your own (logical) name for this export
• Export Type, this is the placement in the export module to distinguish Reports from Exports.
• Campaign, The bound campaign
• Data source. You can use a SQL stored procedure or a query builder object.
• Data connection, Only available for general exports. Defines the SQL connection to use.
• Data procedure, will list all available stored procedures under the given SQL connection or All Query builder objects

1.2.2    Parameters

When a stored procedure is chosen as data source, then the parameters of this procedure will be shown (if any available).

Note that 3 types can be defined:

• Textbox
• Dropdown
• Checkbox

A textbox parameter consists of:

• Parameter, from here you can choose the parameter to edit/define
• Name as presented to the user in the export module
• Parameter type, defines the control used to get input from the user. You can define tekst, dropdown and checkbox
• Max length, Maximum input length. Defaults to the procedure definition, but can be made ‘shorter’.
• Default value, you can define a default value by using an CCP variables, functions or fixed value.
• Required, when checked, the item cannot be left blank.
• Validation, can contain a valid RegEx string to validate the input from the user.
• Error message, will be displayed if the user input isn’t valid by RegEx or left blank and Required is checked.

A dropdown parameter consists of:

• Parameter, from here you can choose the parameter to edit/define
• Name as presented to the user in the export module
• Parameter type, defines the control used to get input from the user. You can define text, dropdown and checkbox
• Dynamic, when checked, uses a query to populate the dropdown together with Display field and Value field to determine what to use as value and what to display to the user.
• Dropdown value, consists of name value pairs separated by a comma to populate the dropdown. When dynamic is checked, this field contains the query to get the list items. Note that the query MUST return unique values!
• Display field, only when dynamic is checked. Will hold the fieldname which contains the texts to present to the user in the dropdown.
• Value field, only when dynamic is checked. Will hold the field name for the values which are used as parameter content.
• Default value, you can define a default value by using an CCP variables, functions or fixed value.
• Required, when checked, the item cannot be left blank.
• Error message, will be displayed if the user input isn’t valid by RegEx or left blank and Required is checked.

A checkbox parameter consists of:

• Parameter, from here you can choose the parameter to edit/define
• Name as presented to the user in the export module
• Parameter type, defines the control used to get input from the user. You can define tekst, dropdown and checkbox
• Value on, this value will be passed to the query when checked
• Value off, this value will be passed to the query when unchecked
• Default value, you can define a default value by using an CCP variables, functions or fixed value.

1.2.3    File module

Every export uses a file module to create a file.

The current modules are :

• Excel (xlsx), used for plain Excel output without need for formatting, optimized for large data sets
• ffExcel (xlsx/xlsxm), used for Excel with a template. Optimized for large data sets
• Text (txt, csv, TAB etc), used for every text-based export
• Office excel (xlsx, xls for reporting use only), Used for reports with small dataset (with use of template). Note that excel is used for this module for high compatibility but can’s be used for large data (>1000 rows).

The items to define the File module are:

• File module, the export module to use (Excel, Text, etc)
• Filename, the file name to use, can be made dynamic with CCP functions and variables
• Parameters, every file module has its own parameter set. The format to pass these parameters are: Name=value&NextName=value (see chapter 7)
• Row header, if supported, row headers are generated when Yes.

For the office excel some other parameters will be shown:

The fields filename and row header are the same as with the other file modules, the others are:

• Template: Let’s you use an uploaded excel template to use. Note that the template will be opened as it was while saving. The last active sheet will be used to write to! So if you have a data sheet, keep that sheet open while saving.
• Header Bold: Makes the header text bold.
• Data bold: Makes the data bold.
• Header border: Gives the header the default border.
• Data border: Gives the data a standard border.
• Last row bold: Makes the last row bold.
• Macro Name: Executes the macro from the used templates just after the data is inserted.
• Write data as text: Writes all data as text, numbers will be converted to text fields.
• Start cel X: Start column from where to write the result set of the query (zero based).
• Start cel Y: Start column from where to write the result set of the query (zero based).

This module is to be used with reports! It’s not optimized for large results sets.

Also note that

• the implemented excel version is 2007 in Dutch.
• The file extension MUST be the same as used for the template

So, if an xlsm template (with macro’s) is used the exported file extension must be xlsm too!

 

1.3     Uploading templates

In CCP you can upload Excel (xlsx and xlsxm) templates for use with an export.

To do this go to Menu->Export->Export templates.

To add a new template, simply click the button “New Template”, give it a logical name and locatie the file on youre computer. When clicking “Ok” in the popup, youre template will be uploaded and ready for use.

At this moment its only possible to delete and edit just the name of the template.

1.3.1    Some notes about using templates

There are some behaviors you need to know when using templates.

• Data is always written to the active sheet. This is the sheet visible when saving the file! Its easy to swap the active sheet within a macro (Sheets(“<Name of sheet>”).select
• You have to use language independ function in youre macro’s to be sure its working without the need for localisation. The decimal indicator is a“.”.
• You have to use the same file extention in the template AND export filename. When using a xlsm as template, the filename in the export module has to be xlsm to! Else you cant open the file when downloaded until you rename.
• There are 2 Excel modules that supports templates, both have a different purpose.

 

1.3.2    Office excel module

With the office excel module you can define where to start to write your data on both x and y axis. Altough the data will still be written as a table, but it wont override settings in the columns, only the value.

So you can make a good looking layout as template and just write your report in there.

Data can also be written on a “data” sheet to be used on the template for a nice presentation of your’e data. But be carefull, use aggregated data and no data dumps.

A real installation of excel is used for high compatibility, but acces to excel is slow.

 

1.3.3    ffExcel module

ffExcel is a new module developed internally to speed up the writing to Excel.

The main purpose of the module is to “dump” data to a data sheet.  The data in this sheet can be used across other sheets to make an attractive presentation to the end user.

This module has less capabilities then the office excel module, but it’s much faster!

Note that when you want to use a template that everything on the ‘active’ sheet will be overwritten.

1.4     Copy an export

Within CCP, you can copy exports, but not all!

You can only copy exports when:

• There bound to a campaign
• The Data source is set to CCP Export

When you click the copy button after an export definition in the “Export overview” screen, you’ll see:

Just choose the campaign and ok to copy, or cancel to abort the copy.

1.5     Delete an export

To delete an export, just click the delete link after the export in the “export overview” screen.

You have to click OK in the popup to delete the export, or cancel to abort.

2             Executing exports

To create exports, go to “Menu -> Export -> Exports”.

On this screen, you can select the campaign and exports or reports to execute and download.

When you open an export, the default values are evaluated and presented to the user.

The user can edit these parameters and will get an error when it doesn’t comply to the parameter validation rules. Note that CCP itself will do no validation on its own since this will restrict functionality.

When you have selected the needed export, click “Execute” to perform the export.

A new part on the screen will appear when exports are executing, pending or waiting.

After the export name, some icons will appear:

• A red clock, Export is pending
• A green clock, Export is running
• A cross, click on the cross to remove from the list.
• A arrow down, click on this icon to download the export.

Always delete exports when ready.

3             Commands for QueryBuilder and Exports

All commands need to be between hashtags (#), all returned dates are in the format YYYYMMDD, the same as CCL uses in callfiles.

Command	Example	Explanation
DateAdd(<value>)	#DateAdd(0)#	Adds days to the current date.
WeekStart(<value>)	#WeekStart(0)#	Gets the start date of a week, -1 is last week, -2 the week before last week, 0 the current week etc.
WeekEnd(<value>)	#WeekEnd(0)#	Gets the end date of a week, -1 is last week, -2 the week before last week, 0 the current week etc.
MonthStart(<value>)	#MonthStart(0)#	Gets the start date of a month, -1 is last month, -2 the week before last month, 0 the current month etc.
MonthEnd(<value>)	#MonthEnd(-1)#	Gets the end date of a month, -1 is last month, -2 the week before last month, 0 the current month etc.

4             Variables for Querybuilder and exports

Name	Explanation
#CampagneName#	Returns the CCP campaign name, useful if you have some fields that needs to be dynamic by campaign, this way you can still use one query.
#CampagneNote#	Returns the CCP campaign note.
#CompanyName#	Returns your company name, useful in case you have multiple company’s
#CampagneType#	Returns the campaign type for use in your query, the current values are: Chat, Outbound, Inbound, manual call and Email.
#CampagneIds#	Gives the campaign name between single quotes. For inbound: gives all the defined ccl campaigns separated by a comma. This variable is very useful within the where of a query to get all the data for your CCP campaign.
#CampagneSelectionType#	For inbound only, gives first or last. In OdCalls you also have First and Lastcampaign. The first is the DID the call was initiated, last is where the call has ended. Very useful if using IVR’s to route to virtual DID’s.
#ContactPersoon#	Returns your contact person name
#ht#	Returns only the # sign. #tmp must be written as #ht#tmp.

5             Parameters for Export modules

Parameters to the export modules are passed as name value pairs in the format: <Name>=<value>. To use multiple parameters, use the & sign.

At the moment only the text module supports these parameters. The other do have them, but can be defined in the interface with options.

The used parameter for the text module are:

Name	Explanation
SEPERATOR	Defines the text separator, when none defined a TAB is used. Example: separator=;
TEXTQUALIFIER	Defines a text qualifier, this will put this sign around text valued fields. Example: textqualifier=”. When none defined, none will be used.
STREAMTYPE	Defines the filetype to be used to write the textfile. When none defined, ANSI will be used. Other options are: -      UTF7 -      UTF8 -      UTF32 -      UNICODE -      ASCII Example: streamtype=UTF8

6             Variable tables per campaign type

Within query builder you can also use variables for the campaign tables.

Name	Explanation
#clientfile#	For all campaigns, gives the clientfile.
#callfile#	For outbound only, gives the callfile used by the campaign.

7             Scheduling exports

With the scheduler, it’s possible to automatically upload exports to an ftp, sftp or ftps location. The schedule can be set at minute, hour, day and week.

To add or edit schedules got to menu-> exports -> Schedules and a screen like below will be presented.

Click on new task to create a new schedule. To edit an existing schedule, just click on the name and the following screen will be visible.

In the first section, you can enter:

Name	The name of the schedule
Start date and time	Date and time to start the schedule.
Interval	The interval used, can be set to minute, hour, day and week.
Frequency	An integer numeric value, for the interval to be added. If this is set to 1 and frequency is set to day, then every day the export will run at the given time.
Campaign	The campaign or generic.
Export	The export to be scheduled belonging to the campaign.
Export to	Can be ftp, sftp or ftps
Send check mail	Send a notification if the schedule had run.
Active	When active the schedule will run, when inactive not. Used to deactivate schedules temporarily.

The second section (Send Check mail).      

To	Here you can enter the email address for the receiver
Subject	The subject of the email
Body	The text you want to send

The last section (ftp settings)

FTP URL	The URL of the (s)ftp(s) location without (s)ftp(s):// prefix.
Port number	Portnumber of the ftp service
Username	Username
Password	Password
Folder	Destination folder on the ftp site, use a slash (/) when using the root directory.