CODEWITHSUNDEEP
sql-serverreporting-servicescount
Luke
Luke

Reputation: 443

SSRS Count Occurances based on multiple columns

I have a dataset which returns data in the following format;

StartDate | EndDate | Resource | WBS

yyyy-mm-01 | yyyy-mm-07 | John Doe | ABCD

yyyy-mm-01 | yyyy-mm-07 | John Doe | EFGH

yyyy-mm-01 | yyyy-mm-07 | Jane Doe | ABCD

yyyy-mm-01 | yyyy-mm-07 | Joe Blogs | IJKL

yyyy-mm-08 | yyyy-mm-14 | John Doe | ABCD

etc

How can I count the occurrences of the startdate, enddate and resource only (not the wbs), and have that count display on each row - so that i can use the count to calculate the allocation per wbs, per week.

e.g. in the above example, the output should be;

StartDate | EndDate | Resource | WBS | Allocation

yyyy-mm-01 | yyyy-mm-07 | John Doe | ABCD | 50%

yyyy-mm-01 | yyyy-mm-07 | John Doe | EFGH | 50%

yyyy-mm-01 | yyyy-mm-07 | Jane Doe | ABCD | 100%

yyyy-mm-01 | yyyy-mm-07 | Joe Blogs | IJKL | 100%

yyyy-mm-08 | yyyy-mm-14 | John Doe | ABCD | 100%

etc

Thanks in advance for any help - please let me know if i've been unclear in anyway.

Sidenote - I've tried adding row groups, and can get the correct count - however this does not put the count on each row, instead it does the following (not sure how clear that one is);

StartDate | EndDate | Resource | WBS | Allocation

yyyy-mm-01 | yyyy-mm-07 | John Doe | ABCD || 25%

yyyy-mm-01 | yyyy-mm-07 | John Doe | EFGH ||

yyyy-mm-01 | yyyy-mm-07 | Jane Doe | ABCD || 100%

yyyy-mm-01 | yyyy-mm-07 | Joe Blogs | IJKL || 100%

yyyy-mm-08 | yyyy-mm-14 | John Doe | ABCD || 100%

etc

EDIT: The proposed solution by @Hannover Fist seemed to be working, but I'm getting some unexpected behaviour from the lookupset.

The last row is being overwritten with the second-to-last row. when the lookupset is set as Fields!StartDate.Value & "|" & Fields!EndDate.Value, all columns show the correct values (except the allocation). But if i add in the & "|" & Fields!Resource.Value;

I the second-to-last row repeated. i.e.

StartDate | EndDate | Resource | WBS

yyyy-mm-01 | yyyy-mm-07 | John Doe | ABCD

yyyy-mm-01 | yyyy-mm-07 | John Doe | ABCD

While my dataset actually has 2 rows;

yyyy-mm-01 | yyyy-mm-07 | John Doe | ABCD

yyyy-mm-01 | yyyy-mm-07 | John Doe | EFGH

Any help would be greatly appreciated. I actually have no idea whats happening with it.. At first i thought it could be a conversion error or something - but that doesn't make sense seeming it doesnt repeat the row when just the start and end date are shown (they would need to be converted to a string because of the & "|"). Even still - i tried putting the start and end dates in a CSTR(). Still repeating though.

Upvotes: 0

Views: 819

Answers (1)

Hannover Fist
Hannover Fist

Reputation: 10860

I would use the SumLookup function to get the overall total of each group and divide your count by the total.

Allocation expression:

=COUTROWS() / Code.SumLookup(LookupSet(
 Fields!StartDate.Value & "|" & Fields!EndDate.Value & "|" & Fields!Resource.Value, 
 Fields!StartDate.Value & "|" & Fields!EndDate.Value & "|" & Fields!Resource.Value, 
 1, "Dataset1"))

Code:

Function SumLookup(ByVal items As Object()) As Decimal

If items Is Nothing Then
  Return Nothing
End If

Dim suma As Decimal = New Decimal()
Dim ct as Integer = New Integer()
suma = 0
ct = 0

For Each item As Object In items
  suma += Convert.ToDecimal(item)
  ct += 1
Next

If (ct = 0) Then return 0 else return suma 
End Function

Summing lookups in SSRS

http://salvoz.com/blog/2013/05/27/sum-result-of-ssrs-lookupset-function/

Upvotes: 1

Related Questions