Home Forums Reporting Services Reporting Services how to use seperate query to populate 3 values (columns) on one row RE: how to use seperate query to populate 3 values (columns) on one row

  • 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?