CrossTab Forumla Help 1

[puter55]

CR XI

My tables have a hierarchal setup.
DistUnits
DistCodes
DealerCodes

Not All DistCodes have a DealerCode


I have a report with a crosstab. Currently the report is grouped by
Group1 Date
Group2 DistUnit

Within Group 2 is a crosstab setup as follows
Column is customerName
Row is a formula @routename that just has DistcodeName
Summarized Field @circCount is DistCodeCirculationCount

Everything is working up to this point. I need the crosstab to show the DealerCodeNames instead of the DistCodeName if there is data in the DealerCode information, if there isn't I need it to show the DistCodeName

I assume I need to do this with forumulas for the rows and summarized fields, but I can't get the data display correctly.
If I use @routename

if dealercodecirculationcount > 0 then dealercodeName
else distcodeName

Then only the records with that have a dealercode linked to them show up on the report. The records with DistCodes only disappear.

Any help is appreciated.

Thanks

 

[lbass]

You need to use a left join FROM DistCodes TO DealerCodes, with no selection criteria on the DealerCodes fields. Then use a formula like this:

if isnull({dealercode.Name}) then
{distcode.Name} else
{dealercode.Name}

-LB

 

[puter55]

lbass

That is really close, it is doing something that I don't quite understand though. It appears to be combining all of the distcode.Name into 1 row and displaying it as a blank name. The circulation Counts are correct, but for some reason it rolled them all up into 1 row in the Crosstab.

Any ideas as to why?

 

[lbass]

If the crosstab is in a group #2 section, SHOULD there be more than one distcode.name per DistUnit? Try copying the crosstab into the Group #1 footer and/or the Report footer to see how it displays there.

-LB

 

[puter55]

Yes, the crosstab is in Group #2 and there are multiple distcodes per distUnit. When I move the crosstab to the report foot it does the same thing, it rolls everything up that doesn't have dealercode into 1 line with a blank name.

Thanks for all of your help so far.

 

[lbass]

Please show the exact formula you used.

-LB

 

[puter55]

if isnull({AoDealerCodes.Name}) then
{AoDistCodes.Name} else
{AoDealerCodes.Name}

Although it would probably help if I updated the correct formula. Everything is working fine. You were correct a few posts up. Sorry.

Again, Thanks for your help.