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