CSV string from Table Column values in Textbox

CSV string from Table Column values in Textbox

Posted by: syed.tahir on 17 February 2025, 6:30 pm EST

• 

  syed.tahir

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 17 February 2025, 6:30 pm EST

  Hello -
  
  User requirement to create a comma separated string of all values entered in a given column of a table. Using Table Column 2 with 5 rows = “Col2Row1Value, Col2Row2Value, Col2Row3Value, Col2Row4Value, Col2Row5Value”. Join combined with LookupSet seemed possible, but no joy as only applicable within another Table…we need to pull this into a Textbox as a ‘summary’ of the values entered in the Table, based on the values in a Column, possibly with the need to ensure the list is of unique values from the Table.

  Normally we’d do in the SP - but in this case the dataset supplied is fixed, so we need to manipulate within the Report.

  Options?

• 

  katyayny.raghuvanshi

  

  • Post Options:
  • Link
    Copy

  Posted 18 February 2025, 12:48 am EST

  Hi Tahir,

  To achieve the goal of extracting and concatenating all values from Column2 into a comma-separated string in your report, you can use the following expression:

  {Join(LookupSet(1, 1, Column2, "DataSet1"), ", ")}

  Here, 1, 1 acts as a dummy key-value pair to return all values in the “Column2” field. Since 1 = 1 for all rows, it grabs all values in “Column2” from “DataSet1”. Join takes the array of values returned by LookupSet and concatenates them into a single string, separated by ", ".

  You may refer to the attached sample demonstrating the same. We hope this helps!

  SummaryTB.zip

• 

  syed.tahir

  • Post Options:
  • Link
    Copy

  Posted 18 February 2025, 11:51 am EST

  Hello - Thank you for the reply - we have used this exact entry - but the result is consistently a blank when generating the report. Documentation states the LookupSet function is only usable in Table, Tablix, …but not Textbox, so possibly root issue we thought, but have created 1x1 table with same formula with same results of blank, thus this post.

  {Join(LookupSet(1, 1, Column2, “question3”), ", ")}

  question3 is a table with 3 columns and in the data are 5 rows with values.
  
  question3 is a dataset within a dataset, D_S_452, would this alter the syntax?

  D_S_452
  
  |_question3
  
  |_other fields…

  In the Expression Editor Data Sets D_S_452 and question3 are both shown as separate data sets.

  D_S_452
  
  |_question3
  
  |_other fields…
  
  question3
  
  |_Column1
  
  |_Column2
  
  |_Column 3

  Hopefully something here points to the issue…thanks again for the time.

  or from the report json directly…

      "DataSets": [
          {
              "Name": "D_S_452",
              "Query": {
                  "CommandText": "jpath=$.[*]",
                  "DataSourceName": "S_452"
              },
              "Fields": [
                  {
                      "Name": "question3",
                      "DataField": "question3"
                  },
                  {
                      "Name": "question2",
                      "DataField": "question2"
                  },
  ...
          },
          {
              "Name": "question3",
              "Query": {
                  "DataSourceName": "$dataset:D_S_452/question3"
              },
              "Fields": [
                  {
                      "Name": "Column1",
                      "DataField": "Column1"
                  },
                  {
                      "Name": "Column2",
                      "DataField": "Column2"
                  },
                  {
                      "Name": "Column 3",
                      "DataField": "Column 3"
                  }
              ],
  

  The JSON records are injected by application at report generation…

• 

  katyayny.raghuvanshi

  

  • Post Options:
  • Link
    Copy

  Posted 19 February 2025, 12:08 am EST

  Hi Tahir,

  Based on your dataset structure, the issue seems to be caused by question3 being nested inside D_S_452. To resolve this and use LookupSet effectively, you need to ensure question3 is treated as a separate dataset.

  We used the JSON path as

  $.D_S_452[*].question3[*]

  to extract question3 as an independent dataset and then used the Lookup expression in the textbox as

  {Join(LookupSet(1, 1, Column2, "DataSet1"), ", ")}

  and it worked fine at our end. Please see the attached sample demonstrating the same.

  Also, LookupSet is primarily used within Table, Tablix, and other data-bound elements. However, it can work within a Textbox when combined with Join(), as long as the dataset is structured correctly and accessible.

  We hope this helps!

  SummaryTB_Updated.zip