October 11, 2011 at 7:54 am
I have a report that lists expenses for individuals and I have been able to create a data driven subscription that will create emails each containing the report for the individual and to be emailed to the individual. The problem is is that if the person has, for example, three expenses, it will create the same email, containing the same report (each that correctly show the three expenses) - three times (or however many expanses there are). So I have ended up sending the emails to me - I take out any duplicates and then forward on just one email per person.
Quick Example
Name Ref Amount Email
-------------------------------
John 001 10.00 JS@web.com
John 032 15.00 JS@web.com
Paul 021 20.00 PA@web.com
I want my subscription to create two emails, one to John (with totals of 25.00) and one to Paul (with a total of 20.00), but I am getting three, TWO to John (both with the correct totals of 25.00) and one to Paul. So if I didn't intervene, John would get two emails exactly the same. I can provide more info regarding the set up, but thought I would try this to start.
Many thanks in advance of any help.
Lydia.
October 11, 2011 at 10:37 am
I am not sure what your code looks like to create a data driven subscription, but I guessing the issue is there. Your report know to sum up the data, but the sql you are using to call the report and send out the emails is not properly selecting distinct email addresses. If you want to post your sql code I am sure someone here would be able to help you change it to get the results you want.
October 17, 2011 at 5:00 am
Thank you for responding.
Yes - this is where I start "winging" it. Using the simple example above - this is how I have set up my report.
select Name, Ref, Amount, Email from profile
where transdate = '01/01/2011;
In the report parameters I have added one called 'email' using the Email as the Value field. And I also have a filter where "=Fields!Email.value = Parameters!Email.value". Without even using a subscription if I just open the report within Visual Studio via the Preview, I get the option to first select the Email and again it provides too many options - the drop down would contain the following:
when I just want one of JS@web.com. Selecting either one works fine...
In the data driven subscription in Step 3 - I have entered the same query as above
"select Name, Ref, Amount, Email from profile
where transdate = '01/01/2011;"
and where it says to specify a parameter value - I select "Get value from the database - Email"
Hope this is enough info. Tearing my hair out - sure this can work correctly - just can't get it right - all help appreciated greatly!
October 17, 2011 at 5:42 am
lema-922661 (10/17/2011)
Thank you for responding.Yes - this is where I start "winging" it. Using the simple example above - this is how I have set up my report.
select Name, Ref, Amount, Email from profile
where transdate = '01/01/2011;
In the report parameters I have added one called 'email' using the Email as the Value field. And I also have a filter where "=Fields!Email.value = Parameters!Email.value". Without even using a subscription if I just open the report within Visual Studio via the Preview, I get the option to first select the Email and again it provides too many options - the drop down would contain the following:
when I just want one of JS@web.com. Selecting either one works fine...
In the data driven subscription in Step 3 - I have entered the same query as above
"select Name, Ref, Amount, Email from profile
where transdate = '01/01/2011;"
and where it says to specify a parameter value - I select "Get value from the database - Email"
Hope this is enough info. Tearing my hair out - sure this can work correctly - just can't get it right - all help appreciated greatly!
I would switch your subscription select to be:
"select Name, Ref, sum(Amount), Email from profile
where transdate = '01/01/2011;"
group by email, Name, Ref
This should cause you to only get one record per person.
October 17, 2011 at 8:03 am
Many thanks - unfortunately, no - this didn't work for me. Thinking about it - I think my problem occurs prior to the subscriptions.
As above, if I preview my report in the designer I get the duplicates appearing the parameter box when I preview - I see the duplication here. I will play around a bit more - wonder if it is related to my parameters and how I have set these up?
October 17, 2011 at 8:07 am
The sql code I gave you should remove the duplicates. If it does not, it is because one of the other fields is unique for each row. If I had to guess it is probably the ref field. So if you still need that field, but it doesn't matter which record you take it from you could remove it from the group by and just select the max. So in the select you would have max(ref) as ref.
October 17, 2011 at 8:09 am
Many thanks - I will give this a go and let you know how I do.
October 17, 2011 at 8:52 am
yes - the Amount or Reference could be unique and will need to be - the report we want the individual to receive will contain a small table showing their individual expenses for the period.
So JS will receive one report that contains a table with two expenses on it.
In the design studio if I don't add a report parameter - I get 2 reports as I want - there is no duplication. I add the parameter and in the drop down I have 3 options (2 for JS@web.com). Can we add "distinct" to the parameter somehow?
October 17, 2011 at 9:00 am
lema-922661 (10/17/2011)
yes - the Amount or Reference could be unique and will need to be - the report we want the individual to receive will contain a small table showing their individual expenses for the period.So JS will receive one report that contains a table with two expenses on it.
In the design studio if I don't add a report parameter - I get 2 reports as I want - there is no duplication. I add the parameter and in the drop down I have 3 options (2 for JS@web.com). Can we add "distinct" to the parameter somehow?
You should be able to populate the drop down report parameter with a custom query. I would suggest you do that and in that case you could do a select distinct email
and just leave the other fields off.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply