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