Deep dive: Aggregate questionnaires via Power BI template: Difference between revisions
Marek Rodak (talk | contribs) |
Marek Rodak (talk | contribs) |
||
| Line 10: | Line 10: | ||
==About the template== | ==About the template== | ||
The template consists mostly of queries, functions, and parameters. Visuals are not included, as the questionnaire data is use-case-specific. | |||
===Parameters=== | |||
You have to define the parameters as soon as the template opens. | |||
; Mandatory parameters: | ; Mandatory parameters: | ||
*OrgUrl - clean Environment URL to which you want to connect (no https:// or tailings .../). | *OrgUrl - clean Environment URL to which you want to connect (no https:// or tailings .../). | ||
If only the OrgUrl param is defined, all questionnaires are queried and deserialised. | If only the OrgUrl param is defined, all questionnaires are queried and deserialised. You can then define a query for each template separately. TemplateA query is an example you can duplicate or edit. | ||
; Optional params: | ; Optional params: | ||
*TemplateID - | *TemplateID - If you wish to deserialize one specific questionnaire template, use this parameter. | ||
*Questions - | *Questions -If you wish to deserialize a specific questions, you can use this parameter, e.g. modelinfo, serialnumber, ... . | ||
* | |||
===Queries=== | |||
*Questionnaires query contains all questionnaire data as is from the dataverse. | |||
*Answers query contains deserialized and processed answers in the form of records. This query is pre-pivot. That means a final pivot query is necessary to structure the data in the columns: question name, and the rows: question answer format. | |||
*TemplateA is a sample query containing the final pivot step. This query can be duplicated or edited in the advanced editor. | |||
Revision as of 09:57, 6 February 2026
| Warning | Work in progress! We are in the process of updating the information on this page. Subject to change. |
In this guide, we will demonstrate a prebuilt Power BI template for deserializing questionnaire answers. Questiannaire answers are inherently stored in the resco_serializedanswers columns in JSON format. To use this data in external reporting tools, we need to deserialize it back into a structured format.
In [link to deep dive], we deserialized JSON manually using Power Query. In this guide, we will use a Power BI template. Power BI templates are pre-built templates that usually include parameterized imports, Power Query, and visualizations. Templates are used as an industry standard for report distribution.
Download Power BI templates
For your convenience, we provide two templates depending on your storage entity:
- questionnaires
- questionnaireanswers
About the template
The template consists mostly of queries, functions, and parameters. Visuals are not included, as the questionnaire data is use-case-specific.
Parameters
You have to define the parameters as soon as the template opens.
- Mandatory parameters
- OrgUrl - clean Environment URL to which you want to connect (no https:// or tailings .../).
If only the OrgUrl param is defined, all questionnaires are queried and deserialised. You can then define a query for each template separately. TemplateA query is an example you can duplicate or edit.
- Optional params
- TemplateID - If you wish to deserialize one specific questionnaire template, use this parameter.
- Questions -If you wish to deserialize a specific questions, you can use this parameter, e.g. modelinfo, serialnumber, ... .
Queries
- Questionnaires query contains all questionnaire data as is from the dataverse.
- Answers query contains deserialized and processed answers in the form of records. This query is pre-pivot. That means a final pivot query is necessary to structure the data in the columns: question name, and the rows: question answer format.
- TemplateA is a sample query containing the final pivot step. This query can be duplicated or edited in the advanced editor.