Cross tab report - How to show all the criteria used in the filters?

Kalaiselvan shared this question 12 months ago
Answered

Hi Team,

In Cross tab report, in the report output how i can show all the accounts that i selected in the filters. Even the account doesn't have any values.

Please refer Oracle BI screenshot of my requirements.

Regards,

Kalaiselvan

Comments (4)

photo
1

Hi Kalaiselvan,


can you please confirm something for me (to make sure I have not misunderstood the situation):

- If you do not filter by Journal Category do all of the accounts get displayed?

In other words, in the below example if I do not filter by JOB then I see all EMPNO:

/ZCPle41aRaOQe43Md+TIwzH+PMoawu41Mq0z6l4jeUl5IElqW4tG+ltSpnBBHQCcFaIRAPS09qKR6dnY0jVoXnYKHw4xP72tZTSSwkLkBXTyDH2mbR2a1cd8otapXiMnDzTpz7bT1mWMRqa9JzTF+BEAnB2iEQD0tNjRCAAAxxGNAKCnEY0AAPAQjQCgpxGNAADwEI0AoKcRjQAA8BCNAKCnEY0AAPAQjQCgp7WMRiMAAPQGohEA9LSW0QgAANSJRgDgPNsdDQAA2UA0AgDH2e5oAADIBqIRADjOdkcDAEA2EI0AwHG2OxoAALKBaAQAjrPd0QAAkA1EIwBwnO2OBgCAbCAaAYDjbHc0AABkA9EIABxnu6MBACAbiEYA4DjbHQ0AANlANAIAx9nuaAAAyAaiEQA4znZHAwBANhCNAMBxtjsaAACy4f8Abxyov8slrXUAAAAASUVORK5CYII=

Whereas if I filter by JOB = SALESMAN then I only get the 4 employees whose job is SALESMAN:

/U1SyaARJsOIAAAAASUVORK5CYII=

Is the situation you are dealing with?

If not then can you please tell me more about how you configured your report so I can set up a similar one over here.


regards,

David

photo
1

Hi David,

Thanks for your response. The details that you mentioned are the usual one.

For example, from your screenshot, if i filter Employee No - In list (7499,7521,7654) and the values are 300,500,(blank) respectively. My result should have all the three employee no, even 7654 have blank value. Is there any option to enable this?

Hope this makes clear. Please let me know if any further clarification required.

Thanks

Kalaiselvan

photo
1

Hi Kalaiselvan,

Check your underlying SQL. Make sure that your where clause "Journal Category" <> 'Revaluation' AND Quarter IN ('2018 Q 3') aren't filtering out the Account code you wish to display. You may want to create a virtual table so that the financial period is there even when there is no value before applying the filter. That way you will have the account code in the financial period. You will also need to COALESCE the journal category in at least a calculated field. The below example will make sure that every account_code has a financial period and when the calculated field is in the filter rather than actual field from the journal it will be displayed as -99. For example:

Select account_code, 
  Financial_Period.Quarter, 
  COALESCE(journal_cat, -99) "Journal Category"

From
  Accounts

Left Join 
   (
       Select distinct 
           Convert(Varchar, Year(cal_date)) + 'Q ' 
           + datepart(quarter, cal_year) Quarter
       from 
          calendar_table
    ) AS Financial_Period

Left Join 
   Journal_Table ON 
    (
        Accounts.account_code = Journal_Table.account_code
    )
    AND 
   (
       Convert(Varchar, Year(Journal_Table.account_period)) + 'Q ' 
           + datepart(quarter, Journal_Table.account_period) = Financial_Period.quarter
   ) 

photo
1

My database has a calendar table. If yours doesn't then find a relatively small table or run the same against your jorunal/transaction table. If you have an index on the date it'll run fast enough.

photo
1

Thanks Larry for your response.

photo
photo
2

wow, great advice from Larry (as always!). Definitely worth a try.

Regarding your particular example Kalaiselvan, I followed the steps: Firstly I set employee 7654 to have a blank value

/A2rWCahfDDUMAAAAAElFTkSuQmCC


and then for me, that column did not disappear:

/tUGAMH4f5rVF8MaKgTdAAAAAElFTkSuQmCC

so maybe you should check that your Column Formatting->Display->Display Empty Values = TRUE

regards,

David

photo
1

Hi David,

Thanks for your response. I couldn't find the option "Display Empty Values" (I am using version 7.4.7) in Column formatting. Attached screenshot for your reference. Your screenshot from V8?

I am looking for exactly what you are showing here :-)

Thanks

Kalaiselvan P

photo
1

Hi Kalaiselvan,

I'm definitely using 7.4.7! I have attached a short video to show you.

From your latest screenshot I can see your report is not a crosstab report, you've only got "Period Name" as a column, whereas in a crosstab report there will be at least 1 column, 1 row and 1 measure:

/AUGhs5+e4FR2AAAAAElFTkSuQmCC


The "Display Empty Values" option is only available for crosstab reports, so this explains why you can't find it.

regards,

David

photo
1

Hi David,

Thanks for your response and video reference. Yes, you are right. I can find in Cross tab report. I was checking in other report. I thought this option will be available in all format report.

Thanks again for your clarification.

Thanks

Kalaiselvan P

photo