如何在aspx中将存储过程输出为html报告格式 [英] how to do Stored procedure output as html report format in aspx

问题描述

您好b $ b

我在mssql 2012中执行了存储过程。在服务器上正常工作。



我有一个问题我需要将我的SP的输出转换成aspx.page中的HTML报告格式，包含Sub和Grand Total

我不需要GridView



请建议



Maideen



我的SP

  SET   ANSI_NULLS   ON  
  GO  
  SET   QUOTED_IDENTIFIER   ON  
  GO  
 
  ALTER   PROCEDURE  [dbo]。[Z_usp_Circ_Details_Member] 
 
  AS  
  BEGIN  
 
  SET   NOCOUNT   ON ; 
  SELECT  *  FROM （ SELECT  
 MEMBER，[ LANGUAGE ]，[ TYPE ]，[PLATFORM]，副本，CATEGORY ，MAINAREA，YearNo 
  FROM  Z_tbl_Circulation_Details 其中 mainarea = ' 半岛马来西亚' 和语言=  '  English'）TableYearNo 
 pivot（Sum（copies） for  [member] ]  （[太阳]，[星际]））pivotTable 
  END  

结果

英文报纸打印免费半岛马来西亚 2012   603706   5210  
英文报纸打印 Free Peninsular Malaysia  2013   301853   98754  
英文报纸打印支付半岛马来西亚 2012   2547   569146  
英文报纸打印支付半岛马来西亚 2013   3257   284573  

解决方案

嗨Sameer



我有以下动态SP工作正常。我已将数据转储到临时表中并与报表查看器绑定。

但是基于性能级别，它与许多用户使用不兼容。我们已决定以html格式导出

但我不知道如何做到这一点。请给我建议



谢谢



Maideen



这是我的SP

  GO  
 
  SET   ANSI_NULLS   ON  
  GO  
  SET   QUOTED_IDENTIFIER   ON  
  GO  
 
 
  ALTER   PROCEDURE  [dbo]。[Z_usp_Circ_Head_AVGIssue_Paid] 
 
  @ YearStart   VARCHAR （ 4 ），
  @ YearEnd   VARCHAR （ 4 ），
 < span class =code-sdkkeyword> @ Language   VARCHAR （ 20 ），
  @输入  VARCHAR （ 20 ），
  @Platform   VARCHAR （ 100 ），
  @ Category   VARCHAR （ 50 ），
  @ MainArea   VARCHAR （ 100 ）
   -    @ Mode VARCHAR（50） 
  AS  
  BEGIN  
 声明  @ where   varchar （ 1500 ）， @sSQL   VARCHAR （ 2000 ）， @ yearint   INT ， @ i   INT ，  @ vYEAR   VARCHAR （ 1000 ），< span class =code-sdkkeyword> @ vTemp   INT ，
  @ calYear   VARCHAR （ 10 ）
  set   @ where  = ' ' 
 
  IF （ @ Language <> ' 所有'） BEGIN  
  IF （ @ where <> ' '）选择  @ where  =  @ where  + ' 和' 
 选择  @ where  =  @ where  + '  LANGUAGE =''' + ltrim（rtrim（ @Language ））+ ' ''' 
  END  
 
  IF （ @ Type  <> ' 所有'） BEGIN  
 < span class =code-keyword> IF （ @ where <> ' '） select   @ where  =  @ where  + ' 和' 
 选择  @ where  =  @ where  + '  TYPE =''' + ltrim （rtrim（ @ Type ））+ ' '''  
  END  
 
  IF （ @ Platform <> ' 所有'）< span class =code-keyword> BEGIN  
  IF （ @ where  <> ' '）选择  @ where  =  @ where  + ' 和' 
 选择  @ where  =  @ where  + '  PLATFORM =''' + ltrim（rtrim（  @ Platform ））+ ' ''' 
  END  
 
  IF （ @ Category <> ' 所有'） BEGIN  
  IF （ @ where <> ' '）选择  @ where  =  @ where  + ' 和' 
 选择  @ where  =  @ where  + '  CATEGORY =''' + ltrim（rtrim（ @Category ））+ ' ''' 
  END  
 
  IF （ @ MainArea  <> '  All'） BEGIN  
  IF （ @ where <> ' '） select   @ where  =  @ where  + ' 和' 
 选择  @ where  =  @ where  + '  MAINAREA =''' + ltrim （rtrim（ @ MainArea ））+ ' '''  
  END  
 
  SET   @ vYEAR  = ' ' 
 
  SET   @ yearint  = CAST（ @ YearEnd   AS   INT ） -  CAST（ @ YearStart   AS   INT ）
  SET   @ yearint  =  @ yearint  +  1  
  SET   @ i  =  0  
 
  WHILE （ @ i <  @ yearint ）
  BEGIN  
  SET   @ vTemp  =  @ YearStart  +  @我 
 
   -    SET @vYEAR = @vYEAR +'['+ CAST（@vTemp AS VARCHAR（4））+'06'+''' 
   -    SET @vYEAR = @vYEAR +'，'+'['+ CAST（@vTemp AS VARCHAR（4））+'12'+''' 
  SET   @ vYEAR  =  @ vYEAR  + '  [' + '  JUN - ' + CAST（ @ vTemp   AS   VARCHAR （ 4 ））+ ' ]' 
  SET   @ vYEAR  =  @ vYEAR  + ' ，' + '  [' + '  DEC  - ' + CAST（ @ vTemp   AS   VARCHAR （ 4 ））+ ' ]' 
 
 < span class =code-keyword> IF （@ i< @ yearint-1）
  BEGIN  
  SET   @ vYEAR  =  @ vYEAR  + ' ，' 
  END  
  SET   @i  =  @ i  +  1  
 结束 
 
 
 
 设置  @ sSQL  = '  SELECT * FROM 
（SELECT 
 MEMBER，[LANGUAGE]， [TYPE]，[PLATFORM]，[AVGIssue_Paid]，CATEGORY，MAINAREA，REMARKS，MY 
来自Z_tbl_Circulation_Head' 
 
  IF   @ where <> ' ' 
  BEGIN  
 设置  @ sSQL  =  @ sSQL  + ' 其中' +  @ where  
  END  
 
 设置  @sSQL  =  @ sSQL  + ' ）TableYearNo 
 pivot（我的IN的总和（AVGIssue_Paid）（' + @ vYEAR + '  ））pivotTable' 
 
   -    PRINT @ssql  
  INSERT   INTO  [dbo]。[AVG_Paid] 
  EXEC （ @ sSQL ）
 
 
  END  

Hi
I did the stored procedure in mssql 2012. working fine in server.

I have an issue that I need the output of my SP into HTML Report Format in aspx.page with Sub and Grand Total
I no need GridView

Pls advice

Maideen

My SP

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Z_usp_Circ_Details_Member]

AS
BEGIN

	SET NOCOUNT ON;
	SELECT * FROM (SELECT 
	MEMBER,[LANGUAGE],[TYPE],[PLATFORM],copies,CATEGORY,MAINAREA,YearNo
	FROM Z_tbl_Circulation_Details where mainarea='Peninsular Malaysia' and Language='English') TableYearNo
	pivot (Sum(copies) for [member] in ([The sun],[The Star]))pivotTable
END

Results

English	Newspaper	Print	Free 	Peninsular Malaysia	2012	603706	5210
English	Newspaper	Print	Free 	Peninsular Malaysia	2013	301853	98754
English	Newspaper	Print	Paid	Peninsular Malaysia	2012	2547	569146
English	Newspaper	Print	Paid	Peninsular Malaysia	2013	3257	284573

解决方案

Hi Sameer

I have the following Dynamic SP is working fine. I have dumped the data into temp table and bind with Report viewer.
But based on performance level, it is not compatible coz lots of user using. We have decided that export in html format
But I do not know how to do this. Pls advice

Thank you

Maideen

It is my SP

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[Z_usp_Circ_Head_AVGIssue_Paid]
	
	@YearStart VARCHAR(4),
    @YearEnd VARCHAR(4),
	@Language VARCHAR(20),
	@Type VARCHAR(20),
	@Platform VARCHAR(100),
	@Category VARCHAR(50),
	@MainArea VARCHAR(100)
	--@Mode VARCHAR(50)
AS
BEGIN
 declare @where varchar(1500), @sSQL VARCHAR(2000),@yearint INT,@i INT, @vYEAR VARCHAR(1000), @vTemp INT, 
        @calYear VARCHAR(10)
set @where = ''

 IF (@Language <> 'All') BEGIN
	IF (@where <> '') select @where = @where + ' and '
   select @where = @where + 'LANGUAGE = ''' + ltrim(rtrim(@Language )) + ''''
 END

 IF (@Type <> 'All') BEGIN
	IF (@where <> '') select @where = @where + ' and '
   select @where = @where + 'TYPE = ''' + ltrim(rtrim(@Type )) + ''''
 END

 IF (@Platform <> 'All') BEGIN
	IF (@where <> '') select @where = @where + ' and '
   select @where = @where + 'PLATFORM = ''' + ltrim(rtrim(@Platform )) + ''''
 END

 IF (@Category <> 'All') BEGIN
	IF (@where <> '') select @where = @where + ' and '
   select @where = @where + 'CATEGORY = ''' + ltrim(rtrim(@Category )) + ''''
 END

 IF (@MainArea <> 'All') BEGIN
	IF (@where <> '') select @where = @where + ' and '
   select @where = @where + 'MAINAREA = ''' + ltrim(rtrim(@MainArea )) + ''''
 END

 SET @vYEAR = ''

SET @yearint = CAST(@YearEnd AS INT) - CAST(@YearStart AS INT)
SET @yearint = @yearint + 1
SET @i = 0

WHILE (@i < @yearint)
BEGIN
	SET @vTemp = @YearStart + @i

	--SET @vYEAR = @vYEAR + '[' + CAST(@vTemp AS VARCHAR(4)) + '06' + ']'
	--SET @vYEAR = @vYEAR + ',' + '[' + CAST(@vTemp AS VARCHAR(4)) + '12' + ']'
	SET @vYEAR = @vYEAR + '[' + 'JUN-' + CAST(@vTemp AS VARCHAR(4)) + ']'
	SET @vYEAR = @vYEAR + ',' + '[' + 'DEC-' + CAST(@vTemp AS VARCHAR(4)) + ']'

	IF (@i<@yearint-1)
	BEGIN
		SET @vYEAR = @vYEAR + ','
	END
	SET @i = @i + 1
END



Set @sSQL = 'SELECT *  FROM 
(SELECT 
 MEMBER,[LANGUAGE],[TYPE],[PLATFORM],[AVGIssue_Paid],CATEGORY,MAINAREA,REMARKS,MY 
 FROM Z_tbl_Circulation_Head '

IF @where <> ''
BEGIN
	Set @sSQL = @sSQL + ' Where ' + @where
END

Set @sSQL = @sSQL + ' ) TableYearNo
pivot (Sum(AVGIssue_Paid) for my IN ('+@vYEAR+')) pivotTable'

--PRINT @ssql
INSERT INTO [dbo].[AVG_Paid]
EXEC(@sSQL)


 END

这篇关于如何在aspx中将存储过程输出为html报告格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！