Hi,
I would like to create a simple report in Idea Exchange - with a list of ideas submitted, user name / screen names, idea status, kudos received, comments received, etc.
Does such a report exists? If not then how do I create one?
Thanks,
Kiran.
Solved! Go to Solution.
@KiranChadha- Yes, you can achieve this using REST API calls.
SELECT subject, post_time, replies.count(*), kudos.sum(weight), status, author FROM messages WHERE conversation.style = 'idea' AND depth = 0
Thank you!
@KiranChadha - If this is still something you are looking for, the easier way is to just enable and use search export via .csv -> https://community.lithium.com/t5/Search-tools/Export-community-search-results-to-a-CSV-file/ta-p/376...
It has everything you appear to want - Just figured it out myself.
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>
Hi @tolstoshev - this is great. Are you still using this? I want to set this up for our community. I'm by no means a developer, so when you say "For the URL field, you'll need to substitute in your own community URL if you want that link to work" does that mean anywhere it says URL, replace that with www.community.servicemax.com ? (My community)
We also have four idea exchanges, based on four different product features/capabilities. Do you think it's possible to set this up for four? And it would show which board it came from?
Thanks in advance for your help and feedback!!!
@nicoleguzzo you just need to replace the community.logrhythm.com part of the code with your own community URL.
Depending what is assigned here:
<#assign board='Ideation' />
sets which ideation board it's going to run from. You could either setup 4 different components, one for each ideation board, or loop through the whole code 4 times with a different board assignment if you want it all to go on one page. . You might need to add a column for the board if you have it all on one page so that when you export this to excel you can filter by board.
Got it. Thank you for clarifying. @tolstoshev
This is what mine is looking like. I updated the URL, the role name, and where the board name. Do I need to update the board number underneath the board name? I don't even know if board numbers are a thing, but something is telling me to ask!
<#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 == "ServiceMax Employee">
<#if config.getString("phase", "prod") == "stage">
<#assign board='789' />
<#else>
<#assign board='Work_Planning_and_Scheduling_Ideas' />
</#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.servicemax.com${fubar.view_href}
</#list>
</#list>
</#if>
</#list>
</#if>
Looks good - that board number is for testing on staging. Not sure why that one has a number instead of a name, but if you have your staging environment setup exactly like production then you can use the same board name for both assignments.
I put that code in a custom component and made a new page with just that component on it. Then I copy and paste the results into Excel for filtering and such. I'm sure there's a way to make an actual export file, but I didn't get that far in the project.
Hello folks,
Please see my idea to make this an out of the box solution https://community.khoros.com/t5/Li-Product-Ideas/Out-of-the-box-export-of-Product-Ideas/idc-p/520036...
Cheers,
Toby
Hi @tolstoshev this is good. I created a page for it but it doesn't show all the ideas, only about 1/4 of them (most recent). Is that a limitation of the custom content box or something else? Certain status? Completed or not? Have you run into this limitation?
I hadn't run into that issue, but it's probably because when I built the report we only had a couple dozen ideas in the ideation section. It should pull back all everything within the specified board id. That begin said, there might be some limit to the amount of data pulled back by the API that I never ran into for my situation.
Do you just have the one ideation board? That's the only other reason I could think of that it would pull back some but not others.
@tolstoshev we have about 30 exchanges on our Khoros system now and are in the process of migrating others over from other systems. In the end I expect we'll have a couple hundred of them. The exhchanges up and running now have an average of about 200 ideas in them, one has just over 500. I did a test of your code on one that has about 100 ideas in it but only about 1/4 of those display in the custom content box with that code. Dunno. I'll look into it. I really appreciate you sharing your code. It's a good place to start.
My guess would be some sort of rate limit on the API then. You might need to break up the calls to pull the ideas to accomodate for that. Let me know how it goes!
I wish. I'm no developer. 🤔 😏 I'll report back if I can figure something out.
Welcome to the Technology board!
Curious about our platform? Looking to connect on social technology? You've come to the right place!