Trying to figure out syntax for VC formulas using a premade selection to reduce sync time

[Seth_Berman]

I have several tables that are basically 3 columns, for begin and end date, and a column for clients, then between 10 and 20 VCs that are acting as dynamic reports for the end user. the user selects a date range + clients, and the VCs all update accordingly by recalculating. this works great, however the cost in sync time by that many select expressions querying the same 2 or 3 data tables is very bad. according to the performance monitor, something like 90+% of my sync are the VCs.

my thought is to have a single VC run the select for each data table based on the data range and the rest of the VCs use this selection list to run their calculations. I want to see if this will make my sync times more efficient. Where i am running into trouble is errors like “Cannot compare List with Text”.

The Selection VC [Select Specimens] formula:
SELECT(
Specimen Count[Specimen Qty],
AND(
([Date] >= [_THISROW].[Begin Date]),
([Date] < [_THISROW].[End Date]),
IN([Client],[Clients Name])
)
)

This simple formula for averages seems to work ok:
Average([Select Specimens])

examples of current VCs not using the preselected list from [Select Specimens]
this is to count the total shipments:
COUNT(
SELECT(Specimen Count[Specimen Delivery #],
AND(
([Date] >= [_THISROW].[Begin Date]),
([Date] < [_THISROW].[End Date]),
IN([Client],[Clients Name])
)
)TRUE
)

this is to count the total specimens of a particular collection type:
SUM(
SELECT(
Specimen Count[Specimen Qty],
AND(
([Date] >= [_THISROW].[Begin Date]),
([Date] < [_THISROW].[End Date]),
IN([Client],[Clients Name]),
([Collection Type] =“Standard Collection”)
)
)
)

[Steve]

Consider replacing the virtual columns with normal columns, then provide the user with a way to refresh the report.

Seth_Berman:

my thought is to have a single VC run the select for each data table based on the data range and the rest of the VCs use this selection list to run their calculations.

A good idea.

Seth_Berman:

The Selection VC [Select Specimens] formula:
SELECT(
Specimen Count[Specimen Qty],
AND(
([Date] >= [_THISROW].[Begin Date]),
([Date] < [_THISROW].[End Date]),
IN([Client],[Clients Name])
)
)

Should IN([Client],[Clients Name]) be IN([_THISROW].[Client],[Clients Name])? Is the Clients Name column of the Specimen Count table a List or EnumList?

Seth_Berman:

this is to count the total shipments:
COUNT(
SELECT(Specimen Count[Specimen Delivery #],
AND(
([Date] >= [_THISROW].[Begin Date]),
([Date] < [_THISROW].[End Date]),
IN([Client],[Clients Name])
)
)TRUE
)

There appears to be a missing comma before TRUE. Also, again the [_THISROW].[Client] question.

Seth_Berman:

this is to count the total specimens of a particular collection type:
SUM(
SELECT(
Specimen Count[Specimen Qty],
AND(
([Date] >= [_THISROW].[Begin Date]),
([Date] < [_THISROW].[End Date]),
IN([Client],[Clients Name]),
([Collection Type] =“Standard Collection”)
)
)
)

And [_THISROW].[Client] again.

[Seth_Berman]

sorry, i will try and clarify

this is to count the total shipments:
COUNT(
SELECT(Specimen Count[Specimen Delivery #],
AND(
([Date] >= [_THISROW].[Begin Date]),
([Date] < [_THISROW].[End Date]),
IN([Client],[Clients Name])
)
)TRUE
)

Should IN([Client],[Clients Name]) be IN([_THISROW].[Client],[Clients Name]) ? Is the Clients Name column of the Specimen Count table a List or EnumList ?

This expression hasn’t been throwing an error and appears to be functioning. [Clients Name] is an EnumList located in the reports table. [Client] is an Enum located in the data table.
the data table is Specimen Count
the reports table is Client Activity Report

when i modify the select expression by adding IN([_THISROW].[Client],[Clients Name]) I get an error, Error in expression ‘[key].[Client]’ : Unable to find column ‘Client’.

[Seth_Berman]

so i reworked the concept a bit and have another question. i changed the VC in the report table that selects the list from the data table. [Select Specimens], it is now of type List, element Ref, and is selecting on the key column of the data table. i believe this should return the entire row so i can grab other columns from it for expressions.

SELECT(
Specimen Count[Date],
AND(
([Date] >= [_THISROW].[Begin Date]),
([Date] < [_THISROW].[End Date]),
IN([Client],[Clients Name])
)
)

this VC from the reports table is a regular select of the data table with a couple of conditions.
SUM(
SELECT(
Specimen Count[Specimen Qty],
AND(
([Date] >= [_THISROW].[Begin Date]),
([Date] < [_THISROW].[End Date]),
IN([Client],[Clients Name]),
([Collection Type] =“Chain of custody”)
)
)
)

i am trying to convert that VC to use the pre-selected list from [Select Specimens].
i can use the formula SUM([Select Specimens][Specimen Qty]) to sum the specimens, but i cant figure out the syntax to limit the SUM to only rows where [Collection Type]=“Chain of custody” without doing another SELECT.
something like
SUM(
IF(
[Select Specimens][Collection Type]=’“Chain of custody”,[Select Specimens][Specimen Qty],0
)
)

[Steve · Accepted solution]

Good thinking with the reworked concept!

For the Specimen Qty sum, try:

SUM(
  SELECT(
    [Select Specimens][Specimen Qty],
    ("Chain of custody" = [Collection Type])
  )
)

[Seth_Berman]

This worked perfectly. I have been going back, adding selection VCs to my report Views and updating the expressions on the rest of the VCs accordingly. Halfway done and i’ve taken about 10-15 seconds off our sync time. thanks alot!