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.
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 |
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.