a column is missing in pivot when in selection mode..

[msmichael]

hi expert, 

I got a very weird question.

I have a pivot table with many dimensions, two of which are calculated dimensions, like below:

Publish List: =aggr(Concat({<[Report Email Email Type]={"PUBLISH"}>} [Report Email Email], ';'), [%SK_REPORT_OUTPUT_ID])

Distribution List: =aggr(Concat({<[Report Email Email Type]={"DISTRIBUTE"}>} [Report Email Email], ';'), [%SK_REPORT_OUTPUT_ID])

when there's no selection, everything looks fine (we can see both Publish List and Distribution List columns), see this screen shot:

We see in the first row, "Report Output ID" = 11388, its Publish List is empty, and Distribution List it not empty.

When I select this record by click "Report Output Id" field, the Distribution List column is gone, see below screenshot,

However, seems it's also fine when selecting other fields . For example, if I filter "Report Frequency Name" column, the Distribution List column is still there (see below)

I have checked that "Suppress When Value Is Null" is NOT checked for both dimensions.

also, the "Suppress Zero-Values" is NOT checked for both dimensions in "Presentation" tab.

"Suppress Missing" is checked by is also grayed out so I can't change them.. 

What could be the reason for this?

Thanks,

Michael

[uacg0009]

Hi Michael,
I have some questions, but if you can share these data in screenshot, that's really good for me to try to find the reason. You can scram all data.
Q1: The [%SK_REPORT_OUTPUT_ID] is Report Output ID?
Q2: The Report Output ID and [Report Email Email] are in one table in back-end?
Q3: When Report Output ID = 11388, what's the [Report Email Email Type]?
Thanks
Aiolos

[msmichael]

Hi Aiolos,

thanks for your reply. Please find answers to the questions below,

Q1: The [%SK_REPORT_OUTPUT_ID] is Report Output ID?

The %SK_REPORT_OUTPUT_ID and "Report Output ID" are two fields. But they belong to same table. And they come from same field in data source, my load script for this part is something like this:

'REPORT_OUTPUT':
LOAD
[REPORT_OUTPUT_ID] AS [%SK_REPORT_OUTPUT_ID],
[REPORT_OUTPUT_ID] AS [Report Output ID],
FROM qvdfilepath.QVD;

So they essentially are the same, I just load this field into two fields.. In the screenshot, the "Report Output Id" column in the pivot table is [Report Output ID] field. Also, I tried using [Report Output ID] in the calculated dimensions but still got same issue..

Q2: The Report Output ID and [Report Email Email] are in one table in back-end?

No, they are in two tables. The tables are linked by %SK_REPORT_OUTPUT_ID field..

This is the table structure. There are many other tables too, they are all connected by the %SK_REPORT_OUTPUT_ID field.. 

Q3: When Report Output ID = 11388, what's the [Report Email Email Type]?

When Report Output ID = 11388, the [Report Email Email Type] is like below. As you can see there are two values in [Report Email Email Type] field, the "DISTRIBUTE" is the one I used for "Distribution List" column, the "REVIEW" one is not used.

Below is how the data for this 11388 look in database..

This issue happens for all Report_Output_IDs that have null value in "Publish List" and not null values "Distribution List"..

I know it's too hard to tell what the reason for this issue without checking the qvw file, but I apologize that I can't upload the file here... 

Thanks a lot!

Michael

[uacg0009]

Hi Michael,

Thanks for your reply.

I created a sample according to your feedback, but I didn't revert your situation, could you please see my sample?

Could you please add some fake data to try to revert the pivot table situation so I can find out what the problem is?

Thanks.

Aiolos

[msmichael]

Hi Aiolos,

Please see attached, I am able to re-produce this issue. If you click in the "Report Output ID" column that do not have anything in "Publish List", you'll see this issue.

I also checked your qvw file. If you change the table type to Pivot table, and click 11388 in "RID" column, you'll see same issue.

Thanks,

Michael

[uacg0009 · Accepted solution]

Hi Michael,
I think I found out this problem, because the second aggr is based on the first aggr, so if the first aggr is null, then it won't show the second one.
I have a method, you can change these 2 calculated dimensions to expression, then if you choose the Report ID, it won't disappear, you can try it.
If you can't move the dimension to expression, please tell me, I will try to find another way.
Thanks
Aiolos

[msmichael]

Hi Aiolos,

It makes sense...

I think I can move the dimension to expression. 

Thanks a lot!

Michael

[uacg0009]

You're welcome, glad to help you.
If it fixed, could you please mark this question as solved?
Thanks.
Aiolos