0

There are two tablix in ssrs report. I am using the same dataset for both tablix. First tablix which shows JOB details and $amnt BY Date (5 month worth of data) and second tablix shows records Grouped by Job and total of $amonts from tablix1. Tablix 2 shows correct $Sum but for some records there are duplicate rows- if Tablix#1 has more than 1 $amnt.

Example Tablix1: ProjectABC - 1/1/2019 =$2 ; 1/5/2019=$5
                 ProjectHTG -1/1/2019 =$3

Exampl  Tablix2: ProjectABC -$7
                 ProjectABC -$7
                 ProjectHTG -$3

how do i modify my expression "=sum(Fields!units.Value,"project2")" to print "ProjectABC -$7" as one line?

avariant
  • 2,234
  • 5
  • 25
  • 33
Angelika
  • 1
  • 3

1 Answers1

0

Assuming that your field name if JOB for the project, you would add the field along with the dash to your current expression.

You should NOT group by amount if you want to SUM the amount. You are getting a separate line for each different amount for the same JOB. Only JOBs with the same amounts will be SUMmed as one.

=Fields!JOB.Value & " - " & sum(Fields!units.Value)

A few other issues:

Why are you using the Dataset name in your SUM? It sounds like you have a simple table that groups by JOB and Amount. The table is associated to the Dataset that you want to use. You should only use the dataset name in a table when you're referring to a different dataset than the table is using.

Why do you need two datasets if they have the same info? The second table can do the grouping and summing (and already is) from the same dataset as the first table.

Hannover Fist
  • 10,393
  • 1
  • 18
  • 39
  • thank you for comments. my expression for cell 'units' where $total would be"=sum(Fields!units.Value,"project2")". Here "project2" is a group for which I need to display $amnt(unit field) in second cell (table like structure) – Angelika Feb 08 '19 at 23:50
  • First Tablix displays Details Matrix like table. Project in rows, Dates in columns and $unit as cell data. Second Tablix displays Total table like Grouped by Project in one cell and second cell will have total $amn for this project for all dates. – Angelika Feb 08 '19 at 23:52
  • ok, I fixed my issue: by creating second dataset for second Tablix which doing grouping. Now I have Two datasets and two corresponding Tablix and EACH dataset has Important Grouping by Project. I need help with creating third Tablix where I can add Sum of values from two Tablix GROUPED by Project.(Note) my two Tablix has different projects shown based on selected parameter. Third Tablix should COMBINE those projects from First and Second Tablix. – Angelika Feb 11 '19 at 15:18
  • Conbining two datasets is a bit of work. You need to base the table on one and then use LOOKUPSET to get the values of the other table. You'll also need a function to sum them. See https://stackoverflow.com/questions/36131860/need-help-in-calculation-using-two-datasets-using-expression-ssrs/ – Hannover Fist Feb 11 '19 at 18:00
  • got it! Thank you. I used Question 36131860 as reference and " restricted my dataset to the desired outcome" and did build my Third Tablix with new dataset. All great! Last question promise! How accomplish this - my second Tablix Visibility was set up with expression:= Iif (CountRows() > 0, false, true) - meaning if no data - don't show this Tablix.How to display msg to the users that there is no data?? – Angelika Feb 11 '19 at 19:33
  • There's a NoRowsMessage property on most objects to show text instead of the empty table. You can remove your visibility expression and use the property instead. – Hannover Fist Feb 11 '19 at 19:47