Adding percentage on a tablix

  • Hello

    I've got a report which has this output below

    month Leads 2012 Leads 2013 Oppos 2012 Oppos 2013Enquiries 2012 Enquiries 2013

    January7 11557 15429 1378315433 21211

    February 10 6015 11412 684011415 10892

    March290 7973 80670 6903 9230

    April13348 5722 180010 6088 7740

    which is derived from this quiry

    SELECT cast(datepart(yyyy, o.Oppo_CreatedDate) AS varchar) [Year], cast(datepart(MM, o.Oppo_CreatedDate) AS varchar) [month], count(o.Oppo_OpportunityId) Oppos,

    '' AS Leads, '' AS Converted_Leads

    FROM Opportunity o

    WHERE o.oppo_brandid = 6001 AND o.Oppo_CreatedDate >= @Date_from AND o.Oppo_CreatedDate <= @Date_to

    GROUP BY cast(datepart(yyyy, o.Oppo_CreatedDate) AS varchar), cast(datepart(MM, o.Oppo_CreatedDate) AS varchar)

    /*ORDER BY cast(datepart(yyyy,o.Oppo_CreatedDate) AS varchar) , cast(datepart(MM,o.Oppo_CreatedDate) AS varchar)*/ UNION ALL

    /*All Leads */ SELECT year(l.Lead_CreatedDate) [Year], month(l.Lead_CreatedDate) [Month], '' AS Oppos, count(l.Lead_LeadID) [Converted_Leads], '' AS Converted_Leads

    FROM Lead l

    WHERE l.lead_brandid = 6001 /*AND l.Lead_OpportunityID IS NULL */ AND l.Lead_CreatedDate >= @Date_from AND l.Lead_CreatedDate <= @Date_to

    GROUP BY year(l.Lead_CreatedDate), month(l.Lead_CreatedDate)

    /*ORDER BY [year] , [Month]*/ UNION ALL

    /*Leads converted*/ SELECT year(l.Lead_CreatedDate) [Year], month(l.Lead_CreatedDate) [Month], '' AS Oppos, '' AS Leads, count(l.Lead_LeadID) [Converted_Leads]

    FROM Lead l

    WHERE l.lead_brandid = 6001 AND l.Lead_OpportunityID IS NOT NULL AND l.Lead_CreatedDate >= @Date_from AND l.Lead_CreatedDate <= @Date_to

    GROUP BY year(l.Lead_CreatedDate), month(l.Lead_CreatedDate)

    ORDER BY cast(datepart(yyyy, o.Oppo_CreatedDate) AS varchar), cast(datepart(MM, o.Oppo_CreatedDate) AS varchar)

    I've created a report on ssrs 2008 r2, used a tablix now I want to add a percentage field between the years, one can ad one or more years.. Can anyone please tell me if its possible ?

    I've attached an excel file here

  • You have a few options generally, although I'm not entirely sure what you're trying to achieve.

    a) add computed columns to your T-SQL statement

    b) add new columns to your tablix and assign an Expression to achieve the percentage

    c) add a Group

    d) add a List

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply