MDX - Filter on Date but Display higher Period level along with drill ability

[eliza_jane]

Is there a way to display a chart with date range prompts and date series and allow drill on the date series?

Basically we need to display a time series chart starting at months along the x axis with ability to drill up and down and have date range prompts as well.

We are using a DMR model.

[CognosPaul]

I'm not following what you need with the drills. If I understand the other requirement, you need to show months in the axis, but filter by dates?

If the user selects Jan 15, do you want to show the entire month of January, or only the part of January that's included in the date range?

First thing to do is to get that range of dates filter([cube].[dim].[hier].[day level],[cube].[dim].[hier].[day level].[Date attribute] between ?startDate? and ?endDate?)

Test that in another query. It should return all of the days in the range you select.

If that works, stick that in the slicer and try running the report. If all goes well, your chart should only include the months of the days you've selected.

[eliza_jane]

Hi Paul,

The requirement is not just to display months on the x axis and filter by dates but also allow the months to be drillable. I have been able to filter by dates and display the months but the drills seem to be the issue.

Example, when i filter for say Jan 1 - Jul 31 and display months and then drill up to quarters and then in the date range prompt choose dates corresponding to say q4 2012 my chart comes out blank. I understand why this is happening but is there a way to be able to tie the date prompts to the period being displayed so it brings back the corresponding periods on drill. I have experimented with member sets and advanced drill behaviour but with no luck.

Hope this helps

[CognosPaul]

I'm beginning to understand now.

In your example, if you drill up to 2012, Q4 should not be blank? That sounds counter-intuitive to me. I would expect to see data that matches the date range that I selected. So Q3 should show only the data that occurred in July.

That being said, if you want to show all of the data for Q3, do you also want to show all of the data for July? What should July show if you select Jan 1 - Jul 15?

How are you filtering by dates at the moment? Detail filter or slicer?

[eliza_jane]

So if I select dates corresponding to say Q1 2013 and Q2 2013 and have the charts display the months and then do a drill up to quarters the chart needs to show Q1 2013 and Q2 2013. And then when I select dates corresponding to Q4 2012 in the date range prompt since I am at the quarter level and have chosen dates corresponding to Q4 2012 the chart only needs to display Q4 2012.

So basically just have a way to filter on dates, start with display of months and allow the ability to drill up to quarters/years or drill down to weeks/dates all allowing for date range prompts to be entered.

Since I need to display the Months along the axis cant use slicers else every period comes up with just the required months having data.

Also when I try to filter on Months but use dates for the boolean expression Cognos throws errors complaining about filtering on a lower level set than the projection.

Any way this requirement can be met?

[CognosPaul]

There should be a way, it will have to wait until Sunday though. In the meantime, try setting the overriding dimensional info to yes, and drag in your day set and the month set as two separate dimensions.

[eliza_jane]

The issue with having a day set and month set is since this is a chart report and for some reason properties set on chart nodes dont seem to have any effect or at least for me thats how it is. Is there something I am doing wrong for it to not to work? I have the categories set to the month and in the chart node properties for the category i have set the date data item to take effect.

So I am forced to have just one data item that does both the display and the filtering work.

[CognosPaul]

Instead of using the properties, slice the query by the day set. Attempting to use a single data item to display the months while filtering on dates will only end in frustration.

I just attempted this on one of my client's cubes.

A single date hierarchy, years->quarters->months->dates. I dragged the years level to the crosstab columns, sales types into the rows, and sales into the measures. In the query I sliced by a static set of Jan 15:Feb 14 of 2012. I set the crosstab to suppress columns and enabled drill downs.

2012 is the only year shown, and drilling down or up continues to show only the data I expect to see.

[eliza_jane]

Hi Paul,

I wanted to avoid suppression since we have data for many years and it impacts performance. Anyway we can avoid the suppression?

Thanks.

[eliza_jane]

Hi Paul, This does not seem to work for me. I pulled in months and created a slicer on the dates based on the date prompts defaulted to display 6 months data. The   chart displays the months and the drill works but when I select a different time period than the one in display on the chart then I get no data.

So the chart gets me data for 6 months of this year and then i drill up and see quarters of 2013. Then when I choose dates for 2012 I get no data.

I just have the months level pulled in for x axis, a measure, and a product dimension for the series.

My slicer is defined as filter([Date Level], [Date Attrib] between #prompt('p_FromDate','Date',timestampMask ( _add_months ( $current_timestamp , -6 ) , 'yyyy-mm-dd' ))#
and
#prompt('p_ToDate','Date',timestampMask ($current_timestamp, 'yyyy-mm-dd' ))#
)

Anything I am missing out?

[CognosPaul]

To be clear:

filter([Date Level], [Date Attrib] between #prompt('p_FromDate','Date',timestampMask ( _add_months ( $current_timestamp , -6 ) , 'yyyy-mm-dd' ))#
and
#prompt('p_ToDate','Date',timestampMask ($current_timestamp, 'yyyy-mm-dd' ))#
)

doesn't work for you when you select values for FromDate and ToDate?

Create a new query with the data item:
filter([Date Level], [Date Attrib] between '2013-01-01' and '2013-02-01')
Run in tabular format.

Are you getting the list of values expected? How about filter([Date Level], [Date Attrib] between 2013-01-01 and 2013-02-01) ?


Is this on SQL Server?

Also, instead of suppressing, try filtering your month level with filter(month,measure is not null) or something similar.

[eliza_jane]

Hi Paul, the slicer works and the drill works except when I drill and then choose a different set of dates than the ones covered in what is being displayed in the charts I dont get any data back.

Example, the first run gets me data for the last 6 months and then i drill up and see quarters for the current year but then when i choose dates belonging to prior year i dont see the quarters corresponding to prior year instead i get no data available.

This is against DMR and DQM.

[eliza_jane]

Hi Paul, The requirement just got more complex. We need to display a crosstab as well with similar requirement but in addition also display YTD, PYTD. Since we have the date slicer, YTD and PYTD gets back data sliced by those dates.

[CognosPaul]

YTD and PYTD are not complicated requirements and are fairly easy to accomplish. Let's get the primary goal working before moving on to them.


Next question, is this a pure DMR model? Mixing DMR and Relational styles can cause problems.

Open the following report in RS and adapt to your model. In my system this does exactly what you're asking for, but I have a proper OLAP cube.

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/8.0/" useStyleVersion="10" expressionLocale="en">
<modelPath>/content/folder[@name='Models']/package[@name='Cube Package']/model[@name='model']</modelPath>

<layouts>
<layout>
<reportPages>
<page name="Page1">
<style>
<defaultStyles>
<defaultStyle refStyle="pg"/>
</defaultStyles>
</style>
<pageBody>
<style>
<defaultStyles>
<defaultStyle refStyle="pb"/>
</defaultStyles>
</style>
<contents><selectDate parameter="test" multiSelect="false" range="true" required="true"/><promptButton type="reprompt">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton><crosstab horizontalPagination="true" name="Crosstab1" refQuery="Query1">
<crosstabCorner>
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="xm"/>
</defaultStyles>
</style>
</crosstabCorner>


<noDataHandler>
<contents>
<block>
<contents>
<textItem>
<dataSource>
<staticValue>No Data Available</staticValue>
</dataSource>
<style>
<CSS value="padding:10px 18px;"/>
</style>
</textItem>
</contents>
</block>
</contents>
</noDataHandler>
<style>
<CSS value="border-collapse:collapse"/>
<defaultStyles>
<defaultStyle refStyle="xt"/>
</defaultStyles>
</style>
<crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Sales" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Filtered Months" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows></crosstab></contents>
</pageBody>
</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2010-01-19T09:55:33.940Z" output="no"/></XMLAttributes><queries><query name="Query1"><source><model/></source><selection><dataItem name="Date" aggregate="none" rollupAggregate="none"><expression>filter([Cube].[Time Dim].[Time Hier].[Date],[Cube].[Time Dim].[Time Hier].[Date].[Date] in_range ?test?)</expression></dataItem><dataItem name="Sales"><expression>[Cube].[Measures].[Sales]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="9" output="no"/></XMLAttributes></dataItem><dataItemSetExpression name="Months"><expression>[Cube].[Time Dim].[Time Hier].[Month]</expression><dmDimension><DUN>[Cube].[Time Dim]</DUN><itemCaption>Date</itemCaption></dmDimension><dmHierarchy><HUN>[Cube].[Time Dim].[Time Hier]</HUN><itemCaption>Time Hier</itemCaption></dmHierarchy></dataItemSetExpression><dataItemSetExpression name="Filtered Months"><expression>filter([Months],[Sales]&gt;0)</expression><dmDimension><DUN>[Cube].[Time Dim]</DUN><itemCaption>Date</itemCaption></dmDimension><dmHierarchy><HUN>[Cube].[Time Dim].[Time Hier]</HUN><itemCaption>Time Hier</itemCaption></dmHierarchy></dataItemSetExpression></selection><slicer><slicerMemberSet>[Date]</slicerMemberSet></slicer><memberSetStructure><memberSet name="Months MS" refDataItem="Months"><memberSets><memberSet name="Filtered Months MS" refDataItem="Filtered Months"/></memberSets></memberSet></memberSetStructure></query></queries><drillBehavior drillUpDown="true" modelBasedDrillThru="true"><drillQueryBehavior refQuery="Query1"><drillDataItemBehavior refDataItem="Months"><drillUpBehavior><drillSimpleType type="replaceExpression"/></drillUpBehavior><drillDownBehavior><drillSimpleType type="replaceExpression"/></drillDownBehavior></drillDataItemBehavior><drillDataItemBehavior refDataItem="Filtered Months"><drillUpBehavior><drillSimpleType type="preserve"/></drillUpBehavior><drillDownBehavior><drillSimpleType type="preserve"/></drillDownBehavior></drillDataItemBehavior></drillQueryBehavior></drillBehavior></report>

[eliza_jane]

Hi Paul, My report is similar except I am using individual date prompts instead of the range dates.

I am wondering of this has something to do with DMR against DQM. We do not have cubes with dates in them so planning on building something quick to test if there is a difference in behaviour.

[eliza_jane]

Hi Paul, I know this thread is pretty old and even though this requirement is no longer relevant, I am stumped as to why this isn't working only for me. I even tried this against a cube but no luck. The report is straightforward and similar to the one you have posted.

I tried attaching screenshots to explain the scenario that is not working but am unable to upload any documents. So I have tried explaining the scenario below as detailed as possible. Hope this helps. Just wanted to know if this works for you.

In my report page I have 2 date prompts defaulted to say Jan 1, 2012 and Jan 1, 2013 and a crosstab. The report brings back month level members from Jan 2012 to Jan 2013 as expected. So the slicer is working.

I drill up on Jan 2012 and see Q1 2012 to Q4 2012 as expected.

Now I change my date prompts to Jan 1, 2013 and Mar 1, 2013. I expect to see Q1 2013 in the report. But that does not happen.

If I change my date prompts to Jan 1, 2012 and Mar 1, 2012 I see Q1 2012 as expected.

So basically if I select dates in the range being displayed in the report(Q1 2012 to Q4 2012) it works. If I select dates that fall outside the range being displayed in the report it does not work.

[CognosPaul]

Looking at this with fresh eyes, I might have an explanation.

When you drill up, you are explicitly replacing the node in the crosstab with the parent and it's immediate siblings. So drilling up from Jan 2012 returns the quarters of 2012. This is the default behaviour, but in this case let's try something else.

Go to Data -> Drill Behavior -> Advanced. Click on the correct data item, and change the drill up option to "Depth Based Expression". Run the report and tell me what happens.

Remind me, does your node have the filter function embedded inside it? If so, we'll probably need to play around with member sets.

[eliza_jane]

Hi Paul, Sorry for the delay in responding...The depth based expression does not work, again blank output when out of range.

And my node is the Months level with no filter or any other mdx expression since I need to display data at a higher level and the slicer is on the date prompts.

Seems like Cognos works with a particular data set for every drill action and the slicer takes effect only on this data set.

I am curious though. Shouldn't the query get re-executed when I select dates and hit the Finish button on the page? In this case if the report brought back Months(the starting level of display) with the sliced dates that would make sense. But this is weird where in the Finish button works on the previous data set and executes slicers on that data set. Your thoughts?

[CognosPaul]

By default, when you drill up Cognos explicitly sets the node to be

children(parent(parent([drill member])))

The depth based expression should have set the node to be the level.

How about adding each level to the query, and using drill behavior "Change Expression". Thay way you can specify the level to show.