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]

You left out the >0 in the first line.

-LB

 

[supportsvc]

Humh,
still not totaling correct?

here's in details

here's in Group by CheckNo

It should be

Here's the CRW

http://files.engineering.com/getfil...-a813-d395cdda100b&file=PR_Pension_CUSTOM.rpt

 

[lbass]

You should be displaying the detail section and applying the group selection formula to get the display you show last. Please do that and then show me the results. If the results differ from what is desired, also show the expected results.

-LB

 

[supportsvc]

The 1st pic is displaying the detail section with the group formula

The last pic is what should be resulting WITH the columns of the deductions

Since I can display the actual report results due to the issues, the last pic is from Check History.
So the details shown there along with the totals of the check history is what needs to display in the Pension report with the columns of the Deductions.

Here's the Design with the Details section highlighted

Here's the Report Selections Formula

 

[lbass]

Sorry, the group selection formula should use count instead of distinctcount. Please see if that fixes the issue.

-LB

 

[supportsvc]

Thanks for hanging in there ...

Almost

Not showing the voided check on 811511 - due to Count on >1? Need same CheckNo repeated when it's a voided one.
Think that's why the voided check is not showing?

So the Running Total is not deducting the voided check

Also, is there a way to combine the duplicating lines into one for the amount for each deduction with the Details?
That was one reason for having Group on CheckNo

NOTE: I get the same Running Totals when the Details are suppressed and the Group #3 on CheckNo is unsuppressed.
Need to display the way Group #3 on CheckNo displays with the sum of the Deductions grouped together per CheckNo, however with the correct Running Totals AND display the same CheckNo when there's a voided transaction on the same CheckNo.

The Report Selections Formulas on Group is still not showing the voided check nor calculating that into the Running Totals.

The same Running Totals on Details as well as in Group on CheckNo. Grouping is "cleaning" in display but none of the voided checks on same CheckNo shows as well as it not calculating it in the Running Total even with the Report Selections Formulas Group formula provided.

 

[lbass]

Is there some kind of code for voided checks in another field? The easiest way of doing this would be to have a different field that indicated a void.

You also should not be suppressing any rows using the section expert, but instead, you should ONLY be using the group selection formula.

-LB

 

[supportsvc]

Hi,
Yea, so was searching to see if there's some other identifier.
Found HeaderDetailSeqNo
Grouped on that and moved it to that Group.

That seems to be working

Thanks again for you help!