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? 
Adding sorting?
Plugging happens on summing another field of report doesn’t it? Maybe sum phone number 
You bet I tried that already
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}
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.
.first may work. I actually forgot about that.
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.
Its interesting that .distinct does actually work, but it lists it in a new column instead of new row… 
