In this blog we are about to see how data can be retrieved by SAQL Query and displayed in UI using LWC Component.
- Salesforce Analytic Query Language(SAQL), a natively developed query language to navigate data.
- Create Data Set using data flow.
- We use SAQL (Salesforce Analytics Query Language) to access data in Analytics Cloud datasets.
- Analytics Cloud uses SAQL behind the scenes in lenses, dashboards, and explorers to gather data for visualizations.
- We use SAQL queries for complex requirements.
SAQL Query in Lens :
In above SAQL query we get all the Opportunity records grouped by Stage Name.
Get Data From SAQL query Using LWC
Apex Code :
- Get Auth token and Http Response. We can get auth token and response from the Apex class and pass to the Lwc Js.
Constant variable Declaration
public class Constants {
public static String POST = 'POST';
public static String PATCH = 'PATCH';
public static String GET = 'GET';
public static String AUTHORIZATION =
'Authorization';
public static String BEARER = 'Bearer ';
public static String BASIC = 'Basic ';
}Http response Declaration
public class RestAPIUtil {
public static HttpResponse getHTTP(String endPoint,
Map<String,String> headers){
HttpRequest request = new HttpRequest();
HttpResponse response = new HttpResponse();
Http http = new Http();
request.setMethod(Constants.GET);
request.setEndpoint(endpoint);
for(String header : headers.keySet()){
request.setHeader(header,headers.get(header));
}
try {
response = http.send(request);
return response;
} catch(System.CalloutException e) {
throw new GateWayException('Error Occured in
Http call out',e);
}
} public static HttpResponse postHTTP(String endpoint,
Map<String,String> headers){
HttpRequest request = new HttpRequest();
HttpResponse response = new HttpResponse();
Http http = new Http();request.setMethod(Constants.POST);
request.setEndpoint(endpoint);
for(String header : headers.keySet()){
request.setHeader(header,headers.get(header));
}try {
response = http.send(request);
return response;
} catch(System.CalloutException e) {
throw new GateWayException('Error Occured in
Http call out',e);
}
}
public class GateWayException extends Exception {}
}Apex class for Get Access token And CurrentVersionId
public class ApiCallForSaql {
@AuraEnabled(cacheable=true)
public static string CreateToken()
{
Map<String, String> headers = new Map<String,
String>();
String url =
'https://innovalleyworks6-dev-ed.my.salesforce.com/services/o
auth2/token?grant_type=password&client_id="Client Id"&client_secret="Your Client Secret"&username = 'Your Username'&
password='Your Password';
headers = new Map<String, String>();
headers.put('Content-Type',
'application/json');
httpresponse res = RestAPIUtil.postHTTP(url,
headers);
AccessToken wrpToken = new AccessToken();
wrpToken = (AccessToken)
JSON.deserialize(res.getBody(), AccessToken.class);
return wrpToken.access_token;
} @
AuraEnabled
public static Map<string, String>
getSessionAndVersions()
{
Map<string, String> resultMap = new Map<String,
String>();
String token = CreateToken();
resultMap.put('SESION', token);
Map<String, String> headers = new Map<String,
String>();
String url =
'https://innovalleyworks6-dev- ed.my.salesforce.com/services/data/v53.0/wave/datasets/0Fb5j000000PYF4CAO';
headers = new Map<String, String>();
headers.put('Content-Type',
'application/json');
headers.put('Authorization', 'Bearer ' + token);
httpresponse response =
RestAPIUtil.getHTTP(url, headers);
resultMap.put('DATASET', response.getBody());
return resultMap;
} public class AccessToken{
public String access_token{get;set;}
}
}LWC JS :
We can get the Data from SAQL Query directly from LWC Js Using the Auth token and response received from Apex.
import { LightningElement } from 'lwc';
//End point from CustomLabel(enter your endpoint URL)
import endpoint from '@salesforce/label/c.endpoint';Import
getSessionAndVersions
from'@salesforce/apex/ApiCallForSaql.getSessionAndVersions';
export default class GetSaqlQueryData extends LightningElement {
error;
endError;
responseData = [];
connectedCallback() {
this.logoUrl = endpoint;
this.dataLoad();
}
dataLoad() {
getSessionAndVersions()
.then(result => {
this.responseValue = result.DATASET;this.parseValue = JSON.parse(this.responseValue);
console.log('this.parseValue==',this.parseValue);
this.token = result.SESION;
console.log('this.token ==',this.token);
let AuthToken ="Bearer "+this.token;
console.log('AuthToken ==',AuthToken);
let req = new XMLHttpRequest();
req.open('POST',this.endPoint , true);
let queryStr ="q =load
\"0Fb5j000000PYF4CAO/"+this.parseValue.currentVersionId+"\";
q = group q by 'StageName';
q = foreach q generate 'StageName' as 'StageName', count() as 'count',
sum('Amount') as 'sum_Amount', max('Amount') as 'max_Amount',
min('Amount') as 'min_Amount';
q = order q by 'StageName' asc;q = limit q 2000;"
let data = {query: queryStr}
let fileBody= JSON.stringify({"query": data});
const endPoints = this.logoUrl;
fetch(
endPoints,
{
method: "POST",
headers: {
"Authorization": AuthToken,
"Content-Type" : 'application/json',
"Accept" : 'application/json'
},
body: JSON.stringify(data)
}
).then(response => response.json())
.then(datas =>{
console.log('datas==',datas);
this.responseData = datas.results.records;
});
})
.catch(error => {
this.error = error;
this.responseValue = undefined;
this.isLoading = false;
});}
}
UI Code
<template>
<template if:true={responseData}>
<div class="slds-box slds-theme_shade table-scroll"
style="background:#FFFFFF !important;margin:10px ;padding:0px">
<table id="example" class="table table-striped table-bordered
slds-table slds-table_bordered slds-table_cell-buffer" >
<thead>
<tr>
<th class="backgroundHead" scope="col" rowspan='2'>
<div class="slds-truncate
slds-text-title_bold"title="Accounts">StageName</div>
</th>
<th class="backgroundHead" scope="col" rowspan='2'>
<div class="slds-truncate slds-text-title_bold"
title="YTD Revenue">Count</div>
</th>
<th class="backgroundHead" scope="col" rowspan='2'>
<div class="slds-truncate slds-text-title_bold" title="%
Sucessful">Max Ammount</div>
</th>
<th class="backgroundHead" scope="col" rowspan='2'>
<div class="slds-truncate slds-text-title_bold"
title="Total">Min Ammount</div>
</th>
<th class="backgroundHead" scope="col" colspan='2' >
<div class="slds-truncate slds-text-title_bold"
title="YTD Revenue">Sum Ammount</div>
</th>
</tr>
</thead>
<tbody>
<template for:each={responseData} for:item="gridData">
<tr key={gridData.StageName}>
<td data-label="accName" class="" scope="col" >
<div class="slds-truncate" title="Start Week" >
<p><lightning-formatted-textvalue={gridData.StageName}>
</lightning-formatted-text>
</p>
</div>
</td>
<td data-label="ydtRevenue" class="" scope="col" >
<div class="slds-truncate" title="Start Week" >
<p class = "alignright">
<lightning-formatted-number
value={gridData.count}></lightning-formatted-number>
</p>
</div>
</td>
<td data-label="ydtsales" class="" scope="col" >
<div class="slds-truncate" title="YTD Split Revenue">
<p class = "alignright">
<lightning-formatted-number
value={gridData.max_Amount}></lightning-formatted-number>
</p>
</div>
</td>
<td data-label="totalSumValue" class="" scope="col" >
<div class="slds-truncate" title="YTD Split Revenue">
<p class = "alignright">
<lightning-formatted-number
value={gridData.min_Amount} ></lightning-formatted-number>
</p>
</div>
</td>
<td data-label="domesticRevene" class="" scope="col">
<div class="slds-truncate" title="YTD Domestic
Revenue">
<p class = "alignright">
<lightning-formatted-number
value={gridData.sum_Amount} format-style="currency" currency-code="GBP"maximum-fraction-digits="0"
minimum-fraction-digits="0"></lightning-formatted-number>
</p>
</div>
</td>
</tr>
</template>
</tbody>
</table>
</div>
</template>
</template>Output :
.