Report Tags Distinct expression?

Is there a expression or something I can use to select only distinct entity custom fields?

For example if I use it like this it will give me multiple of the same name.

{REPORT ENTITY DETAILS:EC.Full Name:(ET=Customers)}

John Doe
John Doe
Doe John
Jen Doe
etc.

Basically, if there are more than one same name, I would only need one instance of it.

Any ideas? :sweat_smile:

Adding sorting?
Plugging happens on summing another field of report doesn’t it? Maybe sum phone number :rofl:

You bet I tried that already :rofl: :rofl:

Just looked again, but not expecting smart merging on the 4 examples are you?

Yeah thats just example, in the actual database there is quite a lot of same names so yeah, merging the duplicates as just one row would be perfect.

I am not sure how to achieve that using report tags and I could do it with SQL query if I knew how to deal with the JSON object in CustomData field with SQL.

Get distinct values by entity field name:

stored procedure dml:
will throw an error if no matching entity type is found

/* Get Distinct Entity Custom Field Values */

IF OBJECT_ID('sc.p_DistinctEntityCustomFieldValuesGet') IS NOT NULL
  DROP PROCEDURE sc.p_DistinctEntityCustomFieldValuesGet;
GO

CREATE PROCEDURE sc.p_DistinctEntityCustomFieldValuesGet
  @EntityTypeName NVARCHAR(MAX),
  @CustomFieldName NVARCHAR(MAX)
AS
  BEGIN
    DECLARE @ErrorMessage NVARCHAR(MAX);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    BEGIN TRY
      DECLARE @EntityTypeId INT;
      DECLARE @CustomData NVARCHAR(MAX);
      DECLARE @FieldData NVARCHAR(MAX);
      DECLARE @FieldName NVARCHAR(MAX);
      DECLARE @FieldValue NVARCHAR(MAX);
      DECLARE @CustomFieldValues TABLE
      (
        Value NVARCHAR(MAX) NULL
      );

      SELECT @EntityTypeId = Id
          FROM dbo.EntityTypes
          WHERE Name = @EntityTypeName;

      IF (@EntityTypeId IS NULL)
        BEGIN
          SET @ErrorMessage = CONCAT('Entity type ', '"', @EntityTypeName, '"', ' not found');
          SET @ErrorSeverity = 15;
          SET @ErrorState = -1;

          RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
          RETURN 1;
        END;

      DECLARE cur_custom_data CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
      SELECT CustomData
          FROM dbo.Entities
          WHERE EntityTypeId = @EntityTypeId;

      OPEN cur_custom_data;

      FETCH NEXT FROM cur_custom_data
      INTO @CustomData;

      WHILE @@FETCH_STATUS = 0
        BEGIN
          DECLARE cur_entity_fields CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
          SELECT Value
              FROM OPENJSON(@CustomData);

          OPEN cur_entity_fields;

          FETCH NEXT FROM cur_entity_fields
          INTO @FieldData;

          WHILE @@FETCH_STATUS = 0
            BEGIN

              SELECT @FieldName  = FieldName,
                     @FieldValue = FieldValue
                  FROM
                OPENJSON(@FieldData)
                WITH (FieldName NVARCHAR(MAX) '$.Name',
                      FieldValue NVARCHAR(MAX) '$.Value');

              INSERT INTO @CustomFieldValues
              (Value)
              VALUES
              (@FieldName);

              IF (@FieldName = @CustomFieldName)
                BEGIN
                  INSERT INTO @CustomFieldValues
                  (Value)
                  VALUES
                  (@FieldValue);
                END;

              FETCH NEXT FROM cur_entity_fields
              INTO @FieldData;
            END;

          CLOSE cur_entity_fields;
          DEALLOCATE cur_entity_fields;

          FETCH NEXT FROM cur_custom_data
          INTO @CustomData;
        END;

      CLOSE cur_custom_data;
      DEALLOCATE cur_custom_data;

      SELECT DISTINCT
             (Value)
          FROM @CustomFieldValues
          WHERE Value IS NOT NULL
                AND Value <> '';
    END TRY
    BEGIN CATCH
      SET @ErrorMessage = ERROR_MESSAGE();
      SET @ErrorSeverity = ERROR_SEVERITY();
      SET @ErrorState = ERROR_STATE();
      RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH;
  END;
GO

in SSMS:


sc.p_DistinctEntityCustomFieldValuesGet @EntityTypeName = 'Customers',
                                        @CustomFieldName = 'Name';

report expression:
{REPORT SQL DETAILS:sc.p_DistinctEntityCustomFieldValuesGet @EntityTypeName = 'Customers', @CustomFieldName = 'Name';:F.Value}

1 Like

Report tags were not built for that. Report tags are doing what they should even though those have same name it is in fact different entities.

@Memo wow! Ill give it a go

Jesse I know and understand the results are exactly what they are supposed to be showing, but my use case is a bit different.

Since we have .sum I was hoping there was like a .distinct or something similar I can use. I will try out what Memo has posted

I’m not sure, my specials setup results in lots of orders which are result of same product but where order name is changed on the fly and the item sames report does exactly what your trying to do, it’s clearly not merging based on product and in theory each order is a seperate order in the orders table.
Perhaps sum just doesn’t work on phone? What output did you get on sum phone number?
Could you perhaps add a specific numerical field and sum that with sort on name?
Another poible option do an @ with sql to generate list of distinct names and report entities based on that list?

Try .first

and…20 characters.

3 Likes

.first may work. I actually forgot about that.

1 Like

It breaks the report using the .sum on EC.Full Name.sum entity custom field or any other field, it just returns nothing.

It does work haha but it gives me only one name.

I just made an example here, so basically, my query should return two distinct full names.
Arian and Chris.
image

Its interesting that .distinct does actually work, but it lists it in a new column instead of new row… :sweat_smile:

image