Labor Hours Remaining by Workcenter per Job

Hello,

I have a report where each record represents a different job. I'm trying to write a BQL query to calculate the total number of labor hours left at each workcenter based on how much of the job has been completed. Basically, taking Job Qty Released minus Job Route Qty Completed and then multiplying that result times Job Route Sched Run Labor Hours for each job suffix. Then adding up all the job suffix results for each job.

I created a second report to help me put together the query to pass to my Stat function (See image below). The sum circled in the image is the result I'm trying to get in my first report for each record.

This is my BQL formula for the test column:

(LOOKUPVALUE(0,[Jobs.Job],1,[Jobs.Job Suffix],SELECT [Jobs.Job],[CO Line Due Date: Min: Job Qty Released] from [All] WHERE [Jobs.Job Suffix]=%0)-[CO Order Date: Sum: Job Route Qty Completed])*[CO Create Date: Sum: Job Route Sched Run Labor Hours]

I had to use a LOOKUPVALUE function to retrieve Job Qty Released because Job Qty Released "Does not relate with the other columns in the staging bar". And I think this is what is complicating this whole scenario.

When I grab the whole query from this second report and throw it in a STAT function in my first report to SUM by job, it doesn't work the same way. See image below. I'm thinking it's because the LOOKUPVALUE function is trying to pass Job Suffix as a parameter, but the first report does not have a Job Suffix column. What I'm actually trying to pass is the job suffix column value from the query I got from my second report. Is this not possible?

Here's my STAT function:

STAT(SUM,0,[Jobs.Job],SELECT (LOOKUPVALUE(0,[Jobs.Job],1,[Jobs.Job Suffix],SELECT [Jobs.Job],[CO Line Due Date: Min: Job Qty Released] from [All] WHERE [Jobs.Job Suffix]=%0)-[CO Order Date: Sum: Job Route Qty Completed])*[CO Create Date: Sum: Job Route Sched Run Labor Hours], [Work Centers.Work Center], [Jobs.Job], [Jobs.Job Suffix], [Job Route.Job Route Operation] FROM [ALL] WHERE [Jobs.Job] = %0 AND [Work Centers.Work Center] = 'SAW00')

Find more posts tagged with

Birst - General Discussions

Comments

jgordos

Hi Brandon,

Firstly, I need to tell you that I would do this as a DESIGNER REPORT, and the last column would be a SUB-REPORT that accepted parameters from the first report.

SUB-REPORTs in DESIGNER are run as independent queries, and so they only depend on the parameters from the parent report that are passed in.

This seems like a tailor-made use case for this.

Secondly, This is an interesting use case.

This is your STAT() reformatted:

STAT(
SUM,
  0,
  [Jobs.Job],
  SELECT (
      LOOKUPVALUE(0,[Jobs.Job],1,[Jobs.Job Suffix],
          SELECT [Jobs.Job],[CO Line Due Date: Min: Job Qty Released]
          FROM [All]
          WHERE [Jobs.Job Suffix]=%0)
      -[CO Order Date: Sum: Job Route Qty Completed])
      * [CO Create Date: Sum: Job Route Sched Run Labor Hours],
      [Work Centers.Work Center],
      [Jobs.Job],
      [Jobs.Job Suffix],
      [Job Route.Job Route Operation] FROM [ALL]
      WHERE [Jobs.Job] = %0 AND [Work Centers.Work Center] = 'SAW00'
  )

I suspect you have an error in the WHERE [Jobs.Job] = %0.
I think you may want %1, as [Jobs.Job] is being passed in as the second parameter.

I personally have never used STAT() like this; I'm impressed that it sort of works.

I think this might work better for you, but I can't actually run it and test it.

However, I would try moving the LOOKUPVALUE() outside of the subquery, and passing it in as a parameter based on the value in the line.

I probably have written something here that won't return the right value, but it captures the spirit of what I would suggest.

Something like this:

STAT(
SUM
  , 0
  , [Jobs.Job]
  , (LOOKUPVALUE(0,[Jobs.Job],1,[Jobs.Job Suffix],SELECT [Jobs.Job],[CO Line Due Date: Min: Job Qty Released] from [All] WHERE [Jobs.Job Suffix]=%0))
  SELECT
  [Work Centers.Work Center]
  , [Jobs.Job]
  , [Jobs.Job Suffix]
  , [Job Route.Job Route Operation]
  , [CO Line Due Date: Min: Job Qty Released]-[CO Order Date: Sum: Job Route Qty Completed]
  FROM [ALL]
  WHERE
      [Jobs.Job] = %2
      AND [Work Centers.Work Center] = 'SAW00'
)

Finally, I feel obligated to tell you that excessive use of STAT() can negatively affect your report's performance.

I hope this helps,
-john

Pat Collette

I might encourage to take a step back and think about this as a dashabard with more than one visualization (summary and detail) and an ability to interact with filters, etc. on the dashboard. In this regard, the complex STAT and LOOKUP functions become unnecessary. If you want it in one report, then I think it would be cleaner to address part of this in Birst modeling.

0712131256598360.doc

brandon-keyser

I'll have to look into Designer sub-reports. Have not heard of that until now, but it sounds promising.

I think there's some confusion on that last WHERE clause. I had written [Jobs.Job] = %0 because [Jobs.Job] is the only parameter I'm passing into my Stat function. I was also wondering how BQL would deal with a LOOKUPVALUE inside a STAT function and the fact that I'm passing parameters to both of them. Passing LOOKUPVALUE as an input parameter to the STAT function wouldn't work because LOOKUPVALUE depends on values in each row of the query used inside the STAT function.

0712131256598360.doc

brandon-keyser

Initially, I was trying to sum up labor hours left for each work center per job... but I ended up summing labor hours left for each work center per week based on the job sched end date.

Our I.T. department ended up creating a custom measure "Job Route Sched Run Labor Hours Ext" which is Job Qty Released * Job Route Sched Run Labor Hours. I also ended up having I.T. setup another custom measure that calculated the Friday date for the week that the Job Sched End Date resides in. Then I created this report in Visualizer with the following columns:

[Job Route Sched End Date Friday]

[Job Route.Job]

[Job Route.Job Suffix]

[Job Route.Job Route Operation]

[Work Centers.Work Center]

[Job Route.Job Route Sched End Date]

[Hours Left] = [CO Create Date: Job Route Sched Run Labor Hours Ext] - [CO Order Date: Sum: Job Route Qty Completed] * [CO Create Date: Sum: Job Route Sched Run Labor Hours]

Then I did a break by on the [Job Route Sched End Date Friday] column, so I wouldn't need to use a Stat function to calculate all the sub summaries.

Copyright © 2025 Infor. All rights reserved.