SSRS

Hello,

I have table1 that is using  dataset-A. I grouped this table by date. I have other dataset- B which contains total_qty column.

I have to sum this column(total_qty) and show the result in table1 under  every group total  .

Data set A columns:

Date

company_code

location_name

volumes

Data set B columns: 

Date

Company_code

Total_Qty

How can I get Sum(total_qty) from Dataset B into table1 under group total ?Also,total_Qty should change as it is placed in group?

April 30th, 2015 9:08am

Hi shilpa288,

Per my understanding that you have two tables and you create two datasets related to the two tables, now you want to sum the Total_Qty which comes from the table2 to display in the report based on table1 which grouped by the field "Date", right?

As you want to get the SUM(Total_Qty) grouped by the date, so I assumed the field date, Company_code  in the table1 are the same with that in the table2. If so we can use  the lookup function to get the field "Total_Qty" to display in the report and then use the expression " SUM(Total_Qty,"Date")" to get the total sum.

Details information below for your reference:

  1. Before use the lookup() function, make sure your SSRS version is 2008 R2 or later, or it will not support this function.
  2. Make sure you have an relationship field which is unique to be involved in this function, for example: Company_code, then you can add expression like below:
    Expression1:
    =Lookup(Fields!CompanyCode.Value,Fields!CompanyCode.Value,Fields!TotalQty.Value, "DataSetB")
  3. Right click the row group "Date" to Add total after, use below expression to sum the total:
    Expression2:
    =SUM(Lookup(Fields!CompanyCode.Value,Fields!CompanyCode.Value,Fields!TotalQty.Value, "DataSetB"),"Date")
  4. Preview like below:

More details information about the Lookup() and LoopupSet() function:
Lookup Function (Report Builder and SSRS)
LookupSet Function (Report Builder and SSRS)

If your data is not suitable to use the Lookup() or LookupSet() function, I will suggest you to modify the query to join the two tables to get the sum and not create two seperate datasets.

If you still have any problem, please try to provide sample data and more details information.

Regards,
Vicky Liu

Free Windows Admin Tool Kit Click here and download it now
May 1st, 2015 1:42am

Hi Vicky,

Thanks for help! Look Up function is only giving one value.The example you showed above is exactly what I want in my output. 

But using lookup function is not giving all values.

How to get list of all total_qty ?I am getting 11 as total_qty in all rows.

Regards,

May 1st, 2015 9:30am

Hi Vicky,

I used  Lookupset function with Join & it is giving me list of all values.

=Join(Lookupset(Fields!CompanyCode.Value,Fields!CompanyCode.Value,Fields!Total_Qty.Value, "com9_990Count"),",")

But I am not getting Totals? I used custom code to sum the lookupset and it's giving me '0'.

This is the custom code I am using:

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

Thanks again for your help .

Free Windows Admin Tool Kit Click here and download it now
May 1st, 2015 11:27am

Hi shilpa288,

If possible, please try to provide sample data from the two tables to help us better do the analysis.

Regards,
Vicky Liu

May 3rd, 2015 9:02pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics