英文:
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
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
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()
表达式以获取您的评级摘要。
布局如下所示...
报告运行时,看起来像这样...(我在几分钟内快速制作的,所以不太美观! :))
另一种方法是创建一个简单的表格,按客户进行分组,然后在每个组内插入两个表格控件。
很难描述这个过程,但从仅按客户分组的外部表格开始。
下面的屏幕截图中选择了外部表格,下面的行组属于此表格。
我添加了一些空白行,然后在一个单元格中,右键单击并选择“插入 -> 表格”。
接下来的两张图片显示了第一个和第二个表格,同样,行组与所选表格相关。
运行报告时,我们得到以下结果...
如果您遇到困难,请告诉我,我会在某个地方分享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..
And the report runs, it looks like this... (I threw this togther in a few mins so not pretty! )
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.
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.
When we run the report we get the following...
If you are struggling, let me know and I will share the rdl files somewhere for you to download.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论