Summary Fields within Selection

[AtlPayroll]

Is there a way a summary field can be used within the selection criteria? If not, is there a way to write a derived field (SQL formula) to sum up a column of a Report Grouping? Example - column Hours for one employee has REG 45, OT 5, VAC 40, Sum of Hours 90. How could I use this Sum of Hours field as part of my selection criteria?
Thanks,

 

[RSHelp]

You can't use them on the Selection Criteria screen but you can use them at the bottom of the Database Grouping screen in the Group Selection box. This box works very much like the Selection Criteria screen but is for summary fields included in your groups.

 

[jwray100]

I have a similar issue. I'm trying to select all EEs who have a total gross pay for the year >= a certain value.

I have a SUM DISTINCT derived field to extract the gross values for each paycheck and a summary field for the File# group to total all for the year.

Also have SUM derived field for some check view deduction amounts and summary fields for the File# group as well. I also need to select based on total values for these deductions.

I tried the database grouping selection criteria, but did not see the summary fields listed there or a way to include them as RSHelp recommended.

Can you be more specific to point me in the right direction?

Thanks,

Joel Ray
Ronile, Inc.

 

[CharlesCook]

Hey Joel,

These are SQL Derived fields Yes?

If they are go to Database Grouping and add a Selection. The first part of the new selection should be something like data field. Click on this and change it to derived field. Now your derived fields will show up.

FYI:

The regular Selection Critera generates a WHERE clause in the SQL. Database Grouping Selection Critera generates a HAVING clause in the SQL. The WHERE clause is evaluated before your SQL derived fields are resolved. The HAVING clause happens after the SQL derived fields are resolved. Macro derived fields are executed after the SQL has been run.

CharlesCook.com
Specializing in ReportSmith Training and Consulting

 

[jwray100]

Hi Charles,

Thanks for your input. I'm actually trying to create a selection criteria a level deeper, if you will, than my derived fields. Let me clarify a little.

They are SQL derived fields:
SUM(DISTINCT "REPORTS"."V_CHK_VW_DEDUCTION"."CHECKVIEWGROSSPAYA")

SUM( DECODE("REPORTS"."V_CHK_VW_DEDUCTION"."CHECKVIEWDEDCODE", 'K', "REPORTS"."V_CHK_VW_DEDUCTION"."CHECKVIEWDEDAMT", 0) )
[there are 3 like this for 3 different deduction codes]

I then created a report group for File# and 4 sum operation summary fields, one for each of the SQL derived fields (Gross-Sum, Ded1-Sum, Ded2-Sum, Ded3-Sum).

The selection criteria (in pseudo-code) I would like to have is:
Gross-Sum > 20000
AND Ded1-Sum <> 0
OR Ded2-Sum <> 0
OR Ded3-Sum <> 0

I was looking for "Summary Fields" as an option in the Database Grouping Selection Criteria, but no luck. As a test I tried creating this selection criteria for the Gross-Sum comparision:
SUM( ( SUM(DISTINCT "REPORTS"."V_CHK_VW_DEDUCTION"."CHECKVIEWGROSSPAYA") ) ) >= 20000

When I Test the selection I get the message: "ORA-00935: group function is nested too deeply".

I tried including the Gross derived field in the Datbase Grouping, but got the same results.

I also tried to get my desired results by writing the derived field as:
SUM( ( SUM(DISTINCT "REPORTS"."V_CHK_VW_DEDUCTION"."CHECKVIEWGROSSPAYA") ) )
which tests with the error message: "ORA-00978: nested group function without GROUP BY"

I tried to resolve this by adding the derived field to the Database Grouping which tested with error message: "ORA-00934: group function is not allowed here"

I was not sure where to go from here. I'm guessing it may not be possible without writing a macro.

Also, I am familiar with the creation of the WHERE and HAVING clauses as you described, but your comment about the order of the macro derived field execution triggered a question for me. Does that mean that you can not do a Database Grouping Selection Criteria for a macro derived field?

Thanks again for your help. Your input to this forum is very helpful.


Joel Ray
Ronile, Inc.

 

[CharlesCook]

I am familiar with the creation of the WHERE and HAVING clauses as you described, but your comment about the order of the macro derived field execution triggered a question for me. Does that mean that you can not do a Database Grouping Selection Criteria for a macro derived field?

This is correct. The SQL is done before macro derived fields start.

SUM( ( SUM(DISTINCT "REPORTS"."V_CHK_VW_DEDUCTION"."CHECKVIEWGROSSPAYA") ) )

Oracle will not let you do a SUM() of a SUM()

SUM( ( SUM(DISTINCT "REPORTS"."V_CHK_VW_DEDUCTION"."CHECKVIEWGROSSPAYA") ) ) >= 20000

The SQL Derived Field editor will let you create this syntax ( >= 20000 ) but it is not valid in a Derived Field. The reason it will let you create it is this interface is also used in Selection Critera where it would be valid.


Not that I have told you No No No etc. let's try another approch. Don't tell me what you did tell me at a very high level what you are looking for. Explain it to me like I was you grand mother.


CharlesCook.com
Specializing in ReportSmith Training and Consulting

 

[jwray100]

SUM( ( SUM(DISTINCT "REPORTS"."V_CHK_VW_DEDUCTION"."CHECKVIEWGROSSPAYA") ) ) >= 20000

Oops! Sorry, I pasted the wrong thing in my post. The actual derived field I used did not have the >= 20000.

OK, here's the data I'm trying to get in my report:

A list of all EEs with their company code, file#, SSN, first name, last name, total gross earnings for the year, total of deduction code K for the year, total of deduction code 14 for the year, and total of deduction code 11 for the year.

In this list I only want to see information for EEs in a particular company code, with total gross earnings for a particular year in excess of a certain threshold, and a total (for the same year) greater than 0 for at least one of the 3 deduction codes.

Thanks for your help.

Joel Ray
Ronile, Inc.

 

[CharlesCook]

Great, lets start with a power point I did on Database grouping and PCPW.

http://charlescook.com/PPS/ReportSmithandADPPCPayrollforWindows-DatabaseGrouping.pps

Create a new report following the steps in the power point.

In the normal Selection Critera (not the database grouping selections) put a selection for your company code and another one for the year.

Create a SQL Derived Field for code K call it CODE_K:

SUM(DECODE("REPORTS"."V_CHK_VW_DEDUCTION"."CHECKVIEWDEDCODE",
'K',"REPORTS"."V_CHK_VW_DEDUCTION"."CHECKVIEWDEDAMT",
0))

Create a SQL Derived Field for code 14 call it CODE_14:

SUM(DECODE("REPORTS"."V_CHK_VW_DEDUCTION"."CHECKVIEWDEDCODE",
'14',"REPORTS"."V_CHK_VW_DEDUCTION"."CHECKVIEWDEDAMT",
0))

Create a SQL Derived Field for code 11 call it CODE_11:

SUM(DECODE("REPORTS"."V_CHK_VW_DEDUCTION"."CHECKVIEWDEDCODE",
'11',"REPORTS"."V_CHK_VW_DEDUCTION"."CHECKVIEWDEDAMT",
0))

In the Database Grouping Selection Critera add 3 new critera

Derived Field Code_K is not equal number 0
Derived Field Code_14 is not equal number 0
Derived Field Code_11 is not equal number 0

At the top of the Database Grouping Selection Critera change the ALL to ANY

The power point had you make CheckViewChkSeq#,CheckViewPayroll# and CheckViewWeek# "Include In Report" Now make them "Exclude From Report" you don't need them for this report.

The power point had you make CheckViewYear# "Include In Report" Now make it "Query Only" You only need it for your selection critera.

This should get you very close. Let me know how it goes because I am doing this from memory. I don't have access to PCPW right now. We can fix the glitches as we go.

CharlesCook.com
Specializing in ReportSmith Training and Consulting