How to avoid duplicate data in subreport?
I created a SSRS report 'A' and placed a subreport 'B' in 2nd row of Main report 'A'. Report 'A' will generate with a simple select query. Report 'B' will generate based on the values in report 'A'. Now the problem is I have a data in Table 'Z' as follows: Date name Phoneno Address 2011-06-23 XYZ 1231231231 USA 2011-06-23 XYZ 1231231231 INDIA 2011-06-23 ABC 5675675675 BRAZIL 2011-06-24 XYZ 1231231231 UK 2011-06-25 ABC 5675675675 CHINA Report 'A' will take the date value and generate the Report. Report 'A' Query: SELECT * FROM Z WHERE DATE= '2011-06-23' Report 'B' Query: SELECT * FROM Z WHERE DATE between (select DATEADD(d,1,@Date)) and (select DATEADD(d,10,Date)) and [NAME]=@name and Phoneno=@Phoneno order by [DATE] The report is generating as follows: Date name Phoneno Address 2011-06-23 XYZ 1231231231 USA 2011-06-24 XYZ 1231231231 UK 2011-06-23 XYZ 1231231231 INDIA 2011-06-24 XYZ 1231231231 UK 2011-06-23 ABC 5675675675 BRAZIL 2011-06-25 ABC 5675675675 CHINA I want the Report in the following way: Date name Phoneno Address 2011-06-23 XYZ 1231231231 USA 2011-06-23 XYZ 1231231231 INDIA 2011-06-24 XYZ 1231231231 UK 2011-06-23 ABC 5675675675 BRAZIL 2011-06-25 ABC 5675675675 CHINA OR Date name Phoneno Address 2011-06-23 XYZ 1231231231 USA 2011-06-24 XYZ 1231231231 UK 2011-06-23 XYZ 1231231231 INDIA 2011-06-23 ABC 5675675675 BRAZIL 2011-06-25 ABC 5675675675 CHINA
July 6th, 2011 11:28am

Guest 389, Did you tried using Distinct for getting unique records in Report B? Regards Manoj
Free Windows Admin Tool Kit Click here and download it now
July 6th, 2011 12:30pm

I think you didn't get my question. I'm using Report 'B' as a subreport in Report 'A' and placed it in the 2nd row of Report 'A'. Since I have two records with same values for Date, Name and Phoneno in Report 'A' same parameter values are passing to subreport. Hence same data row is repeating in the subreport.
July 6th, 2011 1:02pm

Can anyone help me with this please?
Free Windows Admin Tool Kit Click here and download it now
July 6th, 2011 2:13pm

Does anyone know how to accomplish the above task?
July 7th, 2011 10:35am

Hello Guest389, Why do you want to go with Main - Subreport scenario, Can you do the same using ONLY 1 report? Also, do grouping on your Name & Phoneno column which should give you your expected result set, please note as per your statement "with same values for Date, Name and Phoneno" it doesn't look that "Date" values are the same. please double check. Thank You and please let us know your feedback. KumarKG, MCTS
Free Windows Admin Tool Kit Click here and download it now
July 7th, 2011 3:21pm

@Kumar, With single report, The report is generating as follows: Date name Phoneno Address 2011-06-23 XYZ 1231231231 USA 2011-06-23 XYZ 1231231231 INDIA 2011-06-23 ABC 5675675675 BRAZIL 2011-06-24 XYZ 1231231231 UK 2011-06-25 ABC 5675675675 CHINA Report Query: SELECT * FROM Z WHERE DATE between @Date and (select DATEADD(d,10,Date)) Group By Date,name,Phoneno,Address order by [DATE]. I need the report in the following format. Date name Phoneno Address 2011-06-23 XYZ 1231231231 USA 2011-06-23 XYZ 1231231231 INDIA 2011-06-24 XYZ 1231231231 UK 2011-06-23 ABC 5675675675 BRAZIL 2011-06-25 ABC 5675675675 CHINA OR Date name Phoneno Address 2011-06-23 XYZ 1231231231 USA 2011-06-24 XYZ 1231231231 UK 2011-06-23 XYZ 1231231231 INDIA 2011-06-23 ABC 5675675675 BRAZIL 2011-06-25 ABC 5675675675 CHINA For same values for Date, Name and Phoneno: Please see 1st and 2nd rows of table 'Z'.
July 7th, 2011 4:43pm

Hi Guest389, According to your presentation, the report A’s result set is the data in table Z filtered by condition DATE= '2011-06-23', the report B’s result set is the data in table Z whose columns ‘Name’ and ‘Phoneno’ matches report A and Date’s range between '2011-06-24'And '2011-07-03', so we could combination of report A and report B, please follow the below steps: 1. Set your dataset query string like: SELECT * FROM Z WHERE DATE= '2011-06-23' union SELECT distinct A.* FROM Z as A inner join Z AS B ON A.name=B.name AND A.Phoneno=B.Phoneno WHERE B.[Date]='2011-06-23' AND A.[Date] between ( select DATEADD(d,1,B.[Date])) and (select DATEADD(d,10,B.[Date])) 2. Right click Date textbox, select Expression, set Expression like: =Format(Fields!Date.Value,"yyyy-MM-dd"). 3. Right click Detail item in Row Groups Area, select Group Properties, then click Sorting item in the left panel, click add to set sorting rules like this: 4. Click Ok. After you complete all the steps above, you will get the report data what you expected: If you have anything unclear, please feel free to let me know. Thanks, Bill Lu
Free Windows Admin Tool Kit Click here and download it now
July 11th, 2011 12:42am

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

Other recent topics Other recent topics