RaqForum 19 No.
1 Reply  •  464 View  • 
     ...

How Reporting Tools Access Salesforce Data

 

Many reporting tools do not provide the ready-to-use interface to access Salesforce. Users have to hard code the access in the user-defined data set. That is not easy at all. Some reporting tools encapsulate the interface through which users can access Salesforce using SOQL\SOSL. The method simplifies the data retrieval process, but it cannot make the later computations any easier. Hardcoding will still be needed.

A convenient way is to use SPL, the Java-based, open-source class library. The library offers convenient data retrieval functions, excellent computational capabilities, and the JDBC driver that is easy to be integrated by reporting tools.

SPL has built-in retrieval functions that are convenient and easy to use. The functions can access Salesforce through SOFQL\SOSL. To retrieve data from a Lead table, for instance, we just need to store Salesforce account information in a JSON file and then execute the following SPL script.

A

1

=sf_open("D:\\conf\\user.json")

2

=sf_query(A1,"/services/data/v51.0/query","Select Id,CaseNumber,Subject From Case where Status='New'")

3

=sf_close(A1)

4

return   A2

 

SPL has a wealth of built-in functions to compute multilevel data using simple and intuitive syntax, without the need of complicated hardcoding. To perform a query on orders data according to interval-style conditions, for instance, we have the following SPL code, where p_start and p_end are parameters:

A

3

…// Get orders data and close connection

4

=A2.select(Amount>p_start   && Amount<=p_end)

As code written in an RDB, SPL code can be integrated into a reporting tool through its JDBC driver. Take the above SPL code as an example, we can first save it as a script file and then invoke its name in the reporting tool as we do with a stored procedure. Assume we are using Birt and the code will be as follows:

{call intervalQuery(? , ?)}

More examples:

A

B

3

…

4

=A2.select(Amount>1000   && like(Client,\"*S*\"))

// Fuzzy query

5

=A2.sort(Client,-Amount)"

//Sort

6

=A2 .id(Client)

//Distinct

7

=A2.groups(year(OrderDate);sum(Amount))

//Grouping & aggregation

8

=orcl.query@x("select * from employees")

//Get data from Oracle

9

=join(A2:O,SellerId;A8:E,EId).new(O.OrderID, O.Client,O.Amount,E.Name,E.Gender,E.Dept)

//Join with Oracle

 

SPL also supports standard SQL syntax, including where, group, order, as well as from subquery, with subquery and joins that Salesforce does not support. The above query based on interval-style conditions can be rewritten as the following SQL statement:

A

1

=sf_open("D:\\conf\\user.json")

2

=sf_query(A1,"/services/data/v51.0/query","Select OrderID,Client,Amount,OrderDate from Orders")

3

=sf_close(A1)

4

$select * from {A2} where Amount>? && Amount<=?; p_start, p_end

More examples can be found in Examples of SQL Queries on Files.

 

SPL possesses outstanding computational capabilities, which simplify computations with complex logic, including stepwise computations, order-based computations and post-grouping computations. It can handle many computations effortlessly that are hard to deal with in SQL and stored procedures. For example, we are trying to find the first n big customers whose sales amount occupies at least half of the total and sort them by amount in descending order.

A

B

1

…

 / Retrieve data

2

=A1.sort(amount:-1)

/ Sort data by amount in descending order

3

=A2.cumulate(amount)

/ Get the sequence of cumulative totals

4

=A3.m(-1)/2

/ The last cumulative total is the final total

5

=A3.pselect(~>=A4)

/ Get position of record where the cumulative total reaches at least half of the total

6

=A2(to(A5))

/ Get values by position

 

SPL provides a professional IDE equipped with all-around debugging functionalities and lets users observe the result of each step with grid-style coding, making it particularly suitable for implementing algorithms with complex logic.

undefined 

SPL also supports many more NoSQL data sources, including CSV, XLS, WebService XML, Restful JSON, MongoDB, Hadoop, Redis, ElasticSearch, and Cassandra, and the mixed computations between any types of data sources or databases.

 


 
 •  464 View  •