I dint get a chance to work on this still. But thought can share to my bloggers to read and make use of it. Please find below the tech note with steps to implement the Survey Widget in MicroStrategy.
From the reading i did, we would need to focus on below requirements/things to be aware before implementing it.
- Need to have CREATE TABLE access or Share the scripts to DBA
- MicroStrategy 9.4.0 & Above versions
- Need to have MicroStrategy Transaction Services license (I felt – as it writes back to DB)
- Once Survey Widget is configured, the user is who is going to use must need the Txn Service privilege enabled to work with.
Steps provided for working with Survey Widget in this post focused are mentioned below in bullets:
- Survey Widget Creation Steps
- Survey Widget Title Update Steps
- Survey Widget in Mobile with Conditional flow (Upon one answer to enable other)
- Creating the Question Groups (ie) Related questions grouped together ~ Steps
Survey Widget Creation Steps
In MicroStrategy Mobile 9.4.0 on iPad/iPhone, users have the capability to implement a survey widget through MicroStrategy.
Below is the step-by-step instruction to create a Survey Widget:
- Create a table on the database with the following columns and populate the table with sample data.
create table Survey(surveyID int not null,questionID int not null,questionTitle varchar(255) null,questionType int null,answerID int null,answerTitle varchar(255) null,answerValue varchar(255) null,currentAnswer varchar(255) null)
In this sample, only one survey form is designed. So data is populated only with surveyID=1:
- Create a DSN points to above database, create a database instance(DBI) on it and add the DBI to the MicroStrategy project.
- Add the table Survey to warehouse catalog.
- Create the following attributes based on each of above columns and update the schema:
- Create a report “Survey” with above attributes:
- Create a table for Survey response in the database with the following columns:
create table Response(surveyID int not null,questionID int not null,questionTitle varchar(255) null,questionType int null,answerID int null,answerTitle varchar(255) null,answerValue varchar(255) null,userInput varchar(255) null);
- Create a Freeform SQL transaction report “T”:
- Go to MicroStrategy Web. Create a report services document and add report “Survey” as dataset. Drag the dataset to grid into document.
- Right click on the grid and go to “Properties and formatting”. Go to”Grid”, uncheck “Enable incremental fetch in grid” and apply it. Then go to “Widget” and set as DHTML-Survey.
- Go to Widget properties and match each item:
- Configure transaction to the grid as below:
- Create an action selector button to submit the transaction.
- Save the document and run in Express mode or run the document from iPad:
- Fill in the survey answer and press submit.
- Check the Response table in the database. The record is updated.
Adding Survey Title Steps
Follow the steps below to display optional information such as survey title in Survey Widget in MicroStrategy Web 9.4.0 and Mobile 9.4.0:
- Insert a column surveyTitle in Survey table:
- Create an attribute SurveyTitle based on the above column and update the schema.
- Include the above attribute in the document dataset.
- Edit widget properties. Check “Show survey title” and uncheck “Show only required field”. Match the field for Survey Title.
- Save the change. The survey title can be displayed.
Conditional Statement Update in Survey Widget
This new feature allows defining conditional logic on the Survey Widget to show or hide different questions depending on previous answers, in the MicroStrategy Mobile 9.4.1 iOS Update 5 app.
Questions in a survey can be shown or hidden, depending on the respondents’ answers. For example, a follow-up question can be displayed if the answer to one of the survey questions needs additional comments or clarification.
To show or hide questions in a survey, in addition to the information required to create a survey with simple questions, aCondition statement object must be provided on the widget’s grid.
For each follow-up question, the Condition statement checks the answer to an earlier question on the survey and determines whether the question will be displayed or not.
For example, Question 8 is a follow-up question that can be displayed only if the answer to Question 5 is yes.
The Condition Statement must be in the following format:
SectionID;QuestionID;QuestionNumber;GroupAnswerID=
Where:
– SectionID, if applicable, is the Section ID of the question.
– QuestionID is the Question ID.
– QuestionNumber, if applicable, is the Question Number.
– GroupAnswerID, if applicable, is the Group Answer ID for the question.
– CheckAnswerID, is the regular expression to compare to the answer for the survey question. If the answers match, the follow-up question is shown.
In order to take advantage of this new functionality, follow the steps below:
- Modify the survey table in the database and add a new column called Conditions.
- Add a conditional statement to the questions that will only display under specific conditions.
Example: For Question 5, the conditional statement is 1;2;;=^B .This means that if the Answer ID for Section 1, Question 2 is B, Question 5 is displayed on the survey. For any other answer to Question 2, the question is not displayed.In the screenshot below, the condition statement for Question ID 8 has been defined as follows: 1;7;7;=^1¦4, which means that it will only be visible if the answer for the question with SectionID 1, QuestionID 7 and QuestionNumber 7 is either 1 or 4. Question 7 has been defined as a Radio Button, therefore 1;7;7;=^1¦4 means that selecting either the first or fourth answer will make Question 8 visible:
- Update the Survey table in MicroStrategy through the Warehouse Catalog.
- Create an attribute based on the Conditions column and update Schema.
- Create a report Survey with all the Survey attributes needed including the one created in step 4.
- Create a freeform SQL transaction report that will insert the answers into the Response table. For this particular instance, the transaction report has been defined as follows:
Begin Transaction
insert into SurveyTestCaseCondition(SurveyID, SurveyTitle, SectionID, SectionTitle, QuestionNum, QuestionID, QuestionTitle, QuestionDescription, QuestionType, RequiredFlag, QuestionAction, Condition, Restriction, GroupBreak, GroupStyle, GroupAnswerID, AnswerID, AnswerTitle, AnswerValue, CurrentAnswer)
values ([RTCSurveyID@ID],[RTCSurveyTitle@ID],[RTCSectionID@ID],[RTCSecionTitle@ID],[RTCQsNum@ID],[RTCQsID@I D],[RTCQsTitle@ID],[RTCQsDESC@ID],[RTCQsType@ID],[ RTCRequire@ID][?, [?TF[RTCQsAction@ID],[RTCCondition@ID],[RTCRestric tion@ID],[RTCGpBreak@ID],[RTCGpStyle@ID],[RTCGpAns werID@ID],[RTCAnswerID@ID],[RTCAnswerTitle@ID],[RT CAnswerValue@ID],[RTCCurrentAnswer]);
End Transaction
- In MicroStrategy Web, create a Report Services document with the report created in Step 5 as the dataset. Drag the dataset into the document.
- Right click on the grid and go to “Properties and formatting” . Go to”Grid”, uncheck “Enable incremental fetch in grid” and apply it. Then go to “Widget” and set as DHTML-Survey.
- Go to Widget Advanced Properties and map the survey elements to the dataset attributes:
- Right click on the grid and go to “Configure Transaction”.
- Select the transaction report and map the transaction inputs to the grid objects, as shown in the example below:
- Create an action selector button to submit the transaction.
- Save the document and run from the Mobile device. As shown below, when selecting the first or the fourth answer for Question 7, Question 8 will appear:
However, if any other answer is selected, Question 8 will remain hidden:
Refer to the MicroStrategy Dashboards and Widgets Creation guide for further instructions about creating Survey Widgets.
The MicroStrategy Internal Reference number for the feature described in this technical note is 952465.
Question Grouping Usage Steps
Question Group can be used to display a group of related questions as below, where the question “Personal Info” is combination of 4 individual questions:
To enable question group, the following information must be provided, compared with the basic components in TN45567:
Section ID, Section Title, Question Number, Group Style
Follow the steps below to use Question Group in Survey Widget with the above effect:
- In Survey table, add columns SectionID, SectionTitle, QuestionNumber and GroupStyle. Insert a record for QuestionTitle=Personal Info, with QuesitonType=11. Here, QuesitonType=11 means “Personal Info’ will be taken as the main question. Fill in other values as below:
- Create attributes based on above new columns and include them to the report services document datasets.
- Modify the widget properties as below:
Ref: Question Group
Have a Good Day & Happy Reading!