...
本示例在电子表格中实现资产负债表,表格中如“流动资产”、“货币资金”、“短期借款”等数据,都是直接从数据库中获取。当切换年度指标后,点击刷新按钮,数据也随之变化。如下:
切换参数时:
实现步骤
1.数据准备
资产负债表中的所有数据储存于balance_sheet表中,如下:
...
参数备选值及默认值设置如下:
参数相关设置项请参考:参数定义
4.填写命名SQL映射
在 定制管理 ->资源定制 节点下,找到 system >分析报表>SQL映射表,填入两个命名SQL,如下:
其中,命名SQL分别为“期初”和“期末”
- “期初”命名SQL,实现的作用是在数据库中查询出qichu数据,“期末”命名SQL,实现的作用是在数据库中查询出qimo数据
- sql中 select qichu from balance_sheet where f_year=? And kemu=trim( ? ) 的两个?表示需要从公式中传入值代替?所在的位置
5.创建电子表格
在Excel中登录服务,新建电子表格,并填入资产负债表的各个指标,如下:
56.设置年度参数
选中D4单元格,点击工具栏上的填报属性按钮,选择“可修改”、“绑定参数”,在弹出的选择参数窗口中选择步骤3中建立的“年”参数
填报属性相关设置项请参考:填报属性
67.设置“流动资产”指标的年初余额
选中单元格B6,填入公式=SSR_ExecSQLExecNamedSQL("DS.回写","select qichu from balance_sheet where f_year=? And kemu=trim(?)期初",D4,A6)
其中D4单元格对应的是年参数的参数值,假设参数值选择“2010”;A6单元格对应的是“流动资产”
则该公式实现的功能是:在数据源“回写”所连接的数据库中,执行sql语句“select qichu from balance_sheet where f_year="2010" And kemu=trim("流动资产”)"则该公式实现的功能是:在数据源“回写”所连接的数据库中,执行命名SQL 期初,并将返回的唯一结果数据,如“134002”,填写到单元格B6中。
函数说明:
SSR_ExecSQLExecNamedSQL
函数原型:SSR_ExecSQLExecNamedSQL(datasourceid,sqlsqlName,rowIndex,columnIndex,param1,param2,...)
函数功能:执行SQL语句,将查询结构填入单元格中执行命名SQL,将查询结果填入单元格中。
参数说明:datasourceid为数据源ID;sql为需要执行的sql语句;rowIndex为结果集中的行位置,可以忽略不填写;columnIndex为结果集中的列位置,可以忽略不填写;param1为SQL中的参数值,可以是静态数据也可以指定单元格;param2同理,可以传多个参数。datasourceid为数据源ID;sqlName为需要执行的命名SQL,命名SQL可在system->分析报表->映射SQL表中查看;rowIndex为结果集中的行位置,可以忽略不填写;columnIndex为结果集中的列位置,可以忽略不填写;param1为SQL中的参数值,可以是静态数据也可以指定单元格;param2同理,可以传多个参数。
函数详解:
(1)该函数实现的功能是在数据库中执行sql,并将结果集数据填写到单元格中(1)该函数实现的功能是在数据库中执行命名sql中的sql语句,并将结果集数据填写到单元格中。
(2)公式中datasourceid,可以通过在资源定制-》数据源节点中找到对应的数据源,右键-》属性 窗口中的“节点ID”获取
(3)若sql中需要对数据进行过滤,可将sql语句直接填写到公式中,如=SSR_ExecSQL("DS.回写","select qichu from balance_sheet where f_year='2010' And kemu='流动资产'")
- 过滤条件中的值也可以进行传递,在sql中以 ?(问号)代替值,并且在公式中传递值,传递的类型可以是静态数据,也可以是单元格位置。
如:
=SSR_ExecSQL("DS.回写","select qichu from balance_sheet where f_year=? And kemu=trim('流动资产')","2010"),表示将静态数据“2010”传递给第一个?的位置
=SSR_ExecSQL("DS.回写","select qichu from balance_sheet where f_year=? And kemu=trim('流动资产')",D4),表示将D4单元格对应的值传递给第一个?的位置
- sql中若有多个过滤条件中的值需要传递,则在sql中以均以 ? 代替,公式中再传递多个值。
如:
=SSR_ExecSQL
(3)命名sql可以在system->分析报表->SQL 映射表中查看到。在公式中使用命名SQL的名称,如“期初”,则后台执行时会数据库中执行命名SQL对应的语句。
(4)若执行sql中需要对数据进行过滤,可在命名SQL的sql语句中直接写入条件,如:
- 过滤条件中的值也可以进行传递,在sql中以 ?(问号)代替值,并且在公式中传递值,传递的类型可以是静态数据,也可以是单元格位置。
如:命名SQL对应的SQL语句如下:
公式为:=SSR_ExecNamedSQL("DS.回写","select qichu from balance_sheet where f_year=? And kemu=trim(?)期初",D4,A6),表示将D4单元格对应的值传递给第一个?的位置,A6单元格的值传递给第二个?的位置
...
- 传递的值类型:若传递给?的是单元格位置,则传递的值得类型由单元格的格式决定,单元格格式为字符串,则传递的值类型为字符串,若单元格的值为整形,则传递的值类型为整形;若传递给?的是静态数据,则传递的值类型均为字符串。
(4)rowIndex和columnIndex为结果集中的行位置和列位置,(5)rowIndex和columnIndex为结果集中的行位置和列位置,执行sql返回的结果集可能有多组数据,需要根据情况忽略或指定填入单元格的数据在结果集中的位置。行位置和列位置从0开始计数
- 若返回的结果集只有一行一列,则rowIndex和columnIndex均可省略,也可输入该数据的行位置0 和列位置0
如:=SSR_ExecSQLExecNamedSQL("DS.回写","select qichu from balance_sheet where f_year='2010' And kemu='流动资产'期初",0,0) 表示将执行sql返回的结果集中的第一行第一列的数据填入到单元格中
由于该sql执行返回的数据只有一个,因此可以同时省略rowIndex和columnIndex,也可用以下公式替代:=SSR_ExecSQLExecNamedSQL("DS.回写","select qichu from balance_sheet where f_year='2010' And kemu='流动资产'期初")
- 若执行sql返回的存在多组,则需指定行位置和列位置
如:=SSR_ExecSQLExecNamedSQL("DS.回写","select qichu,qimo from balance_sheet where f_year='2010' And kemu='流动资产'期初",2,1) 表示将执行sql返回的结果集中的第3行第2列的数据填入到单元格中
78.设置“流动资产”指标的“年末余额”
选中单元格C6,输入公式=SSR_ExecSQLExecNamedSQL("DS.回写","select qichu from balance_sheet where f_year=? And kemu=trim(?)期末",D4,A6)
89.设置剩余指标数据
分别选中B6和C6单元格,复制到其他单元格,其他单元格会相应改变最后一个传递的单元格位置。也可手动输入公式,如下:
910.添加刷新按钮
(1)工具栏切换到“开发工具”菜单,插入“按钮”
...
点击工具栏上的“发布预览”按钮,保存电子表格。预览。
资源下载
报表资源:资产负债表.xml
建表语句及数据:balance_sheet.sql(注:文件字符集为GBK)