Hi, I have a requirement as described below. The sample data on which the report should be created is given below: Order Name, Fulfillment Date, Program, Project, Price ----------------------------------------- Order 1, 7/1/2011, Program 1, Project 1, 100 Order 1, NULL, Program 2, Project 1, 200 Order 1, NULL, Program 3, Project 2, 200 Order 1, NULL, Program 2, Project 2, 50 Order 2, 6/30/2011, Program 2, Project 3, 500 Order 2, NULL, Program 1, Project 1, 800 Order 2, NULL, Program 3, Project 2, 600 Order 3, 6/29/2011, Program 1, Project 3, 500 Order 3, NULL, Program 2, Project 1, 500 Order 3, NULL, Program 2, Project 3, 800 Note, in the above sample data, the order fulfillment date appears only against the first order name. In the report, the data should be grouped by Program, Project and all the orders details related to that Program and Project as details. However, in the details the order fulfillment date should be displayed against each order name that appears in different Programs and Projects. I tried using the First function and it did not satisfy my requirement and the Order Fulfillment date appears only against the detail row that has the matching Program and Project as in the above sample data. How can we achieve the requirement described above? Can we write custom code in the report to get the Order Fulfillment date based on the order name? Please suggest me the required direction for the above requirement. Thanks in advance. Regards, Raghu

Hi Raghu, It would be easy to understand if you can give a graphical description on the report design, current output and the required output! Pavan Kokkula Tata Consultancy Services.

Use a SQL query. Something like this... -- Create sample data -- IF OBJECT_ID('tempdb..#Orders') IS NOT NULL DROP TABLE #Orders CREATE TABLE #Orders (OrderName CHAR(7), FulfillmentDate Date, Program CHAR(9), Project CHAR(9), Price Money) INSERT INTO #Orders SELECT 'Order 1', '7/1/2011', 'Program 1', 'Project 1', 100 UNION ALL SELECT 'Order 1', NULL, 'Program 2', 'Project 1', 200 UNION ALL SELECT 'Order 1', NULL, 'Program 3', 'Project 2', 200 UNION ALL SELECT 'Order 1', NULL, 'Program 2', 'Project 2', 50 UNION ALL SELECT 'Order 2', '6/30/2011', 'Program 2', 'Project 3', 500 UNION ALL SELECT 'Order 2', NULL, 'Program 1', 'Project 1', 800 UNION ALL SELECT 'Order 2', NULL, 'Program 3', 'Project 2', 600 UNION ALL SELECT 'Order 3', '6/29/2011', 'Program 1', 'Project 3', 500 UNION ALL SELECT 'Order 3', NULL, 'Program 2', 'Project 1', 500 UNION ALL SELECT 'Order 3', NULL, 'Program 2', 'Project 3', 800 -- The acuual solution -- SELECT o.OrderName, s.FulfillmentDate, o.Program, o.Project, o.Price FROM #Orders o LEFT OUTER JOIN ( SELECT OrderName, FulfillmentDate FROM #Orders WHERE FulfillmentDate IS NOT NULL) s ON o.OrderName = s.OrderName It will make a result set that looks like this... OrderName, FulfillmentDate, Program, Project, Price Order 1, 2011-07-01, Program 1, Project 1, 100.00 Order 1, 2011-07-01, Program 2, Project 1, 200.00 Order 1, 2011-07-01, Program 3, Project 2, 200.00 Order 1, 2011-07-01, Program 2, Project 2, 50.00 Order 2, 2011-06-30, Program 2, Project 3, 500.00 Order 2, 2011-06-30, Program 1, Project 1, 800.00 Order 2, 2011-06-30, Program 3, Project 2, 600.00 Order 3, 2011-06-29, Program 1, Project 3, 500.00 Order 3, 2011-06-29, Program 2, Project 1, 500.00 Order 3, 2011-06-29, Program 2, Project 3, 800.00 Jason Long

Hi Raghu, If you are using SSRS 2008 R2 there is another solution although not optimal Although I prefer Jasons approach to develop the report Suppose the following query returns the records CREATE TABLE #Orders ( OrderName CHAR(7), FulfillmentDate Date, Program CHAR(9), Project CHAR(9), Price Money ) INSERT INTO #Orders(OrderName,FulfillmentDate,Program,Project,Price) SELECT 'Order 1', NULL, 'Program 1', 'Project 1', 100 UNION ALL SELECT 'Order 1', '7/1/2011', 'Program 2', 'Project 1', 200 UNION ALL SELECT 'Order 1', NULL, 'Program 3', 'Project 2', 200 UNION ALL SELECT 'Order 1', NULL, 'Program 2', 'Project 2', 50 UNION ALL SELECT 'Order 2', '6/30/2011', 'Program 2', 'Project 3', 500 UNION ALL SELECT 'Order 2', NULL, 'Program 1', 'Project 1', 800 UNION ALL SELECT 'Order 2', NULL, 'Program 3', 'Project 2', 600 UNION ALL SELECT 'Order 3', '6/29/2011', 'Program 1', 'Project 3', 500 UNION ALL SELECT 'Order 3', NULL, 'Program 2', 'Project 1', 500 UNION ALL SELECT 'Order 3', NULL, 'Program 2', 'Project 3',800 SELECT OrderName,FulfillmentDate,Program,Project,Price FROM #Orders order by FulfillmentDate desc DROP TABLE #Orders Note that I have Ordered by Fulfilment Date desc so that the first always returns the date Now Your RDL has a dataset named DataSet1 and you added a tablix with 2 groups named Program and Project In detail row you added fulfilment date and Order Name Now if you want FulfilmentDate to be displayed across order name you use a newly added function called Lookup =Lookup(Fields!OrderName.Value,Fields!OrderName.Value,Fields!FulfillmentDate.Value,"DataSet1") this will return the first(thats why we added sort by fulfilmentdate desc) fulfilmentdate after matching the OrderName Spandan B

Hi, Thank you for your responses. The sample data I have given above comes from a SharePoint list and the sample data I have menioned is at the dataset level in SSRS report. So, it is not possible to use the queries that are suggested. Because of that, I am looking for some way to implement custom code at the SSRS report. Sample data comes from the dataset is shown in my initial post. The sample report, I was looking is in this format for the sample data mentioned above by Program and Project with page-break by Program: Program 1 Order Name, Fulfillment Date, Price Project Name: Project 1 Order 1, 7/1/2011, 100 Order 2, 6/30/2011, 800 Project Name: Project 3 Order 3, 6/29/2011, 500 Program 2 Order Name, Fulfillment Date, Price Project Name: Project 1 Order 1, 7/1/2011, 200 Order 3, 6/29/2011, 500 Project Name: Project 2 Order 1, 7/1/2011, 50 Project Name: Project 3 Order 2, 6/30/2011, 500 Order 3, 6/29/2011, 800 Program 3 Order Name, Fulfillment Date, Price Project Name: Project 2 Order 1, 7/1/2011, 200 Order 2, 6/30/2011, 600 Thank you, Raghu

You can add a empty hidden table which groups on OrderName and populates the FulfilmentDate in a Dictionary object in custom code. Now use this populated list in your subsequent table where you are displaying data by passing the OrderName which does a lookup using OrderName as key and getting fulfilment date as value. Hope you got what I am trying to tell. This is not a good solution if the number of orders are too many. Spandan

I agree with the previous posts that altering the query is the best way to go about this. Pulling the data from the SharePoint List into a datawarehouse would be ideal, and then you can build your queries any way you want. I have a blog post on how to pull SharePoint List data into a datawarehouse, here: http://dataqueen.unlimitedviz.com/2011/06/how-to-use-a-sharepoint-list-as-a-data-source-in-your-ssis-package However, if you really have to manage this in the report, another option is to use a subreport. 1. Make a copy of your existing report, I’ve renamed it to Test Subreport, and alter the COPY as follows. 2. Change the query to add a parameter to the dataset to filter on a single OrderName, and to filter out any null Fulfillment Dates. SELECT OrderName, FulfillmentDate FROM ( <myQuery> ) as T1 WHERE OrderName = @OrderName and FulfillmentDate is not null 3. Remove page header, page footer and all groups except OrderName and remove all columns and rows except Fulfillment date so you end up with one single cell containing just the FulfillmentDate. 4. Save the subreport to your solution. 5. In your main report setup and group the report as you normally would, and place a subreport from the Toolbox to the cell in your tablix where the FulfillmentDate should appear. 6. Rename the textbox that has [OrderName] in the report and change the name of that textbox to OrderName. You will refer to this textbox in the next step. 7. Configure the subreport by right clicking on it and select Subreport Properties. a. In “Use this report as a subreport:” choose the subreport that you created in the first step. b. In the Parameter section click the Add button and add the parameter which is in the subreport. In this case the Parameter Name is OrderName and the Value is =ReportItems!OrderName2.Value which refers back to the textbox you named in step 6. This will give you the result exactly as you described. You can now add your page breaks and formatting as desired. MartinaMartina White

Hi Martina, I could go with the subreport option you have suggested. However, when the report is exported to Excel, the sub-report data does not get exported and I would like to avoid using sub-reports. I will try the option Spandan suggesting if there are no other ways as it may be more efficient compared to sub-report as all the information can be stored in memory and act like a "look-up". Thank you, Raghu

Hi ranumula, Which version of SQL Server Reporting Services (SSRS) you are using? Based on all description, the data come from a SharePoint list, so SQL query is inproper here. And, you have tried to use subreport as Martina White suggested, problem is the subreport can not show when exported to excel. I tried to use subreport too, it works fine in ssrs 2008/ssrs 2008 R2, but in ssrs 2005 a message”Subreports within table/matrix are ignored” shows when viewing in excel. This is the limitation of the Excel rendering extension. SSRS 2008/2008 R2 provides a full solution to this, since they have a new engine and rendering extensions. If your envrioment is SSRS 2008 or SSRS 2008 R2, and still can not export subreport normally, please let me know. If you are using SSRS 2008 R2 or planning to upgrade to SSRS 2008 R2, we can also use a new function LookUp included in SSRS 2008 R2. The main steps here: Create hierarchy groups for the data region. First group detail data by Project, then group all data by Program. Adjusting the apprences as needed. Clear the textbox in details row containing [FulfillmentDate].Then right-click the textbox, choose expression. And, set the expression as “=Lookup(Fields!OrderName.Value,Fields!OrderName.Value,Fields!Fulfillment.Value,”dataset”)” More information about Lookup function, please refer to: http://msdn.microsoft.com/en-us/library/ee210531.aspx. Thanks, Lola Wang Please remember to mark the replies as answers if they help.