前言
本篇文章将介绍如何从Power BI报表中实现自定义备注信息的回传与实时显示,从而方便用户对报表内容进行反馈与评论。
在Power BI报表中回传数据到数据源中并不是一件简单的事情,之前已知的方案中都具有不少的问题,要么技术门槛较高比较难以实现,要么用户体验不佳,比如:
- Embedded嵌入门户,通过编程等方式在门户中添加输入与回传组件
- 借助Power Apps视觉对象将画布应用嵌入报表中,从而借助Power Apps来实现数据回传
- 在报表中添加问卷等第三方提交数据的工具的链接或二维码,跳转到外部应用,借助其它工具来实现数据回传
但目前,借助免费的ParaHTMLViewer视觉对象与PowerAutomate自动化云端流,即可实现用户体验较佳的数据回传方案,并且技术门槛也不高。其中,ParaHTMLViewer视觉对象可以提交数据到任意API接口中,考虑到使用编程语言开发API接口的难度,因此搭配使用PowerAutomate来创建API接口,并在API内部把数据回写到数据源中。最后,再通过Direct Query等方式连接到数据源,即可实现数据的回传与实时显示。
下面先来看一下最终的效果,如下图所示:
以上效果只是该方案的其中一种应用,本篇文章将基于此效果介绍其实现方式,理解原理后可以自行化用。其中,使用到的组件或工具有:
- Power BI
- SQL Server
- Power Automate
- ParaHTMLViewer视觉对象
实现的简要步骤如下:
1、根据需求,设计存储回传数据的表的表结构,并在SQL Server中创建该表,为方便描述,将其称为回传数据表
2、使用Power Automate创建API接口,在API内部将提交的数据写入到回传数据表
3、在报表中使用ParaHTMLViewer视觉对象提交自定义数据至上述API接口
4、Power BI使用DirectQuery模式连接到SQL Server中的回传数据表,获取实时数据
5、配置自动页面刷新
回传数据表的表结构设计
目标是为了在矩阵或表格中添加一个用户自定义备注信息的列,如下图所示,其中,客户城市和产品类别是作为维度的行标签,销售金额则是度量值。
回传数据表需要存储在矩阵或表格中添加的自定义备注信息,那么,不妨以矩阵的行标签,即客户城市和产品类别为组合主键,再添加一个备注信息字段即可。
如果回传数据表采取以上表结构,则客户城市与产品类别的同一个组合只能存在一行数据,每次回写时都需要在原地把备注信息更改最新的内容。虽然该方案也行得通,但无法保留备注信息的历史记录,并且无法识别该备注信息是谁填写的。因此,不妨再添加一个时间字段,以及填写人的字段,然后在矩阵中显示时间最新的那个备注信息即可。
综合考虑后,回传数据表的表结构确定为如下:
最后,在SQL Server数据库中按以上表结构创建表即可。在数据库中存储的数据示例如下:
回传数据表的表结构决定了后续数据回写时的方式,也会影响到报表中如何显示最新备注信息的逻辑,因此需要针对具体的应用场景进行分析与设计。
使用Power Automate创建API并回写数据
首先,登录到Power Automate的云端门户(make.powerautomate.com),创建自动化云端流。
在以下界面,不太方便选择触发器,先直接点击跳过,进入到编辑器后再来添加触发器。
进入编辑器界面后,先点击左上角处修改流的名称,然后在内置菜单栏里找到请求,并选择“当收到Http请求时”的触发器。
并按下图所示,进行触发器参数的配置。
其中,请求正文JSON架构的内容,可以点击“使用示例有效负载生成架构”,并把每次自报表中传递过来的JSON内容粘贴进去即可自动生成。
在报表中通过ParaHTMLViewer视觉对象提交到目标API的数据是以JSON格式来表示的,JSON中的字段名称是其HTML模板里对应的id属性的值,其示例如下:
{
"RemarkMessageCity": "北京市",
"RemarkMessageCategory": "手机配件",
"RemarkMessage": "xxxxxxxxx",
"RemarkMessageUser": "张三",
"RemarkMessageSubmitTime": "2024-1-1T00:00:00"
}
配置完触发器后,新增一个步骤,搜索“SQL Server”,找到“插入行”的操作。
然后点击右上角三点,选择添加新连接,并填写连接到SQL Server数据库的凭证信息。
然后,选择回传数据表,并把触发器里获取到的数据插入到回传数据表所对应的字段中。
然后,再添加一个新步骤,响应此次API请求的结果。在内置菜单栏下找到请求,并选择响应操作。
状态代码设置为200,并只在正文里填写Success即可。
最后,点击保存按钮,待保存完成后,回到第一个触发器步骤,此时会生成一个URL,此URL即为API接口,后续在ParaHTMLViewer视觉对象中会使用到。
ParaHTMLViewer视觉对象的配置与使用
ParaHTMLViewer视觉对象可以显示HTML或SVG格式的内容,而且大多数HTML标签都可以通过ID属性绑定到度量值,从而可以动态更改显示的内容,并且其支持提交数据到任意API中,最关键的是免费。
ParaHTMLViewer视觉对象在使用时需要先在模型中创建一个表来存储HTML内容,并在Category与Template中使用该HTML字段,其中Category提供上下文,若Category有多项,那么将针对每项按其对应的HTML Template生成内容,但由于最终只有第一个HTML Template可以绑定度量值,因此最佳实践就是创建一个单行单列的表来存储HTML内容,并在Category与Template中均使用该HTML字段。此外,Value字段窗口可以放置一些度量值,若HTML标签的ID属性的值与这些度量值的名称一致,那么该度量值的值将绑定到该标签,其中度量值的名称可以是别名。
Html Template的内容可以从ParaHTMLViewer视觉对象的示例文件中获得,该示例文件可以在视觉对象商店中下载。
下面是从示例文件中提取的其中一个HTML Template:
"<!DOCTYPE html>
<html>
<head>
<link rel=""stylesheet"" href=""https://unpkg.com/purecss@1.0.1/build/pure-min.css"">
</head>
<body>
<h2>Pure Form Two</h2>
<p>Pure CSS test two the measure is impressions and email address, USERNAME()</p>
<form class=""pure-form pure-form-stacked"">
<fieldset>
<legend>A Stacked Form</legend>
<label for=""message"">Message </label>
<input type=""text"" id=""message"" placeholder=""Enter something here..."" />
<label for=""impressions"">impressions</label>
<input type=""text"" id=""impressions"" placeholder=""Enter something here..."" />
<label for=""email"">Email</label>
<input type=""email"" id=""email"" placeholder=""Email"" />
<span class=""pure-form-message"">This is a required field.</span>
<label for=""duedate"">Due Date</label>
<input type=""date"" id=""duedate""/>
<button type=""submit"" class=""pure-button pure-button-primary"">Send</button>
</fieldset>
</form>
<p id=""demo"">When you click it will submit the Form to the target URL</p>
</body>
</html>"
该HTML Template经过渲染后的样式为:
将渲染结果与HTML Template的内容对照着来看,可以看出每个输入框都对应着两个标签,其中Label标签是输入框的标题,Input标签则控制输入框。因此即使不懂HTML也无妨,可以照猫画虎,按回传数据表的表结构来修改每个输入框及其标题,从而得到所需要的表单内容。
下面是自定义修改后的HTML Template的内容,其中引用的CSS文件的内容与官方的一致,这里为了避免后续该视觉对象无法使用或其他情况,特地将官方CSS文件进行了克隆。
"<!DOCTYPE html>
<html>
<head>
<link rel=""stylesheet"" href=""https://img.xifenghhh.top/OtherCSSAndJS/pure-min.css"">
</head>
<body>
<h2>备注信息填写</h2>
<form class=""pure-form pure-form-stacked"">
<fieldset>
<legend>点击表格中的某一行,填写或修改相应的备注信息!</legend>
<label for=""RemarkMessageCity"">客户城市 </label>
<input type=""text"" id=""RemarkMessageCity"" placeholder=""Enter something here..."" />
<label for=""RemarkMessageCategory"" >产品类别</label>
<input type=""text"" id=""RemarkMessageCategory"" placeholder=""Enter something here..."" />
<label for=""RemarkMessage"" >填写备注:</label>
<input type=""text"" id=""RemarkMessage"" />
<label for=""RemarkMessageUser"" >用户</label>
<input type=""text"" id=""RemarkMessageUser"" placeholder=""User"" />
<label for=""RemarkMessageSubmitTime"">提交时间</label>
<input type=""text"" id=""RemarkMessageSubmitTime"" placeholder=""SubmitTime"" />
<button type=""submit"" class=""pure-button pure-button-primary"">提交</button>
</fieldset>
</form>
</body>
</html>"
该HTML Template经过渲染后的样式为:
然后,为了实现点击表格中的某一行时,能够将该行的值自动填充到表单的对应输入框中,还需要创建一些度量值来获取被选中的行的对应数据,最后再把度量值绑定到表单的输入框中即可。创建的度量值具体如下:
RemarkMessageCity = IF(SELECTEDVALUE('客户城市'[客户城市])==BLANK(),"Select an row to start!",SELECTEDVALUE('客户城市'[客户城市]))
RemarkMessageCategory = IF(SELECTEDVALUE('产品表'[产品类别])==BLANK(),"Select an row to start!",SELECTEDVALUE('产品表'[产品类别]))
RemarkMessage =
IF(ISFILTERED('产品表'[产品类别])&&ISFILTERED('客户城市'[客户城市]),
MAXX(TOPN(1,'RemarkMessage','RemarkMessage'[UpdatedTime]),'RemarkMessage'[Remark]),
IF(ISINSCOPE('产品表'[产品类别])&&ISINSCOPE('客户城市'[客户城市]),BLANK(),"Select an row to start!")
)
RemarkMessageUser = USERNAME()
RemarkMessageSubmitTime = UTCNOW()+TIME(8,0,0)
其中RemarkMessage度量值由于需要在表格与表单中同时使用,因此判断条件更多一些。最后,把上面的度量值放到ParaHTMLViewer视觉对象的值字段中即可。
这里由于度量值的名称与HTML Template内容中各个Input标签的ID属性的值一致,因此不需要进行别名的重命名,但如果度量值真正的名称与ID属性的值不一致,则需要对度量值设置别名。双击值字段中的度量值即可设置别名,如下图所示:
然后,将Power Automate生成的API接口填入到Target URL中,如下图所示:
此时,点击提交按钮就会将输入框中的数据作为参数发送到API接口中,从而触发Power Automate里配置的流,将对应的数据回写到数据库中。
最后,为了美观或防止用户修改客户城市与产品类别的值,可以通过CSS代码(style="display:none")将表单中的一些输入框进行隐藏,只保留备注信息的输入框即可。隐藏的CSS代码可以直接在HTML Template中修改,具体内容如下:
"<!DOCTYPE html>
<html>
<head>
<link rel=""stylesheet"" href=""https://img.xifenghhh.top/OtherCSSAndJS/pure-min.css"">
</head>
<body>
<h2>备注信息填写</h2>
<form class=""pure-form pure-form-stacked"">
<fieldset>
<legend>点击表格中的某一行,填写或修改相应的备注信息!</legend>
<label for=""RemarkMessageCity"" style=""display:none"">客户城市 </label>
<input type=""text"" id=""RemarkMessageCity"" placeholder=""Enter something here..."" style=""display:none""/>
<label for=""RemarkMessageCategory"" style=""display:none"">产品类别</label>
<input type=""text"" id=""RemarkMessageCategory"" placeholder=""Enter something here..."" style=""display:none""/>
<label for=""RemarkMessage"" style=""display:none"">填写备注:</label>
<input type=""text"" id=""RemarkMessage"" />
<label for=""RemarkMessageUser"" style=""display:none"">用户</label>
<input type=""text"" id=""RemarkMessageUser"" placeholder=""User"" style=""display:none""/>
<label for=""RemarkMessageSubmitTime"" style=""display:none"">提交时间</label>
<input type=""text"" id=""RemarkMessageSubmitTime"" placeholder=""SubmitTime"" style=""display:none""/>
<button type=""submit"" class=""pure-button pure-button-primary"">提交</button>
</fieldset>
</form>
</body>
</html>"
对应的渲染结果为:
至此,大部分配置都已完成,但为了使回传的数据能够实时展示,还需要配置页面的自动刷新。
配置自动页面刷新
为了使回传的数据能够实时展示,首先需要使用Direct Query模式来连接回传数据表。
只有存在Direct Query模式的表,才会有页面刷新的配置选项,该选项的所在位置如下图:
其中,自动刷新的时间间隔会受到许可证类型的影响,最小刷新间隔可以在云端Server的租户设置中进行更改。关于自动页面刷新的使用与限制,可以查阅官方文档:PowerBI 中的自动页面刷新,这里不再赘述。
总结
通过ParaHTMLViewer视觉对象可以在报表中调用API,使Power BI可以与其他第三方组件进行关联,而且通过绑定度量值的方式,还可以动态的传递参数,因此可以实现很多有趣的功能,并不局限于数据回传。最重要的是该视觉对象还是免费的,可以说是必须要掌握的一个视觉对象了。