February 25, 2013 at 6:56 am
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
February 27, 2013 at 7:55 am
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