How to create reports?

[gouki2005]

I am using sql server 2008 but i just want create reports usin sql statements just sql without any other application like Visual studio is posible??

[buddylee17]

I've created (and automated) many html email based reports using just T-SQL and Database Mail. I like using For XML PATH to turn a result set into an html formatted table. Try the following:

DECLARE @Table TABLE (
Column1 VARCHAR(9) NULL,
Column2 VARCHAR(9) NULL
)
INSERT @Table
SELECT 'Col1Value', 'Col2Value'

DECLARE @HTML VARCHAR(MAX)
DECLARE @CrLf CHAR(2) 
SET @CrLf = CHAR(13) + CHAR(10)

SET @HTML = '
<h1>Here is your report!</h1>
<table>
<tr><th>Column1</th><th>Column2</th></tr>' + @CrLf +
    CAST ( ( SELECT td = Column1, '',td = Column2,''
			   FROM @Table
              FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX) ) +
+ @CrLf + '</table>'

Print @HTML

This will print an html formatted table.

You can then use database mail to send the report to the recipients:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'insert your db mail profile name here', --you have to set this up before you run
    @recipients = 'insert a recipient email address here',
    @body = @HTML,
    @subject = 'Here is your report',
    @body_format ='HTML';