cross tab problem

[swiss2007]

hi,
my data looks like this

paper paper paper paper Entry paper
date code in stock to use location

12/20/2007 AT F T 300 DR
12/20/2007 AT T F 423 DR
01/06/2008 CR T T 29 CPA
01/07/2008 CM T F 10 XTM
so on

i want to highlight entry like 300- and 423+ based on paper in stock.i.e
if paper in stock = F then entry followed by (-) sign else if paper in stock = T and paper in stock = F then entry followed by (+) sign else just entry.

This works fine at the row level,but I have to use a cross tab as the paper locations could be dynamic. cross tab data is shown below

Rows
Paper date
Paper code

Columns
paper location

summarized field
@entry.

@entry formula:
if paperinstock = 'F' then entry & '-'
if paperinstock = 'T' and papertouse = 'F'
then entry & '+'
else entry

but a cross tab can have max,min,count etc as summarized fields.if there is only one row then @entry works fine but there are sometimes multiple rows and brings the max or min(depending on the summary I use)etc regardless of paperinstock or papertouse condition.
Is there a way to highlight the entry values with + or -.
If there are multiple rows,only one row to be returned as the sequence in @entry formula.
Thanks

 

[lbass]

It is unclear what you want to display in the case of multiple rows. Is it that you want to sum the results, but using the + and - to determine whether to add or subtract from the total to be displayed?

-LB

 

[swiss2007]

I want this way in a cross tab format as there could be many locations.

paper paper Entry paper
date code location

12/20/2007 AT 300- DR
01/06/2008 CR 29 CPA
01/07/2008 CM 10+ XTM

since there are 2 rows for same paper date,paper code,only paper in stock = "F" is required.

Thanks a lot.

 

[lbass]

You could use a formula like this for {@entry}:

if paperinstock = 'F' then
val(entry + '-')
if paperinstock = 'T' and
papertouse = 'F' then
val(entry + '+')
else 0

...and use this to sort on. Then right click on this formula->format field->display string and enter:

if paperinstock = 'F' then
entry + '-'
if paperinstock = 'T' and
papertouse = 'F'
then entry + '+' else
entry

Not sure you need a crosstab.

-LB

 

[CrysRptDev]

I am good at writing query before using Crosstab and if you need it, you can try this query below. You can add this query in Add Command in Database Expert. From there work with your Command and you can use CrossTab. However, try to test this query in your Database before using it so making sure if works for you. One more thing, is there possible that you can have paper_in_stock = 'F' and paper_to_use = 'F'? I believe that wouldn't happen right?


select paper_date,
paper_code,
paper_in_stock,
paper_to_use,
entry,
(case when paper_in_stock = 'F' then to_char(entry) || '-'
when paper_in_stock = 'T' and paper_to_use = 'F' then to_char(entry) || '+'
else to_char(entry) end) as new_entry,
paper_location
from
(select * from
(select paper_date, paper_code, paper_in_stock, paper_to_use, entry, paper_location, t_f_ind,
max(t_f_ind) over (partition by paper_date, paper_code) as max_t_f
from
(select a.*, (case when paper_in_stock = 'F' then 1 else 0 end) as t_f_ind
from test_a a))
where t_f_ind = max_t_f)