Help wanted to organize a report Query

Hi everyone,

I have got a problem about how to organize a Query for a report, which is listed as Picture 1, which looks simple:

Picture 1

Basically, the AccountName column is from MainAccount Table, and the column1 through column4 are the values from a single field [GeneralJournalAccountEntry].[TransactionCurrencyAmount], but based on difference scenarios on the field of [MainAccount].[Type], the values should pick up ONLY 1 column from column_1 to column_4. Let’s say, if [MainAccount].[Type] = ‘revenue’, then value is put into column_1, if [MainAccount].[Type] = ‘balance sheet’, use column_2, etc.

The Query I got is here, as Picture 2:

Picture 2.

So, when develop a Report, using this Query as the dataset’s data source, I only have 1 field to use as below

2555.Rerpot In VS.jpg

Picture 3

So, it seems I should have something else created in the Query? I thought about creating display methods on table of [GeneralJournalAccountEntry], but the judging field is on [MainAccount], the value field is on table [GeneralJournalAccountEntry] table, I am a little lost here.

Any advice is greatly appreciated~

Kwen