Hiding And Displaying Sections Problem

[sqw]

Hello
I am really desperate for some help on a report I have been working on for ages. It is a report that goes to our customers stating their name, address in group header 1a this information is to always be visable on the report. Then in group header 1b is a list of all support calls that the customer has logged with us in the previous month. That part of the report all works and I can view each customer and all their support calls for the previous month. Here is the part that I am struggling with the report works fine but if the customer has no calls in the last month the group header 1b is blank so all the report has is the customers name and anything else in the group header 1a. I want to show a section that states "no calls this month" instead of the blank section I currently get. I have tried to use the section expert and the supress option with the following formulas

{field name}<>lastfullmonth

But I cannot get the new section to show. Any help would be greatly appreatiated

Thanks

 

[dgillz]

Please explain the table structures and linking used in this report. Are subreports being used?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[sqw]

Hi

Thanks for the quick feedback did not think anyone would bother replying on a sunday night! Unfortunatly due to the layout of our db I have had to link in lots of tables with numerous link most are inner joins. I have added the sql below as you can there are lots of links hope this helps

Thanks again for the assistance

SELECT "xredccmcalls"."callid", "contact"."name", "xredccmtext"."textlong",
"project"."name", "address"."city", "address"."county",
"address"."zipcode", "address"."address1", "address"."address2",
"appointment"."do_by", "appointment"."endDate",
"xredccmcalls"."registered", "udlist"."name", "person_1"."firstname",
"person_1"."lastname", "udlist_2"."name", "text"."text",
"xredccmtext_Conclusion"."textlong", "udprojectsmall"."double06",
"udprojectsmall"."double05", "udprojectsmall"."string05",
"udprojectsmall"."string06", "associate"."name", "projtype"."name",
"projstatus"."name", "udlist_1"."name",
"xredccmtext_Conclusion"."texttypeid", "xredccmtext"."texttypeid"

FROM ((((((((((((((("SuperOffice"."crm5"."xredccmcalls" "xredccmcalls"
INNER JOIN "SuperOffice"."crm5"."xredccmtext" "xredccmtext" ON
"xredccmcalls"."xredccmcalls_id"="xredccmtext"."xredccmcalls_id") INNER
JOIN "SuperOffice"."crm5"."xredccmlinks" "xredccmlinks" ON
"xredccmcalls"."xredccmcalls_id"="xredccmlinks"."xredccmcalls_id") INNER
JOIN "SuperOffice"."crm5"."contact" "contact" ON
"xredccmcalls"."contact_id"="contact"."contact_id") INNER JOIN
"SuperOffice"."crm5"."udlist" "udlist_1" ON
"xredccmcalls"."calltypeidx"="udlist_1"."UDList_id") INNER JOIN
"SuperOffice"."crm5"."udlist" "udlist" ON
"xredccmcalls"."statusidx"="udlist"."UDList_id") INNER JOIN
"SuperOffice"."crm5"."person" "person_1" ON
"xredccmcalls"."person_id"="person_1"."person_id") INNER JOIN
"SuperOffice"."crm5"."udlist" "udlist_2" ON
"xredccmcalls"."categoryidx"="udlist_2"."UDList_id") INNER JOIN
"SuperOffice"."crm5"."xredccmtext" "xredccmtext_Conclusion" ON
"xredccmcalls"."xredccmcalls_id"="xredccmtext_Conclusion"."xredccmcalls_id")
LEFT OUTER JOIN "SuperOffice"."crm5"."associate" "associate" ON
"xredccmcalls"."associate_id"="associate"."associate_id") RIGHT OUTER JOIN
"SuperOffice"."crm5"."project" "project" ON
"xredccmcalls"."project_id"="project"."project_id") INNER JOIN
"SuperOffice"."crm5"."address" "address" ON
"contact"."contact_id"="address"."owner_id") LEFT OUTER JOIN
"SuperOffice"."crm5"."appointment" "appointment" ON
"xredccmlinks"."appointment_id"="appointment"."appointment_id") INNER JOIN
"SuperOffice"."crm5"."udprojectsmall" "udprojectsmall" ON
"project"."userdef_id"="udprojectsmall"."udprojectSmall_id") INNER JOIN
"SuperOffice"."crm5"."projtype" "projtype" ON
"project"."type_idx"="projtype"."ProjType_id") INNER JOIN
"SuperOffice"."crm5"."projstatus" "projstatus" ON
"project"."status_idx"="projstatus"."ProjStatus_id") LEFT OUTER JOIN
"SuperOffice"."crm5"."text" "text" ON "project"."text_id"="text"."text_id"

WHERE "xredccmtext_Conclusion"."texttypeid"=3 AND
"projstatus"."name"='Open' AND "xredccmtext"."texttypeid"=1 AND
"projtype"."name"='Support Bank'

ORDER BY "contact"."name", "xredccmcalls"."callid"

 

[dgillz]

I suspect a join problem, but there is no way I am going thru that bunch of SQL for free. You need to get someone who understands the database to simplify the SQL and joins IMHO.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[sqw]

How much would you charge to sort this issue out

 

[raja2008]

if this is all about supressing and displaying "no calls this month" and if i got your problem correctly then this should help a little:


carete a summary for the group and supress it .
place a text box with the text "no calls this month" in the details section and
and go to each and every field in the details and in the textbox: formatfield->superss ->x-2
write code like summary=0

 

[raja2008]

iam sorry you dont need to supress each and every detail with the formula
just the textbox

 

[lbass]

If the headers are always showing, then all you need is a formula something like this in GH_b:

if isnull({table.supportcallID}) then
"No Calls this month"

-LB

 

[sqw]

HI
Thanks for all the usefull replys I have tried these and they work in a fashion but I get "no calls this month" repeated for each call that was not in the month for each customer. I have tried to supress if duplicated but this still does not work it shows more that one text for each customer. I get the feeling it is something to do with the table links but I dont have a clue where to start looking for the problem. Please could someone help.

 

[lbass]

I don't know what your group field is, but try this instead:

if isnull(maximum({table.supportcallID},{table.groupfield})) then
"No calls this month."

-LB

 

[sqw]

Sorry for the trouble but still having a few issues is there anyway of saying

if details is supressed then show group header1

Thanks for all the help

 

[lbass]

Try this. Create a formula {@lastmo}:

if {table.calldate} in lastfullmonth then 1

Then use a formula like this for your message:

if isnull(maximum({table.supportcallID},{table.groupfield})) or
sum({@lastmo},{table.groupfield}) = 0 then
"No calls this month."

-LB