For anyone else looking for a custom report on ideas, I put something together for our product managers. The data is formatted to do one row per vote, as they wanted to be able to see easily which customer voted for each idea (by email address). They'll then copy the output into a csv file that they'll manipulate in Excel. The fields I'm reporting on are:
Idea title, email of the person who submitted the idea, email of the person who voted for the idea, status, creation date, topic ID of the idea, first label, and URL to the idea.
There's a check for whether the person viewing the data is an employee, which looks to make sure they have the Employee role. If you use another role on your community for admins or employees you can change that roleName out. That way you aren't showing email addresses to non-admins if they find the custom page you put it on.
Then there's a check for staging vs production, and which ideation board to point to in either case. You'll replace those with the boardID of the respective staging and production Ideation sections that you are reporting on.
I'm only showing the first label, if there are any on the idea, as that's what our product team is using to categorize which product manager the idea is assigned to. If there are no labels then it shows "missing label".
For the URL field, you'll need to substitute in your own community URL if you want that link to work.
Put this code into a custom component and then you can put it a custom page to get the output.
If you have any questions or need help getting it to work, please let me know!
<#assign roles = restadmin("/users/id/" + user.id?c + "/roles").roles.role>
<#assign roleSize = roles?size>
<#if (roleSize > 1) >
<#list roles as role>
<#assign roleName = role.name?trim />
<#if roleName == "Employee">
<#if config.getString("phase", "prod") == "stage">
<#assign board='789' />
<#else>
<#assign board='Ideation' />
</#if>
Idea Name, Submitter email, Voter email, Status, Day, Year, IdeaID, Label, URL
<#assign idea_obj = restadmin("2.0","/search?q=" + "Select * FROM messages WHERE board.id = '${board}' AND conversation.style='idea' AND depth=0"?url) />
<#list idea_obj.data.items as fubar>
<#assign authoremail = restadmin("2.0","/search?q=" + "Select * FROM users WHERE id='${fubar.author.id}'"?url) />
<#assign voters = restadmin("2.0","/search?q="+fubar.kudos.query?url) />
<#list voters.data.items as uservotes>
<#assign emailaddy = restadmin("2.0","/search?q=" + "Select * FROM users WHERE id='${uservotes.user.id}'"?url) />
<#assign labels = restadmin("2.0","/search?q=" + "Select * FROM labels WHERE messages.id = '${fubar.id}'"?url) />
<#assign firstlabel = "missing label">
<#attempt>
<#assign firstlabel = labels.data.items[0].text />
<#recover>
</#attempt>
<br>${fubar.subject},${authoremail.data.items[0].email},${emailaddy.data.items[0].email},${fubar.status.key},${fubar.post_time?date},${fubar.id},${firstlabel},http://community.logrhythm.com${fubar.view_href}
</#list>
</#list>
</#if>
</#list>
</#if>
