locked
How to Create a Pivot Report RRS feed

  • Question

  • Hello,

    I have two Account tables.The first table has Account information like Name,Address,Taxid,Account Nature etc.The second table has all the Alternate address fields.I join these two tables based on Account Number.There can be more than one Alternate address for an Account. For some accounts there can be 10 alternate addresses and for some accounts there can be just 2 alternate addresses.

    I built a report by joining these two tables and I see the account number repeats in the Report depending on the number of alternate addresses it has.But my users does not like this.They want to see only one row for an account.How can I do this.Should I use a matrix to achieve this?If i should use a matrix which field should I use for column grouping and which fields should I use for row grouping.

    Can somebody help me with this.I am totally confused.Thankyou!!

    Thursday, August 8, 2013 9:45 PM

Answers

  • Hi Deteminism,

    In your scenario, there are many rows Address for each Account, the users want to see one row for each Account, right? In this case, we can use the [Account] fiel on the row group, and then use expression to calculate the string total for [Address]. I have tested it on my local environment, the steps below are for your reference. In my test data, there are many phone_number for each Subscriber_ID.

    1. Drag a matrix control to design surface.
    2. Drag [Account] to row group.
    3. Copy the custom code below and paste it on your report.
      Dim Num As String = 0
      Dim tempstr As String = ""
      Function AggregateString(Account as Integer, Address as String) 
      If Account = Num Then
         tempstr = tempstr + "/ " + Address 
      Else
        Num = Account
        tempstr = Address 
      End If 
      Return tempStr 
      End Function
    4. Ues the expression below on the Data.
      =runningvalue(Code.AggregateString(Fields!Account.Value,Fields!Address.Value),Last,"Account")

    The report looks like below.

    If this is not you want, please elaborate your dataset structure with some sample data, if possible provide us some screenshots about it, so that we can make further analysis.

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.

    Charlie Liao
    TechNet Community Support

    • Marked as answer by Charlie Liao Sunday, August 18, 2013 11:24 AM
    Monday, August 12, 2013 2:14 AM

All replies

  • You should be able to use a regular tablix and group the row by the account number. Make sure to include a footer and header. In the header, you should drop the fields account number and account name. Then in the details section, you can drop in the address fields and such.

    Once you run the report, you should have one line for the account number and name, then the details rows you should have online line for each different address for that one account.

    Is this what you're looking for?

    Thursday, August 8, 2013 9:57 PM
  • Hi Gonzalo,

    Thankyou for your reply.

    I tried the exact samething you suggested.But my users want to see the Alternate addresses also on the same line as Account Number and Name.They will export this report to Excel and they want to see only one row for an Account with all the details - Account Number,Name,Address,Account nature,Alternate Address 1,Alternate Address2..........Alternate Address n.

    Do you think we can do this.

    Friday, August 9, 2013 2:51 PM
  • Yes, you would just add more columns and include those Alternate Address Fields! in the same row as the Account Number Row Group, make sense? 


    Friday, August 9, 2013 11:54 PM
  • Hi Deteminism,

    In your scenario, there are many rows Address for each Account, the users want to see one row for each Account, right? In this case, we can use the [Account] fiel on the row group, and then use expression to calculate the string total for [Address]. I have tested it on my local environment, the steps below are for your reference. In my test data, there are many phone_number for each Subscriber_ID.

    1. Drag a matrix control to design surface.
    2. Drag [Account] to row group.
    3. Copy the custom code below and paste it on your report.
      Dim Num As String = 0
      Dim tempstr As String = ""
      Function AggregateString(Account as Integer, Address as String) 
      If Account = Num Then
         tempstr = tempstr + "/ " + Address 
      Else
        Num = Account
        tempstr = Address 
      End If 
      Return tempStr 
      End Function
    4. Ues the expression below on the Data.
      =runningvalue(Code.AggregateString(Fields!Account.Value,Fields!Address.Value),Last,"Account")

    The report looks like below.

    If this is not you want, please elaborate your dataset structure with some sample data, if possible provide us some screenshots about it, so that we can make further analysis.

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.

    Charlie Liao
    TechNet Community Support

    • Marked as answer by Charlie Liao Sunday, August 18, 2013 11:24 AM
    Monday, August 12, 2013 2:14 AM