User Tools

Site Tools


projects:ceo_bi_reports

CEO BI Reports

Description

A list of SQL requests to be used into Power BI App. All requirements are on the requirements file.

All the requests don't add a specific format on Dates or Currency amounts. These formats will be added on the Power BI tool.

As that is not specified in the requirement document, we added the Job_number field on all the requests.

Requests

Sales Requests

Request 1: Sales Count and Revenue

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports.pbix tab Sales Count and Revenue.

In that report, we have introduced a week number selector. So when you select a week among two weeks/year, please select both of them.

Updated on 20/06/2019.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   cont_pay.d_pay_rec AS Received,
   cont_pay.d_banked AS Banked,
   cont_pay.c_pay AS Amount,
   doc.s_name AS Name_Tender,
   entity.s_name AS "Salesperson Name",
   doc.c_retail_total AS "Total Amount" 
FROM
   FworkSQLEcm.dbo.cont_pay 
   INNER JOIN
      FworkSQLEcm.dbo.cont 
      ON cont.l_cont_id = cont_pay.l_cont_id 
   INNER JOIN
      FworkSQLEcm.dbo.job_cont 
      ON job_cont.l_cont_id = cont.l_cont_id 
   INNER JOIN
      FworkSQLEcm.dbo.job 
      ON job.l_job_id = job_cont.l_job_id 
   INNER JOIN
      FworkSQLEcm.dbo.doc 
      ON doc.l_job_id = job.l_job_id 
   INNER JOIN
      FworkSQLEcm.dbo.cont_comm 
      ON cont_comm.l_cont_comm_id = cont.l_comm_prim_id 
   INNER JOIN
      FworkSQLEcm.dbo.entity 
      ON FworkSQLEcm.dbo.entity.l_entity_id = cont_comm.l_spers_e_id 
   INNER JOIN
      list_item 
      ON list_item.l_list_item_id = cont_pay.l_pay_type_gl_id 
   INNER JOIN
      FworkSQLEcm.dbo.cst_claim 
      ON cst_claim.l_cst_claim_id = cont_pay.l_cst_claim_id 
   INNER JOIN
      list_item AS jobStatus 
      ON job.l_job_status_gl_id = jobStatus.l_list_item_id 
   INNER JOIN
      rDiv 
      ON job.l_context_id = rDiv.l_context_id 
WHERE
   cont.d_pay_ideposit_act IS NOT NULL 
   AND doc.s_name = 'Tender 1'

Request 2: Contract Conversion Count and Contract Revenue based on Contract Signed

Updated on 20/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports.pbix tab Contracts Conversion.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   doc.d_signed_act AS Contract_signed,
   cont.c_retail_internal AS Base_house_price,
   entity.s_name AS Name_Salesperson 
FROM
   FworkSQLEcm.dbo.doc 
   INNER JOIN
      FworkSQLEcm.dbo.job 
      ON job.l_job_id = doc.l_job_id 
   INNER JOIN
      FworkSQLEcm.dbo.job_cont 
      ON job_cont.l_job_id = job.l_job_id 
   INNER JOIN
      FworkSQLEcm.dbo.cont 
      ON cont.l_cont_id = job_cont.l_cont_id 
   INNER JOIN
      FworkSQLEcm.dbo.cont_comm 
      ON cont_comm.l_cont_comm_id = cont.l_comm_prim_id 
   INNER JOIN
      FworkSQLEcm.dbo.entity 
      ON entity.l_entity_id = cont_comm.l_spers_e_id 
   INNER JOIN
      list_item AS jobStatus 
      ON job.l_job_status_gl_id = jobStatus.l_list_item_id 
   INNER JOIN
      rDiv 
      ON job.l_context_id = rDiv.l_context_id 
WHERE
   doc.d_signed_act IS NOT NULL 
   AND cont.c_retail_internal > 0

Request 3: Contract Conversion Duration between Contract Checked and Contract Signed dates

Updated on 20/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports.pbix tab Contracts Conversion between Checked & Signed.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   doc.d_signed_act AS contract_signed,
   udf_job1.d_contchecked AS contract_checked,
   {fn TIMESTAMPDIFF( SQL_TSI_DAY, udf_job1.d_contChecked, doc.d_signed_act ) } AS interval_days,
   cont.c_retail_internal AS Base_house_price,
   entity.s_name AS Name_Salesperson 
FROM
   FworkSQLEcm.dbo.doc 
   INNER JOIN
      FworkSQLEcm.dbo.job 
      ON job.l_job_id = doc.l_job_id 
   INNER JOIN
      FworkSQLEcm.dbo.job_cont 
      ON job_cont.l_job_id = job.l_job_id 
   INNER JOIN
      FworkSQLEcm.dbo.cont 
      ON cont.l_cont_id = job_cont.l_cont_id 
   INNER JOIN
      FworkSQLEcm.dbo.cont_comm 
      ON cont_comm.l_cont_comm_id = cont.l_comm_prim_id 
   INNER JOIN
      FworkSQLEcm.dbo.entity 
      ON entity.l_entity_id = cont_comm.l_spers_e_id 
   INNER JOIN
      FworkSQLEcm.dbo.udf_job1 
      ON udf_job1.l_job_id = job.l_job_id 
   INNER JOIN
      list_item AS jobStatus 
      ON job.l_job_status_gl_id = jobStatus.l_list_item_id 
   INNER JOIN
      rDiv 
      ON job.l_context_id = rDiv.l_context_id 
WHERE
   doc.d_signed_act IS NOT NULL 
   AND udf_job1.d_contchecked IS NOT NULL 
   AND cont.c_retail_internal > 0

Request 4: Contracts not Converted based on Contract Checked date and Contract Signed = null

Updated on 20/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports.pbix tab Contracts not Converted.

SELECT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   udf_job1.d_contchecked AS contract_checked,
   cont.c_retail_internal AS Base_house_price,
   entity.s_name AS Name_Salesperson 
FROM
   FworkSQLEcm.dbo.doc 
   INNER JOIN
      FworkSQLEcm.dbo.job 
      ON job.l_job_id = doc.l_job_id 
   INNER JOIN
      FworkSQLEcm.dbo.job_cont 
      ON job_cont.l_job_id = job.l_job_id 
   INNER JOIN
      FworkSQLEcm.dbo.cont 
      ON cont.l_cont_id = job_cont.l_cont_id 
   INNER JOIN
      FworkSQLEcm.dbo.cont_comm 
      ON cont_comm.l_cont_comm_id = cont.l_comm_prim_id 
   INNER JOIN
      FworkSQLEcm.dbo.entity 
      ON entity.l_entity_id = cont_comm.l_spers_e_id 
   INNER JOIN
      FworkSQLEcm.dbo.udf_job1 
      ON udf_job1.l_job_id = job.l_job_id 
   INNER JOIN
      list_item AS jobStatus 
      ON job.l_job_status_gl_id = jobStatus.l_list_item_id 
   INNER JOIN
      rDiv 
      ON job.l_context_id = rDiv.l_context_id 
WHERE
   doc.d_signed_act IS NULL 
   AND udf_job1.d_contchecked IS NOT NULL 
   AND cont.c_retail_internal > 0

Request 5: Sales location

Updated on 20/06/2019.

See Power Bi Desktop report \\fileprint\group\Weeks Group\Information Systems\Operations\99 Projects\Bi Reports WBG\CEO Reports\CEO Reports.pbix tab Sales Location.

SELECT DISTINCT
   job.s_job_num AS "Job Number",
   jobStatus.s_name AS "Job Status",
   rDiv.s_name AS Company,
   udf_job1.d_contchecked AS CHECK_DATE,
   ISNULL(addr.s_lot_num + ', ', '') + ISNULL(addr.s_street_num + ', ', '') + ISNULL(addr.s_street_name + ', ', '') + ISNULL(suburb.s_name + ', ', '') + ISNULL(state.s_name + ', ', '') + ISNULL(suburb.s_postcode, '') AS "Land FULL Address" 
FROM
   FworkSQLEcm.dbo.job 
   INNER JOIN
      FworkSQLEcm.dbo.doc 
      ON doc.l_job_id = job.l_job_id 
   INNER JOIN
      FworkSQLEcm.dbo.udf_job1 
      ON udf_job1.l_job_id = job.l_job_id 
   INNER JOIN
      FworkSQLEcm.dbo.client 
      ON client.l_client_id = doc.l_client_id 
   INNER JOIN
      FworkSQLEcm.dbo.addr 
      ON addr.l_addr_id = client.l_addr_id 
   LEFT JOIN
      FworkSQLEcm.dbo.land 
      ON land.l_addr_id = addr.l_addr_id 
   LEFT JOIN
      FworkSQLEcm.dbo.suburb 
      ON suburb.l_suburb_id = addr.l_suburb_id 
   LEFT JOIN
      FworkSQLEcm.dbo.state 
      ON state.l_state_id = suburb.l_state_id 
   INNER JOIN
      list_item AS jobStatus 
      ON job.l_job_status_gl_id = jobStatus.l_list_item_id 
   INNER JOIN
      rDiv 
      ON job.l_context_id = rDiv.l_context_id 
WHERE
   client.l_client_id > 0 
   AND doc.d_signed_act IS NULL 
   AND udf_job1.d_contchecked IS NOT NULL

Other Requests

projects/ceo_bi_reports.txt · Last modified: 2019/06/20 10:39 by 192.168.2.152