Change values in report depending on certain criteria

[hughess7]

Hi all

I have a query which returns summary results grouped on a level (0-5). The
levels are static and there will always be one for each, if no results 0 is
forced in the query.

Sample data below:

FROM QUERY
Level, Desc, ClaimValue,Idenitifed,Applied
0, OK, 3600,0,0
1,Def,6765,0,0
2,FD,3289,3289,0
3,CB%,2150,2150,1050
4,CB100,2190,2190,2190
5,MisRep,1069,1069,1069
Totals:

The report needs to show text instead for some levels on the Applied column:

Level, Desc, ClaimValue,Idenitifed,Applied
0, OK, 3600,-,-
1,Def,6765,-,-
2,FD,3289,3289,tba
3,CB%,2150,2150,1050
4,CB100,2190,2190,2190
5,MisRep,1069,1069,1069
Totals:

I can force this in a query but because the report needs to add the values
and display totals at the bottom this doesn't work - I get a mismatch error
(because of the text). If I force 0's instead it works but is there a way of
getting the text displayed? I am thinking I could overlay the applied column
on the report with a text box holding the appropriate text and choose which
one to display on the report somehow depending on which level it is - but I
am not sure how to do this? Also, for the Applied column I need a minimum and
maximum total. The minimum is easy as it is just a sum of the Applied column.
The Maximum though needs to be the Sum([Applied]+[Levels2IdentifiedAmount].
Is this possible?

Thanks...

 

[hughess7]

Sorry please ignore, solved this now...

 

[david_barna]

Hi all

I have a query which returns summary results grouped on a level (0-5). The
levels are static and there will always be one for each, if no results 0
is
forced in the query.

Sample data below:

FROM QUERY
Level, Desc, ClaimValue,Idenitifed,Applied
0, OK, 3600,0,0
1,Def,6765,0,0
2,FD,3289,3289,0
3,CB%,2150,2150,1050
4,CB100,2190,2190,2190
5,MisRep,1069,1069,1069
Totals:

The report needs to show text instead for some levels on the Applied
column:

Level, Desc, ClaimValue,Idenitifed,Applied
0, OK, 3600,-,-
1,Def,6765,-,-
2,FD,3289,3289,tba
3,CB%,2150,2150,1050
4,CB100,2190,2190,2190
5,MisRep,1069,1069,1069
Totals:

I can force this in a query but because the report needs to add the values
and display totals at the bottom this doesn't work - I get a mismatch
error
(because of the text). If I force 0's instead it works but is there a way
of
getting the text displayed? I am thinking I could overlay the applied
column
on the report with a text box holding the appropriate text and choose
which
one to display on the report somehow depending on which level it is - but
I
am not sure how to do this? Also, for the Applied column I need a minimum
and
maximum total. The minimum is easy as it is just a sum of the Applied
column.
The Maximum though needs to be the
Sum([Applied]+[Levels2IdentifiedAmount].
Is this possible?

Thanks...