Hi Niket, with respect to the above Query i am continuing where Anjnee had left.
Below is the sample xml for that particular panel:
<panel>
<table>
<search id="monthly_summary" ref="cop_monthly_summary_savedsearch"></search>
</table>
</panel>
<row depends="$ou_level$">
<panel>
<table id="poc_monthly2">
<title>$sgccr_year$ YTD COP contract performance – Application Services - Summary at OU (as of $sgccr_month$ YTD)</title>
<search base="target_master">
<query>| table geography og ou contract_number contract_name primary_csg master_client_name |join contract_number [|savedsearch cop_sgccr_sg_savedsearch | where service_group="APPLICATION OUTSOURCING" OR service_group="SYSTEM INTEGRATION" | stats sum(ytd_nr) as r_ytd_nr_curr sum(ytd_cci) as ytd_cci_curr by contract_number | fillnull value=0 ]| join contract_number type=outer [|savedsearch cop_previousyr_sgccr_sg_savedsearch | where service_group="APPLICATION OUTSOURCING" OR service_group="SYSTEM INTEGRATION" | stats sum(q1_nr) AS "q1_nr" sum(q1_cci) AS "q1_cci" sum(q2_nr) AS "q2_nr" sum(q2_cci) AS "q2_cci" sum(q3_nr) AS "q3_nr" sum(q3_cci) AS "q3_cci" sum(q4_nr) AS "q4_nr" sum(q4_cci) AS "q4_cci" by contract_number | fillnull value=0 | eval q1_cci=if(isnull(q1_cci),0,q1_cci) |eval q2_cci=if(isnull(q2_cci),0,q2_cci)| eval q3_cci=if(isnull(q3_cci),0,q3_cci)| eval q4_cci=if(isnull(q4_cci),0,q4_cci)| eval q1_nr=if(isnull(q1_nr),0,q1_nr) |eval q2_nr=if(isnull(q2_nr),0,q2_nr)| eval q3_nr=if(isnull(q3_nr),0,q3_nr)| eval q4_nr=if(isnull(q4_nr),0,q4_nr) | eval fy_cci_prev = q1_cci + q2_cci + q3_cci + q4_cci | eval fy_nr_prev = q1_nr + q2_nr + q3_nr + q4_nr | eval forecast_month="1"|eval current_quarter_no=case(forecast_month=9 OR forecast_month=10 OR forecast_month=11,1,forecast_month=12 OR forecast_month=1 OR forecast_month=2,2,forecast_month=3 OR forecast_month=4 OR forecast_month=5,3,forecast_month=6 OR forecast_month=7 OR forecast_month=8,4) | eval ytd_nr_prev=case(current_quarter_no=1,q1_nr,current_quarter_no=2,q1_nr+q2_nr,current_quarter_no=3,q1_nr+q2_nr+q3_nr,current_quarter_no=4,q1_nr+q2_nr+q3_nr+q4_nr) | eval ytd_cci_prev=case(current_quarter_no=1,q1_cci,current_quarter_no=2,q1_cci+q2_cci,current_quarter_no=3,q1_cci+q2_cci+q3_cci,current_quarter_no=4,q1_cci+q2_cci+q3_cci+q4_cci)| fields - current_quarter_no forecast_month | fields - q* yearofpublish_sgccr ytd_nr ytd_cci] |stats sum(fy_cci_prev) as fy_cci_prev sum(fy_nr_prev) as fy_nr_prev sum(r_ytd_nr_curr) as r_ytd_nr_curr sum(ytd_cci_curr) as ytd_cci_curr sum(ytd_cci_prev) as ytd_cci_prev sum(ytd_nr_prev) as ytd_nr_prev by ou| eval fiscaly_prev = round(fy_cci_prev/fy_nr_prev *100,1) | eval yeartd_curr = round(ytd_cci_curr/r_ytd_nr_curr *100,1) | eval yeartd_prev = round(ytd_cci_prev/ytd_nr_prev *100,1) | fields - fy* ytd* | eval ytd_vs_prev = yeartd_curr-yeartd_prev|eval r_ytd_nr_curr = round(r_ytd_nr_curr,0) | eval r_ytd_nr_curr = tostring(r_ytd_nr_curr, "commas")|search ou="$primary_ou$"|table ou fiscaly_prev r_ytd_nr_curr yeartd_curr yeartd_prev ytd_vs_prev |rename fiscaly_prev as "AS Full Prev FY CCI%" r_ytd_nr_curr as "AS YTD actuals FY18 NR" yeartd_curr as "AS YTD actuals FY18 CCI%" yeartd_prev as "AS YTD Prev FY CCI%" ytd_vs_prev as "AS YTD FY18 CCI% actuals vs AS YTD Prev FY CCI%" contract_number as "Contract" ou as "Primary OU"</query>
<earliest>0</earliest>
<latest></latest>
</search>
<option name="wrap">true</option>
<option name="rowNumbers">false</option>
<option name="dataOverlayMode">none</option>
<option name="drilldown">cell</option>
<option name="count">10</option>
</table>
</panel>
</row>
The savedsearch had the below code:
| pivot cop_datapack Target_Master_Control_List latest(month) AS "month" latest(Status) AS "Status" latest(contract_name) AS "contract_name" latest(cop_owner_name) AS "cop_owner_name" latest(ao) AS "ao" latest(bpo) AS "bpo" latest(cop_wave) AS "cop_wave" latest(engagement_id) AS "engagement_id" latest(engagement_name) AS "engagement_name" latest(hri_list_status) AS "hri_list_status" latest(ic) AS "ic" latest(io) AS "io" latest(og) AS "og" latest(master_client_name) AS "master_client_name" latest(master_contract_name) AS "master_contract_name" latest(mc) AS "mc" latest(opera_contract) AS "opera_contract" latest(primary_csg) AS "primary_csg" latest(revised_rbe) AS "revised_rbe" latest(sc) AS "sc" latest(si) AS "si" latest(tc) AS "tc" latest(OU) AS "ou" latest(delivery_coach) AS "delivery_coach" latest(delivery_lead_name) AS "delivery_lead_name" latest(target_cci) as target_cci SPLITROW geography AS geography SPLITROW contract_number AS contract_number|where Status="Active"
... View more