Birst Expression Causing Multiple Rows

Good morning,

New to Birst so trying to learn it's syntax and rules. I have a table that I want to show one row per WC Department (screenshot below). The field that is causing the table to split into multiple rows is the "Jobs Idle Over 1 Day" expression.

Here is my formula for the Jobs Idle Over 1 day expression. IIF(DateDiff(Day,[Jobs.Job Last Transaction Date],NOWDATE) <= 1,0,[Job Date: # Distinct Job]). I figured out what is happening, but I am unsure how to fix it. So, what it's doing is creating a new row for each Job ID that has a different value (2nd screenshot below). All I want is how many Job IDs have a datediff > 1, and I want to show the total per WC Department. Any help would be appreciated.

Find more posts tagged with

Comments

Dan Rosen

Hi Phil - assuming you have two different dimensions, one for jobs and one for departments... a simpler solution might be to calculate this in your jobs script in the modeler, using a similar formula and deriving a 1 or 0 value for each job. If you set that field as a measure, you should be able to slice it by your department dimension (so long as your job script contains your department key and is set to grain to the department dimension)

Phil Bongiovanni

Hi Dan,

Yes, these are two different dimensions. You are probably right and this would have to be done in the modeler. But unfortunately, I do not have access to edit the modeler. Might have to talk to someone about potentially making this adjustment if I can't do it using a report expression. Appreciate the feedback!

thank you,

Dan Rosen

In that case, you could try a STAT expression, maybe something like this:

STAT(SUM, 0, [Jobs.Job Last Transaction Date], SELECT [Job Date: # Distinct Job] FROM [ALL] WHERE (DateDiff(Day,%0,NOWDATE) <= 1))

Dan Rosen

If you're unfamiliar with the STAT function:

SUM: the aggregation type

0: the field in the query to return

[Jobs.Job Last Transaction Date]: a comma separated list of fields from the report query to pass in

SELECT [Job Date: # Distinct Job] FROM [ALL] WHERE (DateDiff(Day,%0,NOWDATE) <= 1): the query, where %0 represents the first item in your comma separated list

This will run the query at more of a database level and allow you to go outside of the level of your report (someone technical can correct me if that's off)

Phil Bongiovanni

thank you, I will give this a try.

Copyright © 2025 Infor. All rights reserved.