EducationSoftwareStrategy.com
StrategyCommunity

Knowledge Base

Product

Community

Knowledge Base

TopicsBrowse ArticlesDeveloper Zone

Product

Download SoftwareProduct DocumentationSecurity Hub

Education

Tutorial VideosSolution GalleryEducation courses

Community

GuidelinesGrandmastersEvents
x_social-icon_white.svglinkedin_social-icon_white.svg
Strategy logoCommunity

© Strategy Inc. All Rights Reserved.

LegalTerms of UsePrivacy Policy
  1. Home
  3. Topics

KB19571: Filter qualification is not shown in SQL when applying a View filter in MicroStrategy

Stefan Zepeda

Salesforce Solutions Architect • Strategy

This article notes how view filters differ from report filters

SYMPTOM:
After applying a view filter for a report in Strategy, the filtering criteria is not shown anywhere in the SQL of the report.
 
The absence of the view filter in SQL can be seen in the example below, where a report with and without a view filter are compared:
 

  • Report without a View filter and its SQL view:
ka02R000000kWLQQA2_0EM440000002V1L.jpeg
  • Report with a View filter and its SQL view:
ka02R000000kWLQQA2_0EM440000002V1K.jpeg

CAUSE:
This is the expected behavior of a view filter.
 
A view filter is a qualification that is applied to the report data set in the memory of the Intelligence server, and therefore does not have to be re-executed against the data warehouse. When the view filter is applied to the report it will not alter the report SQL.
 
One of the advantages of view filters is that it retrieves information quickly by not requiring a database query and dynamically accessing the data that is in memory instead.
 
ACTION:
If the filter qualification is required to be in the SQL of the report, a Report Filter should be used. Report filter qualifications are queried against the warehouse and will appear in the SQL of a report.
 
The differences between a view filter and report filter are summarized in the table below:
 
 

select pa12. AS QUARTER_ID,
   a13. AS QUARTER_DESC0,
   pa12. AS WJXBFS1,
   pa12. AS WJXBFS2
from pa12,
    a13
where pa12. = a13.
 and (pa12. = 1
 and pa12. = 1)
FilterReferenceEngineSQLApplicationOLAPReport FilterMay reference any object, whether or not it is part of the reportSQL engineGenerated every time there is a change in the filter. It is often part of the WHERE clause of a SQL statementApplied before the generation of the SQL and is then sent to the data warehouseNot an OLAP featureView FilterMay reference only objects that reside in the Report Objects WindowAnalytical Engineno SQL generatedApplied after the data is retrieved from the data warehouseOLAP feature only

select pa12. AS QUARTER_ID,
   a13. AS QUARTER_DESC0,
   pa12. AS WJXBFS1,
   pa12. AS WJXBFS2
from pa12,
    a13
where pa12. = a13.
 and (pa12. = 1
 and pa12. = 1)
FilterReferenceEngineSQLApplicationOLAPReport FilterMay reference any object, whether or not it is part of the reportSQL engineGenerated every time there is a change in the filter. It is often part of the WHERE clause of a SQL statementApplied before the generation of the SQL and is then sent to the data warehouseNot an OLAP featureView FilterMay reference only objects that reside in the Report Objects WindowAnalytical Engineno SQL generatedApplied after the data is retrieved from the data warehouseOLAP feature only

select pa12. AS QUARTER_ID,
   a13. AS QUARTER_DESC0,
   pa12. AS WJXBFS1,
   pa12. AS WJXBFS2
from pa12,
    a13
where pa12. = a13.
 and (pa12. = 1
 and pa12. = 1)
FilterReferenceEngineSQLApplicationOLAPReport FilterMay reference any object, whether or not it is part of the reportSQL engineGenerated every time there is a change in the filter. It is often part of the WHERE clause of a SQL statementApplied before the generation of the SQL and is then sent to the data warehouseNot an OLAP featureView FilterMay reference only objects that reside in the Report Objects WindowAnalytical Engineno SQL generatedApplied after the data is retrieved from the data warehouseOLAP feature only

select pa12. AS QUARTER_ID,
   a13. AS QUARTER_DESC0,
   pa12. AS WJXBFS1,
   pa12. AS WJXBFS2
from pa12,
    a13
where pa12. = a13.
 and (pa12. = 1
 and pa12. = 1)
FilterReferenceEngineSQLApplicationOLAPReport FilterMay reference any object, whether or not it is part of the reportSQL engineGenerated every time there is a change in the filter. It is often part of the WHERE clause of a SQL statementApplied before the generation of the SQL and is then sent to the data warehouseNot an OLAP featureView FilterMay reference only objects that reside in the Report Objects WindowAnalytical Engineno SQL generatedApplied after the data is retrieved from the data warehouseOLAP feature only

select pa12. AS QUARTER_ID,
   a13. AS QUARTER_DESC0,
   pa12. AS WJXBFS1,
   pa12. AS WJXBFS2
from pa12,
    a13
where pa12. = a13.
 and (pa12. = 1
 and pa12. = 1)
FilterReferenceEngineSQLApplicationOLAPReport FilterMay reference any object, whether or not it is part of the reportSQL engineGenerated every time there is a change in the filter. It is often part of the WHERE clause of a SQL statementApplied before the generation of the SQL and is then sent to the data warehouseNot an OLAP featureView FilterMay reference only objects that reside in the Report Objects WindowAnalytical Engineno SQL generatedApplied after the data is retrieved from the data warehouseOLAP feature only

select pa12. AS QUARTER_ID,
   a13. AS QUARTER_DESC0,
   pa12. AS WJXBFS1,
   pa12. AS WJXBFS2
from pa12,
    a13
where pa12. = a13.
 and (pa12. = 1
 and pa12. = 1)
FilterReferenceEngineSQLApplicationOLAPReport FilterMay reference any object, whether or not it is part of the reportSQL engineGenerated every time there is a change in the filter. It is often part of the WHERE clause of a SQL statementApplied before the generation of the SQL and is then sent to the data warehouseNot an OLAP featureView FilterMay reference only objects that reside in the Report Objects WindowAnalytical Engineno SQL generatedApplied after the data is retrieved from the data warehouseOLAP feature only

select pa12. AS QUARTER_ID,
   a13. AS QUARTER_DESC0,
   pa12. AS WJXBFS1,
   pa12. AS WJXBFS2
from pa12,
    a13
where pa12. = a13.
 and (pa12. = 1
 and pa12. = 1)
FilterReferenceEngineSQLApplicationOLAPReport FilterMay reference any object, whether or not it is part of the reportSQL engineGenerated every time there is a change in the filter. It is often part of the WHERE clause of a SQL statementApplied before the generation of the SQL and is then sent to the data warehouseNot an OLAP featureView FilterMay reference only objects that reside in the Report Objects WindowAnalytical Engineno SQL generatedApplied after the data is retrieved from the data warehouseOLAP feature only

select pa12. AS QUARTER_ID,
   a13. AS QUARTER_DESC0,
   pa12. AS WJXBFS1,
   pa12. AS WJXBFS2
from pa12,
    a13
where pa12. = a13.
 and (pa12. = 1
 and pa12. = 1)
FilterReferenceEngineSQLApplicationOLAPReport FilterMay reference any object, whether or not it is part of the reportSQL engineGenerated every time there is a change in the filter. It is often part of the WHERE clause of a SQL statementApplied before the generation of the SQL and is then sent to the data warehouseNot an OLAP featureView FilterMay reference only objects that reside in the Report Objects WindowAnalytical Engineno SQL generatedApplied after the data is retrieved from the data warehouseOLAP feature only

select pa12. AS QUARTER_ID,
   a13. AS QUARTER_DESC0,
   pa12. AS WJXBFS1,
   pa12. AS WJXBFS2
from pa12,
    a13
where pa12. = a13.
 and (pa12. = 1
 and pa12. = 1)
FilterReferenceEngineSQLApplicationOLAPReport FilterMay reference any object, whether or not it is part of the reportSQL engineGenerated every time there is a change in the filter. It is often part of the WHERE clause of a SQL statementApplied before the generation of the SQL and is then sent to the data warehouseNot an OLAP featureView FilterMay reference only objects that reside in the Report Objects WindowAnalytical Engineno SQL generatedApplied after the data is retrieved from the data warehouseOLAP feature only

select pa12. AS QUARTER_ID,
   a13. AS QUARTER_DESC0,
   pa12. AS WJXBFS1,
   pa12. AS WJXBFS2
from pa12,
    a13
where pa12. = a13.
 and (pa12. = 1
 and pa12. = 1)
FilterReferenceEngineSQLApplicationOLAPReport FilterMay reference any object, whether or not it is part of the reportSQL engineGenerated every time there is a change in the filter. It is often part of the WHERE clause of a SQL statementApplied before the generation of the SQL and is then sent to the data warehouseNot an OLAP featureView FilterMay reference only objects that reside in the Report Objects WindowAnalytical Engineno SQL generatedApplied after the data is retrieved from the data warehouseOLAP feature only

select pa12. AS QUARTER_ID,
   a13. AS QUARTER_DESC0,
   pa12. AS WJXBFS1,
   pa12. AS WJXBFS2
from pa12,
    a13
where pa12. = a13.
 and (pa12. = 1
 and pa12. = 1)
FilterReferenceEngineSQLApplicationOLAPReport FilterMay reference any object, whether or not it is part of the reportSQL engineGenerated every time there is a change in the filter. It is often part of the WHERE clause of a SQL statementApplied before the generation of the SQL and is then sent to the data warehouseNot an OLAP featureView FilterMay reference only objects that reside in the Report Objects WindowAnalytical Engineno SQL generatedApplied after the data is retrieved from the data warehouseOLAP feature only

select pa12. AS QUARTER_ID,
   a13. AS QUARTER_DESC0,
   pa12. AS WJXBFS1,
   pa12. AS WJXBFS2
from pa12,
    a13
where pa12. = a13.
 and (pa12. = 1
 and pa12. = 1)
FilterReferenceEngineSQLApplicationOLAPReport FilterMay reference any object, whether or not it is part of the reportSQL engineGenerated every time there is a change in the filter. It is often part of the WHERE clause of a SQL statementApplied before the generation of the SQL and is then sent to the data warehouseNot an OLAP featureView FilterMay reference only objects that reside in the Report Objects WindowAnalytical Engineno SQL generatedApplied after the data is retrieved from the data warehouseOLAP feature only

select pa12. AS QUARTER_ID,
   a13. AS QUARTER_DESC0,
   pa12. AS WJXBFS1,
   pa12. AS WJXBFS2
from pa12,
    a13
where pa12. = a13.
 and (pa12. = 1
 and pa12. = 1)
FilterReferenceEngineSQLApplicationOLAPReport FilterMay reference any object, whether or not it is part of the reportSQL engineGenerated every time there is a change in the filter. It is often part of the WHERE clause of a SQL statementApplied before the generation of the SQL and is then sent to the data warehouseNot an OLAP featureView FilterMay reference only objects that reside in the Report Objects WindowAnalytical Engineno SQL generatedApplied after the data is retrieved from the data warehouseOLAP feature only

select pa12. AS QUARTER_ID,
   a13. AS QUARTER_DESC0,
   pa12. AS WJXBFS1,
   pa12. AS WJXBFS2
from pa12,
    a13
where pa12. = a13.
 and (pa12. = 1
 and pa12. = 1)
FilterReferenceEngineSQLApplicationOLAPReport FilterMay reference any object, whether or not it is part of the reportSQL engineGenerated every time there is a change in the filter. It is often part of the WHERE clause of a SQL statementApplied before the generation of the SQL and is then sent to the data warehouseNot an OLAP featureView FilterMay reference only objects that reside in the Report Objects WindowAnalytical Engineno SQL generatedApplied after the data is retrieved from the data warehouseOLAP feature only

select pa12. AS QUARTER_ID,
   a13. AS QUARTER_DESC0,
   pa12. AS WJXBFS1,
   pa12. AS WJXBFS2
from pa12,
    a13
where pa12. = a13.
 and (pa12. = 1
 and pa12. = 1)
FilterReferenceEngineSQLApplicationOLAPReport FilterMay reference any object, whether or not it is part of the reportSQL engineGenerated every time there is a change in the filter. It is often part of the WHERE clause of a SQL statementApplied before the generation of the SQL and is then sent to the data warehouseNot an OLAP featureView FilterMay reference only objects that reside in the Report Objects WindowAnalytical Engineno SQL generatedApplied after the data is retrieved from the data warehouseOLAP feature only

select pa12. AS QUARTER_ID,
   a13. AS QUARTER_DESC0,
   pa12. AS WJXBFS1,
   pa12. AS WJXBFS2
from pa12,
    a13
where pa12. = a13.
 and (pa12. = 1
 and pa12. = 1)
FilterReferenceEngineSQLApplicationOLAPReport FilterMay reference any object, whether or not it is part of the reportSQL engineGenerated every time there is a change in the filter. It is often part of the WHERE clause of a SQL statementApplied before the generation of the SQL and is then sent to the data warehouseNot an OLAP featureView FilterMay reference only objects that reside in the Report Objects WindowAnalytical Engineno SQL generatedApplied after the data is retrieved from the data warehouseOLAP feature only

select pa12. AS QUARTER_ID,
   a13. AS QUARTER_DESC0,
   pa12. AS WJXBFS1,
   pa12. AS WJXBFS2
from pa12,
    a13
where pa12. = a13.
 and (pa12. = 1
 and pa12. = 1)
FilterReferenceEngineSQLApplicationOLAPReport FilterMay reference any object, whether or not it is part of the reportSQL engineGenerated every time there is a change in the filter. It is often part of the WHERE clause of a SQL statementApplied before the generation of the SQL and is then sent to the data warehouseNot an OLAP featureView FilterMay reference only objects that reside in the Report Objects WindowAnalytical Engineno SQL generatedApplied after the data is retrieved from the data warehouseOLAP feature only

select pa12. AS QUARTER_ID,
   a13. AS QUARTER_DESC0,
   pa12. AS WJXBFS1,
   pa12. AS WJXBFS2
from pa12,
    a13
where pa12. = a13.
 and (pa12. = 1
 and pa12. = 1)
FilterReferenceEngineSQLApplicationOLAPReport FilterMay reference any object, whether or not it is part of the reportSQL engineGenerated every time there is a change in the filter. It is often part of the WHERE clause of a SQL statementApplied before the generation of the SQL and is then sent to the data warehouseNot an OLAP featureView FilterMay reference only objects that reside in the Report Objects WindowAnalytical Engineno SQL generatedApplied after the data is retrieved from the data warehouseOLAP feature only

Comment

0 comments

Details

Knowledge Article

Published:

July 18, 2017

Last Updated:

July 18, 2017