Sunday, March 31, 2019

Create a Saved Search to pull up Total Component Quantity Usage per Month on Assembly Builds

The most convenient way to generate the required data is through a Transaction Saved Search.

Here are the steps:

1. Navigate to Reports>Saved Searches>All Saved Searches>New

2.
Select Search Type = Transaction

3.
Enter the desired title of the search under the Search Title field

4. Navigate to Criteria tab>Standard subtab>Filter column and add the following filters:

a. Type = is Assembly Build

b. Formula (Numeric) | Formula: Case when {linesequencenumber} = 0 then 1 else 0 end | Formula (Numeric): not equal to | Value: 1

c. Date = the date range that covers one calendar year.  Example: 01/01/2012 – 12/31/2012

5. Navigate Results tab>Sort By: Item

6. Navigate to Results tab>Columns subtab>Field column and add the following fields:

a. Item | Summary Type: Group

b. Type

c. Number

d. Location

e. Formula (Numeric) | Summary Type: Sum | Formula: case when  {linesequencenumber} <> 0 and to_char({trandate},'MM') = '01' then -nvl({quantity},0) else null end | Custom Label: JAN | Summary Label: JAN

f. Formula (Numeric) | Summary Type: Sum | Formula: case when  {linesequencenumber} <> 0 and to_char({trandate},'MM') = '02' then -nvl({quantity},0) else null end | Custom Label: FEB | Summary Label: FEB

g. Formula (Numeric) | Summary Type: Sum | Formula: case when  {linesequencenumber} <> 0 and to_char({trandate},'MM') = '03' then -nvl({quantity},0) else null end | Custom Label: MAR | Summary Label: MAR

h. Formula (Numeric) | Summary Type: Sum | Formula: case when  {linesequencenumber} <> 0 and to_char({trandate},'MM') = '04' then -nvl({quantity},0) else null end | Custom Label: APR | Summary Label: APR

i. Formula (Numeric) | Summary Type: Sum | Formula: case when  {linesequencenumber} <> 0 and to_char({trandate},'MM') = '05' then -nvl({quantity},0) else null end | Custom Label: MAY | Summary Label: MAY

j. Formula (Numeric) | Summary Type: Sum | Formula: case when  {linesequencenumber} <> 0 and to_char({trandate},'MM') = '06' then -nvl({quantity},0) else null end | Custom Label: JUN | Summary Label: JUN

k. Formula (Numeric) | Summary Type: Sum | Formula: case when  {linesequencenumber} <> 0 and to_char({trandate},'MM') = '07' then -nvl({quantity},0) else null end | Custom Label: JUL | Summary Label: JUL

l. Formula (Numeric) | Summary Type: Sum | Formula: case when  {linesequencenumber} <> 0 and to_char({trandate},'MM') = '08' then -nvl({quantity},0) else null end | Custom Label: AUG | Summary Label: AUG

m. Formula (Numeric) | Summary Type: Sum | Formula: case when  {linesequencenumber} <> 0 and to_char({trandate},'MM') = '09' then -nvl({quantity},0) else null end | Custom Label: SEP| Summary Label: SEP

n. Formula (Numeric) | Summary Type: Sum | Formula: case when  {linesequencenumber} <> 0 and to_char({trandate},'MM') = '10' then -nvl({quantity},0) else null end | Custom Label: OCT| Summary Label: OCT

o. Formula (Numeric) | Summary Type: Sum | Formula: case when  {linesequencenumber} <> 0 and to_char({trandate},'MM') = '11' then -nvl({quantity},0) else null end | Custom Label: NOV | Summary Label: NOV

p. Formula (Numeric) | Summary Type: Sum | Formula: case when  {linesequencenumber} <> 0 and to_char({trandate},'MM') = '12' then -nvl({quantity},0) else null end | Custom Label: DEC | Summary Label: DEC

Notes:

The following are limitations of the above Search

-The Search cannot accurately pull up data if the date range set is greater than one calendar year.  Example: Jan 15, 2012 to Jan 15, 2013.

-Components that are not associated in assembly build on the date range specified will not show up in the report.

No comments:

Post a Comment