1. 

   SongezoM, Member

   Hi,

   I'm creating a report which requires that I select from a list of skills (input param) and return all Assignment in my db with that/those selected categories.

   The report must take the following format:
   Selected input param item(s) first - for example Java (Java, Asp, C#)
   Highlight the selected items yellow

   Sample report

   Skills: Data Analysis, Java, Python, Scrum

   Name Surname Skills
   Songezo Maha Java (Highlighted yellow), Asp, C#
   Misiwe Sangweni Scrum (Highlighted yellow), Business Analysis

   Is there such functionality in myDBR, highlighting a piece of text and ordering according to input parameters?

   Thanks a lot in advance,
   Songezo
2. 

   myDBR Team, Key Master

   Hi,
   yes you can do that.

   If you want the person's skills to be listed as a comma separated list (instead of a separate rows), use GROUP_CONCAT in MySQL (and STUFF / FOR XML in SQL Server). Actual highliting can be done via dbr.purehtml. To keep the report clean, you may want to create function that does the formatting for you.

   As for the order, you list the skills based on the criteria if the skills exist in the parameters.
   --
   myDBR Team
3. 

   SongezoM, Member

   Thanks for your response. Particularly I'm looking into Highlighting a piece of text in the concatenated field.

   Can I highlight a number of skills if they're selected as input parameters? How?

   Thanks a lot in advance,
   Songezo
4. 

   myDBR Team, Key Master

   Assuming you have the parameter skills already in a temporary table you can do something like:

   select m.person, concat('dbr.purehtml:', group_concat(
   
   	concat( if (p.skill is not null, '<span style="background:yellow">', ''),
   
   		m.skill,
   
   		if (p.skill is not null, '</span>', '') )
   
   	order by ifnull(p.skill, 'zzzz'))) as 'Skills'
   
   from mydata_tmp m
   
     left join skills_param_tmp p on p.skill = m.skill
   
   group by m.person;
   
   

   The report code will be much if you create a function which takes p.skill and m.skill and formats the skill based on the criteria. This way you can remove the formatting from the actual report code.

   --
   myDBR Team
5. 

   SongezoM, Member

   That's perfect. Thanks a lot
6. 

   SongezoM, Member

   This highlights the field, not just a single skill. I take it this cannot be done.

   Thanks again,
   Songezo
7. 

   myDBR Team, Key Master

   It will highlite those skills that exists in both parameters (skills_param_tmp) and in person's own skills (mydata_tmp). The skills not included in the parameter list, will not be highlited.

   Please check the report for errors.

   --
   myDBR Team
8. 

   SongezoM, Member

   Perfect. Thanks again

   Songezo
9. 

   SongezoM, Member

   One last thing, please help. I can't get it to work on multiple fields. I've then had to divide my skills to three columns (I have 820 in my db).

   it works for the first columns, everything is accordingly highlighted and sorted.
   But for the other two columns it doesn't work. I used the exact same code

   Please advise

   Thanks a lot in advance,
   Songezo
10. 

    myDBR Team, Key Master

    Without seeing your query it is bit hard to tell what might be wrong. There is really nothing different with the highlighting even if you have multiple fields.

    --
    myDBR Team
11. 

    SongezoM, Member

    I've got it. Works perfect.

    Again, thanks a lot.

    Songezo

Reply

You must log in to post.