×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Dynamic Columns with multiple selections by user input

Dynamic Columns with multiple selections by user input

Dynamic Columns with multiple selections by user input

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

CODE

{PR_PayrollHistoryHeader.CheckDate} = {?Check Date Range} and
(IsNull({PR_PayrollHistoryDetail.DeductionCode}) or {PR_PayrollHistoryDetail.DeductionCode} in {?DeductionCode}) 
but getting an error testing it
It does not like the {?DeductionCode}

CODE

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:

CODE

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

RE: Dynamic Columns with multiple selections by user input

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

-LB

RE: Dynamic Columns with multiple selections by user input

(OP)
Hello,
I noted that in the post.

along with the code attempting it from your link.

it does not like the parameter at Select

RE: Dynamic Columns with multiple selections by user input

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

RE: Dynamic Columns with multiple selections by user input

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

thanks again!

RE: Dynamic Columns with multiple selections by user input

(OP)
Sorry!

How do you end it?

CODE

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"

RE: Dynamic Columns with multiple selections by user input

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

RE: Dynamic Columns with multiple selections by user input

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

Am getting a script error



RE: Dynamic Columns with multiple selections by user input

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

RE: Dynamic Columns with multiple selections by user input

(OP)
Thanks.

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

Updated:

CODE

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

RE: Dynamic Columns with multiple selections by user input

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

RE: Dynamic Columns with multiple selections by user input

(OP)
DOH! Ugh
wrong formulas!

All works now!

thanks

How do I close threads?

RE: Dynamic Columns with multiple selections by user input

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

-LB

RE: Dynamic Columns with multiple selections by user input

(OP)
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 :(

RE: Dynamic Columns with multiple selections by user input

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

RE: Dynamic Columns with multiple selections by user input

(OP)
Getting "Too many arguments have been given to this function"

CODE

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

RE: Dynamic Columns with multiple selections by user input

(OP)
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

RE: Dynamic Columns with multiple selections by user input

(OP)
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

RE: Dynamic Columns with multiple selections by user input

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

RE: Dynamic Columns with multiple selections by user input

(OP)
Getting the following error ... a boolean is required here?

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

RE: Dynamic Columns with multiple selections by user input

You left out the >0 in the first line.

-LB

RE: Dynamic Columns with multiple selections by user input

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

RE: Dynamic Columns with multiple selections by user input

(OP)
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

RE: Dynamic Columns with multiple selections by user input

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

-LB

RE: Dynamic Columns with multiple selections by user input

(OP)
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.

RE: Dynamic Columns with multiple selections by user input

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

RE: Dynamic Columns with multiple selections by user input

(OP)
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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close