Oracle Analytics Cloud and Server

Home› Oracle Analytics› Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Help with Advanced Editor Raw SQL Syntax

Received Response
71
Views
10
Comments
mrmmickle1
mrmmickle1 ✭✭✭
edited August 13 in Oracle Analytics Cloud and Server

Hello all-

Thank you for reviewing my Advanced Editor SQL Question.  I have two queries that work alone by themselves.  I want to add or subtract these numbers together.  Here is an image of each result along with the SQL that is generated.

I would like to Add or Sum the results of Query 1 with the results of Query 2 to get one end result

I would like one record that returns -3003

i.e.  -3039+36   = -3003

QUERY #1 :

Prior Year Headcount *-1 :

Prior Year HC.jpg

SQL GENERATED IN ADVANCED EDITOR:

SET VARIABLE PREFERRED_CURRENCY='Local Currency',PREFERRED_CURRENCY='Local Currency'; SELECT CAST(COUNT(PYHC.s_1) AS INTEGER)*-1 saw_0 FROM (SELECT   0 s_0,   "Human Resources - Workforce Deployment"."Basic Information"."Person Number" s_1,   "Human Resources - Workforce Deployment"."Department Hierarchy"."SUBLOB Name" s_2,   '1. 2018 YEAR END HC' s_3,   DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."SUBLOB Name") s_4,   REPORT_COUNT("Human Resources - Workforce Deployment"."Basic Information"."Person Number" BY ) s_5,   REPORT_COUNT("Human Resources - Workforce Deployment"."Basic Information"."Person Number" BY DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."SUBLOB Name")) s_6FROM "Human Resources - Workforce Deployment"WHERE(("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ((cast(substring("Gregorian Calendar"."Month Name" from 8 for 2) as integer) = 12) AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -2 ) else  (cast(valueof("CURRENT_YEAR") as integer) -1 ) end)))ORDER BY 4 ASC NULLS LAST, 3 ASC NULLS LAST, 5 ASC NULLS LAST, 2 ASC NULLS LAST) PYHC

QUERY #2 :

Terminations:

Terms.jpg

SQL GENERATED IN ADVANCED EDITOR:

SET VARIABLE PREFERRED_CURRENCY='Local Currency',PREFERRED_CURRENCY='Local Currency'; SELECT COUNT(TERMS.s_1) saw_0 FROM (SELECT   0 s_0,   "Human Resources - Workforce Deployment"."Basic Information"."Person Number" s_1,   "Human Resources - Workforce Deployment"."Department Hierarchy"."SUBLOB Name" s_2,   '2. YTD Terminations' s_3,   DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."SUBLOB Name") s_4,   REPORT_COUNT("Human Resources - Workforce Deployment"."Basic Information"."Person Number" BY ) s_5,   REPORT_COUNT("Human Resources - Workforce Deployment"."Basic Information"."Person Number" BY DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."SUBLOB Name")) s_6FROM "Human Resources - Workforce Deployment"WHERE((DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Job"."Job Code" <> 'LTDJBA') AND (("Workforce Event Facts"."Employee Voluntary Termination Count" > 0) OR ("Workforce Event Facts"."Employee Involuntary Termination Count" > 0)) AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -1 ) else  (cast(valueof("CURRENT_YEAR") as integer)  ) end) AND ("Gregorian Calendar"."Month Name" =  VALUEOF("PREVIOUS_MONTH_OBIA")))ORDER BY 4 ASC NULLS LAST, 3 ASC NULLS LAST, 5 ASC NULLS LAST, 2 ASC NULLS LAST) TERMS

Any help someone could provide would be much appreciated.  Thank you in advance for reviewing my inquiry.

Tagged:

Answers

  • Frank Kulash
    Frank Kulash ✭

    Same question is in the SQL/PLSQL forum:

    which seems to be a better place for it.  Locking this thread.

    [EDIT: Muy mistake.  I'll unlock this thread and lock the one in the SQL Forum]

  • Gianni Ceresa
    Gianni Ceresa mod

    Let's start from the beginning: which exact version of OBIEE? It looks like a 12c, but even if maybe it has no impact it's always good to know what we are talking about.

    Second thing: why going directly into the advanced editor? Can't you achieve the expected result with a normal simple analysis?

    Doing an union maybe? So you have both queries and you then just need to put them together? (thing that OBIEE often does automatically)

  • Christian Berg-0racle
    Christian Berg-0racle ✭✭✭✭✭

    I wasn't going to respond to this thread, but Gianni made the first step, so here goes:

    1.) As he implied: this is pretty much the wrongest way to think about things in OBIEE.

    2.) Since I said "OBIEE" - I know this is OBI*A* you're facing but still - BOTH your queries come from "Human Resources - Workforce Deployment". The same Subject Area. So you can combine things in any case.

    3.) There are tons of other columns in your two queries which could influence the query grain if....you wouldn't constantly be using he same columns. So again that's the same.

  • mrmmickle1
    mrmmickle1 ✭✭✭

    Hey Gianni I had typed up a long response when the other thread was locked.  Darn. 

    I believe it is OBIEE in the cloud?  That's 12c right?

    I have tried a number of different techniques that I will detail below in diagrams and Images.  The main issue is that I am unable to get the results of what we call net other into a single query to feed a waterfall graph.  I can get all portions of the equation with the exception of Transfers which is the last portion of the equation I need to calculate Net Other (defined below).  I posted a thread asking a generally inquiry about how to get my data into a waterfall graph and it has no response.  So rather than giving up I decided that there was more than one way to skin a cat and I could try to post smaller inquiries in an effort to build on the knowledge and then eventually combine all of the small answers into the Grand Opus.  So therefore I thought I could start with a basic sql query..... (Here is my previously posted thread with no responses: Waterfall Graph Based On Calculation )    I am going to detail my waterfall issue again below with a diagram that should help explain my issue more.  I have tried to color code it for easier consumption.

    End Goal is a Dynamic Waterfall Graph that contains Net Other Calculation and Ties out Graphic Start and Ending headcounts (The values have been hard coded below for an example):

    Waterfall Example.jpg

    This is the Equation I need to solve for: Net Other = Net Other = '6. Prior Month End HC'+'2. YTD Terminations'+'3. YTD Transfer Outs'-'4. YTD Hires'-'5. YTD Transfer Ins'-'1. 2018 Year End HC'

    I can get these parts of my equation into one query in the correct waterfall format:

    '1. 2018 Year End HC'

    '2. YTD Terminations'

    '4. YTD Hires'

    '6. Prior Month End HC'

    Missing Transfers due to inability to aggregate data...I think it may be because of the sub queries involved in the transfer query

    I can get all of the information in the pivot table below but this is not capable of feeding my waterfall graph as this is not a graph option with a pivot.  (Please see the below pivot table with the correct information in the total row)

    Transfers are defined as such and are derived in the tool using sub queries:

    • Transfer Ins - Employees that are not in the starting headcount roster and are not in the hires roster, but are present in the ending headcount roster.
    • Transfer Outs - Employees that are not in the ending headcount roster and are not in the terminations roster, but are present in the starting headcount roster.
    • Net Other -  Net Other contains strange transactions like Deaths and Returns from Leave of Absence etc.... that cause our waterfall graph not to tie out.  In order to make them tie we calculate them and display them visually:

    I can get all of this in a pivot table but that still does not help me to create the visual that I need to correctly display the waterfall:

    Waterfall Table.jpg

    Transfer Query SQL and Data Format:

    SET VARIABLE PREFERRED_CURRENCY='Local Currency';SELECT   0 s_0,   "Human Resources - Workforce Deployment"."Basic Information"."Person Number" s_1,   "Human Resources - Workforce Deployment"."Department Hierarchy"."SUBLOB Name" s_2,   '5. YTD Transfer Ins' s_3,   DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."SUBLOB Name") s_4,   REPORT_COUNT("Human Resources - Workforce Deployment"."Basic Information"."Person Number" BY ) s_5,   REPORT_COUNT("Human Resources - Workforce Deployment"."Basic Information"."Person Number" BY DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."SUBLOB Name")) s_6FROM "Human Resources - Workforce Deployment"WHERE(("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Job"."Job Code" <> 'LTDJBA') AND ("Basic Information"."Person Number"||"Department Hierarchy"."SUBLOB Name" NOT IN  (  SELECT saw_1 FROM (SELECT    "Gregorian Calendar"."Month Name" saw_0,   "Basic Information"."Person Number"||"Department Hierarchy"."SubLOB Name" saw_1 FROM "Human Resources - Workforce Deployment" WHERE ("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND (cast(substring("Gregorian Calendar"."Month Name" from 8 for 2) as integer) = 12) AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -2 ) else  (cast(valueof("CURRENT_YEAR") as integer) -1 ) end) ) nqw_1  )) AND ("Basic Information"."Person Number"||"Department Hierarchy"."SUBLOB Name" NOT IN  (  SELECT saw_1 FROM (SELECT    "Gregorian Calendar"."Month Name" saw_0,   "Basic Information"."Person Number"||"Department Hierarchy"."SubLOB Name" saw_1 FROM "Human Resources - Workforce Deployment" WHERE ("Workforce Event Facts"."Employee Hire Count" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -1 ) else  (cast(valueof("CURRENT_YEAR") as integer)  ) end) AND ("Gregorian Calendar"."Month Name" <=  VALUEOF("PREVIOUS_MONTH_OBIA"))
     ) nqw_1  )) AND ("Gregorian Calendar"."Month Name" =  VALUEOF("PREVIOUS_MONTH_OBIA")))ORDER BY 4 ASC NULLS LAST, 3 ASC NULLS LAST, 5 ASC NULLS LAST, 2 ASC NULLS LASTFETCH FIRST 500001 ROWS ONLY

    Data Result Image (I would like a single number ie. 10 in one row.  The results don't seem to aggregate correctly.  Maybe it's because of the complexity of the query??? ):

    So like in the waterfall graph image above:

    Waterfall Category         Count

    Transfers In                     10

    Transfer Sub Query.jpg

    Transfer Query Image.jpg

    Overall Diagram of my issues:

    SQL Explanation.jpg

    Any help or advice on how to get all of my data in one query would be much appreciated.  I would become a hero at work if I could solve this issue.  We have faced it for many years.  Therefore we do much of our analysis outside of the tool which is really really frustrating.  If we can get this one portion in the tool it would allow my team to save 1000's of man hours per year in auditing and manual steps needed to accomplish the result outside of the system.

  • Christian Berg-0racle
    Christian Berg-0racle ✭✭✭✭✭

    Hold on "in the cloud" - so that's actuall OTBI inside one of the cloud products? Is that Fusion HCM?

  • Gianni Ceresa
    Gianni Ceresa mod

    I'm not going to digest everything tonight but, the first thing I believe to get is that you have some serious data quality issues and you try to picture that into this "fake" category you call "Other", right?

    So you try to create a not existing category in your data, which is already an issue as OBIEE never generate things not existing, you can trick it a bit, but if something doesn't exists at some point it doesn't exist.

    For your calculation: wouldn't it be easier if you just make the delta between the total (all categories) of current month and end of previous month? It's supposed to give you the same result, but it's maybe an easier calculation to do.

    It would clearly help to know exactly what product you are using, somebody must know what that thing is (as OBIEE in the cloud could be almost anything with tons of various versions etc.).

  • mrmmickle1
    mrmmickle1 ✭✭✭

    Hey Christian and Gianni -

    Thank you for your responses.  After talking to some of my co-workers I believe we have HCM as the system of record and that feeds OTBI.  The analytics tool looks like this if it helps:

    Cloud.jpg

    Another note on Net Other.  I believe we certainly have data quality issues but the way the transactions occur causes the issue with Net Other. Let me try to explain with a couple of scenarios.

    Say we have a person out on Long Term Disability (LTD) that has been out for a fair amount of time....

    1. LTD means that we do not count the employee in our headcount (I believe this is a legal issue with public reporting so it is what it is).  So when I pull my 2018 Q4 Headcount roster this person is not in the file. (This is our starting headcount)

    2. Next the person leaves the company by terminating.  So now they come into my terminations roster.

    3. Since the person is not in our starting headcount and now we have a termination it puts the net of headcount down -1

    Similarly Deaths are not counted as terminations....  they fall into a sub group called Other

    So we could have a scenario like this:

    1. So when I pull my 2018 Q4 Headcount roster this person is alive.  So they are in our file.

    2. When they die they are removed from the roster.  This means they will not be in my ending headcount.

    3. They are not a termination or transfer out.  So now again the balance of the start and end is off.....and thus we need net other to balance the equation.

    Maybe think of the waterfall like this:

    (Start Headcount - Terminations - Transfer Outs + Hires + Transfer Ins ) = X       If X is not = to Ending Headcount then we need to "plug in" a number to make it equal the ending.  That way the graph ties out if you add all the way across.  I agree this is frustrating and I wish the transactions tied out.  They do a lot of the time, but sometimes they don't just depends at what level of the organization you are looking at.

    Hope this helps.  Thanks again you all!!!

  • mrmmickle1
    mrmmickle1 ✭✭✭

    UPDATE:

    I am able to get the data in a format that aggregates the data.  Can someone please help me to make it the last mile.  Here is the LSQL for this result.  Now I believe I just need to be able to do the calculation in between the unions.....  :

    So Close.jpg

    LSQL:

    SET VARIABLE PREFERRED_CURRENCY='Local Currency'; SELECT Massive.saw_0, SUM(Massive.saw_3) AS TTL FROM (SELECT    saw_0,   saw_1,   saw_2,   saw_3 FROM ((SELECT    CASE WHEN "Gregorian Calendar"."Year" > 2018 THEN '6. Prior Month End HC'  ELSE '1. 2018 Year End HC' END saw_0,   "Department Hierarchy"."SUBLOB Name" saw_1,   "Basic Information"."Person Number" saw_2,   1 saw_3 FROM "Human Resources - Workforce Deployment" WHERE ("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND (((cast(substring("Gregorian Calendar"."Month Name" from 8 for 2) as integer) = 12) AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -2 ) else  (cast(valueof("CURRENT_YEAR") as integer) -1 ) end)) OR ("Gregorian Calendar"."Month Name" =  VALUEOF("PREVIOUS_MONTH_OBIA")))) UNION (SELECT    '4. YTD Hires' saw_0,   "Department Hierarchy"."SUBLOB Name" saw_1,   "Basic Information"."Person Number" saw_2,   1 saw_3 FROM "Human Resources - Workforce Deployment" WHERE ("Workforce Event Facts"."Employee Hire Count" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -1 ) else  (cast(valueof("CURRENT_YEAR") as integer)  ) end) AND ("Gregorian Calendar"."Month Name" <=  VALUEOF("PREVIOUS_MONTH_OBIA"))) UNION (SELECT    '2. YTD Terminations' saw_0,   "Department Hierarchy"."SUBLOB Name" saw_1,   "Basic Information"."Person Number" saw_2,   1 saw_3 FROM "Human Resources - Workforce Deployment" WHERE (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Job"."Job Code" <> 'LTDJBA') AND (("Workforce Event Facts"."Employee Voluntary Termination Count" > 0) OR ("Workforce Event Facts"."Employee Involuntary Termination Count" > 0)) AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -1 ) else  (cast(valueof("CURRENT_YEAR") as integer)  ) end) AND ("Gregorian Calendar"."Month Name" =  VALUEOF("PREVIOUS_MONTH_OBIA"))) UNION (SELECT    '3. YTD Transfer Outs' saw_0,   "Department Hierarchy"."SUBLOB Name" saw_1,   "Basic Information"."Person Number" saw_2,   1 saw_3 FROM "Human Resources - Workforce Deployment" WHERE ("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Job"."Job Code" <> 'LTDJBA') AND ("Basic Information"."Person Number"||"Department Hierarchy"."SUBLOB Name" NOT IN  (  SELECT saw_1 FROM (SELECT    "Gregorian Calendar"."Month Name" saw_0,   "Basic Information"."Person Number"||"Department Hierarchy"."SubLOB Name" saw_1 FROM "Human Resources - Workforce Deployment" WHERE ("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Gregorian Calendar"."Month Name" =  VALUEOF("PREVIOUS_MONTH_OBIA")) ) nqw_1  )) AND ("Basic Information"."Person Number"||"Department Hierarchy"."SUBLOB Name" NOT IN  (  SELECT saw_1 FROM (SELECT    "Gregorian Calendar"."Month Name" saw_0,   "Basic Information"."Person Number"||"Department Hierarchy"."SubLOB Name" saw_1 FROM "Human Resources - Workforce Deployment" WHERE (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Gregorian Calendar"."Month Name" =  VALUEOF("PREVIOUS_MONTH_OBIA")) AND (("Workforce Event Facts"."Employee Voluntary Termination Count" > 0) OR ("Workforce Event Facts"."Employee Involuntary Termination Count" > 0)) AND ("Job"."Job Code" <> 'LTDJBA') ) nqw_1  )) AND (cast(substring("Gregorian Calendar"."Month Name" from 8 for 2) as integer) = 12) AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -2 ) else  (cast(valueof("CURRENT_YEAR") as integer) -1 ) end)) UNION (SELECT    '5. YTD Transfer Ins' saw_0,   "Department Hierarchy"."SUBLOB Name" saw_1,   "Basic Information"."Person Number" saw_2,   1 saw_3 FROM "Human Resources - Workforce Deployment" WHERE ("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Job"."Job Code" <> 'LTDJBA') AND ("Basic Information"."Person Number"||"Department Hierarchy"."SUBLOB Name" NOT IN  (  SELECT saw_1 FROM (SELECT    "Gregorian Calendar"."Month Name" saw_0,   "Basic Information"."Person Number"||"Department Hierarchy"."SubLOB Name" saw_1 FROM "Human Resources - Workforce Deployment" WHERE ("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND (cast(substring("Gregorian Calendar"."Month Name" from 8 for 2) as integer) = 12) AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -2 ) else  (cast(valueof("CURRENT_YEAR") as integer) -1 ) end) ) nqw_1  )) AND ("Basic Information"."Person Number"||"Department Hierarchy"."SUBLOB Name" NOT IN  (  SELECT saw_1 FROM (SELECT    "Gregorian Calendar"."Month Name" saw_0,   "Basic Information"."Person Number"||"Department Hierarchy"."SubLOB Name" saw_1 FROM "Human Resources - Workforce Deployment" WHERE ("Workforce Event Facts"."Employee Hire Count" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -1 ) else  (cast(valueof("CURRENT_YEAR") as integer)  ) end) AND ("Gregorian Calendar"."Month Name" <=  VALUEOF("PREVIOUS_MONTH_OBIA")) ) nqw_1  )) AND ("Gregorian Calendar"."Month Name" =  VALUEOF("PREVIOUS_MONTH_OBIA"))))  ORDER Bsaw_0, saw_1, saw_2, saw_3) Massive GROUP BY saw_0, saw_1 ORDER BY ASC saw_0, ASC saw_1;

    Similarly I can get the other numbers in the negative format for the waterfall with this query:

    So Close 2.jpg

    SET VARIABLE PREFERRED_CURRENCY='Local Currency';SELECT Massive.saw_0, SUM(Massive.saw_3) AS TTL FROM (SELECT    saw_0,   saw_1,   saw_2,   saw_3 FROM ((SELECT    CASE WHEN "Gregorian Calendar"."Year" > 2018 THEN '6. Prior Month End HC'  ELSE '1. 2018 Year End HC' END saw_0,   "Department Hierarchy"."SUBLOB Name" saw_1,   "Basic Information"."Person Number" saw_2,   -1 saw_3 FROM "Human Resources - Workforce Deployment" WHERE ("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND (((cast(substring("Gregorian Calendar"."Month Name" from 8 for 2) as integer) = 12) AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -2 ) else  (cast(valueof("CURRENT_YEAR") as integer) -1 ) end)) OR ("Gregorian Calendar"."Month Name" =  VALUEOF("PREVIOUS_MONTH_OBIA")))) UNION (SELECT    '4. YTD Hires' saw_0,   "Department Hierarchy"."SUBLOB Name" saw_1,   "Basic Information"."Person Number" saw_2,   -1 saw_3 FROM "Human Resources - Workforce Deployment" WHERE ("Workforce Event Facts"."Employee Hire Count" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -1 ) else  (cast(valueof("CURRENT_YEAR") as integer)  ) end) AND ("Gregorian Calendar"."Month Name" <=  VALUEOF("PREVIOUS_MONTH_OBIA"))) UNION (SELECT    '2. YTD Terminations' saw_0,   "Department Hierarchy"."SUBLOB Name" saw_1,   "Basic Information"."Person Number" saw_2,   1 saw_3 FROM "Human Resources - Workforce Deployment" WHERE (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Job"."Job Code" <> 'LTDJBA') AND (("Workforce Event Facts"."Employee Voluntary Termination Count" > 0) OR ("Workforce Event Facts"."Employee Involuntary Termination Count" > 0)) AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -1 ) else  (cast(valueof("CURRENT_YEAR") as integer)  ) end) AND ("Gregorian Calendar"."Month Name" =  VALUEOF("PREVIOUS_MONTH_OBIA"))) UNION (SELECT    '3. YTD Transfer Outs' saw_0,   "Department Hierarchy"."SUBLOB Name" saw_1,   "Basic Information"."Person Number" saw_2,   1 saw_3 FROM "Human Resources - Workforce Deployment" WHERE ("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Job"."Job Code" <> 'LTDJBA') AND ("Basic Information"."Person Number"||"Department Hierarchy"."SUBLOB Name" NOT IN  (  SELECT saw_1 FROM (SELECT    "Gregorian Calendar"."Month Name" saw_0,   "Basic Information"."Person Number"||"Department Hierarchy"."SubLOB Name" saw_1 FROM "Human Resources - Workforce Deployment" WHERE ("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Gregorian Calendar"."Month Name" =  VALUEOF("PREVIOUS_MONTH_OBIA")) ) nqw_1  )) AND ("Basic Information"."Person Number"||"Department Hierarchy"."SUBLOB Name" NOT IN  (  SELECT saw_1 FROM (SELECT    "Gregorian Calendar"."Month Name" saw_0,   "Basic Information"."Person Number"||"Department Hierarchy"."SubLOB Name" saw_1 FROM "Human Resources - Workforce Deployment" WHERE (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Gregorian Calendar"."Month Name" =  VALUEOF("PREVIOUS_MONTH_OBIA")) AND (("Workforce Event Facts"."Employee Voluntary Termination Count" > 0) OR ("Workforce Event Facts"."Employee Involuntary Termination Count" > 0)) AND ("Job"."Job Code" <> 'LTDJBA') ) nqw_1  )) AND (cast(substring("Gregorian Calendar"."Month Name" from 8 for 2) as integer) = 12) AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -2 ) else  (cast(valueof("CURRENT_YEAR") as integer) -1 ) end)) UNION (SELECT    '5. YTD Transfer Ins' saw_0,   "Department Hierarchy"."SUBLOB Name" saw_1,   "Basic Information"."Person Number" saw_2,   -1 saw_3 FROM "Human Resources - Workforce Deployment" WHERE ("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Job"."Job Code" <> 'LTDJBA') AND ("Basic Information"."Person Number"||"Department Hierarchy"."SUBLOB Name" NOT IN  (  SELECT saw_1 FROM (SELECT    "Gregorian Calendar"."Month Name" saw_0,   "Basic Information"."Person Number"||"Department Hierarchy"."SubLOB Name" saw_1 FROM "Human Resources - Workforce Deployment" WHERE ("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND (cast(substring("Gregorian Calendar"."Month Name" from 8 for 2) as integer) = 12) AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -2 ) else  (cast(valueof("CURRENT_YEAR") as integer) -1 ) end) ) nqw_1  )) AND ("Basic Information"."Person Number"||"Department Hierarchy"."SUBLOB Name" NOT IN  (  SELECT saw_1 FROM (SELECT    "Gregorian Calendar"."Month Name" saw_0,   "Basic Information"."Person Number"||"Department Hierarchy"."SubLOB Name" saw_1 FROM "Human Resources - Workforce Deployment" WHERE ("Workforce Event Facts"."Employee Hire Count" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -1 ) else  (cast(valueof("CURRENT_YEAR") as integer)  ) end) AND ("Gregorian Calendar"."Month Name" <=  VALUEOF("PREVIOUS_MONTH_OBIA")) ) nqw_1  )) AND ("Gregorian Calendar"."Month Name" =  VALUEOF("PREVIOUS_MONTH_OBIA")))) t1 ORDER BY saw_0, saw_1, saw_2, saw_3) Massive GROUP BY saw_0, saw_1 ORDER BY ASC saw_0, ASC saw_1;
  • mrmmickle1
    mrmmickle1 ✭✭✭

    Got it working.  I was able to get the LSQL to work.  Most likely an easier way to do it, but I'll take it:

    SET VARIABLE PREFERRED_CURRENCY='Local Currency';SELECT saw_0,saw_1 FROM ((SELECT  saw_0,  SUM(MassiveT.saw_3) saw_1FROM (SELECT  saw_0,  saw_1,  saw_2,  saw_3FROM ((SELECT  '1. 2018 Year End HC' saw_0,  "Department Hierarchy"."SUBLOB Name" saw_1,  "Basic Information"."Person Number" saw_2,  1 saw_3FROM "Human Resources - Workforce Deployment"WHERE("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ((cast(substring("Gregorian Calendar"."Month Name" from 8 for 2) as integer) = 12) AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -2 ) else  (cast(valueof("CURRENT_YEAR") as integer) -1 ) end))) UNION (SELECT  '4. YTD Hires' saw_0,  "Department Hierarchy"."SUBLOB Name" saw_1,  "Basic Information"."Person Number" saw_2,  1 saw_3FROM "Human Resources - Workforce Deployment"WHERE("Workforce Event Facts"."Employee Hire Count" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -1 ) else  (cast(valueof("CURRENT_YEAR") as integer)  ) end) AND ("Gregorian Calendar"."Month Name" <=  VALUEOF("PREVIOUS_MONTH_OBIA"))) UNION (SELECT  '2. YTD Terminations' saw_0,  "Department Hierarchy"."SUBLOB Name" saw_1,  "Basic Information"."Person Number" saw_2,  -1 saw_3FROM "Human Resources - Workforce Deployment"WHERE(DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Job"."Job Code" <> 'LTDJBA') AND (("Workforce Event Facts"."Employee Voluntary Termination Count" > 0) OR ("Workforce Event Facts"."Employee Involuntary Termination Count" > 0)) AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -1 ) else  (cast(valueof("CURRENT_YEAR") as integer)  ) end) AND ("Gregorian Calendar"."Month Name" =  VALUEOF("PREVIOUS_MONTH_OBIA"))) UNION (SELECT  '3. YTD Transfer Outs' saw_0,  "Department Hierarchy"."SUBLOB Name" saw_1,  "Basic Information"."Person Number" saw_2,  -1 saw_3FROM "Human Resources - Workforce Deployment"WHERE("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Job"."Job Code" <> 'LTDJBA') AND ("Basic Information"."Person Number"||"Department Hierarchy"."SUBLOB Name" NOT IN(SELECT saw_1 FROM (SELECT  "Gregorian Calendar"."Month Name" saw_0,  "Basic Information"."Person Number"||"Department Hierarchy"."SubLOB Name" saw_1FROM "Human Resources - Workforce Deployment"WHERE("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Gregorian Calendar"."Month Name" =  VALUEOF("PREVIOUS_MONTH_OBIA"))) nqw_1)) AND ("Basic Information"."Person Number"||"Department Hierarchy"."SUBLOB Name" NOT IN(SELECT saw_1 FROM (SELECT  "Gregorian Calendar"."Month Name" saw_0,  "Basic Information"."Person Number"||"Department Hierarchy"."SubLOB Name" saw_1FROM "Human Resources - Workforce Deployment"WHERE(DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Gregorian Calendar"."Month Name" =  VALUEOF("PREVIOUS_MONTH_OBIA")) AND (("Workforce Event Facts"."Employee Voluntary Termination Count" > 0) OR ("Workforce Event Facts"."Employee Involuntary Termination Count" > 0)) AND ("Job"."Job Code" <> 'LTDJBA')) nqw_1)) AND (cast(substring("Gregorian Calendar"."Month Name" from 8 for 2) as integer) = 12) AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -2 ) else  (cast(valueof("CURRENT_YEAR") as integer) -1 ) end)) UNION (SELECT  '5. YTD Transfer Ins' saw_0,  "Department Hierarchy"."SUBLOB Name" saw_1,  "Basic Information"."Person Number" saw_2,  1 saw_3FROM "Human Resources - Workforce Deployment"WHERE("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Job"."Job Code" <> 'LTDJBA') AND ("Basic Information"."Person Number"||"Department Hierarchy"."SUBLOB Name" NOT IN(SELECT saw_1 FROM (SELECT  "Gregorian Calendar"."Month Name" saw_0,  "Basic Information"."Person Number"||"Department Hierarchy"."SubLOB Name" saw_1FROM "Human Resources - Workforce Deployment"WHERE("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND (cast(substring("Gregorian Calendar"."Month Name" from 8 for 2) as integer) = 12) AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -2 ) else  (cast(valueof("CURRENT_YEAR") as integer) -1 ) end)) nqw_1)) AND ("Basic Information"."Person Number"||"Department Hierarchy"."SUBLOB Name" NOT IN(SELECT saw_1 FROM (SELECT  "Gregorian Calendar"."Month Name" saw_0,  "Basic Information"."Person Number"||"Department Hierarchy"."SubLOB Name" saw_1FROM "Human Resources - Workforce Deployment"WHERE("Workforce Event Facts"."Employee Hire Count" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -1 ) else  (cast(valueof("CURRENT_YEAR") as integer)  ) end) AND ("Gregorian Calendar"."Month Name" <=  VALUEOF("PREVIOUS_MONTH_OBIA"))) nqw_1)) AND ("Gregorian Calendar"."Month Name" =  VALUEOF("PREVIOUS_MONTH_OBIA")))) t1 ORDER BY saw_0, saw_1, saw_2, saw_3)MassiveTGROUP BY saw_0, saw_1 ORDER BY saw_0)UNION(SELECT  '6. Net Other' saw_0,  SUM(Massive.saw_3) saw_1FROM (SELECT  saw_0,  saw_1,  saw_2,  saw_3FROM ((SELECT  '1. 2018 Year End HC' saw_0,  "Department Hierarchy"."SUBLOB Name" saw_1,  "Basic Information"."Person Number" saw_2,  -1 saw_3FROM "Human Resources - Workforce Deployment"WHERE("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ((cast(substring("Gregorian Calendar"."Month Name" from 8 for 2) as integer) = 12) AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -2 ) else  (cast(valueof("CURRENT_YEAR") as integer) -1 ) end))) UNION (SELECT  '4. YTD Hires' saw_0,  "Department Hierarchy"."SUBLOB Name" saw_1,  "Basic Information"."Person Number" saw_2,  -1 saw_3FROM "Human Resources - Workforce Deployment"WHERE("Workforce Event Facts"."Employee Hire Count" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -1 ) else  (cast(valueof("CURRENT_YEAR") as integer)  ) end) AND ("Gregorian Calendar"."Month Name" <=  VALUEOF("PREVIOUS_MONTH_OBIA"))) UNION (SELECT  '2. YTD Terminations' saw_0,  "Department Hierarchy"."SUBLOB Name" saw_1,  "Basic Information"."Person Number" saw_2,  1 saw_3FROM "Human Resources - Workforce Deployment"WHERE(DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Job"."Job Code" <> 'LTDJBA') AND (("Workforce Event Facts"."Employee Voluntary Termination Count" > 0) OR ("Workforce Event Facts"."Employee Involuntary Termination Count" > 0)) AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -1 ) else  (cast(valueof("CURRENT_YEAR") as integer)  ) end) AND ("Gregorian Calendar"."Month Name" =  VALUEOF("PREVIOUS_MONTH_OBIA"))) UNION (SELECT  '3. YTD Transfer Outs' saw_0,  "Department Hierarchy"."SUBLOB Name" saw_1,  "Basic Information"."Person Number" saw_2,  1 saw_3FROM "Human Resources - Workforce Deployment"WHERE("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Job"."Job Code" <> 'LTDJBA') AND ("Basic Information"."Person Number"||"Department Hierarchy"."SUBLOB Name" NOT IN(SELECT saw_1 FROM (SELECT  "Gregorian Calendar"."Month Name" saw_0,  "Basic Information"."Person Number"||"Department Hierarchy"."SubLOB Name" saw_1FROM "Human Resources - Workforce Deployment"WHERE("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Gregorian Calendar"."Month Name" =  VALUEOF("PREVIOUS_MONTH_OBIA"))) nqw_1)) AND ("Basic Information"."Person Number"||"Department Hierarchy"."SUBLOB Name" NOT IN(SELECT saw_1 FROM (SELECT  "Gregorian Calendar"."Month Name" saw_0,  "Basic Information"."Person Number"||"Department Hierarchy"."SubLOB Name" saw_1FROM "Human Resources - Workforce Deployment"WHERE(DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Gregorian Calendar"."Month Name" =  VALUEOF("PREVIOUS_MONTH_OBIA")) AND (("Workforce Event Facts"."Employee Voluntary Termination Count" > 0) OR ("Workforce Event Facts"."Employee Involuntary Termination Count" > 0)) AND ("Job"."Job Code" <> 'LTDJBA')) nqw_1)) AND (cast(substring("Gregorian Calendar"."Month Name" from 8 for 2) as integer) = 12) AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -2 ) else  (cast(valueof("CURRENT_YEAR") as integer) -1 ) end)) UNION (SELECT  '5. YTD Transfer Ins' saw_0,  "Department Hierarchy"."SUBLOB Name" saw_1,  "Basic Information"."Person Number" saw_2,  -1 saw_3FROM "Human Resources - Workforce Deployment"WHERE("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Job"."Job Code" <> 'LTDJBA') AND ("Basic Information"."Person Number"||"Department Hierarchy"."SUBLOB Name" NOT IN(SELECT saw_1 FROM (SELECT  "Gregorian Calendar"."Month Name" saw_0,  "Basic Information"."Person Number"||"Department Hierarchy"."SubLOB Name" saw_1FROM "Human Resources - Workforce Deployment"WHERE("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND (cast(substring("Gregorian Calendar"."Month Name" from 8 for 2) as integer) = 12) AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -2 ) else  (cast(valueof("CURRENT_YEAR") as integer) -1 ) end)) nqw_1)) AND ("Basic Information"."Person Number"||"Department Hierarchy"."SUBLOB Name" NOT IN(SELECT saw_1 FROM (SELECT  "Gregorian Calendar"."Month Name" saw_0,  "Basic Information"."Person Number"||"Department Hierarchy"."SubLOB Name" saw_1FROM "Human Resources - Workforce Deployment"WHERE("Workforce Event Facts"."Employee Hire Count" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND (cast("Gregorian Calendar"."Year" as integer)  = case when (cast(substring(Valueof("CURRENT_MONTH") from 8 for 2) as integer)-1) = 0 then (cast(valueof("CURRENT_YEAR") as integer) -1 ) else  (cast(valueof("CURRENT_YEAR") as integer)  ) end) AND ("Gregorian Calendar"."Month Name" <=  VALUEOF("PREVIOUS_MONTH_OBIA"))) nqw_1)) AND ("Gregorian Calendar"."Month Name" =  VALUEOF("PREVIOUS_MONTH_OBIA"))) UNION (SELECT  '6. Prior Month End HC' saw_0,  "Department Hierarchy"."SUBLOB Name" saw_1,  "Basic Information"."Person Number" saw_2,  1 saw_3FROM "Human Resources - Workforce Deployment"WHERE("Workforce Deployment Facts"."Employee Headcount" > 0) AND (DESCRIPTOR_IDOF("Human Resources - Workforce Deployment"."Department Hierarchy"."LOB Name") = '300000015389973') AND ("Pay Grade"."Pay Grade Name" <> 'DEV') AND ("Gregorian Calendar"."Month Name" =  VALUEOF("PREVIOUS_MONTH_OBIA")))) t1 ORDER BY saw_0, saw_1, saw_2, saw_3) MassiveGROUP BY saw_0, saw_1 ORDER BY saw_0))  t1 ORDER BY saw_0, saw_1
  • Christian Berg-0racle
    Christian Berg-0racle ✭✭✭✭✭

    Good for you. You could see that we bowed out of the conversation as this is a very good sum of "everything that's wrong with how one could use the product". Not directed against you personally. Just an observation about the approach in general.