CODEWITHSUNDEEP
matrixreporting-servicesssrs-2008-r2
Dmitry
Dmitry

Reputation: 315

Get 1 value of each date SSRS

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.

Upvotes: 0

Views: 122

Answers (1)

grafgenerator
grafgenerator

Reputation: 819

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:

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

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

Upvotes: 2

Related Questions