In this blog, we are about to see how to display records in LWC using SAQL query by retrieving data and showing it on the UI with the help of a Lightning Web 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.
etch and Display Records in LWC using SAQL Query
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 :
We are Inno Valley Works, We are a passionate team of developers, best thinkers and consultants who can solve anything and everything.
With our highly engaging team, you can easily bring the vision to all your business ventures come true.
We have team, put your problem, get your solution
🎥 Check Out Our YouTube Channel
Explore helpful tutorials, product updates, and feature walkthroughs from the team at Innovalley Works.
👉 Visit Our Channel
.