How to Create a Pivot Report

hello,

i have 2 account tables.the first table has account information name,address,taxid,account nature etc.the second table has alternate address fields.i join these 2 tables based on account number.there can more 1 alternate address account. accounts there can 10 alternate addresses , accounts there can 2 alternate addresses.

i built report joining these 2 tables , see account number repeats in report depending on number of alternate addresses has.but users not this.they want see 1 row account.how can this.should use matrix achieve this?if should use matrix field should use column grouping , fields should use row grouping.

can me this.i totally confused.thankyou!!

hi deteminism,

in scenario, there many rows address for each account, users want see 1 row each account, right? in case, can use [account] fiel on row group, , use expression calculate string total [address]. have tested on local environment, steps below reference. in test data, there many phone_number each subscriber_id.

1. drag matrix control design surface.
2. drag [account] row group.
3. copy custom code below , paste on report.
   dim num string = 0
   dim tempstr string = ""
   function aggregatestring(account integer, address string) 
   if account = num
      tempstr = tempstr + "/ " + address 
   else
     num = account
     tempstr = address 
   end if 
   return tempstr 
   end function
4. ues expression below on data.
   =runningvalue(code.aggregatestring(fields!account.value,fields!address.value),last,"account")

the report looks below.

if not want, please elaborate dataset structure sample data, if possible provide screenshots it, can make further analysis.

regards,
charlie liao

if have feedback on our support, please click here.

---

charlie liao
technet community support

SQL Server  >  SQL Server Reporting Services, Power View