Its a busy world and time is in short supply. In order to try and avoid disappointing people who may come across our tools and in order to avoid repeatedly answering questions via email we have composed an FAQ.

1. 1)Why use Talend ?
   

2. 2)How can you create the excel reports from a webpage ?
   

3. 3)How do I install the component ?
   

4. 4)Where can I find more information on the scripting languages the tool uses ?
   

5. 5)How can I create an excel report using drop down / combo boxes ?
   

6. 6)How do you create a pivot table ?
   

7. 7)What future plans do you have for the tool?
   

17. Q)Why Talend ?
    

1. A)The decision to use an ETL tool was taken since it would allow a great deal of different data sources to be used and would allow for features such as grouping and filtering. We also chose Talend since we wanted to be able to create excel reports by selecting parameters from a a webpage and have the generated report streamed to the end user.
   

17. Q)How can you create the excel reports from a webpage?
    

A) It is possible to call the reports from a JSP page. We will try and upload some sample code over the next few weeks.

Q) How do I install the component?

1. A)After downloading the component from either the talend website or from our downloads section you need to tell Talend where to find the component. This is done by loading Talend and clicking on Window, Preferences, Talend, Components. On this screen you can define a folder which contains you extensions. After performing this action you will need to reload Talend. You should then find the Excel Reporting Component in the palette.
   

17. Q)Where can I find more information on the scripting languages the tool uses?
    

1. A)Behind the scenes the tool uses the excellent JXLS component. More information about JXLS can be found on sourceforge. Please not due to the way that objects are created in the tool you might not be able to get all of the samples to work. You should also look at the Excel Reporting Manual, since this contains lots of useful information.
   

17. Q)How can I use drop down / combo boxes
    

A) Excel offers numerous drop down boxes, which it is possible to incorporate into your reports.

17. Q)How do you create a pivot table?
    

1. A)Pivot tables are not supported out of the box, however it is possible to use some VBA in the excel template spreadsheet to accomplish this goal. Some sample code is given below.
   

Lets assume that you will be using the component to extact data from a source system and will be loading this into an excel spreadsheet which contain the titles, Sales, Years, Months and Person. A macro like the one below can be used to create a pivot table off this data. This particular piece of VBA uses the workbook_open() event, which will ensure that the code fires after the end user opens the spreadsheet.  It assumes you will load the source data into a sheet called sheet1 and will produce a pivot table on sheet2.

If you wish to try something like this, perhaps the quickest and simplest way would be to place some sample data into a speadsheet and the use the macro recorder. You can then use a combination of the recorded macro and the code below to achieve your objectives. Ultimately all this code below really does is erase any existing pivot table and ensure that the newly created one contains all of the rows from the extract.

Note to access the visual based editor in excel press alt+f11 for all version prior to 2007. From 2007 onwards you need to activate the developer tools.

Option Explicit

Private Sub Workbook_Open()

Dim WB As Workbook
Dim WSSource As Worksheet
Dim WSTarget As Worksheet
Dim lastRow As Integer
Dim PT As PivotTable

On Error GoTo err:

Set WB = ThisWorkbook
Set WSSource = WB.Sheets(”Sheet1″)
Set WSTarget = WB.Sheets(”Sheet2″)

‘Delete the existing pivot (if any)

‘ Delete any prior pivot tables
For Each PT In WSTarget.PivotTables
PT.TableRange2.Clear
Next PT

‘Change this to the source sheet name

lastRow = WSSource.UsedRange.Rows.Count

WSSource.Activate
WSSource.Range(”A1:D” & lastRow).Select

‘CHANGE THE R1C1 Reference (Row and Column) And Sheet Name

WSSource.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
“Sheet1!R1C1:R” & lastRow & “C4″, TableDestination:=”Sheet2!R1C1″, TableName:=”PivotTable1″
ActiveSheet.PivotTables(”PivotTable1″).SmallGrid = False
With ActiveSheet.PivotTables(”PivotTable1″).PivotFields(”Sales”)
    .Orientation = xlDataField
    .Position = 1
End With
With ActiveSheet.PivotTables(”PivotTable1″).PivotFields(”Year”)
    .Orientation = xlColumnField
    .Position = 1
End With
    With ActiveSheet.PivotTables(”PivotTable1″).PivotFields(”Month”)
    .Orientation = xlRowField
    .Position = 1
End With
    With ActiveSheet.PivotTables(”PivotTable1″).PivotFields(”Person”)
    .Orientation = xlPageField
    .Position = 1
End With

‘Clean up
Set WB = Nothing
Set WSSource = Nothing
Set WSTarget = Nothing

Exit Sub

err:
MsgBox (”Error – ” + err.descrption)

End Sub

17. Q)What future plans do you have for the tool ?
    

A) We would like the ability to report from multi-dimensional data sources in a dynamic way. There is the need for some preliminary work on a different component before this could happen. Ultimately we would like to be able to create reports with data extracted from Microsoft SQL Server Analysis Services (SSAS)