Distinct Total

Hi,

I hope someone can help with my problem described below.

I have a dataset with the following information....

Code1 Amount Code2

-- --

56209087 249.35 N01L1

56209087 249.35 N01L2

56209087 249.35 N02L1

56209903 437.52 N04L1

56209903 437.52 N01L1

56209903 437.52 N01L2

56209903 437.52 N03L1

I need my SSRS report to display a total field which is the sum of the distinct "Amount" values.... (249.35 + 437.52) = 686.87. I need to get the distinct amount per Code1 and then sum these distinct amounts.

Thanks

Hi Lorraine M

Create On more Dataset (Totals) with the following Query

Eg:

SELECT SUM(Amount) AS Total FROM

(SELECT DISTINCT code1, Amount FROM [Give the First data set Query here]) AS S

In the Report Give the Following Expression for the Sum field
=Sum(Fields!Total.Value, "Totals")

This will cause executing first data set two times

|||

Hi Chandra,

Thanks for your response.

I forgot to mention that the dataset is constructed not from a query but a recordset returned by a procedure. Therefore I cannot manipulate a query to add a new column. I should have mentioned in my first post.

Lorraine.

|||

Hello Lorraine,

This will guide you exactly to solve this problem. I was experiencing the same scenario and this link helped me.

Deepak -- > http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=831083&SiteID=1

bWelcker --> http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

Hope this helps..

-- Deepak

|||Thanks Deepak. I created a function similar to the example above. I have one issue to iron out but this approach should work. Thanks for your help.|||Could you please mark this as answer. Anyone with similar question can view this.sql