question

TedBest-1978 avatar image
0 Votes"
TedBest-1978 asked TedBest-1978 answered

Adding Calculations to Matrix total

102251-image.png102233-image.png

This is killing me. Any help on this would be greatly appreciated. How would I add some calculated fields to a group in a Matrix? For example I would like to add Yield Ratio which would be the # Binds/# Submissions. Hit ratio would be # Binds /# Quotes


sql-server-reporting-services
image.png (9.4 KiB)
image.png (29.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Joyzhao-MSFT avatar image
1 Vote"
Joyzhao-MSFT answered Joyzhao-MSFT edited

Hi @TedBest-1978 ,
I tested it locally. I think the method worth trying is to use TSQL to complete the calculation.
Here are the steps :
1.I created a Tablix named T1 in SSMS. As shown in your image.

2.Execute the following statement when creating the Dataset:

 select * from T1
 union
 select a.YearMonth,'Yield Ratio' Type,cast(a.Count as float)/cast(b.count as float) count,5
 from (select * from T1
 where Type='Binds') a
 inner join (select * from T1
 where Type='Submission') b
 on a.YearMonth=b.YearMonth
 union
 select a.YearMonth,'Hit ratio' Type,cast(a.Count as float)/cast(b.count as float) count,6
 from (select * from T1
 where Type='Binds') a
 inner join (select * from T1
 where Type='Quoted') b
 on a.YearMonth=b.YearMonth

102279-04.jpg

3.Create a matrix and sort by "SortOrder", as shown in the figure below:
Design:
102322-02.jpg
Preview:
102323-03.jpg
Best Regards,
Joy


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


04.jpg (73.1 KiB)
02.jpg (5.4 KiB)
03.jpg (38.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TedBest-1978 avatar image
0 Votes"
TedBest-1978 answered

Thank you so much Joy! That was very helpful!

I was wondering if you could help me with one other thing

The yellow highlighted field (Average TWP) is a calculated field using values in the New Business, Renewals & Total WP group. I tried the following calculated field but it doesn't work. I'm assuming because I'm looking for a value in a different group. Is there a way to pull that value from a different group?

Also is there a way to add a blank line in between one more more of the groups but not all in a Matrix? (Green arrow)

 =Switch(Fields!Section.Value = "New Business",VAL(REPLACE(Sum(IIF(Fields!Type.Value = "Declines/Not Quoted", Fields!Value.Value, 0))/Sum(IIF(Fields!Type.Value = "Submission", Fields!Value.Value, 0)),"NaN",0)),
     Fields!Section.Value = "Renewals",VAL(REPLACE(Sum(IIF(Fields!Type.Value = "Renewals Declined", Fields!Value.Value, 0))/Sum(IIF(Fields!Type.Value = "Renewals Expiring", Fields!Value.Value, 0)),"NaN",0)),
     ***Fields!Section.Value = "Total WP",Sum(IIF(Fields!Type.Value = "CY TWP", Fields!Value.Value, 0))/(Sum(IIF(Fields!Type.Value = "Binds", Fields!Value.Value, 0))+Sum(IIF(Fields!Type.Value = "Renewals Bound", Fields!Value.Value, 0)))***)

105453-image.png










image.png (58.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.