Dynamic Columns with multiple selections by user input 1

[supportsvc]

Ok so Ibass got me started with the below and to add the following in the Select Expert.

 {PR_PayrollHistoryHeader.CheckDate} = {?Check Date Range} and
(IsNull({PR_PayrollHistoryDetail.DeductionCode}) or [i]{PR_PayrollHistoryDetail.DeductionCode} in {?DeductionCode})[/i]

but getting an error testing it
It does not like the {?DeductionCode}

Select {?DeductionCode}
Case "000002" : {PR_DeductionCode.DeductionDesc}
Case "000003" : {PR_DeductionCode.DeductionDesc}
//all the ones listed below
Default : ""

//000002	Car Expense
//000003	United Way
//000004	Intl Pension
//000005	Dues #5
//000006	FAIR
//000007	Dues #7
//000008	ABC-CENTURY
//000009	Supplemental Life
//000010	Wage Garnish
//000011	Long Term Disability
//000012	L7 Reimb
//000013	401K
//000014	VOC Reimb
//000015	E-Brd
//000016	Legal Reimb
//000017	Nego Reimb
//000018	BA Reimb
//000019	Strike Reimb
//000020	Intl Loan - Monthly
//000021	Deputy Sec Reimb
//000022	VOTE (OPEIU)
//000023	Roth IRA
//000024	Political Education
//000025	UFCW Intl Hardship
//000026	UFCW Minority Coalition
//000027	United Latinos
//000028	Child Care
//000029	Steward Training Reimb
//000030	Steward Conference
//000031	Mileage Reimb
//000032	Non Tax - Auto
//000035	Per Diem - Overnite
//000039	Travel (Misc Conf)
//000040	H&W Insurance Co-Pay
//000041	HRQ
//000042	Non Tax - Auto organizing
//000043	H&W Co-Pay Owed
//000099	Credit Union
//KU		401K Catchup
//RU		Roth Catch Up

NOTE: I had preset 6 columns for 6 specific deductioncodes but found out today the user wants to select whatever and will have multiple selections.

Each column it needs to have the following formula to handle null to display 0 otherwise the PayAmt
Example:

If IsNull({PR_PayrollHistoryDetail.DeductionCode}) then 0 else
If {PR_PayrollHistoryDetail.DeductionCode} = "000013"
then if IsNull({PR_PayrollHistoryDetail.DeductionCode}) then 0
else {PR_PayrollHistoryDetail.PayAmt}

Seeking additional help with this dynamic columns based on user's multiple selections and handling of NULLs

 

[lbass]

Did you try the technique mentioned in the thread I referred you to? If so, what were the results?

-LB

 

[supportsvc]

Hello,
I noted that in the post.

along with the code attempting it from your link.

it does not like the parameter at Select

 

[lbass]

Sorry, I see. The link I gave you assumed different fields, and you are working with one field for deduction code. You should set up the parameter for multiple values. Tnen write one formula for each column, like this:

//{@Col1}:
if not isnull({PR_PayrollHistoryDetail.DeductionCode}) and
ubound({?DeductionCode})>=1 and
{PR_PayrollHistoryDetail.DeductionCode}={?DeductionCode}[1] then
{table.deductionamount} //whatever field captures this amount

//{@Col2}:
if not isnull({PR_PayrollHistoryDetail.DeductionCode}) and
ubound({?DeductionCode})>=2 and
{PR_PayrollHistoryDetail.DeductionCode}={?DeductionCode}[2] then
{table.deductionamount}

...etc. Note that if the result is null, a zero will appear without specifying this, since it is the implied default

Then create labels to replace each column name, like this:

//{@Col1 Label}:
select {?DeductionCode}[1]
case "000002" : "Car Expense"
case "000003" : "United Way"
case "000004" : "Intl Pension" //etc.

//{@Col1 Label}:
select {?DeductionCode}[2]
case "000002" : "Car Expense"
case "000003" : "United Way"
case "000004" : "Intl Pension" //etc.

Then you can right click on the column formulas and insert summaries at the Check group level and/or employee level, as long as the deductions don't repeat.

This is essentially a manual crosstab.

If you wanted to, instead of creating the formula for the labels manually, you could generate the case statement for the formula in a separate Crystal Report so that you don't have to manually create it. Create a formula like this:

"case"+'"'+{PR_PayrollHistoryDetail.DeductionCode}+ '" :'+'"'+{PR_DeductionCode.DeductionDesc}+'"'

Then export the results to text (Notepad application) and copy it from there into your formula.

-LB

 

[supportsvc]

Oh wow. ok thank you.
will try this today and let you know

thanks again!

 

[supportsvc]

Sorry!

How do you end it?

if not isnull({PR_PayrollHistoryDetail.DeductionCode}) and 
ubound({?Deductions})>=2 and
{PR_PayrollHistoryDetail.DeductionCode}={?Deductions}[2] then
{PR_PayrollHistoryDetail.PayAmt}

//{@Col3}:
if not isnull({PR_PayrollHistoryDetail.DeductionCode}) and 
ubound({?Deductions})>=3 and
{PR_PayrollHistoryDetail.DeductionCode}={?Deductions}[3] then
{PR_PayrollHistoryDetail.PayAmt}

//{@Col4}:
if not isnull({PR_PayrollHistoryDetail.DeductionCode}) and 
ubound({?Deductions})>=4 and
{PR_PayrollHistoryDetail.DeductionCode}={?Deductions}[4] then
{PR_PayrollHistoryDetail.PayAmt}

//{@Col5}:
if not isnull({PR_PayrollHistoryDetail.DeductionCode}) and 
ubound({?Deductions})>=5 and
{PR_PayrollHistoryDetail.DeductionCode}={?Deductions}[5] then
{PR_PayrollHistoryDetail.PayAmt}

//{@Col6}:
if not isnull({PR_PayrollHistoryDetail.DeductionCode}) and 
ubound({?Deductions})>=6 and
{PR_PayrollHistoryDetail.DeductionCode}={?Deductions}[6] then
{PR_PayrollHistoryDetail.PayAmt}

Default : "" --- what goes here to end it?

Getting "The remaining text does not to be part of the formula

 

[lbass]

These are fine as is—they are separate formulas. Each one is for a separate column. If a user chooses three items, three deduction columns will appear, in the order they choose them. If they choose 20, 20 columns will appear. You don’t have to specify 0 as a default for number fields—it is automatically the default unless you specify otherwise.

-LB

 

[supportsvc]

Ok, got it. I was a bit confused how this works ...

Am getting a script error

 

[lbass]

Sorry. Each label formula should check to make sure there is a corresponding parameter value. So add a line to each like this:

If ubound({?DeductionCode})>=3 then
(
Select {?DeductionCode}[3]
Case “0000004”: “abdcde” //etc.

The ubound() function checks the total number of parameter options checked.

-LB

 

[supportsvc]

Thanks.

Ok, so same error if less than 6 selected.
When all 6 are selected it produces results.

Updated:

//{@Col3}:
if not isnull({PR_PayrollHistoryDetail.DeductionCode}) and 
If ubound({?DeductionCode})>=3 then
({PR_PayrollHistoryDetail.DeductionCode}={?DeductionCode}[3]) then
{PR_PayrollHistoryDetail.PayAmt}

 

[lbass]

Your formula makes no sense. The error you showed earlier was for a col3 LABEL formula, not the col3 field, so you neeed to change the label formulas to build in that first line exactly as I showed.

You can also use column field formulas exactly as I showed earlier—-you can literally copy them into your formulas. You appear to be changing them and thus introducing errors.

-LB

 

[supportsvc]

DOH! Ugh
wrong formulas!

All works now!



How do I close threads?

 

[lbass]

You don’t need to close threads. Only admin staff do that, I think.

-LB

 

[supportsvc]

Hi there ... issue arose with the Grouping on this.

So the help to get the Running Total on the 2nd Group on CheckNo was what was needed.
However there's an issue when the CheckNo is repeated due to a VOID.

So for instance in this result, it repeats, not even sure on why some repeats more than the other ones,
however, it needs to show the last ones for each CheckNo

CheckNo: 811511 needs to ONLY show the last two, original check (positive) and then the Voided one (negative).
CheckNo: 811512 is fine, only shows once
CheckNo: 811521, just the last one

both 811511 and 811521 has a value in one of the columns (in this case the 1st one).

NOTE: the report is set to show all checks regardless of having a deduction or not and will show 0.

Now not sure that the Running Total on CheckNo is correct?
Hope this makes sense and if so, not sure what and how to resolve it?

I tried this and change the Running Total on CheckDate, but it's incorrect

 

[lbass]

In you first set of data in the last post, the total for hours and earnings is not correct, based on the data shown. Are you showing all data contributing to those totals? The totals for the deduction columns seem to be correct. So, please show the results you would EXPECT to see for that data.

What is your rule for showing data? Is it that all checks must be shown, but for those that having deductions, show only the rows containing deductions? For those with no deductions, show only one row? If this is correct, try going to report->selection formulas->GROUP and enter:

Sum({@col1},{@col2},...{@col6})>0 or
Distinctcount({table.checkno},{table.checkno})=1

-LB

 

[supportsvc]

Getting "Too many arguments have been given to this function"

Sum({@col1},[b]{@col2},{@Col3},{@Col4},{@Col5},{@col6}[/b])>0 or
Distinctcount({PR_PayrollHistoryHeader.CheckNo},{PR_PayrollHistoryHeader.CheckNo})=1

 

[supportsvc]

The totals for Hours and Earnings for this person is

NOTE: the 8 in front of the check numbers have been cut off in this screen shot

 

[supportsvc]

This is what it looks like showing everything on Details
Hence the Groups on
Employee
CheckDate
CheckNo

with Running Totals on change on CheckNo
Issue again is the CheckNo that's voided needs to show both positive, when check was issued AND the voided negative transaction.

so that the Running Total totals correctly, I believe
In this pic, the checkno 811511 is ONLY showing the VOIDED one and NOT the positive one, so the total is incorrect here

 

[lbass]

Sorry, wrong syntax. Should have been:

Sum([{@col1},{@col2},...{@col6}]) or //etc

You should be displaying the detail section once you have implemented the group selection.

-LB

 

[supportsvc]

Getting the following error ... a boolean is required here?

Sum([{@col1},{@col2},{@Col3},[highlight] {@Col4},{@Col5},{@col6}][/highlight]) or
Distinctcount({PR_PayrollHistoryHeader.CheckNo},{PR_PayrollHistoryHeader.CheckNo})=1