SSRS Field not Displaying Properly

Question

I have a stored procedure that pulls in data used for a report in SSRS. Please see the attached question that I posted to Stack Overflow here. My issue occurs when I use a field which does not seem to follow the matrix groupings.

My sample data:

    CREATE TABLE Table1  
        ([period] int, [state] varchar(2), [company] varchar(8), [rate] numeric, [rate_num] numeric, [rate_denom] numeric)  
    ;  
/*  
-- Alternate option  
  
CREATE TABLE Table1  
    ([period] int, [state] varchar(2), [company] varchar(8), [rate] DECIMAL(7,6), [rate_num] DECIMAL(7,2), [rate_denom] DECIMAL(10,2))  
;  
*/  
          
    INSERT INTO Table1  
        ([period], [state], [company], [rate], [rate_num], [rate_denom])  
    VALUES  
        (202004, 'ST', 'Company1', 0.000497, NULL, 10562.51),  
        (202004, 'ST', 'Company1', 0.000497, 50, 6984.57),  
        (202004, 'ST', 'Company1', 0.000497, 100, 223199.73),  
        (202004, 'ST', 'Company1', 0.000497, NULL, 60627.17),  
        (202005, 'ST', 'Company1', NULL, NULL, 4291.98),  
        (202005, 'ST', 'Company1', NULL, NULL, 420617.49),  
        (202005, 'ST', 'Company1', NULL, NULL, 49403.02),  
        (202005, 'ST', 'Company1', NULL, NULL, 64282.61),  
        (202005, 'ST', 'Company2', NULL, NULL, 2287.94),  
        (202005, 'ST', 'Company2', NULL, NULL, 63990.73),  
        (202005, 'ST', 'Company2', NULL, NULL, 455426.94),  
        (202006, 'ST', 'Company2', 0, NULL, 3000.9),  
        (202006, 'ST', 'Company2', 0, NULL, 2000.61),  
        (202006, 'ST', 'Company2', 0, 0, 60000),  
        (202007, 'ST', 'Company2', 0.000131, NULL, 11434.4),  
        (202007, 'ST', 'Company2', 0.000131, NULL, 2347.94),  
        (202007, 'ST', 'Company2', 0.000131, 97.5, 727027.39)  
    ;  

I have the rate field which I calculated in SQL. Rate_calc is an expression to calculate the rate in SSRS via the values I used to calculate the rate in SQL. I am using the following code for my matrix table data:

-- Rate_calc  
=IIF(Sum(Fields!rate_denom.Value) <= 0, "", Sum(Fields!rate_num.Value) / Sum(Fields!rate_denom.Value))  
  
-- Rate  
=IIF(IsNothing(Fields!rate.Value), "", Fields!rate.Value)  

My matrix groupings:

I get the output below which also contains my expected output. When using rate it seems to bypass the grouping and fill the rows which are specific to each individual company. For example 202004 only had data for Company1 but the same info shows for Company2. Neither company had data in 202005 and only Company2 had data in the remaining periods.

To clarify, my expected output only needs 1 rate calculation. I would prefer it use the rate field since I don't need to display the same information twice.

-- Output  
  
+----------+-------+-----------+--------+--------+--------+--------+  
| Company  | State |           | 202007 | 202006 | 202005 | 202004 |  
+----------+-------+-----------+--------+--------+--------+--------+  
| Company1 | ST    | Rate_Calc |        |        | 0.00%  | 0.05%  |  
|          |       | Rate      | 0.01%  | 0.00%  |        | 0.05%  |  
| Company2 | ST    | Rate_Calc | 0.01%  | 0.00%  | 0.00%  |        |  
|          |       | Rate      | 0.01%  | 0.00%  |        | 0.05%  |  
+----------+-------+-----------+--------+--------+--------+--------+  
  
  
-- Expected  
  
+----------+-------+----------+--------+--------+--------+--------+  
| Company  | State |          | 202007 | 202006 | 202005 | 202004 |  
+----------+-------+----------+--------+--------+--------+--------+  
| Company1 | ST    | Expected |        |        |        | 0.05%  |  
| Company2 | ST    | Expected | 0.01%  | 0.00%  |        |        |  
+----------+-------+----------+--------+--------+--------+--------+  

Am I using the rate field wrong? Otherwise I could try and use IsNothing but sometimes I need to SUM values for a period which may also contain a null value. Any suggestions on achieving the expected output are appreciated.

Finally, the SO post may provide this in a better format -- sorry if it does not format properly here this is my first post on this forum.

Answer 1

Hi,
Did not hear from you , did you resolve this issue ?

Answer 2

Hi,

Your design page is not quite the same as your out put. And your out put is not correspond to you expected result. So, this is not easy for outsiders to understand your structure and issue.

But I made one test with your sample data and expression. And I got seemly correct outcome.

This is my design you could understand the grouping by the yellow and grey square bracket.

Not sure if your design or raw data has issue. I cast the rate from string to decimal in the dataset query.
I changed a bit in the expression :

Rate_ Recal   =IIF(IsNothing(Fields!rate.Value) And IsNothing(Fields!rate_num.Value),"",IIF(Sum(Fields!rate_denom.Value) <= 0, "", Sum(IIF(IsNothing(Fields!rate_num.Value),Fields!rate_num.Value,0)) / Sum(Fields!rate_denom.Value)))  
Rate    =IIF(IsNothing(Fields!rate.Value), " ", Fields!rate.Value)  

Hope this could help.
Regards,
Lukas

---

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.