asked on July 3, 2024
We have date values in MS Analysis Services which we are filtering on. Flexmonster recognizes them as Date values and allows for filtering with before, after, between etc, however it's returning incorrect values. In this dataset the values are in the format MM/dd/yyyy, please see screenshot attached.
Example returned values:
=04/07/2024 (dd/MM/yyyy) > 9/30/2021 (MM/dd/yyyy)
=01/07/2039 > 7/20/2022
=01/06/2039 > 7/11/2022
I have tried on a different cube and dataset and it works there.
How is it calculating the result? What is causing this dataset to be handled incorrectly?
⋅ July 4, 2024
Hello, Yosef!
Thank you for reaching out to us.
As an entry point, we recommend checking date configurations on the cube where the described behavior occurs and comparing them to your working samples.
If the cubes' configurations are the same, but the dates are filtered incorrectly when connecting Flexmonster to one of them, please provide us with a reproducible sample. With this information, we'll be able to further investigaate the case.
Looking forward to hearing from you.
Kind regards,
Solomiia
⋅ July 9, 2024
Hi Solomiia,
Thanks for your response.
I compared the Date fields in the cubes and haven't spotted a difference. Can you tell anything from the information below? I don't have specific steps to reproduce it yet.
HTTP Response for working cube:
<Tuple>
<Member Hierarchy="[Date].[Date]">
<UName>[Date].[Date].&[2024-07-04T00:00:00]</UName>
<Caption>7/4/2024</Caption>
<LName>[Date].[Date].[Date]</LName>
<LNum>1</LNum>
<DisplayInfo>0</DisplayInfo>
<PARENT_UNIQUE_NAME>[Date].[Date].[All]</PARENT_UNIQUE_NAME>
<HIERARCHY_UNIQUE_NAME>[Date].[Date]</HIERARCHY_UNIQUE_NAME>
<MEMBER_TYPE>1</MEMBER_TYPE>
</Member>
</Tuple>
HTTP Response for not working cube:
<Tuple>
<Member Hierarchy="[Ledger Journal - Fact].[Transaction Date]">
<UName>[Ledger Journal - Fact].[Transaction Date].&[2021-09-30T00:00:00]</UName>
<Caption>9/30/2021</Caption>
<LName>[Ledger Journal - Fact].[Transaction Date].[Transaction Date]</LName>
<LNum>1</LNum>
<DisplayInfo>0</DisplayInfo>
<PARENT_UNIQUE_NAME>[Ledger Journal - Fact].[Transaction Date].[All]</PARENT_UNIQUE_NAME>
<HIERARCHY_UNIQUE_NAME>[Ledger Journal - Fact].[Transaction Date]</HIERARCHY_UNIQUE_NAME>
<MEMBER_TYPE>1</MEMBER_TYPE>
</Member>
</Tuple>
Request query excerpt for working cube:
([Date].[Date].CurrentMember.MemberValue>=CDate('2024-07-05') AND [Date].[Date].CurrentMember.MemberValue<CDate('2024-07-06')))})
Request query excerpt for not working cube:
([Ledger Journal - Fact].[RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61].CurrentMember.MemberValue>=CDate('2024-07-04') AND [Ledger Journal - Fact].[RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61].CurrentMember.MemberValue<=CDate('2024-07-05')))})
Cube column definition for working cube:
"table": {
"name": "Date",
"dataCategory": "Time",
"columns": [{
"name": "Date",
"dataType": "dateTime",
"sourceColumn": "Date",
"formatString": "General Date",
"sourceProviderType": "DBDate"
},
Cube column definition for not working cube:
"table": {
"name": "Ledger Journal - Fact",
"dataCategory": "Time",
"columns": [{
"name": "Transaction Date",
"dataType": "dateTime",
"sourceColumn": "TransactionDate",
"formatString": "General Date",
"sourceProviderType": "DBDate"
},
⋅ July 9, 2024
Hello, Yosef!
Thank you for providing more details about your use case.
Kindly note that from our perspective, the request sent from Flexmonster contains a filter that should be applied to dates, and the structure looks valid.
However, our team has noticed that the level name for the "Transaction Date" in the request and response are not the same:
//Request:
[Ledger Journal - Fact].[RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61] ...
//Response:
Member Hierarchy="[Ledger Journal - Fact].[Transaction Date]" ...
We suggest checking how this filter request is handled on the cube side and if the correct data is queried.
Hope it helps.
Kind regards,
Solomiia
⋅ July 10, 2024
Hi Solomiia,
Thanks for your response and your team for noticing this.
I got the full <execute> XMLA statement in the request, modified the portion below and ran it on the cube:
[Ledger Journal - Fact].[RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61] ...
to
[Ledger Journal - Fact].[TransactionDate] ...This returns correct results.
Here is the full statement:
<Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
<Command>
<Statement><![CDATA[
SELECT
NON EMPTY {[Measures].[Ledger Transaction Amount]}
DIMENSION PROPERTIES PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME, MEMBER_TYPE
ON COLUMNS,
NON EMPTY Hierarchize({DrilldownLevel({[Ledger Journal - Fact].[Transaction Date].[All]})})
DIMENSION PROPERTIES PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME, MEMBER_TYPE
ON ROWS
FROM
(
SELECT
({Filter([Ledger Journal - Fact].[RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61].levels(1),
([Ledger Journal - Fact].[RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61].CurrentMember.MemberValue >= CDate('2024-07-09')
AND
[Ledger Journal - Fact].[RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61].CurrentMember.MemberValue < CDate('2024-07-10')))})
ON COLUMNS
FROM
(
SELECT
({[Year - Filter].[Year].&[2024]})
ON COLUMNS
FROM [Model]
)
)
WHERE
([Year - Filter].[Year].&[2024])
CELL PROPERTIES VALUE, FORMATTED_VALUE
]]></Statement>
</Command>
<Properties>
<PropertyList flexmonster="true">
<DataSourceInfo>theDataSource</DataSourceInfo>
<Catalog>theCatalog</Catalog>
<AxisFormat>TupleFormat</AxisFormat>
<Content>Data</Content>
<Format>Multidimensional</Format>
</PropertyList>
</Properties>
</Execute>
What is causing Flexmonster to generate the query with the `[Ledger Journal - Fact].[RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61]`?
⋅ July 10, 2024
Hello, Yosef!
Thank you for your swift response.
We are glad to hear that the filter works well after changing the level name.
Regarding your question about what is causing Flexmonster to generate the query with the RowNumber..., please note that working with OLAP cubes, Flexmonster shows exactly what was passed to the component from the cube.
So, the RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61 was sent to Flexmonster as the field or level name in one of the previous queries. We recommend checking previous cube responses to determine when the level name was changed.
Hope our answer has addressed your question.
Best regards,
Solomiia
⋅ July 11, 2024
Hi Solomiia,
Thanks for the additional information.
I checked the previous responses and found,
In the MDSCHEMA_MEASUREGROUP_DIMENSIONS request for the not working cube, the response contains the following row:
<row>
<CATALOG_NAME>DW-UAT</CATALOG_NAME>
<CUBE_NAME>Model</CUBE_NAME>
<MEASUREGROUP_NAME>Ledger Journal - Fact</MEASUREGROUP_NAME>
<MEASUREGROUP_CARDINALITY>ONE</MEASUREGROUP_CARDINALITY>
<DIMENSION_UNIQUE_NAME>[Ledger Journal - Fact]</DIMENSION_UNIQUE_NAME>
<DIMENSION_CARDINALITY>ONE</DIMENSION_CARDINALITY>
<DIMENSION_IS_VISIBLE>true</DIMENSION_IS_VISIBLE>
<DIMENSION_IS_FACT_DIMENSION>true</DIMENSION_IS_FACT_DIMENSION>
<DIMENSION_GRANULARITY>[Ledger Journal - Fact].[RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61]</DIMENSION_GRANULARITY>
</row>
For the working cube it is similar:
<row>
<CATALOG_NAME>BIMS</CATALOG_NAME>
<CUBE_NAME>Model</CUBE_NAME>
<MEASUREGROUP_NAME>Date</MEASUREGROUP_NAME>
<MEASUREGROUP_CARDINALITY>ONE</MEASUREGROUP_CARDINALITY>
<DIMENSION_UNIQUE_NAME>[Date]</DIMENSION_UNIQUE_NAME>
<DIMENSION_CARDINALITY>ONE</DIMENSION_CARDINALITY>
<DIMENSION_IS_VISIBLE>true</DIMENSION_IS_VISIBLE>
<DIMENSION_IS_FACT_DIMENSION>true</DIMENSION_IS_FACT_DIMENSION>
<DIMENSION_GRANULARITY>[Date].[RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61]</DIMENSION_GRANULARITY>
</row>
The MDSCHEMA_HIERARCHIES request contains the following in the response:
<row>
<CATALOG_NAME>DW-UAT</CATALOG_NAME>
<CUBE_NAME>Model</CUBE_NAME>
<DIMENSION_UNIQUE_NAME>[Ledger Journal - Fact]</DIMENSION_UNIQUE_NAME>
<HIERARCHY_NAME>Transaction Date</HIERARCHY_NAME>
<HIERARCHY_UNIQUE_NAME>[Ledger Journal - Fact].[Transaction Date]</HIERARCHY_UNIQUE_NAME>
<HIERARCHY_CAPTION>Transaction Date</HIERARCHY_CAPTION>
<DIMENSION_TYPE>1</DIMENSION_TYPE>
<HIERARCHY_CARDINALITY>0</HIERARCHY_CARDINALITY>
<DEFAULT_MEMBER>[Ledger Journal - Fact].[Transaction Date].[All]</DEFAULT_MEMBER>
<ALL_MEMBER>[Ledger Journal - Fact].[Transaction Date].[All]</ALL_MEMBER>
<DESCRIPTION />
<STRUCTURE>0</STRUCTURE>
<IS_VIRTUAL>false</IS_VIRTUAL>
<IS_READWRITE>false</IS_READWRITE>
<DIMENSION_UNIQUE_SETTINGS>1</DIMENSION_UNIQUE_SETTINGS>
<DIMENSION_IS_VISIBLE>true</DIMENSION_IS_VISIBLE>
<HIERARCHY_ORDINAL>189</HIERARCHY_ORDINAL>
<DIMENSION_IS_SHARED>true</DIMENSION_IS_SHARED>
<HIERARCHY_IS_VISIBLE>true</HIERARCHY_IS_VISIBLE>
<HIERARCHY_ORIGIN>2</HIERARCHY_ORIGIN>
<HIERARCHY_DISPLAY_FOLDER />
<INSTANCE_SELECTION>0</INSTANCE_SELECTION>
<GROUPING_BEHAVIOR>1</GROUPING_BEHAVIOR>
<STRUCTURE_TYPE>Natural</STRUCTURE_TYPE>
</row>
The MDSCHEMA_LEVELS request contains the following row in the response:
<row>
<CATALOG_NAME>DW-UAT</CATALOG_NAME>
<CUBE_NAME>Model</CUBE_NAME>
<DIMENSION_UNIQUE_NAME>[Ledger Journal - Fact]</DIMENSION_UNIQUE_NAME>
<HIERARCHY_UNIQUE_NAME>[Ledger Journal - Fact].[Transaction Date]</HIERARCHY_UNIQUE_NAME>
<LEVEL_NAME>(All)</LEVEL_NAME>
<LEVEL_UNIQUE_NAME>[Ledger Journal - Fact].[Transaction Date].[(All)]</LEVEL_UNIQUE_NAME>
<LEVEL_CAPTION>(All)</LEVEL_CAPTION>
<LEVEL_NUMBER>0</LEVEL_NUMBER>
<LEVEL_CARDINALITY>0</LEVEL_CARDINALITY>
<LEVEL_TYPE>1</LEVEL_TYPE>
<CUSTOM_ROLLUP_SETTINGS>0</CUSTOM_ROLLUP_SETTINGS>
<LEVEL_UNIQUE_SETTINGS>0</LEVEL_UNIQUE_SETTINGS>
<LEVEL_IS_VISIBLE>true</LEVEL_IS_VISIBLE>
<LEVEL_ORDERING_PROPERTY>(All)</LEVEL_ORDERING_PROPERTY>
<LEVEL_DBTYPE>3</LEVEL_DBTYPE>
<LEVEL_KEY_CARDINALITY>1</LEVEL_KEY_CARDINALITY>
<LEVEL_ORIGIN>2</LEVEL_ORIGIN>
</row>
The date field filter query request returns a HierarchyInfo:
// Request excerpt
<Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
<Command>
<Statement><![CDATA[
SELECT
Subset(
DISTINCT(
{[Ledger Journal - Fact].[Transaction Date].levels(0).allmembers,
[Ledger Journal - Fact].[Transaction Date].levels(1).allmembers}
), 0, 5000
)
DIMENSION PROPERTIES PARENT_UNIQUE_NAME
ON 0
FROM [Model]
]]>
</Statement>
</Command>
<Properties>
<PropertyList flexmonster="true">
<Catalog>DW-UAT</Catalog>
<AxisFormat>TupleFormat</AxisFormat>
<Content>Data</Content>
<Format>Multidimensional</Format>
</PropertyList>
</Properties>
</Execute>
// Response excerpt
<HierarchyInfo name="[Ledger Journal - Fact].[Transaction Date]">
<UName name="[Ledger Journal - Fact].[Transaction Date].[MEMBER_UNIQUE_NAME]" type="xsd:string" />
<Caption name="[Ledger Journal - Fact].[Transaction Date].[MEMBER_CAPTION]" type="xsd:string" />
<LName name="[Ledger Journal - Fact].[Transaction Date].[LEVEL_UNIQUE_NAME]" type="xsd:string" />
<LNum name="[Ledger Journal - Fact].[Transaction Date].[LEVEL_NUMBER]" type="xsd:int" />
<DisplayInfo name="[Ledger Journal - Fact].[Transaction Date].[DISPLAY_INFO]" type="xsd:unsignedInt" />
</HierarchyInfo>
The closest I can find is the MDSCHEMA_MEASUREGROUP_DIMENSIONS request however the working cube has a similar response. Are any of these the source of the RowNumber in the data query?
⋅ July 11, 2024
Hello, Yosef!
Thank you for providing us with additional details. It helped us greatly to investigate the case.
Kindly note that by default, Flexmonster takes the level names from GranularityNames when filtering dates, which can, in some cases, contain the ids instead of uniqueNames.
We recommend setting the useGranularityNamesForDateFilters to false in the dataSource object as follows:
report: {
dataSource: {
//other properties to connect to the cube
useGranularityNamesForDateFilters: false
},
//other report properties
}
Please let us know if disabling GranularityNames for date filters helps to resolve the case.
Looking forward to hearing your feedback.
Kind regards,
Solomiia
⋅ July 12, 2024
Hi Solomiia,
Thanks for the `useGranularityNamesForDateFilters` property, setting it to false has resolved the issue for us, the correct dates are returned for the filter.
Thank you for your help
⋅ July 12, 2024
Hello, Yosef!
Thank you for your feedback.
We are glad to hear the useGranularityNamesForDateFilters property works well for your case.
Feel free to reach out to us in case of any other questions.
Best regards,
Solomiia