Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

how to use seperate query to populate 3 values (columns) on one row Expand / Collapse
Author
Message
Posted Monday, August 11, 2014 4:07 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 8:10 AM
Points: 656, Visits: 1,533
what are search terms to use to find a way to append one row of data to a report, using a seperate query?
Post #1602103
Posted Monday, August 11, 2014 4:19 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 5:49 PM
Points: 774, Visits: 4,986
sounds like you need to union one result to another so that they end up in the same dataset.
Post #1602107
Posted Monday, August 11, 2014 11:37 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 8:10 AM
Points: 656, Visits: 1,533
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?


  Post Attachments 
LeadsExample.bmp (5 views, 91.97 KB)
Post #1602149
Posted Tuesday, August 12, 2014 8:15 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:33 AM
Points: 1,978, Visits: 3,292
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

Post #1602299
Posted Tuesday, August 12, 2014 8:26 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 8:10 AM
Points: 656, Visits: 1,533
very grateful to read this. will try it
Post #1602305
Posted Tuesday, August 12, 2014 8:40 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 8:10 AM
Points: 656, Visits: 1,533
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?
Post #1602316
Posted Tuesday, August 12, 2014 8:52 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:33 AM
Points: 1,978, Visits: 3,292
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

Post #1602326
Posted Tuesday, August 12, 2014 9:16 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 8:10 AM
Points: 656, Visits: 1,533
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


Post #1602337
Posted Tuesday, August 12, 2014 9:36 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:33 AM
Points: 1,978, Visits: 3,292
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
Post #1602349
Posted Tuesday, August 12, 2014 9:55 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 8:10 AM
Points: 656, Visits: 1,533
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.
Post #1602359
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse