Get 1 value of each date SSRS

Question

Ussing SSRS, I have data with duplicate values in Field1. I need to get only 1 value of each month.

   Field1   |        Date        |   
----------------------------------
    30      |     01.01.1990     |  
    30      |     01.01.1990     |  
    30      |     01.01.1990     |  
    50      |     02.01.1990     |  
    50      |     02.01.1990     |  
    50      |     02.01.1990     |  
    50      |     02.01.1990     |  
    40      |     03.01.1990     | 
    40      |     03.01.1990     |  
    40      |     03.01.1990     |

It should be ssrs expression with average value of each month or mb there are other solutions to get requested data by ssrs expression. Requested data in table:

    30      |     01.01.1990     | 
    50      |     02.01.1990     |  
    40      |     03.01.1990     |

Hope for help.

I guess you are getting this data from a database? Based on the samples, you just need to use `SELECT DISTINCT` — Nick.Mc, Nov 12 '16 at 13:33
Yes,but I have really big matrix with dataset more than 60 fields. This statement will not give result. Thats why i am trying to solve this problem with calculated field. — Dmitry, Nov 12 '16 at 14:00
If you are using a tablix you can set the property `HideDuplicates` to your DataSet name. — alejandro zuleta, Nov 12 '16 at 14:19
A report has a complex structure and uses all 60 fields of the data set. The table above is just an example of the desired result for the expression. I need expression with smth like: =SUM(IIF((Fields!Date.Value) ????, Fields!Field1.Value, nothing)) — Dmitry, Nov 12 '16 at 16:35
Why didn't you explain this in the original question? Maybe you should go back a step and understand why you have duplicates. Maybe you have an incorrect join. Posting the query would help. — Nick.Mc, Nov 12 '16 at 22:34

score 2 · Accepted Answer · edited Feb 21 '17 at 20:04

There is no SumDistinct function in SSRS, and it is real lack of it (CountDistinct exist although). So you obviously can't achieve what you want easy way. You have two options:

Implement a new stored procedure with select distinct, returning reduced set of fields to avoid repeated data that you need. You then need to use this stored procedure to build new dataset and use in your table. But this way obviously may be not applicable in your case.
The other option is to implement your own function, which will save state of aggregation and perform distinct sum. Take a look at this page, it contains examples of code that you need.

Get 1 value of each date SSRS

1 Answers1