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

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
Gary DemosPresidentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
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
0
Gary DemosPresidentAuthor 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)
0
Raghavendra HullurSoftware DeveloperCommented:
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)
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Gary DemosPresidentAuthor 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)
0
Raghavendra HullurSoftware DeveloperCommented:
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.
0
Gary DemosPresidentAuthor 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?
0
Raghavendra HullurSoftware DeveloperCommented:
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.
0
Gary DemosPresidentAuthor 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
0
Raghavendra HullurSoftware DeveloperCommented:
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?
0
Gary DemosPresidentAuthor 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")
0
mlmccCommented:
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
0
Gary DemosPresidentAuthor Commented:
When editing my formula, I get an error on Char indicating that it needs to be a number, date, string, etc expected here.
0
mlmccCommented:
Sorry, use CHR

mixing systems
0
Gary DemosPresidentAuthor 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
0
mlmccCommented:
Can you upload the report file?

mlmcc
0
Gary DemosPresidentAuthor Commented:
0
mlmccCommented:
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
0
Gary DemosPresidentAuthor 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
0
mlmccCommented:
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
0
mlmccCommented:
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
0
Gary DemosPresidentAuthor 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
0
mlmccCommented:
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
0
mlmccCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gary DemosPresidentAuthor Commented:
mlmcc you are a genius - those were very helpful comments and now the report is looking great!  Thanks very much!
0
Gary DemosPresidentAuthor Commented:
Thanks for all your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.