Power BI Paginated Reports / SSRS:为每个客户重复表格。

huangapple go评论65阅读模式
英文:

Power BI Paginated Reports / SSRS : Repeat tables for each customer

问题

I'm trying to create a Paginated Report in PBI Report Server.
Below is the layout that I want to achieve.

the table1 and table2 should be repeated for each customer dynamically.
All the data is coming from the same dataset (i.e., a single table)

Any help would be appreciated.

Customer#1
attribute1

Table1


Table2

Customer#2
attribute1

Table1


Table2

Below is how the final output should look like in the Paginated Report

Power BI Paginated Reports / SSRS:为每个客户重复表格。

Below is the sample data

limit_instrument_desc limit_time_band_desc limit_original_amount customer gib_rating sp_rating moodys_rating group_industry_code
Total Exposure 1Y 766996330.805987 CustomerA 51
All 1Y 894384273.139102 CustomerA 51
All Inf 186847061.527764 CustomerA 51
UnsignedExpo 1Y 0 CustomerA 51
Treasury 1Y 73674799.171963 CustomerA 51
Money Market 1W 148988667.957832 CustomerA 51
Money Market 1M 126840196.5805 CustomerA 51
Money Market 3M 34510269.2820345 CustomerA 51
Commercial 1Y 784862506.199867 CustomerA 51
Commercial Inf 35847481.4481261 CustomerA 51
Guarantees Issued 1Y 0 CustomerA 51
Over Draft 1Y 0 CustomerA 51
Investment 1Y 12611360.8837044 CustomerA 51
Investment Inf 18324428.2383007 CustomerA 51
Bond Senior 1Y 3218721.44708836 CustomerA 51
Bond Senior 5.5Y 16214789.0762881 CustomerA 51
Total Exposure 1Y 61922330.3734412 CustomerA 51
All 1Y 887568667.12561 CustomerA 51
All Inf 88616749.299263 CustomerA 51
UnsignedExpo 1Y 0 CustomerA 51
Treasury 1Y 156798592.511412 CustomerA 51
Money Market 1W 34376291.5741296 CustomerA 51
Money Market 1M 79754205.0821452 CustomerA 51
Money Market 3M 118497267.312187 CustomerA 51
Commercial 1Y 93298935.8880307 CustomerA 51
Commercial Inf 40336015.7818662 CustomerA 51
Guarantees Issued 1Y 0 CustomerA 51
Over Draft 1Y 0 CustomerA 51
Investment 1Y 6136794.35933319 CustomerA 51
Investment Inf 29273009.6458268 CustomerA 51
Bond Senior 1Y 32129960.5494631 CustomerA 51
Bond Senior 5.5Y 7750546.55290664 CustomerA 51
0 0 0 CustomerA 51
0 0 0 CustomerA 51
0 0 0 CustomerA 3+ 51
0 0 0 CustomerA 3+ A A1 51
0 0 0 CustomerA 51
0 0 0 CustomerA 51
0 0 0 CustomerA 51
All 1Y 96333197.1475847 CustomerB 51
All Inf 30390748.5923403 CustomerB 51
Commercial 1Y 148552579.860602 CustomerB 51
Commercial Inf 130716081.77009 CustomerB 51
Over Draft 1Y 0 CustomerB 51
All 1Y 25999064.83092 CustomerB 51
All Inf 6595013.64393004 CustomerB 51
Commercial 1Y 154870082.260809 CustomerB 51
Commercial Inf 100453898.957276 CustomerB 51
0 0 0 CustomerB 3 51
0 0 0 CustomerB 3 A2 51
0 0 0 CustomerB 51
All 1Y 16491288.
英文:

I'm trying to create a Paginated Report in PBI Report Server.
Below is the layout that I want to achieve.

the table1 and table2 should be repeated for each customer dynamically.
All the data is coming from same dataset (i.e. single table)

Any help would be appreciated.


Customer#1
attribute1

Table1


Table2


Customer#2
attribute1

Table1


Table2

Below is how the final output should look like in Paginated Report

Power BI Paginated Reports / SSRS:为每个客户重复表格。

Below is sample data

limit_instrument_desc limit_time_band_desc limit_original_amount customer gib_rating sp_rating moodys_rating group_industry_code
Total Exposure 1Y 766996330.805987 CustomerA 51
All 1Y 894384273.139102 CustomerA 51
All Inf 186847061.527764 CustomerA 51
UnsignedExpo 1Y 0 CustomerA 51
Treasury 1Y 73674799.171963 CustomerA 51
Money Market 1W 148988667.957832 CustomerA 51
Money Market 1M 126840196.5805 CustomerA 51
Money Market 3M 34510269.2820345 CustomerA 51
Commercial 1Y 784862506.199867 CustomerA 51
Commercial Inf 35847481.4481261 CustomerA 51
Guarantees Issued 1Y 0 CustomerA 51
Over Draft 1Y 0 CustomerA 51
Investment 1Y 12611360.8837044 CustomerA 51
Investment Inf 18324428.2383007 CustomerA 51
Bond Senior 1Y 3218721.44708836 CustomerA 51
Bond Senior 5.5Y 16214789.0762881 CustomerA 51
Total Exposure 1Y 61922330.3734412 CustomerA 51
All 1Y 887568667.12561 CustomerA 51
All Inf 88616749.299263 CustomerA 51
UnsignedExpo 1Y 0 CustomerA 51
Treasury 1Y 156798592.511412 CustomerA 51
Money Market 1W 34376291.5741296 CustomerA 51
Money Market 1M 79754205.0821452 CustomerA 51
Money Market 3M 118497267.312187 CustomerA 51
Commercial 1Y 93298935.8880307 CustomerA 51
Commercial Inf 40336015.7818662 CustomerA 51
Guarantees Issued 1Y 0 CustomerA 51
Over Draft 1Y 0 CustomerA 51
Investment 1Y 6136794.35933319 CustomerA 51
Investment Inf 29273009.6458268 CustomerA 51
Bond Senior 1Y 32129960.5494631 CustomerA 51
Bond Senior 5.5Y 7750546.55290664 CustomerA 51
0 0 0 CustomerA 51
0 0 0 CustomerA 51
0 0 0 CustomerA 3+ 51
0 0 0 CustomerA 3+ A A1 51
0 0 0 CustomerA 51
0 0 0 CustomerA 51
0 0 0 CustomerA 51
All 1Y 96333197.1475847 CustomerB 51
All Inf 30390748.5923403 CustomerB 51
Commercial 1Y 148552579.860602 CustomerB 51
Commercial Inf 130716081.77009 CustomerB 51
Over Draft 1Y 0 CustomerB 51
All 1Y 25999064.83092 CustomerB 51
All Inf 6595013.64393004 CustomerB 51
Commercial 1Y 154870082.260809 CustomerB 51
Commercial Inf 100453898.957276 CustomerB 51
0 0 0 CustomerB 3 51
0 0 0 CustomerB 3 A2 51
0 0 0 CustomerB 51
All 1Y 16491288.6087766 CustomerC 116
All Inf 2472355.74665353 CustomerC 116
Investment 1Y 32314300.9405387 CustomerC 116
Investment Inf 67131490.8304831 CustomerC 116
Bond Senior 1Y 4336071.84047384 CustomerC 116
Bond Senior 5.5Y 3074577.78883941 CustomerC 116
Bond Senior 10.5Y 20408043.916635 CustomerC 116
All 1Y 10448193.178791 CustomerC 116
All Inf 56881298.8618535 CustomerC 116
Investment 1Y 2007532.20869549 CustomerC 116
Investment Inf 5373840.76691889 CustomerC 116
Bond Senior 1Y 27431454.1143082 CustomerC 116
Bond Senior 5.5Y 64748560.2235789 CustomerC 116
Bond Senior 10.5Y 62423495.1756199 CustomerC 116
0 0 0 CustomerC 4+ 116
0 0 0 CustomerC 4+ 116
0 0 0 CustomerA 51
0 0 0 CustomerA 51
0 0 0 CustomerA 51
0 0 0 CustomerA 51
0 0 0 CustomerA 51
0 0 0 CustomerA 51
0 0 0 CustomerA 51
0 0 0 CustomerA 51
0 0 0 CustomerA 51
0 0 0 CustomerC 116

答案1

得分: 1

以下是翻译好的内容:

有两种方法可以做到这一点。最简单的方法(如果您需要精确的布局可能不太适合您),是将所有内容放在一个单一的表中,按客户和仪器以及时间段列进行分组,然后在组之间插入一些空白行,在客户组的末尾,您可以添加MAX()表达式以获取您的评级摘要。

布局如下所示...

Power BI Paginated Reports / SSRS:为每个客户重复表格。

报告运行时,看起来像这样...(我在几分钟内快速制作的,所以不太美观! :))

Power BI Paginated Reports / SSRS:为每个客户重复表格。

另一种方法是创建一个简单的表格,按客户进行分组,然后在每个组内插入两个表格控件。

很难描述这个过程,但从仅按客户分组的外部表格开始。

下面的屏幕截图中选择了外部表格,下面的行组属于此表格。

Power BI Paginated Reports / SSRS:为每个客户重复表格。

我添加了一些空白行,然后在一个单元格中,右键单击并选择“插入 -> 表格”。

接下来的两张图片显示了第一个和第二个表格,同样,行组与所选表格相关。

Power BI Paginated Reports / SSRS:为每个客户重复表格。

Power BI Paginated Reports / SSRS:为每个客户重复表格。

运行报告时,我们得到以下结果...

Power BI Paginated Reports / SSRS:为每个客户重复表格。

如果您遇到困难,请告诉我,我会在某个地方分享rdl文件供您下载。

英文:

There are two ways you can do this. The simplest ( which might not be quite suitable for you if you need that exact layout), would be do to it all in a single table, group by customer and then the instrument and time band columns, then put some blank rows between the groups, at the end of the customer group you can put the MAX() expressions in to get your rating summary.

The layout looks like this..

Power BI Paginated Reports / SSRS:为每个客户重复表格。

And the report runs, it looks like this... (I threw this togther in a few mins so not pretty! Power BI Paginated Reports / SSRS:为每个客户重复表格。 )

Power BI Paginated Reports / SSRS:为每个客户重复表格。

The other way to do this would be to create a simple table that groups by customer and then insert two tablix controls within each group.

It's hard to describe the process but start with the outer table with just a simple group by customer.

The outer tablix is selected in the following screen shot, the row groups below belong to this tablix.

Power BI Paginated Reports / SSRS:为每个客户重复表格。

I added a few blank rows and then in one of the cells, right-clicked and did "Insert -> Table"

The next two images show the first and second tablix, again the row groups show relate to the selected tablix.

Power BI Paginated Reports / SSRS:为每个客户重复表格。

Power BI Paginated Reports / SSRS:为每个客户重复表格。

When we run the report we get the following...

Power BI Paginated Reports / SSRS:为每个客户重复表格。

If you are struggling, let me know and I will share the rdl files somewhere for you to download.

huangapple
  • 本文由 发表于 2023年5月17日 19:59:44
  • 转载请务必保留本文链接:https://go.coder-hub.com/76271853.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定