how to do Stored procedure output as html report format in aspx

Question

0.00/5 (No votes)

See more:

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

SQL

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

SQL

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

Posted 22-Jul-14 15:17pm

Maideen Abdul Kader

Add a Solution

Comments

sameer549 23-Jul-14 12:40pm

you can do it in your c# code in the code behind, on your button_click event you can store the procedure result in DataTable, use a for loop and build a html string using string builder,meanwhile add Total/GrandTotal and then you can use,

here is some sample code

StringBuilder Htmlstr = new StringBuilder();
//build html string for report here
Htmlstr.Append("<script = language='javascript'>");
Htmlstr.Append("window.open('/Report.aspx');");
Htmlstr.Append("</script>");
ClientScript.RegisterStartupScript(GetType(), "GenerateReport", Htmlstr.ToString());

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maideen Abdul Kader · Answer 1 · 2014-07-30T23:10:00

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

SQL

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