Report studio list report in one row

[texasw]

hoping this is simple... I have 4 fields with case statements to return a value, eg. when per type shows 20 then show value else null, when per type shows 40 then value else null.
My issue is that it breaks these into separate lines, I am doing a union query and have the case statements on sub queries.

booking   20   40   40hq  40rf        to         booking 20   40    40hq   40rf
abc         100                                                         abc      100 200  300      400
abc                200
abc                        300
abc                                400

[BigChris]

Put a total on each of the fields in the UNION query.

[texasw]

thanks for quick replies BigChris... I did this but I got 'sqlPrepareWithOptions' status='-9'. error so not sure what the issue is I think because it is text instead of number... ?

[BigChris]

That could be it - I was getting some -9 errors yesterday that just disappeared about 10 minutes later. I didn't change anything...the report just worked again after it had been failing.

Anyway, if you can post the actual error message we might be able to shed some light on it for you.

[texasw]

 below is what I get

RQP-DEF-0177

      An error occurred while performing operation 'sqlOpenResult' status='-9'.


my case statement is below, the Amount/percentage should be numeric.

case [PER Code]
when in ('20','D2','R2','O2','F2','T2','P2') THEN [Amount/Percentage(%)]
ELSE NULL
end

[BigChris]

Ahhh...I think I can see the problem (but I could be wrong)

Try

Code Select Expand

case
when [PER Code] in ('20','D2','R2','O2','F2','T2','P2') THEN [Amount/Percentage(%)]
ELSE NULL
end

[texasw]

hmm changed that but now I get a -16 instead of -9 error?