Khoros Atlas Logo

Occasional Contributor KiranChadha
Occasional Contributor

Custom reports in Idea Exchange

Jump to solution

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.

Tags (1)
14 Replies 14
Honored Contributor
Honored Contributor

Re: Custom reports in Idea Exchange

Jump 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

https://community.lithium.com/t5/Developer-Documentation/bd-p/dev-doc-portal?section=commv2&collecti...

 

 

 

Give kudos if you find my posts helpful or mark solution if it answers your query.
Tariq
Reply
Loading...
Occasional Contributor KiranChadha
Occasional Contributor

Re: Custom reports in Idea Exchange

Jump to solution

Thank you!

 

 

Reply
Loading...
Trusted Contributor
Trusted Contributor

Re: Custom reports in Idea Exchange

Jump to solution

@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.

Reply
Loading...
Trusted Contributor
Trusted Contributor

Re: Custom reports in Idea Exchange

Jump to solution

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>

 

Reply
Loading...
Valued Contributor
Valued Contributor

Re: Custom reports in Idea Exchange

Jump to solution

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!!!

Reply
Loading...
Trusted Contributor
Trusted Contributor

Re: Custom reports in Idea Exchange

Jump to solution

@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.

Reply
Loading...
Valued Contributor
Valued Contributor

Re: Custom reports in Idea Exchange

Jump to solution

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>

Reply
Loading...
Trusted Contributor
Trusted Contributor

Re: Custom reports in Idea Exchange

Jump to solution

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.

Reply
Loading...
Esteemed Contributor
Esteemed Contributor

Re: Custom reports in Idea Exchange

Jump to solution

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

Community Manager - Seismic Software
Reply
Loading...