Have had a similar problem but stuck with this

[AnnetteB1305]

Hello,

I have a report which is a sub report in a main report. It displays calls from third party organisations in priority order. I have done this using the third party as a group. I then needed a crosstab to show the summary of the calls by priority. This is in the group footer as there is a new page for every thirdparty. My crosstab is set up
columns:- tablename.priority
row @thirdparty
Summarised field distinctcount of tablename.callref (which is the call number)

The @thirdparty is as follows:-

If {opencall1.suppgroup}='_THIRDPARTY' then {opencall1.owner} else
If {updatedb1.groupid}='_THIRDPARTY' then {updatedb1.aid} else
if left({updatedb1.updatetxt},50) startswith "Call assigned to third party contract 'CCV Ned"
then "NL-CCV" else {@null}

My issue is I need to show all priorities in the crosstab regardless of whether there are any calls for that thirdparty for that priority

i.e. it is currently showing

Priority 3 (Low) Total
9 9


But I need it to show

Priority 1 (High) Priority 2 Med Priority 3(Low) Total
0 0 9 9

When I've had the problem before I've taken out the selection formula of the report and then had a formula for the summarised distinctcount, however this won't work as I've had to do the details of the calls in the report and had to group this by the thirdparty to get a new page per thirdparty.

Hope this is making sense but any help would be appreciated.

Annette

 

[hilfy]

Do you have any sort of "lookup" table that has the priorities in it that you could use as a "master" table? If not, how are your SQL skills?

If you have a lookup table, left join FROM that table to the call table. Assuming that you're subreport is run once for each client, in your selection criteria, use the following logic:

IsNull({CallTable.Client}) or {CallTable.Client} = {?Client link from main report}

Use the priority from this table instead of from the call table in your cross-tab.

If you don't have a lookup table, you're going to have to write a command to pull the data for the subreport. A command is a SQL select statement. It will look something like this:

Select prio.priority, <all of the other fields for your report>
from ( --get the distinct priorities
  select priority
  from CallTable
  group by priority) as prio
left join CallTable as calls
  on prio.priority = calls.priority
where calls.clientID is null or 
  calls.clientID = {?Client from Main Report}

You have to create the parameter in the Command editor in order to use it in a command. You would then link from the main report to the parameter instead of to a field in your query.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[AnnetteB1305]

I do have a lookup table however the subreport is not run once for each client. I've used the subreport as this is different data to the main report, so will I still be able to use this solution, or will I have to create a MYSQL command?

Thanks for your help

Annette

 

[hilfy]

Yes, you will still be able to use this solution. What are your selection criteria? You'll probably have to tweak them a little bit.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[AnnetteB1305]

I've put the lookup table in and left outer joined from it to my call details table. I've put the priority from my lookup table as the column header, however it still isn't showing all priorities.

My selection criteria is:-


{opencall1.companyname} = {?Pm-opencall1.companyname}
and
({@Closedate}>=dateadd('m',1,{?Month}) or {opencall1.closedate}=' ') and
month({@Logdate})<=month({?Month})

I do also have the priorities in a specified order which is
High if the field is like *High
Med if the field is like *Medium and so on

 

[hilfy]

Update your selection critera to something like this (note where the parentheses are - they're important!):
({opencall1.companyname} = {?Pm-opencall1.companyname}and ({@Closedate}>=dateadd('m',1,{?Month}) or {opencall1.closedate}=' ') and month({@Logdate})<=month({?Month}))
or {opencall1.companyname is null}

This will get your priorities even when there is no corresponding data in the call table.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[AnnetteB1305]

The criteria gave an error message so I changed it to this

({opencall1.companyname} = {?Pm-opencall1.companyname}and ({@Closedate}>=dateadd('m',1,{?Month}) or {opencall1.closedate}=' ')
and month({@Logdate})<=month({?Month}))
or isnull({opencall1.companyname})

but now I'm getting a page for anything that doesn't fit my thirdparty criteria but still not showing all the priorities

 

[AnnetteB1305]

Sorry it was doing that anyway due to the linking I've sorted this but still not showing all priorities.

Thanks,

Annette

 

[hilfy]

Put the check for Null first. I should know better - it's one of the things you have to remember about working with nulls: you always have to do the null check BEFORE you do any other fiters.

Sorry about that!

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[AnnetteB1305]

No apologies required, I appreciate the help

I've changed the selection criteria to:-

isnull({opencall1.companyname}) or ({opencall1.companyname} = {?Pm-opencall1.companyname}and
({@Closedate}>=dateadd('m',1,{?Month}) or {opencall1.closedate}=' ')
and month({@Logdate})<=month({?Month}))


but it's still not working

 

[AnnetteB1305]

Is it the isnull({opencall1.companyname} as that field isn't in my crosstab and that is only in my selection criteria as my subreport link as the whole report will be ran for one company name but many customer names if that makes sense and the crosstab is based on third party???

 

[hilfy]

Or you might try something like IsNull({opencall1.priority}) using whatever field you're linking from the lookup to the opencall1 - make sure you have the link going in the right direction and that it's a left outer join.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[AnnetteB1305]

I have the link from the lookup table to the opencall table and it's definitely a left outer join, I've also tried the enforced and not enforced options.

I have tried the isnull({opencall1.priority}) and isnull({priority1.priority}})

neither have made a difference????

I think someone's got it in for me on this report. 2 issues stand between me and completion!

 

[lbass]

The problem is that there are company name fields that are neither null nor meet your date criteria, and these need to be allowed into the report to maintain the effect of the left join if you don't use a command as your datasource.

So there are two approaches. You can either eliminate the selection criteria, and add them into conditional formulas instead or you could start with a command where you add the selection criteria into the FROM clause instead of the WHERE clause. This too would maintain the left outer join, but has the advantage of allowing you to eliminate the records with unwanted dates.

-LB

 

[AnnetteB1305]

I removed the selection criteria from the report and put it in the group selection and then created a conditional formula, only because this version of MYSQL is so old the date functions are a nightmare and think that's what I am having problems with on another report.

This problem is resolved and I really appreciate all your help

Thanks both,

Annette4