Need help in calculation using two Datasets using Expression SSRS

by

You would need to restrict your dataset to the desired month and then sum the results.

LookupSet is used to retrieve data from another dataset based on criteria.

A VBA function, SumLookup is needed to add the results from the LookUp. This VB would go into the CODE section of the report (this can be seen under ‘Report Properties’).

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

If items Is Nothing Then Return Nothing

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

You would then use the function in your expression like: 

=Code.SumLookup(LookupSet(Fields!MONTH.Value, Fields!MONTH.Value, Fields!Total.Value,"Dataset16"))

If your field is a date, then you’d need to convert both to a MMyyyy field with FORMAT:

FORMAT(Fields!MONTH.Value, "MMyyyy")

More Related Contents:

  1. Tablix: Repeat header rows on each page not working – Report Builder 3.0
  2. hide/remove header and footer
  3. How to get rid of blank pages in PDF exported from SSRS
  4. SSRS 2014 report deploy issue
  5. Add alternating row color to SQL Server Reporting services report
  6. SSRS report definition is newer than Server
  7. Access Page number in report body In SSRS
  8. Count within a time period
  9. I need a SQL server Func that will calculate a complete date from the week of the year [closed]
  10. Cannot Connect to Server – A network-related or instance-specific error
  11. Turning a Comma Separated string into individual rows
  12. Foreign Key to multiple tables
  13. Get size of all tables in database
  14. Fuzzy matching using T-SQL
  15. How to get first and last day of previous month (with timestamp) in SQL Server
  16. CTE to get all children (descendants) of a parent
  17. SQL Server – inner join when updating [duplicate]
  18. How do I use the CONCAT function in SQL Server 2008 R2?
  19. EXEC sp_executesql with multiple parameters
  20. newid() inside sql server function
  21. How can I use a single mssql connection pool across several routes in an Express 4 web application?
  22. When must we use NVARCHAR/NCHAR instead of VARCHAR/CHAR in SQL Server?
  23. SSIS reading LF as terminator when its set as CRLF
  24. How to set the maxrecursion option for a CTE inside a Table-Valued-Function
  25. SQL-Server: The backup set holds a backup of a database other than the existing
  26. The EXECUTE permission was denied on the object ‘xxxxxxx’, database ‘zzzzzzz’, schema ‘dbo’
  27. FOR JSON PATH results in SSMS truncated to 2033 characters
  28. Calculate Time Difference Between Two Rows
  29. SQL Server error “Implicit conversion of because the collation of the value is unresolved due to a collation conflict.”
  30. Cannot delete rows from a temporal history table

Leave a Comment