Generate HTML Report using Stored Procedure in ASP.Net

Last Reply on Aug 02, 2014 05:50 AM By Azim

Posted on Jul 31, 2014 03:00 AM

Mr.Mudassar

I have the following 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

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

 

Results 1 - 5 of 8 12
Posted on Jul 31, 2014 03:00 AM

Get the data in DataTable and then you can generate either HTML or PDF reports


Posted on Jul 31, 2014 03:15 AM

Mr.Mudassar

I have tried using data set and data table. But Dynamic SP cannot be populated the parameter.

Pls advice me

 

Maideen

 


Posted on Jul 31, 2014 05:37 AM

Please share the database table name, structure (i.e. columns) and some sample data
So that I can build some sample and give it to you


Posted on Jul 31, 2014 08:11 AM

Mr.Azim

This is table structure and database

Pls advice me.

Thank you

Maideen

  

CREATE TABLE [dbo].[Z_tbl_Circulation_Head](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[member] [nvarchar](50) NULL,
	[language] [nvarchar](30) NULL,
	[type] [nvarchar](10) NULL,
	[PlatForm] [nvarchar](50) NULL,
	[category] [nvarchar](20) NULL,
	[MainArea] [nvarchar](50) NULL,
	[Remarks] [nvarchar](500) NULL,
	[AVGIssue_Paid] [numeric](18, 0) NULL,
	[MY] [nvarchar](50) NULL

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Z_tbl_Circulation_Head] ADD  CONSTRAINT [DF_Z_tbl_Circulation_Head_Type]  DEFAULT (N'Paid') FOR [type]
GO

ALTER TABLE [dbo].[Z_tbl_Circulation_Head] ADD  CONSTRAINT [DF_Z_tbl_Circulation_Head_Category]  DEFAULT (N'NewsPaper') FOR [category]
GO

Data

 

 

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[Z_usp_Insert_data] 
AS
BEGIN
	SET NOCOUNT ON;


-- NEWSPAPER
INSERT INTO [dbo].[Z_tbl_Circulation_Head]([member],[language],[type],[PlatForm],[category],[MainArea],[Remarks],[AVGIssue_Paid],[MY])
VALUES ('Utusan Malaysia','Bahasa Malaysia','Newspaper','Print','Paid','Peninsular Malaysia','remark','181356','JUN-2012')

INSERT INTO [dbo].[Z_tbl_Circulation_Head]([member],[language],[type],[PlatForm],[category],[MainArea],[Remarks],[AVGIssue_Paid],[MY])
VALUES ('Minggunan Malaysia','Bahasa Malaysia','Newspaper','Print','Paid','Peninsular Malaysia','remark','82471','JUN-2012')

INSERT INTO [dbo].[Z_tbl_Circulation_Head]([member],[language],[type],[PlatForm],[category],[MainArea],[Remarks],[AVGIssue_Paid],[MY])
VALUES ('Berita Harian','Bahasa Malaysia','Newspaper','Print','Paid','Peninsular Malaysia','remark','15247','JUN-2012')

INSERT INTO [dbo].[Z_tbl_Circulation_Head]([member],[language],[type],[PlatForm],[category],[MainArea],[Remarks],[AVGIssue_Paid],[MY])
VALUES ('The Star','English','Newspaper','Print','Paid','Peninsular Malaysia','remark','54123','JUN-2012')

INSERT INTO [dbo].[Z_tbl_Circulation_Head]([member],[language],[type],[PlatForm],[category],[MainArea],[Remarks],[AVGIssue_Paid],[MY])
VALUES ('The Edge','English','Newspaper','Print','Paid','Peninsular Malaysia','remark','92123','JUN-2012')

INSERT INTO [dbo].[Z_tbl_Circulation_Head]([member],[language],[type],[PlatForm],[category],[MainArea],[Remarks],[AVGIssue_Paid],[MY])
VALUES ('Sin Chew - Day','Chinese','Newspaper','Print','Paid','Peninsular Malaysia','remark','721323','JUN-2012')

INSERT INTO [dbo].[Z_tbl_Circulation_Head]([member],[language],[type],[PlatForm],[category],[MainArea],[Remarks],[AVGIssue_Paid],[MY])
VALUES ('Guang Ming - Night','Chinese','Newspaper','Print','Paid','Peninsular Malaysia','remark','58423','JUN-2012')

INSERT INTO [dbo].[Z_tbl_Circulation_Head]([member],[language],[type],[PlatForm],[category],[MainArea],[Remarks],[AVGIssue_Paid],[MY])
VALUES ('Utusan Malaysia','Bahasa Malaysia','Newspaper','Print','Paid','Peninsular Malaysia','remark','11356','DEC-2012')

INSERT INTO [dbo].[Z_tbl_Circulation_Head]([member],[language],[type],[PlatForm],[category],[MainArea],[Remarks],[AVGIssue_Paid],[MY])
VALUES ('Minggunan Malaysia','Bahasa Malaysia','Newspaper','Print','Paid','Peninsular Malaysia','remark','91471','DEC-2012')

INSERT INTO [dbo].[Z_tbl_Circulation_Head]([member],[language],[type],[PlatForm],[category],[MainArea],[Remarks],[AVGIssue_Paid],[MY])
VALUES ('Berita Harian','Bahasa Malaysia','Newspaper','Print','Paid','Peninsular Malaysia','remark','914247','DEC-2012')

INSERT INTO [dbo].[Z_tbl_Circulation_Head]([member],[language],[type],[PlatForm],[category],[MainArea],[Remarks],[AVGIssue_Paid],[MY])
VALUES ('The Star','English','Newspaper','Print','Paid','Peninsular Malaysia','remark','87123','DEC-2012')

INSERT INTO [dbo].[Z_tbl_Circulation_Head]([member],[language],[type],[PlatForm],[category],[MainArea],[Remarks],[AVGIssue_Paid],[MY])
VALUES ('The Edge','English','Newspaper','Print','Paid','Peninsular Malaysia','remark','34823','DEC-2012')

INSERT INTO [dbo].[Z_tbl_Circulation_Head]([member],[language],[type],[PlatForm],[category],[MainArea],[Remarks],[AVGIssue_Paid],[MY])
VALUES ('Sin Chew - Day','Chinese','Newspaper','Print','Paid','Peninsular Malaysia','remark','7223','DEC-2012')

INSERT INTO [dbo].[Z_tbl_Circulation_Head]([member],[language],[type],[PlatForm],[category],[MainArea],[Remarks],[AVGIssue_Paid],[MY])
VALUES ('Guang Ming - Night','Chinese','Newspaper','Print','Paid','Peninsular Malaysia','remark','81423','DEC-2012')

END





 


Posted on Jul 31, 2014 09:32 AM

Please give the Table Structure of [dbo].[AVG_Paid] Table