How to display results from 4 formulas in a row without blank areas in the report

Gary Demos
Gary Demos used Ask the Experts™
on
I have 4 simple formulas that evaluate different statuses from a single table/field and display in columns client names depending on what status they are currently in. Currently my report displays as shown in the attachment. I want to have the results line up in a single row across the four columns without the blank areas in the report. I can do it in a text object using database fields, but that method does not seem to work with formulas. I need to use formulas to get the right results.
Sample1.docx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
It should work with formula fields.

One thing to try would be to create a new formula and concatenate the formulas together.
Local StringVar strOut
If Not IsNull({@NewLeads}) then
    strOut := {@NewLeads};
If Not IsNull({@AAppointmentSet}) then
    strOut := strOut + '    ' + {@AppointmentSet};
If Not IsNull({@ContactedLeads}) then
    strOut := strOut + '    ' + {@ContactedLeads};
If Not IsNull({@FourthFormula}) then
    strOut := strOut + '    ' + {@FourthFormula};
Trim(strOut)

Open in new window

Another way would be to build a single formula to evaluate the 4 formulas as

Local StringVar strOut
//NewLeads formula code
if Not IsNull({NewLeadField}) then
    strOut := {NewLeadField} + ' ' + CStr({NewLeadDateField},'mm/dd');

//AppointmentSet formula code
if Not IsNull({AppointField}) then
    strOut := {AppointField} + ' ' + CStr({AppointFieldDate},'mm/dd');

etc

Open in new window


If you need help creating the formula, post the code for the 4 formulas or upload the report
mlmc
Gary DemosPresident

Author

Commented:
I'm getting an error "The remaining text does not appear to be part of the formula".

The error starts right after the first line and highlights everything else.

Here is my formula:

Local StringVar strOut
If Not IsNull({@NewLeads}) then
    strOut := {@NewLeads};
If Not IsNull({@AppointmentsSet}) then
    strOut := strOut + '    ' + {@AppointmentSet};
If Not IsNull({@ContactedLeads}) then
    strOut := strOut + '    ' + {@ContactedLeads};
If Not IsNull({@FollowUp}) then
    strOut := strOut + '    ' + {{@FollowUp};
Trim(strOut)

Commented:
Hi,

Use a semi colon at the end of first line as in below content.

Local StringVar strOut;

If Not IsNull({@NewLeads}) then
    strOut := {@NewLeads};
If Not IsNull({@AppointmentsSet}) then
    strOut := strOut + '    ' + {@AppointmentSet};
If Not IsNull({@ContactedLeads}) then
    strOut := strOut + '    ' + {@ContactedLeads};
If Not IsNull({@FollowUp}) then
    strOut := strOut + '    ' + {{@FollowUp};

Trim(strOut)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Gary DemosPresident

Author

Commented:
The semi-colon eliminated the error. The result was that all data results were in one column instead of spread across four columns to differentiate the four unique statuses.  Here is the formula I used:

Local StringVar strOut;

If Not IsNull({@NewLeads}) then
    strOut := {@NewLeads};
If Not IsNull({@AppointmentsSet}) then
    strOut := strOut + '    ' + {@AppointmentsSet};
If Not IsNull({@ContactedLeads}) then
    strOut := strOut + '    ' + {@ContactedLeads};
If Not IsNull({@FollowUp}) then
    strOut := strOut + '    ' + {@FollowUp};
Trim(strOut)

Commented:
When you say 4 different columns, do you want the details to be displayed under 4 different already existing column headers? Or want the details to be displayed one below the other as 4 rows?

If you want it to be displayed with more gap in between those 4 values to make them look 4 different values, we can do that by increasing the space used in the formula content.

If you have a sample of how you want it to be displayed, we can try to change the design accordingly.
Gary DemosPresident

Author

Commented:
If you look at my initial attachment, imagine moving the results in each of the four columns to the top of the page so that there were no gaps in any individual columns. Does that make sense?

Commented:
Apologies, not exactly following how it should be.
Can you give a screenshot of how it's getting displayed after the changes and how it should be? I hope that will give a better picture.

In the attachment you shared, I can see 4 columns viz. NewLeads, AppointmentsSet, ContactedLeads and last one being blank.

So, it's not clear what is getting displayed how it should be.
Gary DemosPresident

Author

Commented:
OK I've attached another file showing the initial results I was getting before any help here, and at the bottom you can see what I'm trying to achieve - simply moving all the data so that it appears correctly under each column, but has no vertical gaps in any column.
Sample2.docx

Commented:
You are getting the details as separate rows I guess because you are using the fields under detail section.

What are the other fields used in report for display purpose? Are there any groups?
Gary DemosPresident

Author

Commented:
There is one group on the salespeople, then there is one data field that I'm checking a status number. If status = 1, then new lead, if 2, then appointment set, and so on. I created 4 formulas, with each one testing for a particular status #. I added each of the formulas in a row in the Details section which is what produced the results in my attachment.

Here is one of the formulas that checks to see if status = 1 (New Lead)

If {elms_leads.status_id} = 1  
       Then {elms_leads.first_name} + " " + {elms_leads.last_name} + " "
+  ToText ({elms_leads.last_modified}, "MM") + "/" +  ToText ({elms_leads.last_modified}, "dd")
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
To do this you will need to collect the data and print/display it in the report footer
Add a formula to the report header
Name - DeclareVariables
WhilePrintingRecords;
Global StringVar strNewLeads;
Global StringVar strAppointmentSet;
Global StringVar strContactedLeads;
Global StringVar strFourthColumn;
''

Open in new window


Modify your 4 formulas to mimic this
Name - New Leads
WhilePrintingRecords;
Global StringVar strNewLeads;

If {elms_leads.status_id} = 1  Then 
    strNewLeads := strNewLeads + {elms_leads.first_name} + " " + {elms_leads.last_name} + " " 
 +  ToText ({elms_leads.last_modified}, "MM") + "/" +  ToText ({elms_leads.last_modified}, "dd") + Char(13)
''

Open in new window


Similarly for the other 3

Add 4 formulas to the report footer Add the same formula for the other 3 columns
Name - DispNewLeads
WhilePrintingRecords;
Global StringVar strNewLeads;
strNewLeads

Open in new window


mlmcc
Gary DemosPresident

Author

Commented:
When editing my formula, I get an error on Char indicating that it needs to be a number, date, string, etc expected here.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Sorry, use CHR

mixing systems
Gary DemosPresident

Author

Commented:
Attached is the result I got. It repeats one record and does not list consecutive records. I put the Footer formulas in the Group Footer - they didn't produce any results in the Report Footer FYI.
Sample3.docx
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Can you upload the report file?

mlmcc
Gary DemosPresident

Author

Commented:
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
I forgot to have you format the fields (formulas) so they CAN GROW

Right click trhe formulas in the group 1 footer
Click FORMAT FIELD
Click the COMMON Tab
Click CAN GROW option
Click OK

mlmcc
Gary DemosPresident

Author

Commented:
We're getting closer! Now the only problem is that there seems to be no grouping on the sales people. The client names are lining up as I needed on the report, and a whole list of unique names appears in column as expected, but the list of names repeat over and over again in a given group - about 5 or 6 times under some sales people, and only once under some. There is also no differentiation between groups. The same names appear under each group header instead of the specific sales persons clients showing under their respective group header.

The attachment shows a screen shot of page 1 and page 2 for an example.

Thanks mlmcc
Page-1.JPG
Page-2.JPG
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
SOrry about that.  WHen I put it together I assumed you were showing the list in the report footer and not a group footer.

You need to reset the variables to '' in the group header.

Add the HeaderVariables formula to the Group Header and change it to

WhilePrintingRecords;
Global StringVar strNewLeads;
Global StringVar strAppointmentSet;
Global StringVar strContactedLeads;
Global StringVar strFollowUp;
strNewLeads := '';
strAppointmentSet := '';
strContactedLeads := '';
strFollowUp := '';
''

Open in new window


mlmcc
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
I notice you are suppressing the group header in some cases.  DO you still want to see the group footer and the list or should it be suppressed also?

mlmcc
Gary DemosPresident

Author

Commented:
I was just suppressing inactive salespeople in the group header.

The report is almost there - it is actually working perfectly as far as I can tell, except that there are a few blank gaps that I don't understand. See attached - The far right column under "Stadwick" has a gap under the "follow-up" clients before they appear on the report. But under "Zipfel" there are no gaps. Do you know why that is happening? (The completely empty columns are okay - they indicate that no leads have that status for that salesperson)
Report-1a.JPG
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
THe list on the right is actually the list for Stone.

I think you may want to suppress the group footer with the same formula you are using on the group header.

mlmcc
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
One other suggestion

SInce the lists can grow you may want to change them so they can split across a page
Right click the formula in the group footer
Click FORMAT OBJECT
On the COMMON tab uncheck KEEP TOGETHER

You could also change the group so the header is shown on each page
Right click the group header in the left margin
Click CHANGE GROUP
Click the OPTIONS tab
Click the REPEAT HEADER ON EACH PAGE option

mlmcc
Gary DemosPresident

Author

Commented:
mlmcc you are a genius - those were very helpful comments and now the report is looking great!  Thanks very much!
Gary DemosPresident

Author

Commented:
Thanks for all your help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial