Generating Word reports programmatically using SQL Server Reporting Services 2012 with T-SQL(Stored Procedure)

  • Hello,

    How can i generating Word(report export to *.doc, *.docx) reports programmatically using SQL Server Reporting Services 2012 with T-SQL(Stored Procedure)

    Thanks

  • do you mean dynamically select a query and result set, and use SSRS to export it to doc/docX? if that's the question, i don't believe you can. i'm awful sure SSRS requires the report to be defined first, but i'd love to be wrong.

    you can call a report via TSQL, and if it has a subscription that sends a specific report to disk, that can be done via TSQL.

    for adhoc/programmatic queries, i use various CLR's i wrote to export to disk in html and other text-based formats. pdf/word/true excel are a lot harder.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • n.oruc (3/11/2015)


    Hello,

    How can i generating Word(report export to *.doc, *.docx) reports programmatically using SQL Server Reporting Services 2012 with T-SQL(Stored Procedure)

    Thanks

    **This assumes you already have the report in place**

    I personally would create a report subscription that would export the report to a fileshare.

    Once this is done you can then execute the job using EXEC ReportServer.dbo.AddEvent or execute to SQL Server job it creates

    Credit to Sankar Reddy[/url] for the script below

    [font="Verdana"]SELECT CAST(b.name AS UNIQUEIDENTIFIER) AS JobName ,

    e.Name ,

    e.Path ,

    d.Description ,

    a.SubscriptionID ,

    LastStatus ,

    EventType ,

    'EXEC ReportServer.dbo.AddEvent @EventType=''TimedSubscription'', @EventData=''' + CAST(a.SubscriptionID AS VARCHAR(40)) + '''' AS ReportCommand

    FROM ReportServer.dbo.ReportSchedule a

    JOIN msdb.dbo.sysjobs b ON CAST(a.ScheduleID AS NVARCHAR(50)) = CAST(b.name AS NVARCHAR(50))

    JOIN ReportServer.dbo.ReportSchedule c ON CAST(b.name AS NVARCHAR(50)) = CAST(c.ScheduleID AS NVARCHAR(50))

    JOIN ReportServer.dbo.Subscriptions d ON CAST(c.SubscriptionID AS NVARCHAR(50)) = CAST(d.SubscriptionID AS NVARCHAR(50))

    JOIN ReportServer.dbo.Catalog e ON CAST(d.Report_OID AS NVARCHAR(50)) = CAST(e.ItemID AS NVARCHAR(50))[/font]

  • I want genereting report in word format and send by mail.

    I thing.

    First prepared report in SSRS.(I done)

    Second write stored procedure (T-SQL) once a day this stored procedure will execute by JOB

    Procedure must execute and export this report to word format in file system (Can not do it help please)

    Third with sp_send_dbmail send this file by mail(I done)

  • Yes i want call a report and export this report to word format in file system via TSQL and then send this file by mail:-)

  • sql_lock (3/11/2015)


    n.oruc (3/11/2015)


    Hello,

    How can i generating Word(report export to *.doc, *.docx) reports programmatically using SQL Server Reporting Services 2012 with T-SQL(Stored Procedure)

    Thanks

    **This assumes you already have the report in place**

    I personally would create a report subscription that would export the report to a fileshare.

    Once this is done you can then execute the job using EXEC ReportServer.dbo.AddEvent or execute to SQL Server job it creates

    Credit to Sankar Reddy[/url] for the script below

    [font="Verdana"]SELECT CAST(b.name AS UNIQUEIDENTIFIER) AS JobName ,

    e.Name ,

    e.Path ,

    d.Description ,

    a.SubscriptionID ,

    LastStatus ,

    EventType ,

    'EXEC ReportServer.dbo.AddEvent @EventType=''TimedSubscription'', @EventData=''' + CAST(a.SubscriptionID AS VARCHAR(40)) + '''' AS ReportCommand

    FROM ReportServer.dbo.ReportSchedule a

    JOIN msdb.dbo.sysjobs b ON CAST(a.ScheduleID AS NVARCHAR(50)) = CAST(b.name AS NVARCHAR(50))

    JOIN ReportServer.dbo.ReportSchedule c ON CAST(b.name AS NVARCHAR(50)) = CAST(c.ScheduleID AS NVARCHAR(50))

    JOIN ReportServer.dbo.Subscriptions d ON CAST(c.SubscriptionID AS NVARCHAR(50)) = CAST(d.SubscriptionID AS NVARCHAR(50))

    JOIN ReportServer.dbo.Catalog e ON CAST(d.Report_OID AS NVARCHAR(50)) = CAST(e.ItemID AS NVARCHAR(50))[/font]

    How i can call a report and export this report to word format in file system via TSQL [Smile]

  • Lowell (3/11/2015)


    do you mean dynamically select a query and result set, and use SSRS to export it to doc/docX? if that's the question, i don't believe you can. i'm awful sure SSRS requires the report to be defined first, but i'd love to be wrong.

    you can call a report via TSQL, and if it has a subscription that sends a specific report to disk, that can be done via TSQL.

    for adhoc/programmatic queries, i use various CLR's i wrote to export to disk in html and other text-based formats. pdf/word/true excel are a lot harder.

    Yes i want call a report and export this report to word format in file system via TSQL and then send this file by mail [Smile]

  • has the report already been created within SSRS and are you wanting to call directly from TSQL or the OS?

  • sql_lock (3/11/2015)


    has the report already been created within SSRS and are you wanting to call directly from TSQL or the OS?

    I am manually greate report in visual studio then i want execute(call) this report and export(save) report to(in) word format in file system via T-SQL from stored procedure

    I can execute and export to word via PHP but i do this work via t-SQL

    This is code via PHP

    ...........

    define("REPORT", "/KADR/".$repname);

    require_once 'SDKforPHP/bin/SSRSReport.php';

    define("SERVICE_URL", "http://10.0.100.47/ReportServer/");

    try

    {

    $ssrs_report = new SSRSReport(new Credentials('*******', '********'), SERVICE_URL);

    if (isset($_REQUEST['rs:ShowHideToggle']))

    {

    $ssrs_report->ToggleItem($_REQUEST['rs:ShowHideToggle']);

    }

    else

    {

    $ssrs_report->LoadReport2(REPORT, NULL);

    }

    $per_id=$_POST['per_id'];

    $executionInfo = $ssrs_report->LoadReport2(REPORT, NULL);

    $parameters = array();

    $parameters[0] = new ParameterValue();

    $parameters[0]->Name = "per_id";

    $parameters[0]->Value = $per_id;

    $ssrs_report->SetExecutionParameters2($parameters);

    $ssrs_report->LoadReport2(REPORT, NULL);

    $renderAsWORD = new RenderAsWORD();

    $result_WORD = $ssrs_report->Render2($renderAsWORD,

    PageCountModeEnum::$Estimate,

    $Extension,

    $MimeType,

    $Encoding,

    $Warnings,

    $StreamIds);

    if (!$handle = fopen("Report/$file_name.doc", 'wb'))

    {

    echo "Cannot open file for writing output";

    exit;

    }

    if (fwrite($handle, $result_WORD) === FALSE)

    {

    echo "Cannot write to file";

    exit;

    }

    fclose($handle);

    //echo "<center><a id='b' class='openme' href='Report/test.doc'>Hesabati WORD s?klind? a?</a>";

    print "Report/test.doc";

    }

    catch (SSRSReportException $serviceException)

    {

    print("<pre>");

    print_r($serviceException);

    print("</pre>");

    }

    function getPageURL()

    {

    $PageUrl = $_SERVER["HTTPS"] == "on"? 'https://' : 'http://';

    $uri = $_SERVER["REQUEST_URI"];

    $index = strpos($uri, '?');

    if($index !== false)

    {

    $uri = substr($uri, 0, $index);

    }

    $PageUrl .= $_SERVER["SERVER_NAME"] . ":" . $_SERVER["SERVER_PORT"] . $uri;

    return $PageUrl;

    }

  • there is no ability to directly render as any format (ie $renderAsWORD) from within SQL server; those functionalite are all possible with an exdtrnal resource.

    you can do html, fixed width, csv or other delimited formats, but that's the only thing natively available.

    html is just plain text, anyway.

    here's a simple example of embedding the data as html in an email;

    Declare @HTMLBody varchar(max),

    @TableHead varchar(max),

    @TableTail varchar(max)

    Set NoCount On;

    Set @TableTail = '</table></body></html>';

    Set @TableHead = '<html><head>' +

    '<style>' +

    'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +

    '</style>' +

    '</head>' +

    '<body><table cellpadding=0 cellspacing=0 border=0>' +

    '<tr bgcolor=#FFEFD8><td align=center><b>Server Name</b></td>' +

    '<td align=center><b>Product</b></td>' +

    '<td align=center><b>Provider</b></td>' +

    '<td align=center><b>Data Source</b></td>' +

    '<td align=center><b>Is Linked?</b></td></tr>';

    Select @HTMLBody = (Select Row_Number() Over(Order By is_linked, name) % 2 As [TRRow],

    name As

    ,

    product As

    ,

    provider As

    ,

    data_source As

    ,

    is_linked As

    From sys.servers

    Order By is_linked, name

    For XML raw('tr'), Elements)

    -- Replace the entity codes and row numbers

    Set @HTMLBody = Replace(@HTMLBody, '_x0020_', space(1))

    Set @HTMLBody = Replace(@HTMLBody, '_x003D_', '=')

    Set @HTMLBody = Replace(@HTMLBody, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')

    Set @HTMLBody = Replace(@HTMLBody, '<TRRow>0</TRRow>', '')

    Select @HTMLBody = @TableHead + @HTMLBody + @TableTail

    -- output that is used for the body of an email

    Select @HTMLBody

    --this assumes a profile was set as "default", so i don't have to explicitly specify which one to use. else you get this error:

    /*

    Msg 14636, Level 16, State 1, Procedure sp_send_dbmail, Line 112

    No global profile is configured. Specify a profile name in the @profile_name parameter.

    */

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name='My DBMail Profile',

    @recipients='lowell@somedomain.com',

    @subject = 'Simple Email Example',

    @body = @HTMLBody,

    @body_format = 'HTML'

    --@body_format = 'TEXT'

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell (3/11/2015)


    there is no ability to directly render as any format (ie $renderAsWORD) from within SQL server; those functionalite are all possible with an exdtrnal resource.

    you can do html, fixed width, csv or other delimited formats, but that's the only thing natively available.

    html is just plain text, anyway.

    here's a simple example of embedding the data as html in an email;

    Declare @HTMLBody varchar(max),

    @TableHead varchar(max),

    @TableTail varchar(max)

    Set NoCount On;

    Set @TableTail = '</table></body></html>';

    Set @TableHead = '<html><head>' +

    '<style>' +

    'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +

    '</style>' +

    '</head>' +

    '<body><table cellpadding=0 cellspacing=0 border=0>' +

    '<tr bgcolor=#FFEFD8><td align=center><b>Server Name</b></td>' +

    '<td align=center><b>Product</b></td>' +

    '<td align=center><b>Provider</b></td>' +

    '<td align=center><b>Data Source</b></td>' +

    '<td align=center><b>Is Linked?</b></td></tr>';

    Select @HTMLBody = (Select Row_Number() Over(Order By is_linked, name) % 2 As [TRRow],

    name As

    ,

    product As

    ,

    provider As

    ,

    data_source As

    ,

    is_linked As

    From sys.servers

    Order By is_linked, name

    For XML raw('tr'), Elements)

    -- Replace the entity codes and row numbers

    Set @HTMLBody = Replace(@HTMLBody, '_x0020_', space(1))

    Set @HTMLBody = Replace(@HTMLBody, '_x003D_', '=')

    Set @HTMLBody = Replace(@HTMLBody, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')

    Set @HTMLBody = Replace(@HTMLBody, '<TRRow>0</TRRow>', '')

    Select @HTMLBody = @TableHead + @HTMLBody + @TableTail

    -- output that is used for the body of an email

    Select @HTMLBody

    --this assumes a profile was set as "default", so i don't have to explicitly specify which one to use. else you get this error:

    /*

    Msg 14636, Level 16, State 1, Procedure sp_send_dbmail, Line 112

    No global profile is configured. Specify a profile name in the @profile_name parameter.

    */

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name='My DBMail Profile',

    @recipients='lowell@somedomain.com',

    @subject = 'Simple Email Example',

    @body = @HTMLBody,

    @body_format = 'HTML'

    --@body_format = 'TEXT'

    A lot off thanks i understand. I have another some question.

    1)Can i call same php or javascript code via T-SQL?

    2)What another way resolve my problem i must do it.

  • You could just set up a subscription for the report and have that send the email or is that not suitable?

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • mister.magoo (3/11/2015)


    You could just set up a subscription for the report and have that send the email or is that not suitable?

    Thanks all suitable.A don't knew about it.Now i understand.

  • Viewing 13 posts - 1 through 12 (of 12 total)

    You must be logged in to reply to this topic. Login to reply