SSRS Field not Displaying Properly

Kottn 176 Reputation points
2020-10-21T17:11:47.117+00:00

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:
34420-image.png

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.

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,727 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,516 questions
0 comments No comments
{count} votes

Accepted answer
  1. Lukas Yu -MSFT 5,816 Reputation points
    2020-10-30T07:34:48.753+00:00

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

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Lukas Yu -MSFT 5,816 Reputation points
    2020-10-22T03:32:47.897+00:00

    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.
    34087-image.png

    This is my design you could understand the grouping by the yellow and grey square bracket.
    34204-image.png
    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.

    2 people found this answer helpful.