Python Code import json import requests # 3rd party import jsonpath from pyecharts.charts import Map from pyecharts import options as opts from src.covid19_data import nameMap url = 'https://api.inews.qq.com/newsqa/v1/automation/foreign/country/ranklist' resp = requests.post(url).text data = json.loads(resp) # get contry names and confirmed cases $：outer {}， ..: fuzzy match + key name = jsonpath.jsonpath(data, "$..name") # ['美国', '巴西', '印度', '俄罗斯', ... ] confirm = jsonpath.jsonpath(data, "$..confirm") pairs = list(zip(name, confirm)) # [('美国', 5321520), ('巴西', 3109630), ('印度', 2329638), ...] # 2. data visualization map plotting - size,title,color,data map_ = Map(opts.InitOpts(width='1200px', height='600px')) .add(series_name="Global Covid-19 Confirmed Cases", data_pair=pairs, # data entry - country : confirmed maptype="world", # world map name_map=nameMap, # country name mapping is_map_symbol_show=False) # 不显示标记点 # set series options map_.set_series_opts(label_opts=opts.LabelOpts(is_show=False)) map_.set_global_opts(title_opts=opts.TitleOpts(title="Global Covid-19 Pandemic (Chinese)"), visualmap_opts=opts.VisualMapOpts(max_=6000000, is_piecewise=True)) map_.render('../result/covid19.html') covid19_data nameMap = { 'Singapore Rep.': '新加坡', 'Dominican Rep.': '多米尼加', 'Palestine': '巴勒斯坦', 'Bahamas': '巴哈马', 'Timor-Leste': '东帝汶', 'Afghanistan': '阿富汗', 'Guinea-Bissau': '几内亚比绍', "Côte d'Ivoire": '科特迪瓦', 'Siachen Glacier': '锡亚琴冰川', "Br. Indian Ocean Ter.": '英属印度洋领土', 'Angola': '安哥拉', 'Albania': '阿尔巴尼亚', 'United Arab Emirates': '阿联酋', 'Argentina': '阿根廷', 'Armenia': '亚美尼亚', 'French Southern and Antarctic Lands': '法属南半球和南极领地', 'Australia': '澳大利亚', 'Austria': '奥地利', 'Azerbaijan': '阿塞拜疆', 'Burundi': '布隆迪', 'Belgium': '比利时', 'Benin': '贝宁', 'Burkina Faso': '布基纳法索', 'Bangladesh': '孟加拉国', 'Bulgaria': '保加利亚', 'The Bahamas': '巴哈马', 'Bosnia and Herz.': '波斯尼亚和黑塞哥维那', 'Belarus': '白俄罗斯', 'Belize': '伯利兹', 'Bermuda': '百慕大', 'Bolivia': '玻利维亚', 'Brazil': '巴西', 'Brunei': '文莱', 'Bhutan': '不丹', 'Botswana': '博茨瓦纳', 'Central African Rep.': '中非共和国', 'Canada': '加拿大', 'Switzerland': '瑞士', 'Chile': '智利', 'China': '中国', 'Ivory Coast': '象牙海岸', 'Cameroon': '喀麦隆', 'Dem. Rep. Congo': '刚果（金）', 'Congo': '刚果（布）', 'Colombia': '哥伦比亚', 'Costa Rica': '哥斯达黎加', 'Cuba': '古巴', 'N. Cyprus': '北塞浦路斯', 'Cyprus': '塞浦路斯', 'Czech Rep.': '捷克', 'Germany': '德国', 'Djibouti': '吉布提', 'Denmark': '丹麦', 'Algeria': '阿尔及利亚',…

Sampling method is the most efficient and popular data validation method, even it has little coverage with lots of risk. Here I would introduce an feature points sampling method. The reason to add "feature points" is to avoid generic random points that may not cover as much as possible for the report validation. Feature points sampling method takes some special points and generic points to validate a reporting result, and ensure these points have most coverage of the reporting range. Feature points contains less data, and could be validated by human calculation easily. The results of these points can validate the query logic correctness. Once the query logic being validated we could add more complex data combination as generic points and finally compare the query results and report results. Let's take a real automation report as an example. The data come from the log of BluePrism RPA. The existing ETL process has been tested already. I am going to focus on the report result against local data to verify the report result correctness. The report includes daily, weekly, monthly, and yearly. In this article I'm gonna use monthly report for validation. Here're the points used in the verification. Point StartMth EndMth LOB Process Point Note 1 2018-10 2018-10 BM hotspots feature specific lob and process_name at certain time point 2 2018-10 2018-10 BM Shipped not Activate feature different specific lob and process_name at certain time point 3 2018-10 2018-10 BM [ALL] generic same lob and time point, but all process_names 4 2018-11 2018-11 BRS Disconnection feature different specific lob and process_name…

Taking report from web page function sendReport() { var dataInput = {}; reportBody = $(".dataTables_scroll"); reportBody.prepend('<link rel="stylesheet" href="' + location.protocol + '//' + window.location.host + '/Scripts/Main/BusinessSuite.css" type="text/css" />'); reportBody.prepend('<link rel="stylesheet" href="' + location.protocol + '//' + window.location.host + '/Scripts/Libs/BootStrap/css/bootstrap.css" type="text/css" />'); reportBody.prepend('<link rel="stylesheet" href="' + location.protocol + '//' + window.location.host + '/Scripts/Libs/JQueryConfirm/css/jquery-confirm.css" type="text/css" />'); reportBody.prepend('<link rel="stylesheet" href="' + location.protocol + '//' + window.location.host + '/Scripts/Libs/JQueryDatatables/css/jquery.dataTables.css" type="text/css" />'); reportBody.prepend('<link rel="stylesheet" href="' + location.protocol + '//' + window.location.host + '/Css/Table/tableenhacements.css" type="text/css" />'); reportBody.prepend('<link rel="stylesheet" href="' + location.protocol + '//' + window.location.host + '/Scripts/Libs/JQueryUI/jquery-ui.css" type="text/css" />'); dataInput.ReportName = $("#ddlReports option:selected").attr("data-report-name"); dataInput.ReportPage = reportBody.html().replace(/height: 0px;/g, 'height: 30px;padding:5px 0 !important;') .replace(/dataTables_scrollHeadInner"/g, 'dataTables_scrollHeadInner" style="display:none"') .replace(/<table/g, '<table style="font-size:12px !important;"') .replace(/<th/g, '<th style="align:left;"') .replace(/<div class="dataTables_scrollHeadInner".*?(<\/div>)/gm,''); var ajaxParameters = { methodUrl: "Mail/SendReport", dataInput: dataInput }; new BaseClass().AjaxCall( ajaxParameters, function () { }, function (data) { } ); return false; Stored Proc: ALTER proc [dbo].[usp_BS_SendMail]( @PRM_LAN_ID VARCHAR(100)='', @REPORT_NAME nvarchar(150) =null, @REPORT_PAGE nvarchar(max) =null ) as if coalesce(@REPORT_NAME,'')='' or coalesce(@REPORT_PAGE,'')='' return; declare @typeid int,@subject nvarchar(255); select @typeid=ReportTypeID from [dbo].[tbl_BS_Reports_Configuration] where ReportName = @REPORT_NAME insert dbo.tbl_ReportPage (ReportTypeID, ReportName,ReportPage,CreatedBy) values (@typeid,@REPORT_NAME,@REPORT_PAGE,@PRM_LAN_ID) set @subject = 'Report: ' + @REPORT_NAME; EXEC msdb.dbo.sp_send_dbmail @profile_name = null, --@recipients = '"Vat, Moh" <abc.vat@ball.ca>;"Pill, Balak" <def.pill@ball.ca>', @recipients = 'xxxxx@gmail.com', @body = @REPORT_PAGE, @body_format = 'HTML', @subject = @subject; Calling sp using (var fnReturn = new DataTable()) { fnReturn.RunStoredProcedure(filters.Conn, "[dbo].[usp_BS_SendMail]", new Dictionary<string, object>() { { "@PRM_LAN_ID", filters.Lan_ID?? "" }, { "@REPORT_NAME", filters.ReportName}, { "@REPORT_PAGE", filters.ReportPage} }); return fnReturn; } ========================== report contents: <link href="http://localhost:1282/Scripts/Libs/JQueryUI/jquery-ui.css" rel="stylesheet" type="text/css"> <link href="http://localhost:1282/Css/Table/tableenhacements.css" rel="stylesheet" type="text/css"> <link href="http://localhost:1282/Scripts/Libs/JQueryDatatables/css/jquery.dataTables.css" rel="stylesheet" type="text/css"> <link href="http://localhost:1282/Scripts/Libs/JQueryConfirm/css/jquery-confirm.css" rel="stylesheet" type="text/css"> <link href="http://localhost:1282/Scripts/Libs/BootStrap/css/bootstrap.css"…

Problem: business requires an report in excel format to be automated in web application. Final web format: Original data format: Key points: Data must be summarized by date, week, month, year, and job name dimensions. Fact table contains dozen millions of records for several years; The reporting requires to rotate the data by 90 degrees to display summarized data Multiple data presentations in a single column, witch includes integer number, decimal, and percentage. Solution: Get data from data source OpenQuery for linked server does not accept variables, therefore we have to use dynamic query statement. set @openquery = ' SELECT [Date], [Day], JobName, Volume_to_Work, Volume_Worked, Volume_RPC FROM OPENQUERY([S1],'' SELECT cast(a.Date as Date) [Date], datename(dw,date) as [Day], a.JobName, a.Volume_to_Work, a.Volume_Worked, isnull(b.Volume_RPC,0) as Volume_RPC FROM ( –VOLUME select BACKUPDATE date,JobName,sum(VOLUME_SENT) as Volume_to_Work,sum(VOLUME_WORKED) as Volume_Worked from TBL_METRICS_RAPPORTVOLUME where JobName in ('''''+ replace( case @JOB_NAME_LIST when 'All|' then 'Ucm_Arics_ON_Preview|Ucm_Caprice_QC_Preview|UCM_OB_E_PREVIEW|UCM_OB_F_PREVIEW' else @JOB_NAME_LIST end ,'|',''''',''''') +''''' ) and year(BACKUPDATE) = '+cast(@REPORT_YEAR as varchar)+' group by JobName,BACKUPDATE ) a left join ( –RPC select Backupdate,session,count(session) as Volume_RPC from TBL_ACCOUNTS_WORKED a left join ( select RPC,Result from TBL_REFERENCE_DIALERCODES where site = ''''Internal'''' ) b on a.RELEASECODE= b.RESULT where session in ('''''+ replace( case @JOB_NAME_LIST when 'All|' then 'Ucm_Arics_ON_Preview|Ucm_Caprice_QC_Preview|UCM_OB_E_PREVIEW|UCM_OB_F_PREVIEW' else @JOB_NAME_LIST end ,'|',''''',''''') +''''' ) and year(Backupdate) = '+cast(@REPORT_YEAR as varchar)+' and b.RPC = 1 group by Backupdate,session ) b on a.Date = b.Backupdate and a.JobName = b.session where datename(dw,date) <> ''''Sunday'''''' )' create table #campaign ([Date] date,[Day] varchar(20),JobName varchar(255),Volume_to_work int,Volume_worked int,Volume_RPC int) insert #campaign exec(@openquery) generated openquery look like: SELECT [Date], [Day], JobName, Volume_to_Work, Volume_Worked, Volume_RPC FROM OPENQUERY([BREPP1],' SELECT cast(a.Date as Date)…

Power BI is the latest Microsoft BI tool. It was originated from Excel plug-ins, e.g. Power Query, Power Pivot, Power View, and rolled out last year (2015.7). Therefore, it is very similar to working on Excel. It is visualized self-service business intelligence tool for everyone.    Power BI provides 4 components. Power BI services     – you can create a real time online dashboard that can be accessed by anyone from everywhere in the world or by anyone in your organization. Power BI desktop      – run power BI on your desktop Power BI Mobile        – run power BI app on your mobile device Power BI Embedded – allow you to embed the interactive visuals into your app using REST APIs and Power BI SDK. Power BI accept lots of data sources, such as database, files, web page, cloud service…   Power BI allows you to quickly create interactive, responsive, pretty dashboard easily. Here is the sample dashboard.       Power BI provides lots of visualization styles that you can choose.     Power BI is a powerful and the simplest BI tool in current market. Everyone, especially Excel users can use it easily and build powerful, attractive dashboard. Can't wait to try ...