Want to display 3 field values under one Report Column
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)
October 3rd, 2012 12:11pm

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.
Free Windows Admin Tool Kit Click here and download it now
October 4th, 2012 12:17pm

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
October 4th, 2012 12:24pm

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
Free Windows Admin Tool Kit Click here and download it now
October 4th, 2012 1:14pm

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)
October 4th, 2012 1:27pm

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
Free Windows Admin Tool Kit Click here and download it now
October 4th, 2012 2:55pm

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?
October 4th, 2012 3:59pm

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
Free Windows Admin Tool Kit Click here and download it now
October 5th, 2012 4:52am

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
October 5th, 2012 6:07am

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..
Free Windows Admin Tool Kit Click here and download it now
October 5th, 2012 10:29am

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..
October 5th, 2012 10:29am

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
Free Windows Admin Tool Kit Click here and download it now
October 9th, 2012 2:34am

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

Other recent topics Other recent topics