Cleaning up a Payroll Export

[jessimica602]

Hey Everyone!

I am building an export for Payroll. I have all the columns setup and pulling the correct information but I'd like to format this a bit better so it isn't pulling on so many lines.

Each column has a different Type of hours / pay rate / gross amount. Below is an example of what each column's expression looks like:

if ([PRESENTATION VIEW - PAYABLE CHARGE ANALYSIS].[PAYABLE CHARGE PAY TRANSACTION EARN CODE].[externalID] in ('REG')) then ([PRESENTATION VIEW - PAYABLE CHARGE ANALYSIS].[PAYABLE CHARGE PAY TRANSACTION].[quantity]) else (0)

Aggregate Function = None; Rollup Aggregate Function = None. 

I don't want total or maximum since there are different pay rates that need to be on separate lines but it's pulling everything on separate lines. I would like it so if it already has information under that code then it splits onto a new lines, other wise its all on one.

I tried Group Span for External ID and applied to all hours / pay rates / gross amounts but it ended up just pulling 0's across and removing all other lines.

Here is how the file pulls: https://docs.google.com/spreadsheets/d/1cADYHvSDNpXYp6zN107ecYJPaBQM-sYG/edit?usp=sharing&ouid=101280384569872804522&rtpof=true&sd=true

Here are two examples how I'd like them to pull:

https://docs.google.com/spreadsheets/d/1pB10CpH98a8XTm2Pr_CeAOtbg2NXsDeM/edit?usp=sharing&ouid=101280384569872804522&rtpof=true&sd=true

https://docs.google.com/spreadsheets/d/1UgYZQdzLlI_N9ZHg8tN9DErG0BD3eEQO/edit?usp=sharing&ouid=101280384569872804522&rtpof=true&sd=true


Any ideas on how to make this work??

Thank you!

[JCarter]

I can't get to Google Docs from work, but based on your description, I'm going to say that you need to enable aggregation for something. If you set aggregation to None for all fields in your query, then Cognos is going to give you every row in the data source without combining anything. Set aggregation and it should start grouping on everything else.

If you don't have any fields that need totaled or averaged, you might try setting aggregation to Calculated for those quantity fields. In the vast majority of cases, I'd think you would want to total quantities, but you said you don't want that.

If that doesn't work, try explicitly enabling grouping for several of the columns in the list itself?