Counter issue

[Former Member]

Hello,

I am working on a report where I have 7 independent groups that are dynamic.

I am passing a currency information into each group and I need it to be shown correctly.

If a group is a country the currency looks good because there is only 1 currency per country.

When it comes to a higher level, Geographical Regions, Europe for example has multiple currencies, so I need to show 'Mixed', but it brings a first currency in the list. I wrote a Running Total formula to count a number of currencies and put it in Detail section:

whileprintingrecords;

shared numberVar i:=0;

if {Command.CURRENCY_CODE}<> previous({Command.CURRENCY_CODE}) then i:=i+1

else i:=i

Then in a Group Header section I have the following:

shared numberVar i

But it doesn't bring me a correct result.

Please advise.

Thank you,

Tanya.

[Former Member]

Need more help on this.

[Former Member]

Using your formula above (assuming it's called {@RegionCurrency}), create a formula like (basic syntax):

global pageTotal as number

if {@RegionCurrency} <> "Mixed" then
  pageTotal = pageTotal + {value}
end if
formula = ""

and put it on your detail line. In the page header put another formula that zeros the pageTotal global variable, and in the page footer, put yet another formula that simply returns the value of pageTotal.

HTH,

Carl

[Former Member]

I had tried a manual running total before, it didn't work.

I tried again with the logic you sugested:

global pageTotal as number

in Detail section:

if {@Currency_Group1} <> "Mixed" then

pageTotal = pageTotal + 1

end if

formula = ""

in Header section:

global numbervar pageTotal:=0;

in Footer section:

global numbervar pageTotal ;

the result is 0, this is not working. Maybe I am doing something wrong??

[Former Member]

Do you have WhilePrintingRecords in the summing formula? (Oops, I forgot that in my formula...)

HTH,

Carl

[Former Member]

Carl, this is what worked for me:

For the Report Footer Total calculation I put the following in the Group1_Header section:

EvaluateAfter ({@Currency_Group1});

global stringVar pageTotal ;

if {@Currency_Group1} = "Mixed" then

pageTotal:= 'Mixed'

else pageTotal;

For the Group1_Footer Total calculation I put the following in Detail section:

global stringVar pageTotal ;

if {@Currency_Group1} = "Mixed" then

'Mixed'

else '

Thank you,

Tatyana.

[Former Member]

It looks to me like you're trying to use the value (in the Group Header) before it is being set (in the details)...

Since your data is already grouped (assumed on {command.region} below), you can use this in the header (basic syntax):

if minimum({Command.Currency_Code}, {command.region}) _
      <> maximum({Command.Currency_Code}, {command.region}) then
  formula = "Mixed"
else
  formula = minimum({Command.Currency_Code}, {command.region})
end if

HTH,

Carl

[Former Member]

Hi Carl,

Thank you for your advice! This works when the report drills from Geographical Region to a Country. However if a Country is selected as Group1 and Geographical region as Group2 this is not working even though I wrote similar formula for Group2:

if minimum({Command.Currency_Code},{Command.GROUP2}) <> maximum({Command.Currency_Code},{Command.GROUP2}) then formula = "Mixed"

else formula = minimum({Command.Currency_Code},{Command.GROUP2}) end if.

Do you know why?

Thank you,

Tanya.

[Former Member]

Because country is logically a subdivision of region, so having region as your group 2 doesn't really make sense. (Maybe it does for Russia?) If you think about it, showing region as a subgroup of country should only show that country's currency, since the hierarchy is Country -> region, representing "all of the regions in the country". If you really want to see "Mixed" with this grouping, you'll have to use a subreport, passing in the region, grouping only on region, then use the formula given.

HTH,

Carl

[Former Member]

Carl, I understand the hierarchy, but the whole idea of this report is that it to be completely dynamic, and whichever group is picked first or second, it would still bring the correct Currency info. Since this report has 7 groups and even Group7 can be Geographical Region I am afraid that subreport wouldnu2019t work. But I really appreciate your help with that formula. Thank you again!

Tatyana.

[Former Member]

If you base the report on an SQL command, then you can return a field called RegionalCurrency which could have either the single currency or "Mixed". Something like this (MS SQL):

select region, <all your other fields>
from <whatever>
...
join (
select region, case when count(distinct currency) > 1 then "Mixed" else max(currency) as RegionalCurrency
from...
group by region
) b
on a.region = b.region

Then use RegionalCurrency or Currency depending on what the group is.

HTH,

Carl

Edited by: Carl Sopchak on Dec 1, 2009 7:58 AM

[Former Member]

Hi Carl,

Thank you for your reply, but this is exactly how I started in my SQL, but then found some problems and decided to do it in Crystal instead.

If you count non-distinct records you get a wrong result, but you can't use distinct in this case statement...:

select ...

/*, case nvl(p.hlegalentity,0) when 0 then 'usd' else case when count(ii.scode) >1 then 'mixed' else ii.scode end end currency_code */

, case nvl(p.hlegalentity,0) when 0 then 'usd' else ii.scode end currency_code

from

property p

left join intprop ip on p.hmy=ip.hprop

left join intcurrency_info ii on ip.hcurrency=ii.hmy

join attributepropertyxref apxr on apxr.hproperty = p.hmy

join attributeset ast on ast.hmy = apxr.hattributeset

join attributesetxref axr on axr.hattributeset = ast.hmy

join attributename an on an.hmy = axr.hattributename

join attributexref axrf on axrf.hfilerecord = p.hmy

left join (select min(howner) howner, hproperty from propown group by hproperty )po on po.hproperty = p.hmy

left join owner o on o.hmyperson=po.howner

left join attributevalue av on an.hmy=av.hattributename and av.hmy = axrf.hattributevalue and av.svalue <> ' '

join AcctTreeTotalXRef x on ab.hacct = x.hacct

join AcctTreeDetail d on x.htotal = d.hmy and d.stype = 'T' and d.bhide = 0

join AcctTree tr on d.htree=tr.hmy and x.htree=tr.hmy and tr.hmy = v_tree

join

[Former Member]

You missed my point, I think. Create a separate sub-query that returns the region and the single currency or "mixed". Then, join this to the rest of your query based on region. If what I posted above doesn't work, use a sub-sub-query to get the region, distinct count and max, then in the sub-query, just use a simple case statement:

join (
  select region, case when dc > 1 then "Mixed" else currency end as RegionCurrency
  from (
    select region, count(distinct currency) as dc, max(currency) as currency
    from ....
  ) ssq
) sq

HTH,

Carl

[Former Member]

Hi Carl, thank you again for your help, I'll try to do this.

Tatyana.

[Former Member]

Hi Carl,

I have this piece of code in the Group Headers to calculate Mixed currencies:

if minimum({Command.Currency_Code}, {command.region}) _

<> maximum({Command.Currency_Code}, {command.region}) then

formula = "Mixed"

else

formula = minimum({Command.Currency_Code}, {command.region})

end if

result: Europe (Mixed)

USA (USD)

Canada (CAD)

In the Group Footers I have Totals that need to show only if there is one currency on the page, if there are Mixed or different currencies, the Totals in the footers should be suppressed. How would you do this?

Thank you,

T.