使用包含动态 SQL 的存储过程在 C# 中创建报表(报表查看器)

我有一个 SQL Server 存储过程，我想用它在我的应用程序 (c#) 中创建报告.

I have a SQL Server stored procedure that I want to use to create a report in my application (c#).

因为过程中的表是动态创建的，所以我无法使用它来创建报告.

Because the table in the procedure is dynamically created I am having trouble using it to create a report.

如何使用此过程在我的应用程序中创建报告?

How can I use this procedure to create a report in my application?

我使用的是 VS2010、SQL Server 2008 R2

I am using VS2010, SQL Server 2008 R2

我在 VS 中创建了一个数据集，但不确定如何从那里使用它.报表向导无法使用我创建的数据集.

I created a dataset in VS but not sure how to use it from there. The report wizard cannot use the dataset I created.

我曾想以某种方式使用它来创建一个视图，然后从中生成一个数据集，但无法弄清楚.

I gave thought to somehow using this to create a view and then generate a dataset from that but could not figure that out.

我希望我提供了足够的信息，让别人给我一些指导.

I hope I have provided enough info for someone to give me some direction.

PROCEDURE sp_RD_Reporting_View_ExportedData_For_Period    
(   
@StartDate date,   
@EndDate date   
)  
AS  
CREATE TABLE #tmp  
(  
    StartDate DATETIME,  
    EndDate DATETIME,  
    FomatedDate VARCHAR(20)  
)  
--Calculate the date ranges   
;WITH Nbrs ( n ) AS (  
        SELECT 0 UNION ALL  
        SELECT 1+n FROM Nbrs WHERE n < 6 )  
INSERT INTO #tmp  
SELECT  
    DATEADD(DAY,-n,@StartDate),  
    DATEADD(DAY,-n,@EndDate),  
    convert(varchar, DATEADD(DAY,-n,@EndDate), 110)  
FROM  
    Nbrs  
ORDER BY  
    -n  
--The date columns for the pivot  
DECLARE @cols VARCHAR(MAX)  
SELECT  @cols = COALESCE(@cols + ','+QUOTENAME(FomatedDate),  
                     QUOTENAME(FomatedDate))    
FROM   
    #tmp  
--Declaring some dynamic sql and executing it  
DECLARE @query NVARCHAR(4000)=  
N'SELECT  
    *  
FROM  
(  
    SELECT   ExportData.EmployeeID AS EmpID,  
            ABRAempInfo.EmpFullName AS Name,  
            ExportData.PayType AS PayType,   
            tmp.FomatedDate,   
            SUM(ExportData.Quantity) AS Hours   
    FROM ExportData   
    JOIN ABRAempInfo    
    ON ExportData.EmployeeID = ABRAempInfo.EmpID  
    JOIN #tmp AS tmp  
    ON ExportData.ChargeDate = tmp.FomatedDate  
    WHERE ChargeDate BETWEEN tmp.StartDate AND tmp.EndDate  
    GROUP BY ExportData.EmployeeID, ExportData.PayType, tmp.FomatedDate,   ABRAempInfo.EmpFullName  

) AS p  
PIVOT  
(  
    SUM(Hours)  
    FOR FomatedDate IN ('+@cols+')  
) AS pvt'  

EXECUTE(@query)  
DROP TABLE #tmp