- Posts: 5
- Thank you received: 0
Welcome to the LimeSurvey Community Forum
Ask the community, share ideas, and connect with other LimeSurvey users!
Call SSRS report after survey completion
- bulwer
- Topic Author
- Offline
- New Member
Less
More
6 years 2 months ago #162146
by bulwer
Call SSRS report after survey completion was created 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.
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
- Offline
- LimeSurvey Community Team
Less
More
- Posts: 22
- Thank you received: 4
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
... 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
- Topic Author
- Offline
- New Member
Less
More
- Posts: 5
- Thank you received: 0
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"
?>
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
- Offline
- LimeSurvey Community Team
Less
More
- Posts: 22
- Thank you received: 4
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
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.