Results 1 to 8 of 8

Thread: use JOIN to use part of strings

  1. #1
    Join Date
    Oct 2008
    Posts
    17

    use JOIN to use part of strings

    I am using Join(Parameters!aCode.Value, ",") to concatenate selections from dropdownlist and display in a label control.
    I would like to only display part of the selection because some are quite long, so multiple selections can create a very big label.
    I tried: Join(left(Parameters!aCode.Value,3), ",") but got error:
    "Overload resolution failed because no accessible join can be called with these arguments"

  2. #2
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    A Few More Details Please?

    Did you try trimming the entire function, instead of within the JOIN() statement?

    Let me know if I can assist further ...

    Bill

  3. #3
    Join Date
    Oct 2008
    Posts
    17
    thanks for reply - Sorry I probably haven't explained properly. I need to see the start of each of the selections rather than the start of the entire selection. E.g what I have now.
    Richard Jones, Peter Jones, Geoffrey Thompson, Frank Hughes, James Johnson, Doris Smith, Janet Jackson ......

    with each trimmed:
    Richard , Peter Jo, Geoffrey, Frank Hu, James Jo, Doris Sm, Janet Ja ......

    My actual examples are much longer so I'd like to trim after 10 chars.

  4. #4
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    Comments ...

    After trying this in a sample report with multi-value params turned on, I think it could be done with a JOIN(), SPLIT(), TRIM() and then a re - JOIN(), or some such logic, but it will get very stringy. What is the source of your parameter picklist? If a table, you can always create an alternate "short name" that you then pull into the picklist, and avoid the whole stringing exercise, which might degrade performance of the report at runtime, etc. (If it can even be accomplished ...).

    Is the data source OLAP or relational? To what are you pointing your report parameter Value and Label fields? You could generate a third field in the supporting dataset, called (for example) Picklist Label, and either place a trimmed field in the table (etc.), or do it as part of the supporting SQL / MDX query, and then reference the new field in the Label field. The tradeoff would be that the name is also trimmed in the picklist - but if you're talking trimming to 10 characters with what you currently have, I'd think that the picklist is overly wide, as it ...

    Just an idea ... HTH.

    Bill

  5. #5
    Join Date
    Oct 2008
    Posts
    17
    Bill - thanks again for reply. The source is stored procedure reading from SQL Server table. The dataset label shows one column from table containing the field name (text), the dataset value shows the field's codenumber. The text field is long and contains commas, speech marks etc so I only use it as a label, I use the codenumber for SQL joins etc.
    Basically I'm giving a visible output into a label on top of report of what was selected so that when reports are printed out and filed they will know which fields were selected for this report. So the label on report can be more concise than the dropdown selections.
    User wants full text in dropdown because some are similar but with a trimmed display on report of the selections made.

    Can you show how, inside a label expression, I can loop through each element in the split to apply a trim or substring.
    I've tried:
    JOIN(Split(Join(Parameters!aCode.Label, "^"),"^"),"^").substring(0,10))
    which gives first 10 chars of entire list not each element trimmed.
    And this produces an error:
    JOIN(Split(Join(Parameters!aCode.Label, "^"),"^").substring(0,10),"^"))

    I used ^ because text contains commas.

  6. #6
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    Variability is the Killer ...

    What makes it difficult is the variability of the number of possible names, based upon the picklist selection criteria. I still prefer the idea of pre-formatted text from the data source - an added field, in your case, returned by the SPROC that generates the label as you wish it in the overall dataset. Then, when you use Join(), only the truncated labels show, with no further mechanics at the report level. (It might, indeed, be the only way to do this ...)

    Let me know if you hit upon a report-based solution!

    Bill

  7. #7
    Join Date
    Oct 2008
    Posts
    17
    I've decided to use a lot of Replaces outside of the JOIN. I can create custom abbreviations for common words. Its not ideal but workable. Thanks for your help.

  8. #8
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    Alas, the Maintenance ...

    Sorry that you can't simply generate the appropriate "short name" label, but it sounds like you have a plan. Hope it doesn't require too much maintenance ...

    Let me know if I can help further ...

    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •