August 11, 2014 at 4:07 pm
what are search terms to use to find a way to append one row of data to a report, using a seperate query?
August 11, 2014 at 4:19 pm
sounds like you need to union one result to another so that they end up in the same dataset.
August 11, 2014 at 11:37 pm
It's too tricky for a UNION because the total I need at the bottom can only be achieved with a higher level grouping
DDL
create table LeadTracker
(property_name varchar(30), lead_id int);
insert into LeadTracker values
('Property1', 18709),
('Property1', 18323),
('Property1', 19547),
('Property2', 18709),
('Property2', 19015),
('Property2', 18323),
('Property2', 19547),
('Property3', 19015),
('Property3', 18323),
('Property3', 19547),
('Property4', 19015),
('Property4', 19547);
--first query shows how many leads were sent to each property and totals it at the bottom: 12 leads total.
select
property_name,
count(distinct lead_id)
from leadtracker
group by property_name;
--second query shows that only 4 were unique leads. I need for this 'unique lead' total to appear beneath the total leads.
select
count(distinct lead_id)
from LeadTracker;
I need the results to look like this, all in single column
select
property_name,
count(distinct lead_id) as leads_sent
from leadtracker
group by property_name
union all
Select property_name = 'Total', leads_sent = 12;
PLUS
Select count(distinct lead_id) 'TOTAL UNIQUE leads only'
from LeadTracker;
please look at image file I attached. How can I do this in SSRS?
August 12, 2014 at 8:15 am
Add another row at the bottom of the report, and give it an expression along the lines of:
=First(Fields!FieldName.Value, "DataSet2")
Where DataSet2 is the dataset containing the second query.
Can't remember the exact syntax. If you use the expression builder at the bottom of the expression pop up (use Datasets->etc etc) it should generate the correct syntax.
There's probably a way to do it all in one query using ROLLUP but I'm not particularly good with that clause!
Edit:
Can also be done with UNION, might be easier than multiple datasets:
select
property_name,
count(distinct lead_id) AS leads_sent,
1 as OrderField
from leadtracker
group by property_name
union all
select 'Total', sum(DistinctLeads), 2
from
(
select
property_name,
count(distinct lead_id) as DistinctLeads
from leadtracker
group by property_name
) dl
union all
select 'Total Unique Leads', count(distinct lead_id), 3
from leadtracker
order by OrderField, PropertyName
August 12, 2014 at 8:26 am
very grateful to read this. will try it
August 12, 2014 at 8:40 am
Gazereth,
I read your edit. Thanks.
Right now, it seems, if I use the union, I will be able to append the Total unique leads at the bottom of the report. In 'real life' I also have division, region, and date parameters. When I filter with them, it manipulates the first query results very well, and then just appends the Totals of the second union at the bottom resulting in disjointed way.
I will need the report to respond dynamically to provide total unique leads, not just for the whole set but by each property if the selected filter happens to be by property.
Is there a way to apply one set of filters to two seperate queries?
August 12, 2014 at 8:52 am
Hi,
If I'm reading your requirements correctly, you just need to add the same where clause to each section of the union:
select
property_name,
count(distinct lead_id) AS leads_sent,
1 as OrderField
from leadtracker
where ...
group by property_name
union all
select 'Total', sum(DistinctLeads), 2
from
(
select
property_name,
count(distinct lead_id) as DistinctLeads
from leadtracker
where ...
group by property_name
) dl
union all
select 'Total Unique Leads', count(distinct lead_id), 3
from leadtracker
where ...
order by OrderField, PropertyName
August 12, 2014 at 9:16 am
in real life I have filters by division as well. I need the distinct unique leads total (at the bottom) of the report to remain at division level, while the data above it shows distinct by property.
create table LeadTracker
(division_name varchar(30), property_name varchar(30), lead_id int);
insert into LeadTracker values
('DivisionA','Property1', 18709),
('DivisionA','Property1', 18323),
('DivisionA','Property1', 19547),
('DivisionA','Property2', 18709),
('DivisionA','Property2', 19015),
('DivisionA','Property2', 18323),
('DivisionA','Property2', 19547),
('DivisionB','Property3', 19015),
('DivisionB','Property3', 18323),
('DivisionB','Property3', 19547),
('DivisionB','Property4', 19015),
('DivisionB','Property4', 19547);
select
division_name, property_name,
count(distinct lead_id) as leads_sent
from leadtracker
group by division_name, property_name
union all
select
division_name=null, property_name = null,
count(distinct lead_id)
from LeadTracker
August 12, 2014 at 9:36 am
Have you tried it with the same where clause in each section?
If that's not right, remove the where clause from the second/third section as appropriate.
Cheers
August 12, 2014 at 9:55 am
It's dawned on me what you mean by using where clause to regulate results..and somehow doing this in ssrs made me forget basics of query. Thanks for hand up, will try ss soon as at desk. I will add where division_name, etc. Equals the value of the ssrs psrameters passed to it. But, probably will need separate query to control formatting of last two rows. Will try expressions as suggested.
August 12, 2014 at 11:29 am
ok, i've pulled in the second dataset with a WHERE IN clause to get the SSRS parameters value. No issues there.
Now trying to pull inthe specific value returned in the U_SentLeads column using
=Sum(Fields!U_SentLeads.Value, "UniqueLeadsQuery")
while this doesn't error, it doesn't pull in a value, either.
I also tried =First(Sum(Fields!U_SentLeads.Value, "UniqueLeadsQuery"))
and =First(Fields!U_SentLeads.Value, "UniqueLeadsQuery")
and =Fields!U_SentLeads.Value, "UniqueLeadsQuery"
What other SSRS expressions can pull in a specific value?
August 12, 2014 at 10:57 pm
I had to first de-aggregate the source values in the second dataset before I could pull them in. The expression that worked, once I did that, was
=Fields!U_SentLeads.Value, "UniqueLeadsQuery"....which is simply a pull of the value. No functions needed.
also, in my case, no where clause, as the filters are not query based.
Gazereth, you helped me a great deal, conceptually, to help me combine the two datasets for the report. Even though I'm not using Where clause explicitly, it's there. big help to me today! Thanks.
--Quote me
August 13, 2014 at 4:13 am
polkadot (8/12/2014)
I had to first de-aggregate the source values in the second dataset before I could pull them in. The expression that worked, once I did that, was=Fields!U_SentLeads.Value, "UniqueLeadsQuery"....which is simply a pull of the value. No functions needed.
Ah, thanks. Thought the aggregates might be needed, cheers for clearing that up. Does that work for you KC?
Gazereth, you helped me a great deal, conceptually, to help me combine the two datasets for the report. Even though I'm not using Where clause explicitly, it's there. big help to me today! Thanks.
No problem, glad to know this helped!
Cheers
Gaz
August 14, 2014 at 9:24 am
Yes, it worked for me, Gazereth. I deaggregated in the source dataset (by backing out one CTE in which I had been doing the calculations and putting everything into one row), pulled in the raw data (130,000 rows when All is selected), referenced the same fields, and did the aggregations in SSRS. That seems to be how to get pulling in second dataset values into one column to work. At least this time.
Specifying the First,Second, Third column doesn't work for me, as you first showed, though I suspect I missed a nuance. All, the same, all is good.
August 14, 2014 at 9:47 am
Ok, good to hear.
Cheers
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply