SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to use seperate query to populate 3 values (columns) on one row


how to use seperate query to populate 3 values (columns) on one row

Author
Message
KoldCoffee
KoldCoffee
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4301 Visits: 1905
what are search terms to use to find a way to append one row of data to a report, using a seperate query?
pietlinden
pietlinden
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14464 Visits: 14162
sounds like you need to union one result to another so that they end up in the same dataset.
KoldCoffee
KoldCoffee
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4301 Visits: 1905
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;
[code]

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?
Attachments
LeadsExample.bmp (10 views, 91.00 KB)
Gazareth
Gazareth
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7952 Visits: 6046
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


KoldCoffee
KoldCoffee
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4301 Visits: 1905
very grateful to read this. will try it
KoldCoffee
KoldCoffee
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4301 Visits: 1905
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?
Gazareth
Gazareth
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7952 Visits: 6046
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


KoldCoffee
KoldCoffee
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4301 Visits: 1905
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

Gazareth
Gazareth
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7952 Visits: 6046
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
KoldCoffee
KoldCoffee
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4301 Visits: 1905
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search