Requirement in Straight Table

[priyarane]

Hi Community,

I have requirement like

Show Invest ID where not equal to (Country Jan and Country Dec) or (CCR Jan and CCR Dec)

For the above requirement

1. I have added Invest ID as Dimension and disabled in Presentation

2. I have added expression for Invest ID as if([Country Dec]<>[Country Jan] or [CCR Dec]<>[CCR Jan],INVEST_ID)

3. for Expression - Country Dec: Only({<%Month = {'Dec'}>}COUNTRY_NAME)

4. for Expression - Country Jan: Only({<%Month = {'Jan'}>}COUNTRY_NAME)

5. for Expression - CCR Dec: Only({<%Month = {'Dec'}>}CCR)

6. for Expression - CCR Jan: Only({<%Month = {'Jan'}>}CCR)

7. for Expression - Sale Jan: Sum({<%Month = {'Jan'}>}Sales)

8. for Expression - Sale Dec: Sum({<%Month = {'Dec'}>}Sales)

So Now I got straight table like below:

But users are looking out put only where we have values in Inv ID so they don't wanted to see rest of the data

For ex they wanted to see only highlited ones like below.

How can we achieve this requirement, can some one pls help me. Do I need to changes expressions or any other approach.

[Chanty4u]

in dimension tab

chk supress wen values is null?

[Kushal_Chawda]

try adding calculated dimension for Inv ID as

if(len(trim([Inv ID])<=0,null(),[Inv ID])

and check 'suppress when values is null'

[Chanty4u]

orelse u can create a field

Load

if (Fieldname <> 0,FieldName) as Fieldname

[priyarane]

All the suggestions are not working out...

First of all is my approach is correct

[ThornOfCrowns]

You are forcing the blank lines for INVEST_ID, which I presume is the Inv_ID column :

I have added expression for Invest ID as if([Country Dec]<>[Country Jan] or [CCR Dec]<>[CCR Jan],INVEST_ID)

I am also presuming that you don't have an INVEST_ID for every row.

Therefore, if you remove the above expression and then use your INVEST_ID dimension, you'll be able to supress the blank rows as per chanty4u‌ suggestion

[tyagishaila]

if issue is, just want to see data where Inv ID is available,

//But users are looking out put only where we have values in Inv ID so they don't wanted to see rest of the data//

than  solution is

Go to  Dimension Tab --> Check Suppress When Value is Null for Inv ID dimension.

[Chanty4u]

did u tried dis?

[tyagishaila]

what is your dimension in straight table.

as you describe straight table has expressions only.

share qvw file

[jonathandienst]

To suppress the row, all the expressions must valuate to null or zero. So you will need InvID as an expression that evaluates to null (NOT blank) when it has no value, and all the other expression will need to test this to return a zero if InvID is null. If you post a qvw with some representative data, then I can explain in more detail what you need to do, but the other expressions will be something like:

     =If(IsNull(<... expression for invID ...>), 0, <... oriiginal expression ...>)