Adding percentage on a tablix

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Business Intelligence » Adding percentage on a tablix

Adding percentage on a tablix

Rate Topic

Display Mode

Topic Options

Author

Message

Bulelani M

Posted Monday, February 25, 2013 6:56 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 4:22 AM
Points: 63, Visits: 257

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

DuncEduardo

Posted Wednesday, February 27, 2013 7:55 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 10:41 AM
Points: 21, Visits: 189

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 »

Permissions