Highlight/Text Color and Order

(11 posts) (2 voices)

Tags:

  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.