none
Want to display 3 field values under one Report Column

    Question

  • I have 3 fields in a table called Paid1, Paid2 and Paid3.

    I have a single column in my report called Paid.

    When I run my report I want to display all the paid values under the one column called Paid i.e.

    Ref       Date             Paid     AmtPaid

    1       28/09/2012      Yes       100

    2       29/09/2012      Yes       200

    3       03/10/2012       No       500

    For record 1 the value of Paid is stored in a field called Paid1, for record 2 the value of Paid is stored in a field called Paid2, for record 3 the value of Paid is stored in a field called Paid3.

    Anyone know how I would achieve this?

    Thanks.

    Wednesday, October 03, 2012 3:00 PM

Answers

  • Hello JMcCon,

      In my previous post i have overlooked your question and suggested Pivot. As aftaab suggested it should be Unpivot. Along with aftab's suggestion this can be done in couple of ways

    This can be handled in the source query itself with the case statement. I assuming Ref field will be your driving field for this case statement

    CASE Ref
    WHEN 1 THEN Paid1
    WHEN 2 THEN Paid2
    WHEN 3 THEN Paid3
    END

    If not this can be set as an expression in SSRS as below

    =SWITCH(Fields!Ref.Value = 1,Fields!Paid1.Value,Fields!Ref.Value = 2,Fields!Paid2.Value,Fields!Ref.Value = 3,Fields!Paid3.Value)

    • Marked as answer by JMcCon Tuesday, October 09, 2012 10:47 AM
    Wednesday, October 03, 2012 4:11 PM
  • Hi JMcCon,

    May be below workaround solve your pattern of display.

    create a table with all your required columns with an additional column for sum of Paid column values.

    Table Header :      Ref           |  Date           |  Paid1          |  Paid2           | Paid3          | Paid

    Details row    :      Ref.value  |  Date.value |  Paid1.value |  Paid2.value | Paid3.value | = ReportItems!textbox9.value +

                                                                                                                                            reportitems!textbox10.value + reportitems!textbox11.value

    as shown above, under Paid column - use the expression with ( reportitems!textbox9.value +  reportitems!textbox10.value + reportitems!textbox11.value ) textbox9,10,11 are text box controls names used for paid1,2,3 respectively. you can use Cint(ReportItems!textbox9.value) function also incase of column type problem if any. 

    as per requirement you don't need to show the Paid1,Paid2,Paid3 columns - for this you can use the column visibility property by right click on each paid1,2,3 columns and make them hide.. with which we can get the desired output.

    below outputs are copied from the rendered excel document from the SSRS report output.

    Output before making the columns hidden

    Ref

    Date

    Paid1

    Paid2

    Paid3

    Paid

    345hg

    23/03/2012

    100

    0

    0

    100

    560khs

    01/02/2012

    0

    200

    0

    200

    Jh456

    18/04/2012

    0

    0

    300

    300

     

    After setting column visibility property, below is the required output.

    Ref

    Date

    Paid

    345hg

    23/03/2012

    100

    560khs

    01/02/2012

    200

    Jh456

    18/04/2012

    300

    let me know if any issues while using the workaround, please vote if helpful, thank you.. hope it'll solve ur requirement :)

    Best Regards,

    Arun Gangumalla

    • Proposed as answer by Arun Gangumalla Tuesday, October 09, 2012 6:12 AM
    • Marked as answer by JMcCon Tuesday, October 09, 2012 10:48 AM
    Tuesday, October 09, 2012 6:12 AM

All replies

  • Hello JMcCon,

      This can be achived using Matrix control in SSRS. Please refer to the below link for a sample implmentation.

    http://arcanecode.com/2010/07/07/creating-a-matrix-report-in-sql-server-2008-reporting-services/

    Best Regards

    Sorna

    Wednesday, October 03, 2012 3:07 PM
  • Hi JMcCon,

    You can also use the UnPivot concept on your data.

    Take a look into a report sample attached at Sample Report<//a> labeled as Place Bar Chart inside a table and consume un-pivot data  

    Take a look into the command text of this RDL.


    Aftab Ansari

    Wednesday, October 03, 2012 3:57 PM
  • Hello JMcCon,

      In my previous post i have overlooked your question and suggested Pivot. As aftaab suggested it should be Unpivot. Along with aftab's suggestion this can be done in couple of ways

    This can be handled in the source query itself with the case statement. I assuming Ref field will be your driving field for this case statement

    CASE Ref
    WHEN 1 THEN Paid1
    WHEN 2 THEN Paid2
    WHEN 3 THEN Paid3
    END

    If not this can be set as an expression in SSRS as below

    =SWITCH(Fields!Ref.Value = 1,Fields!Paid1.Value,Fields!Ref.Value = 2,Fields!Paid2.Value,Fields!Ref.Value = 3,Fields!Paid3.Value)

    • Marked as answer by JMcCon Tuesday, October 09, 2012 10:47 AM
    Wednesday, October 03, 2012 4:11 PM
  • No I won't be able to use the Ref field as the driving field.

    I'm not sure what expression I need to use.

    I suppose I need an expression which will add either Paid1 or Paid2 or Paid3 to the Paid column in the report depending on which of the 3 fields has been selected in the select statement of my query.

    I would need something like:

    =BlahBlah(Fields!Paid1.Value else Fields!Paid2.Value else Fields!Paid3.Value)

    any ideas?

    Wednesday, October 03, 2012 6:42 PM
  • Hello JMcCon,

      Please provide more details on the data and on which condition these paid1 , paid2 etc., need to be displayed.

      If you can provide source table DDL and business logic to be used on the report that will be helpfull for us for furthe analysis.

    Best Regards

    Sorna

     

    Thursday, October 04, 2012 7:35 AM
  • I want my report to look like this:

    Ref

    Date

    Paid

    345hg

    23/03/2012

    100

    560khs

    01/02/2012

    200

    Jh456

    18/04/2012

    300

    Mg932

    23/09/2012

    100

    12345

    14/06/2012

    500

    Here is an example of how my table looks. You will see from the report that the value of Paid1 or Paid2 or Paid3 is displayed in the Paid column of the report depending on what record it is. The Ref is completely irrelevant. The data is badly stored in the DB in the 3 different fields.

    Ref

    Date

    Paid1

    Paid2

    Paid3

    345hg

    23/03/2012

    100

    560khs

    01/02/2012

    200

    Jh456

    18/04/2012

    300

    Mg932

    23/09/2012

    100

    12345

    14/06/2012

    500

    Thursday, October 04, 2012 8:50 AM
  • Hello JMcCon ,

    The below expression should give you the required output

    =Iif(IsNothing(Fields!Paid1.Value),0,Fields!Paid1.Value) + Iif(IsNothing(Fields!Paid2.Value),0,Fields!Paid2.Value) + Iif(IsNothing(Fields!Paid3.Value),0,Fields!Paid3.Value)

    Alternatively , you could do this column addition in your dataset query itself as below

     
    SELECT Ref,Date,ISNULL(Paid1,0) + ISNULL(Paid2,0) + ISNULL(Paid3,0) As Paid
    FROM <table>

    Best Regards

    Sorna

    Thursday, October 04, 2012 6:32 PM
  • This isn't what I am looking for as it just adds the three fields for the record. Sometimes a record has a value in Paid1 and Paid2 and Paid3 and this just gives me the total for each record.

    What I need is a new record on my report for each time there is a value in either paid1 or paid2 or paid3. I would do a loop in code as For each value in paid1 or paid2 or paid3 create a record but I don't know how to achieve this through SQL..

    Friday, October 05, 2012 2:29 PM
  • Hi JMcCon,

    May be below workaround solve your pattern of display.

    create a table with all your required columns with an additional column for sum of Paid column values.

    Table Header :      Ref           |  Date           |  Paid1          |  Paid2           | Paid3          | Paid

    Details row    :      Ref.value  |  Date.value |  Paid1.value |  Paid2.value | Paid3.value | = ReportItems!textbox9.value +

                                                                                                                                            reportitems!textbox10.value + reportitems!textbox11.value

    as shown above, under Paid column - use the expression with ( reportitems!textbox9.value +  reportitems!textbox10.value + reportitems!textbox11.value ) textbox9,10,11 are text box controls names used for paid1,2,3 respectively. you can use Cint(ReportItems!textbox9.value) function also incase of column type problem if any. 

    as per requirement you don't need to show the Paid1,Paid2,Paid3 columns - for this you can use the column visibility property by right click on each paid1,2,3 columns and make them hide.. with which we can get the desired output.

    below outputs are copied from the rendered excel document from the SSRS report output.

    Output before making the columns hidden

    Ref

    Date

    Paid1

    Paid2

    Paid3

    Paid

    345hg

    23/03/2012

    100

    0

    0

    100

    560khs

    01/02/2012

    0

    200

    0

    200

    Jh456

    18/04/2012

    0

    0

    300

    300

     

    After setting column visibility property, below is the required output.

    Ref

    Date

    Paid

    345hg

    23/03/2012

    100

    560khs

    01/02/2012

    200

    Jh456

    18/04/2012

    300

    let me know if any issues while using the workaround, please vote if helpful, thank you.. hope it'll solve ur requirement :)

    Best Regards,

    Arun Gangumalla

    • Proposed as answer by Arun Gangumalla Tuesday, October 09, 2012 6:12 AM
    • Marked as answer by JMcCon Tuesday, October 09, 2012 10:48 AM
    Tuesday, October 09, 2012 6:12 AM