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

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:

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

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

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

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
   ) 

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

and then for me, that column did not disappear:

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

regards,

David