Hi everyone I need some help, appreciated if some one can help me out it may be simple requirement but I have just started so please help me out I have an existing report in which i have add new column and make the report to drill down hierarchy report.below is the image of requirement.Below is the existing query. SELECT SalesrepList.SalesrepID, vw_all_salesreps.salesrep_name, COALESCE (BookingsYTD.SalesAmount, 0.00) AS BookingsYTD, COALESCE (BookingsYTD.GPPct, 0.0000) AS BookingsYTDGPPct, COALESCE (BookingsLYTD.SalesAmount, 0.00) AS BookingsLYTD, COALESCE (BookingsLYTD.GPPct, 0.0000) AS BookingsLYTDGPPct, COALESCE (BookingsYTD.SalesAmount, 0.00) - COALESCE (BookingsLYTD.SalesAmount, 0.00) AS BookingsVariance, COALESCE (InvoicesYTD.SalesAmount, 0.00) AS InvoicesYTD, COALESCE (InvoicesYTD.GPPct, 0.0000) AS InvoicesYTDGPPct, COALESCE (InvoicesLYTD.SalesAmount, 0.00) AS InvoicesLYTD, COALESCE (InvoicesLYTD.GPPct, 0.0000) AS InvoicesLYTDGPPct, COALESCE (InvoicesYTD.SalesAmount, 0.00) - COALESCE (InvoicesLYTD.SalesAmount, 0.00) AS InvoicesVariance, COALESCE (Backlog.SalesAmount, 0.00) AS CurrentBacklog, COALESCE (Backlog.GPPct, 0.0000) AS BacklogGPPct, COALESCE (QuotesYTD.SalesAmount, 0.00) AS QuotesYTD, COALESCE (QuotesYTD.GPPct, 0.0000) AS QuotesYTDGPPct FROM (SELECT DISTINCT SalesrepID FROM SalesReporting_Snapshots WHERE (RptFlag IN ('BS', 'IS', 'QS', 'OS'))) AS SalesrepList INNER JOIN vw_all_salesreps ON SalesrepList.SalesrepID = vw_all_salesreps.salesrep_id INNER JOIN salesrep_rollup AS sr ON SalesrepList.SalesrepID = sr.salesrep_id INNER JOIN salesrep_branches AS sb ON sr.parent_salesrep_id = sb.salesrep_id INNER JOIN company_division_branch_heirarchy AS cdbh ON sb.branch_id = cdbh.sub_branch_id LEFT OUTER JOIN (SELECT SalesrepID, SUM(SalesValue) AS SalesAmount, CASE WHEN SUM(SalesValue) BETWEEN - 0.1 AND 0.1 THEN 0 ELSE (SUM(SalesValue) - SUM(SalesCost)) / SUM(SalesValue) END AS GPPct FROM SalesReporting_Snapshots AS SalesReporting_6 WHERE (DateRange = 'Year 2009') AND (RptFlag = 'BS') GROUP BY SalesrepID) AS BookingsYTD ON SalesrepList.SalesrepID = BookingsYTD.SalesrepID LEFT OUTER JOIN (SELECT SalesrepID, SUM(SalesValue) AS SalesAmount, CASE WHEN SUM(SalesValue) BETWEEN - 0.1 AND 0.1 THEN 0 ELSE (SUM(SalesValue) - SUM(SalesCost)) / SUM(SalesValue) END AS GPPct FROM SalesReporting_Snapshots AS SalesReporting_5 WHERE (DateRange = 'Year 2008') AND (RptFlag = 'BS') GROUP BY SalesrepID) AS BookingsLYTD ON SalesrepList.SalesrepID = BookingsLYTD.SalesrepID LEFT OUTER JOIN (SELECT SalesrepID, SUM(SalesValue) AS SalesAmount, CASE WHEN SUM(SalesValue) BETWEEN - 0.1 AND 0.1 THEN 0 ELSE (SUM(SalesValue) - SUM(SalesCost)) / SUM(SalesValue) END AS GPPct FROM SalesReporting_Snapshots AS SalesReporting_4 WHERE (DateRange = 'Year 2009') AND (RptFlag = 'IS') GROUP BY SalesrepID) AS InvoicesYTD ON SalesrepList.SalesrepID = InvoicesYTD.SalesrepID LEFT OUTER JOIN (SELECT SalesrepID, SUM(SalesValue) AS SalesAmount, CASE WHEN SUM(SalesValue) BETWEEN - 0.1 AND 0.1 THEN 0 ELSE (SUM(SalesValue) - SUM(SalesCost)) / SUM(SalesValue) END AS GPPct FROM SalesReporting_Snapshots AS SalesReporting_3 WHERE (DateRange = 'Year 2008') AND (RptFlag = 'IS') GROUP BY SalesrepID) AS InvoicesLYTD ON SalesrepList.SalesrepID = InvoicesLYTD.SalesrepID LEFT OUTER JOIN (SELECT SalesrepID, SUM(SalesValue) AS SalesAmount, CASE WHEN SUM(SalesValue) BETWEEN - 0.1 AND 0.1 THEN 0 ELSE (SUM(SalesValue) - SUM(SalesCost)) / SUM(SalesValue) END AS GPPct FROM SalesReporting AS SalesReporting_2 WHERE (RptFlag = 'OS') GROUP BY SalesrepID) AS Backlog ON SalesrepList.SalesrepID = Backlog.SalesrepID LEFT OUTER JOIN (SELECT SalesrepID, SUM(SalesValue) AS SalesAmount, CASE WHEN SUM(SalesValue) BETWEEN - 0.1 AND 0.1 THEN 0 ELSE (SUM(SalesValue) - SUM(SalesCost)) / SUM(SalesValue) END AS GPPct FROM SalesReporting_Snapshots AS SalesReporting_1 WHERE (DateRange = 'Year 2009') AND (RptFlag = 'QS') GROUP BY SalesrepID) AS QuotesYTD ON SalesrepList.SalesrepID = QuotesYTD.SalesrepID WHERE (SalesrepList.SalesrepID IN (SELECT id FROM dbo.GetUserSalesrepIDs(@user) AS GetUserSalesrepIDs_1)) AND (COALESCE (BookingsYTD.SalesAmount, 0.00) <> 0.00 OR COALESCE (BookingsLYTD.SalesAmount, 0.00) <> 0.00 OR COALESCE (InvoicesYTD.SalesAmount, 0.00) <> 0.00 OR COALESCE (InvoicesLYTD.SalesAmount, 0.00) <> 0.00 OR COALESCE (Backlog.SalesAmount, 0.00) <> 0.00 OR COALESCE (QuotesYTD.SalesAmount, 0.00) <> 0.00) OR (COALESCE (BookingsYTD.SalesAmount, 0.00) <> 0.00 OR COALESCE (BookingsLYTD.SalesAmount, 0.00) <> 0.00 OR COALESCE (InvoicesYTD.SalesAmount, 0.00) <> 0.00 OR COALESCE (InvoicesLYTD.SalesAmount, 0.00) <> 0.00 OR COALESCE (Backlog.SalesAmount, 0.00) <> 0.00 OR COALESCE (QuotesYTD.SalesAmount, 0.00) <> 0.00) AND (dbo.GetUserReportLevel(@user) IN ('Division', 'Branch', 'Salesrep')) AND (cdbh.branch_id IN (SELECT branch_id FROM dbo.GetUserBranchIDs(@user) AS GetUserBranchIDs_1)) AND (cdbh.division_id IN (SELECT division_id FROM dbo.GetUserDivisionIDs(@user) AS GetUserDivisionIDs_1))

This question belong to Reporting service I guess http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/threads is better place. Thanks Manish Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Hi, This can be done by creating groups in your tablix control. Create following groups in table 1. Company 2. Division 3. Branch 4. Sales 5. Vendor Make sure this fields is returned in Dataset. Plz refer: http://technet.microsoft.com/en-us/library/dd207141.aspx Regards, Asim Bagwan Kindly mark the replies as Answers if they help!

The thing is its created insaperate data set for each one like company has separate data set likewise branch has separate data set.the thing is i just started learning can u guide step wise how to do it.

Hi, For above requirement, you need to get required fields in one dataset. Start creating reports as explained here : http://technet.microsoft.com/en-us/library/dd207141.aspx Let us know if you stuck somewhere.Regards, Asim Bagwan Kindly mark the replies as Answers if they help!