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

Adding percentage on a tablix Expand / Collapse
Author
Message
Posted Monday, February 25, 2013 6:56 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 24, 2014 6:00 AM
Points: 74, Visits: 364
Hello
I've got a report which has this output below
month Leads 2012 Leads 2013 Oppos 2012 Oppos 2013 Enquiries 2012 Enquiries 2013
January 7 11557 15429 13783 15433 21211
February 10 6015 11412 6840 11415 10892
March 290 7973 8067 0 6903 9230
April 13348 5722 18001 0 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


  Post Attachments 
Marketing Performance report.xls (8 views, 28.50 KB)
Post #1423597
Posted Wednesday, February 27, 2013 7:55 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 9:36 AM
Points: 33, Visits: 239
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
Post #1424549
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse