This blog post will give you an overview of how you can make use of CPQ’s Custom Table API to build your own custom dashboard using python programming.
Use case:
Consider a simple use case wherein we have a list of of quotes stored in some custom table and the requirement is to create a graphical representation for the total count of quotes for each status. So we will be using a bar and a pie chart for this example.
Prerequisites:
3. SAP CPQ user. I have used the CPQ sandbox tenant for this example
The blog post will be divided into 3 sections:
1. Creating custom table in CPQ
2. Creating a python app to make REST API calls
3. Host app in SAP BTP
Creating a custom table in CPQ
For this example to work we will need to create a custom table which holds the list of quotes along with it’s status. Create a custom table by the name Quote_Status.
Go to Setup ->Product Catalog->Custom Tables and Define a new table
The table structure should be as shown below.
Download the below data set and save it as a .xls or .xlsx file. Then import the same into the newly created Quote_Status table.
QUOTENUMBER,STATUS
1234,Preparing
1235,Preparing
1236,Preparing
1237,Preparing
1238,Preparing
1239,Preparing
1240,Preparing
1241,Preparing
1242,Preparing
1243,Preparing
1244,Preparing
1245,Preparing
1246,Preparing
1247,Preparing
1248,Preparing
1249,Preparing
1250,Preparing
1251,Preparing
1252,Preparing
1253,Preparing
1254,Preparing
1255,Preparing
1256,Preparing
1257,Preparing
1258,Preparing
1259,Preparing
1260,Preparing
1261,Preparing
1262,Preparing
1263,Preparing
1264,Preparing
1265,Preparing
1266,Preparing
1267,Preparing
1268,Preparing
1269,Preparing
1270,Preparing
1271,Preparing
1272,Preparing
1273,Preparing
1274,Preparing
1275,Preparing
1276,Preparing
1277,Preparing
1278,Preparing
1279,Preparing
1280,Preparing
1281,Preparing
1282,Preparing
1283,Preparing
1284,Preparing
1285,Preparing
1286,Preparing
1287,Preparing
1288,Preparing
1289,Preparing
1290,Preparing
1291,Preparing
1292,Preparing
1293,Preparing
1294,Preparing
1295,Preparing
1296,Preparing
1297,Preparing
1298,Preparing
1299,Preparing
1300,Preparing
1301,Preparing
1302,Preparing
1303,Preparing
1304,Preparing
1305,Preparing
1306,Preparing
1307,Preparing
1308,Preparing
1309,Preparing
1310,Preparing
1311,Preparing
1312,Preparing
1313,Preparing
1314,Preparing
1315,Preparing
1316,Preparing
1317,Preparing
1318,Preparing
1319,Preparing
1320,Preparing
1321,Preparing
1322,Preparing
1323,Preparing
1324,Preparing
1325,Preparing
1326,Preparing
1327,Preparing
1328,Preparing
1329,Preparing
1330,Preparing
1331,Preparing
1332,Preparing
1333,Preparing
1334,Preparing
1335,Preparing
1336,Customer Accepted
1337,Customer Accepted
1338,Customer Accepted
1339,Customer Accepted
1340,Customer Accepted
1341,Customer Accepted
1342,Customer Accepted
1343,Customer Accepted
1344,Customer Accepted
1345,Customer Accepted
1346,Customer Accepted
1347,Customer Accepted
1348,Customer Accepted
1349,Customer Accepted
1350,Customer Accepted
1351,Customer Accepted
1352,Customer Accepted
1353,Customer Accepted
1354,Customer Accepted
1355,Customer Accepted
1356,Customer Accepted
1357,Customer Accepted
1358,Customer Accepted
1359,Customer Accepted
1360,Customer Accepted
1361,Customer Accepted
1362,Customer Accepted
1363,Customer Accepted
1364,Customer Accepted
1365,Customer Accepted
1366,Customer Accepted
1367,Customer Accepted
1368,Customer Accepted
1369,Customer Accepted
1370,Customer Accepted
1371,Customer Accepted
1372,Customer Accepted
1373,Customer Accepted
1374,Customer Accepted
1375,Customer Accepted
1376,Customer Accepted
1377,Customer Accepted
1378,Customer Accepted
1379,Expired
1380,Expired
1381,Expired
1382,Expired
1383,Expired
1384,Expired
1385,Expired
1386,Expired
1387,Expired
1388,Expired
1389,Expired
1390,Expired
1391,Expired
1392,Expired
1393,Expired
1394,Expired
1395,Expired
1396,Expired
1397,Expired
1398,Expired
1399,Expired
1400,Expired
1401,Expired
1402,Expired
1403,Expired
1404,Expired
1405,Expired
1406,Expired
1407,Open
1408,Open
1409,Open
1410,Open
1411,Open
1412,Open
1413,Open
1414,Open
1415,Open
1416,Open
1417,Open
1418,Open
1419,Open
1420,Open
1421,Open
1422,Open
1423,Open
1424,Open
1425,Open
1426,Open
1427,Open
1428,Open
1429,Open
1430,Open
1431,Open
1432,Open
Creating a python app to design the UI and make API calls
Once the custom table has been created and loaded with quote information, the next step is to build and deploy the python app.
Below is the python app that will be used make REST API call to CPQ and also to design the UI for the dashboard. Download and save the below code as App.py
from dash_bootstrap_components._components.Card import Card
import pandas as pd
from collections import Counter
from dash import html,dcc
from dash.dependencies import Input, Output
import plotly.express as px
import requests
import json
import dash
import dash_bootstrap_components as dbc
from jupyter_dash import JupyterDash
from flask import request
import os
tenant_url='YOUR_TENANT_URL'
cf_port = os.getenv("PORT")
data = {
"username":'YOUR_USER_NAME',
#DO_NOT_CHANGE THE value of GRANT_TYPE. The value has to be 'password' and NOT your password
"grant_type":"password",
"password":'YOUR_PASSWORD',
"domain":'YOUR_DOMAIN'
}
#Get API token using basic auth
def getApiToken():
base_url=tenant_url+"/basic/api/token"
#my_headers={"Authorization" : "Bearer "+token}
r= requests.post(base_url, data=data)
response= json.loads(r.text)
#print(response['access_token'])
access_token=response['access_token']
# print(jwt_token)
return access_token
api_token=getApiToken()
#Get JWT token using api token
def getJWT(token):
base_url=tenant_url+"/api/rd/v1/core/GenerateJWT"
my_headers={"Authorization" : "Bearer "+token}
r= requests.post(base_url, headers=my_headers)
response= json.loads(r.text)
jwt_token=response['token']
#print(jwt_token)
return jwt_token
jwt_token=getJWT(api_token)
#print('Jwt Token is : ' +jwt_token)
#Quote_Status is the name of the custom table that was created in CPQ
#Ensure the table has data
# use the jwt token to get data from custom table
base_url = tenant_url+"/api/custom-table/v1/customTables/Quote_Status/entries"
# #my_headers = {"Authorization" : "Bearer "+jwt_token}
my_headers = {"Authorization" : jwt_token}
response = requests.get(base_url, headers=my_headers)
data = json.loads(response.text)
print(data)
cnt = Counter()
for i in data['value']:
cnt[i['STATUS']] += 1
df=pd.DataFrame(cnt.items(),columns=['Status', 'Count'])
print(df)
app = JupyterDash(external_stylesheets=[dbc.themes.SLATE])
#----------------------------------APP LAYOUT---------------------------#
app.layout = html.Div([
dbc.Card(
[
dbc.CardBody(
[
dbc.Row([
dbc.Col([html.Label(['Select Chart Type'],style={"font-size":"200 px", "color":"white"}),
dcc.Dropdown(
id='my_dropdown',
options=[
{'label': 'Bar Chart', 'value':'Bar Chart' },
{'label': 'Pie Chart', 'value': 'Pie Chart'},
],
value='Bar Chart',
multi=False,
clearable=False,
style={"width": "100%","color":"black"},
)], width=6, align='start', style={"font-size":"200 px","height":"30px"}) ,
])
])]
,style={"width": "50%", "height":"100px"}, color='info', inverse=True),
html.Br(),
html.Div([
dbc.Card(
dbc.CardBody([
dbc.Row([
dbc.Col([
dcc.Graph(id='the_graph')
], width=6)
])
])
)
])
])
#----------------------------------------App Callback Decorators--------------------------------------#
@app.callback(
Output(component_id='the_graph', component_property='figure'),
[Input(component_id='my_dropdown', component_property='value')]
)
def update_output_div(my_dropdown):
if my_dropdown:
if my_dropdown == 'Pie Chart':
piechart=px.pie(
df, values=df.Count, names=df.Status, title='Quote Status'
).update_layout(
template='ggplot2'
)
return piechart
elif my_dropdown == 'Bar Chart':
barchart=px.bar(
df, x=df.Count, y=df.Status, title='Quote Status'
).update_layout(
template='ggplot2'
)
return barchart
else:
return dash.no_update, dash.no_update
if __name__ == '__main__':
if cf_port is None:
app.run_server(host='0.0.0.0', port=5000, debug=True)
else:
app.run_server(host='0.0.0.0', port=int(cf_port), debug=True)
Please note that you have to provide your SAP CPQ tenant’s url ,domain and credentials(basic auth with rest api user) in the App.py file before pushing this app to SAP BTP.
We get the custom table data using the below API endpoint in our python code.
/api/custom-table/v1/customTables/Quote_Status/entries
In order to deploy this app to SAP BTP you will need 4 other files along with the App.py file.
1. Procfile
2. manifest.yaml
3. requirements.txt
4. runtime.txt
All of these files will have to be saved in the same directory as the App.py file.
Save the below as Procfile
web: python App.py
Save the below as manifest.yaml
applications:
- name: quote-status-dash
random-route: true
memory: 1024M
command: python App.py
timeout: 300
Save the below as requirements.txt
pandas
dash
plotly
requests
dash_auth
jupyter_dash
flask
dash_bootstrap_components
Save the below as runtime.txt
3.x
Once you’re done you should be able to see all your files in the same folder, something like this as shown below. I’ve saved all my files under the directory CPQ_QuoteStatus_Dropdown. Now that you have all the required files we can now push this to SAP BTP.
Host app in SAP BTP
Go to the directory/folder where you have saved the above files using your command line or code editor(like VS Code) and login to your CF using your email id and password.
cf login
Push the app to SAP BTP using the below command
cf push quote-status-dash
Once successfully hosted, you can login to your SAP BTP cockpit and check if the app is up and running. Once the app is deployed successfully you should get an Application route url.
Click on the above link and you should be able to view your dashboard.
No comments:
Post a Comment