Hi, I am working on a SSRS report from a store procedure I created. The returned the dataset is showing in the picture http://suesanda.files.wordpress.com/2011/07/report1.jpg I create a dataset using tableadapter. I can preview the dataset without any issue (in pic2) http://suesanda.files.wordpress.com/2011/07/report11.jpg Later I bind the dataset using objectDataSource, and select the datasource in the rdlc file. I drage and drop the values from the field to the table. http://suesanda.files.wordpress.com/2011/07/report2.jpg When I run the result, the first two columns display values correctly. However, the rest columns all return empty string. http://suesanda.files.wordpress.com/2011/07/report3.jpg any idea why?

I tried to created different dataset based on the same store procedures. No luck. I tried to create a new rdlc , same result. It only display the first two columns and leave the rest date string columns empty. This makes no sense to me because basically they are all strings so why some can be displayed while others are not? The only time it did work was when I copy part of the select statement from the store procedure and pasted in the table adpater query window but this is not option for my case. Your help is highly appreciated. Peggy

I did some research. One person raised the similar issue like mine. http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/198b85df-ecb4-4719-b052-3359cb0e5351/ His storeprocedure created a table variable and used cursor to combine different rows into one row then put into the table variable and select the result as the return set. That's very much like mine and he can not display all of his dataset either, only part of them. I suspect SSRS has some issue with returning values from dataset created from store procedures like this. I resolved my problem by creating a physical table instead of a table variable or temptable in the database and insert value into that physical table then return the dataset. This works.

I went back to this problem and fixed by updating my store procedure. I rewrite the query to get rid of cursor and use PATH XML instead. One interesting thing I observed is that after I updated my sp and run the report, it gave out an error msg: "An error occurred during local report processing.Exception has been thrown by the target of an invocation. String was not recognized as a valid DateTime.Couldn't store <> in Stmt_Date_Created Column. Expected type is DateTime." Which is much better than before when the report ran but returned no data for this field. So I checked out the dataset and found out the data type for Stmt_Date_Created was set as DateTime instead of String. This is quite interesting because in the sp I declared the column as string and all the rest Date columns were set as String as well and returned without issue. So I went to the table adapter and manually set the datatype of Stmt_Date_ Created as String and vola, the reports display all data as I want. Below is my updated sp. Hope it can help others. I was referering to this wonderful article by Anith Sen. http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/ ALTER PROCEDURE [dbo].[SP_REPORT_GET_STATUS_DATES] @Statement_Year SMALLINT, @Statement_Month TINYINT AS BEGIN -- BEGIN TRY Declare @Curr_Code nvarchar(10) Declare @Group_Code nvarchar(10) declare @Group_member_Code nvarchar(10) declare @Date_Submitted smalldatetime declare @SuppDates nvarchar(200) declare @CompDates nvarchar(200) declare @ApproveDates nvarchar(200) declare @Date_Approved nvarchar(10) declare @Results table (Group_Code nvarchar(10) not null, Group_Member_Code nvarchar(10) not null, Stmt_Date_Created nvarchar(10) null, Stmt_Date_Updated nvarchar(10) null, Stmt_Date_Approved nvarchar(10) null, Supp_Date_Entered nvarchar(200) null, Composite_Date_Pushed nvarchar(200) null, Group_Composite_Date_Approved nvarchar(200) null) ------------------------------------------------------------------------ ----------------------- -- Insert the Statement Dates for a particular Group, month, Year in to the final return table ------------------------------------------------------------------------ ----------------------- Insert into @Results (Group_Code,Group_Member_Code, Stmt_Date_Created, Stmt_Date_Updated, Stmt_Date_Approved) Select Group_Code,Group_Member_Code, isnull(convert(nvarchar(2),datepart(mm,Date_Created)) + '/' + convert(nvarchar(2),datepart(day,Date_Created)),''), isnull(convert(nvarchar(2),datepart(mm,Date_Updated)) + '/' + convert(nvarchar(2),datepart(day,Date_Updated)),'') , isnull(convert(nvarchar(2),datepart(mm,Date_Approved)) + '/' + convert(nvarchar(2),datepart(day,Date_Approved)),'') From vw_Active_Members M left join vw_statement s on m.member_identity_id = s.dealership_id and statement_year = @ Statement_Year and statement_month = @Statement_Month and original_Statement _specification_id = 0 left join vw_statement_specification ss on s.statement_specification_id = ss.statement_specification_id and ss.manufacturer_id = m.manufacturer_id and ss.manufacturer_id not in (40) ------------------------------------------------------------------------ ---------------------------- -- Insert Supplemental Date ------------------------------------------------------------------------ ---------------------------- Select Group_Member_Code, IsNull(convert(nvarchar(2),datepart(mm,Date_ Created)) + '/' + convert(nvarchar(2),datepart(day,Date_Created)),'') as ' Date_Created'into #tbSuppDate From vw_Active_Members M left join vw_statement_specification ss on ss.manufacturer_id = m.manufacturer_id and ss. manufacturer_id not in (40) left join supplemental_values sv on sv.statement_specification_id = ss.statement_ specification_id and sv.supplemental_month = @Statement_Month and sv.supplemental_year = @Statement_Year and sv.dealership_id = m.member_identity_id -- where group_id = @Group_ID Group By group_member_code, isnull(convert(nvarchar(2), datepart(mm,Date_Created)) + '/' + convert(nvarchar(2),datepart(day,Date_ Created)),'') order by group_member_code,date_created SELECT Distinct p1.Group_Member_Code, ( SELECT Date_Created + CASE WHEN Date_Created='' THEN '' ELSE ', ' END FROM #tbSuppDate p2 WHERE p2.Group_Member_Code = p1.Group_Member_Code ORDER BY Date_Created FOR XML PATH('') ) AS Date_Supp into #Temp FROM #tbSuppDate p1 GROUP BY Group_Member_Code update @Results Set Supp_Date_Entered = case when LEN(t.Date_Supp)>0 then LEFT(t. Date_Supp,LEN(t.Date_Supp)-1) else '' end from #Temp t inner join @Results r on t.Group_Member_Code=r.Group_ Member_Code drop table #tbSuppDate drop table #Temp ------------------------------------------------------------------------ ---------------------------- -- Insert Composite Date ------------------------------------------------------------------------ ---------------------------- Select Group_Member_Code, isnull(convert(nvarchar(2),datepart(mm,status_ date)) + '/' + convert(nvarchar(2),datepart(day,status_date)),'') as ' Status_Date' into #tbCompPushed From vw_Active_Members M left join composite c on m.member_id = c.member_group_id and c.composite_data_type_id = 1 and c.composite_month = @Statement_Month and c.composite_year = @ Statement_Year left join status_history sh on c.composite_id = sh.status_identity_id and sh.status_id = 5 Group By group_member_code,isnull(convert(nvarchar(2),datepart(mm, status_date)) + '/' + convert(nvarchar(2),datepart(day,status_date)),'') order by group_member_code,status_date SELECT Distinct p1.Group_Member_Code, ( SELECT Status_Date + ',' FROM #tbCompPushed p2 WHERE p2.Group_Member_Code = p1.Group_Member_Code ORDER BY Status_Date FOR XML PATH('') ) AS Date_Comp into #Temp1 FROM #tbCompPushed p1 GROUP BY Group_Member_Code update @Results Set Composite_Date_Pushed = LEFT(t1.Date_Comp,LEN(t1.Date_Comp)-1) from #Temp1 t1 inner join @Results r on t1.Group_Member_Code=r.Group _Member_Code drop table #tbCompPushed drop table #Temp1 ------------------------------------------------------------------------ ---------------------------- -- Insert Composite Approved Date ------------------------------------------------------------------------ ---------------------------- Select Group_Code, cast(convert(nvarchar(2),datepart(mm,status_date)) + '/' + convert(nvarchar(2),datepart(day,status_date)) as nvarchar(10)) as 'Date_ Approved' into #tbCompApproved From vw_Active_Members M left join composite c on m.member_id = c.member_group_id and c.composite_data_type_id = 1 and c.composite_month = @Statement_Month and c.composite_year = @ Statement_Year left join Group_Composite_Detail gc on c.Composite_ID=gc.Composite_ID left join status_history sh on gc.composite_id = sh.status_identity_id and sh.status_id = 23 Group By group_code,cast(convert(nvarchar(2),datepart(mm,status_date)) + '/' + convert(nvarchar(2),datepart(day,status_date)) as nvarchar(10)) order by group_code,Date_Approved SELECT Distinct p1.Group_Code, ( SELECT Date_Approved + ',' FROM #tbCompApproved p2 WHERE p2.Group_Code = p1.Group_Code ORDER BY Date_Approved FOR XML PATH('') ) AS Date_Approved into #Temp2 FROM #tbCompApproved p1 GROUP BY Group_Code update @Results Set Group_Composite_Date_Approved = case when Len(t2.Date_Approved)> 0 then LEFT(t2.Date_Approved,LEN(t2.Date_Approved)-1) else '' end from #Temp2 t2 inner join @Results r on t2.Group_Code=r.Group_Code Select * from @Results order by Group_Code,Group_Member_Code drop table #tbCompApproved drop table #Temp2 END