Welcome to the LimeSurvey Community Forum

Ask the community, share ideas, and connect with other LimeSurvey users!

Call SSRS report after survey completion

  • bulwer
  • bulwer's Avatar Topic Author
  • Offline
  • New Member
  • New Member
More
6 years 2 months ago #162146 by bulwer
We are trying to make one of our HR forms electronic by setting up a survey for people to complete a new starter form. What I want to do is call an SSRS subscription once the survey is complete which will email our HR department a form with the most recent data. I have the report setup and a subscription which I can trigger with a T-SQL command but what I can't figure out how to do is to call this when the survey is complete. I tried a SQL trigger on insert on the survey table but this is throwing errors and not saving the data - it also seems like data is inserted into the table before the survey is complete so I haven't tried to fix this method.

Does anyone know of a way that I can trigger a T-SQL command when the survey is completed? I am wondering if the end url can be used somehow but I'm not a programmer so don't know if this can be done in PHP for example.
The topic has been locked.
  • TonisOrmisson
  • TonisOrmisson's Avatar
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
6 years 2 months ago #162147 by TonisOrmisson
Replied by TonisOrmisson on topic Call SSRS report after survey completion
The interview is complete is the 'submitdate' IS NOT NULL. Maybe you can trigger that?

... or create a plugin using PluginEvent('afterSurveyComplete');
github.com/LimeSurvey/LimeSurvey/blob/60...timeHelper.php#L1113

... or create a custom web-app to process your exits with redirects
The following user(s) said Thank You: bulwer
The topic has been locked.
  • bulwer
  • bulwer's Avatar Topic Author
  • Offline
  • New Member
  • New Member
More
6 years 2 months ago #162152 by bulwer
Replied by bulwer on topic Call SSRS report after survey completion
That did occur to me but wasn't too sure on triggers. I actually took a brave pill and followed my own suggestion of PHP, found a sample file that runs a SQL command, changed it and it seems to work perfectly as I just force the End URL to open and pointed it at this PHP file that puts up a thank you message and runs the SQL.

In case anyone wants to do the same, here is the php

<?php
$serverName = "dservername";
$connectionInfo = array( "Database"=>"ReportServer", "UID"=>"xxxxx", "PWD"=>"xxxxx" );
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ) {
die( print_r( sqlsrv_errors(), true));
}

$sql = "exec [ReportServer].dbo.AddEvent @EventType='TimedSubscription', @EventData='442a1b56-f1dc-4285-9cae-54ad362af271'";

$stmt = sqlsrv_query( $conn, $sql, $params);
if( $stmt === false ) {
die( print_r( sqlsrv_errors(), true));
}

echo "Thank you for submitting this form - it has been emailed to HR"
?>
The topic has been locked.
  • TonisOrmisson
  • TonisOrmisson's Avatar
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
6 years 2 months ago #162153 by TonisOrmisson
Replied by TonisOrmisson on topic Call SSRS report after survey completion
Yup, this is basically what i meant by the last suggestion.
Just in case: you can also pass (and catch by your script) any linking variables via exit URL also.
manual.limesurvey.org/URL_fields
The topic has been locked.

Lime-years ahead

Online-surveys for every purse and purpose