system
June 21, 2004, 9:46pm
1
Crosstab report with section…columns missing
section: BSA_CD
rows:(PER_DC +"–"+FY_DC),
COLUMN:FCAT_NM,
MEASURE:PSTNG_AM
I’d like to see all the values for FCAT_NM,but i get only some values.
i’ve used outer joins ,but still i don’t get all the values.
Only When I remove the section , i get all the values for FCAT_NM,
is there anyway to retain all values when section is present…
this is the query generated by BO.
SELECT
JRNL_ACTG.PER_DC,
JRNL_ACTG.FY_DC,
JRNL_ACTG.PSTNG_AM,
JRNL_ACTG.FCAT_CD,
JRNL_ACTG.BSA_CD,
R_BSA.BSA_NM,
R_FCAT.FCAT_NM
FROM
JRNL_ACTG,
R_BSA,
R_FCAT
WHERE
( JRNL_ACTG.FCAT_CD(+)=R_FCAT.FCAT_CD )
AND ( JRNL_ACTG.BSA_CD=R_BSA.BSA_CD(+) and JRNL_ACTG.FY_DC=R_BSA.FY(+) )
any help is highly appreciated…
user222 (BOB member since 2003-11-12)
system
June 21, 2004, 10:18pm
2
I can’t find any cross-references in past posts, but I seem to recall that trying to get “all values” in a crosstab header stops working when you impose sections.
If anyone else can help me find a good reference, I’d appreciate it.
Anita Craig (BOB member since 2002-06-17)
system
June 22, 2004, 12:02am
3
Try a union query: bring in a “dummy” row for every single combination of your master/detail value and the columns you are after.
Andreas (BOB member since 2002-06-20)
system
June 22, 2004, 1:35pm
4
Andreas,
is it same as writing a cartesian product query on the lines:
select col1,col2 from tabl1,table2 (without a where condition… where col1 is section field and col2 is crosstab column field)…
what abt the crosstab row field?any ideas?
user222 (BOB member since 2003-11-12)
system
June 22, 2004, 2:01pm
5
andreas,
dummy rows in the union query works fine…
thanks a lot…
user222 (BOB member since 2003-11-12)