首页 文章

在Telerik报表设计器中，如何使用组 Headers 创建多列表？

提问于
浏览 次
0

我正在使用Telerik Report Designer（独立应用程序，而不是Visual Studio中的应用程序） . 我有一个包含两个字段的数据集：group和task . 我想创建一个表，其中每个组都在它自己的列中，下面列出了该组的任务 . 我提出的最好的是交叉表报告，显示组和任务及其计数，但这不是客户想要的 . 如何在报表设计器中获得我想要的结果？如果Telerik控件不能这样做，我怎么能重写我的SQL查询输出数据是我想要的格式所以我可以直接使用SQL输出来填充表？

这是我的数据集：
enter image description here

我用来获取该数据集的查询是：

SELECT 
  tblDtl.Group
, tblDtl.Task
FROM tblHdr
INNER JOIN tblDtl ON tblHdr.CLHId = tblDtl.CLHId
INNER JOIN tblType ON tblHdr.CLHId = tblType.SelectedId
INNER JOIN tblOrder ON tblType.TypeId = tblOrder.Type
ORDER BY tblDtl.CLDDisplayOrder

这是我想要的输出：
enter image description here

我得到的最好的是一个交叉表，看起来像这样：
enter image description here

sql datatable telerik crosstab telerik-reporting

2 回答

  • 0

    *** Partial Answer ***

    我无法弄清楚如何获得我想要的2列，但我使用下面的代码至少得到第一行中的 Headers 及其下方的详细信息 . 我将此代码放在存储过程中，然后将该存储过程用作Telerik报告中的表的数据源 .

    CREATE TABLE #Checklist
(
	Checklist varchar(200),
	Notes varchar(50)
)


SET NOCOUNT ON;  

DECLARE
 @HeaderName varchar(200),
 @GroupName varchar(200),
 @TaskGroupName varchar(200),
 @TaskDesc nvarchar(200)

DECLARE header_cursor CURSOR FOR   
SELECT DISTINCT tblCheckListHdr.CLHName, tblCheckListDtl.CLDGroupName
	FROM tblCheckListHdr
	INNER JOIN tblCheckListDtl ON tblCheckListHdr.CLHId = tblCheckListDtl.CLHId
	INNER JOIN tblServiceType ON tblCheckListHdr.CLHId = tblServiceType.SelectedCLHId
	INNER JOIN tblWorkOrder ON tblServiceType.ServiceTypeId = tblWorkOrder.ServiceType
WHERE tblWorkOrder.WorkOrderId = @WOid
	AND tblCheckListHdr.CLHActive = 1
ORDER BY tblCheckListDtl.CLDGroupName

OPEN header_cursor 

FETCH NEXT FROM header_cursor INTO @HeaderName, @GroupName

WHILE @@FETCH_STATUS = 0  
	BEGIN  

	INSERT INTO #Checklist (Checklist, Notes)
	SELECT @HeaderName + ' | ' + @GroupName, 'Y, N, NA, Notes' AS Notes

		DECLARE detail_cursor CURSOR FOR   
		SELECT tblCheckListDtl.CLDGroupName
			, tblCheckListDtl.CLDTaskDesc
		FROM tblCheckListHdr
		INNER JOIN tblCheckListDtl ON tblCheckListHdr.CLHId = tblCheckListDtl.CLHId
		INNER JOIN tblServiceType ON tblCheckListHdr.CLHId = tblServiceType.SelectedCLHId
		INNER JOIN tblWorkOrder ON tblServiceType.ServiceTypeId = tblWorkOrder.ServiceType
		WHERE tblWorkOrder.WorkOrderId = @WOid
			AND tblCheckListHdr.CLHActive = 1
			AND tblCheckListDtl.CLDActive = 1
			AND tblCheckListDtl.CLDGroupName = @GroupName
		ORDER BY tblCheckListDtl.CLDDisplayOrder

		OPEN detail_cursor  
		FETCH NEXT FROM detail_cursor INTO @TaskGroupName, @TaskDesc

		WHILE @@FETCH_STATUS = 0  
		BEGIN  

				INSERT INTO #Checklist (Checklist)
				SELECT @TaskDesc

			FETCH NEXT FROM detail_cursor INTO @TaskGroupName, @TaskDesc

		END

		CLOSE detail_cursor  
		DEALLOCATE detail_cursor  
		FETCH NEXT FROM header_cursor INTO @HeaderName, @GroupName
	END   
CLOSE header_cursor;  
DEALLOCATE header_cursor

SELECT #Checklist.Checklist, #Checklist.Notes
FROM #Checklist
    回复于
  • 0

    这可以通过PIVOT实现，像这样的东西 .

    IF ( OBJECT_ID('tempdb..#tmpDB') IS NOT NULL )
    BEGIN
        DROP TABLE  #tmpDB
    END


SELECT  testing ,
        production
INTO    #tmpDB
FROM    ( 


            SELECT 
              tblDtl.GROUP [group]
            , tblDtl.Task [task]
            FROM tblHdr
            INNER JOIN tblDtl ON tblHdr.CLHId = tblDtl.CLHId
            INNER JOIN tblType ON tblHdr.CLHId = tblType.SelectedId
            INNER JOIN tblOrder ON tblType.TypeId = tblOrder.Type
            ORDER BY tblDtl.CLDDisplayOrder




        ) P PIVOT ( MAX(task) FOR [group] IN ( testing, production ) ) PVT;
WITH    CTEtesting
          AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY testing ) ctr ,
                        testing
               FROM     #tmpDB
               WHERE    production IS NULL
             ),
        CTEProduction
          AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY production ) ctr ,
                        production
               FROM     #tmpDB
               WHERE    testing IS NULL
             ),
        CTE
          AS ( SELECT   testing ,
                        production
               FROM     CTEProduction P
                        FULL JOIN CTEtesting T ON T.ctr = P.ctr
             )
    SELECT  *
    FROM    CTE

    结果：

    testing              production
-------------------- --------------------
Add object           Prompt for input
Report object        Show error log
Show error log       Show report
Vaidate object       Validate input
Validate report      Validate object
Vanipulate object    NULL

(6 row(s) affected)
    回复于

相关问题