Calculated Time Periods using DMR Time Dimension

[ukflix]

Hello

I created a DMR Time Dimension and wanted to add some time period for things like current week, prior week etc...  To get Current Day I constructed a string for the MUN of the current day and used this in a parameter map.  This allowed me to create a Calculation like this (to get the member)

name     =  [myPackage].[Current_Day]
function  = #$Current_Day_MUN{'myIndex'}#

This works perfect, so I decided to create a Current Week calculation using Anscestor.

ancestor ( [myPackage].[Current_Day]   ,[myPackage].[Fiscal_Time].[Fiscal_Calendar].[Fiscal_Week]  )

When I test this I get the member of the current week. So far so good.

However, when I put the Current_Week calculation as a column I only see the value for the current day.  I know there are other values for other days within this week but I don't know why they are not being included.  Anyone else had a similar experience please?

Many thanks!

[CognosPaul]

What columns do you have in the output? Current Week and the Day level? If you have Current Week, and Current Day in the query I'd expect that single row.

[ukflix]

Hi CognosPaul and thanks for replying.

Yes for the columns I have [myPackage].[Current_Day] & [myPackage].[Current_Week] side by side.
I have a value in the measures and something like currency, customer or  product for the rows.

What I want is to show the value for the current week next to the value for today.  When I test the MUN in Framework Manager it gives me the member for the week (ie Week 42 2017) so I don't understand why I don't see the value for the week.  The MDX reads fine in theory (as if it were pointing to a SSAS).  Do you know what I can do to create the Current Time Periods (and last/prior etc) based purely on one MUN which has today please?

One thing I could do is just generate all of the MUNS in a Data Item and create a Parameter Map for each of them but I wanted to avoid doing this if possible.

Any help is appreciated, thanks for looking.

Regards

[CognosPaul]

Is this a crosstab?

So you have something like:


MEASURE  |  Current Week  | Current Day
---------+----------------+--------------
Products |    1234        |    1234


In that case I would expect to see both columns. Can you post the report XML?

[ukflix]

Hi CognosPaul

Yes indeed it is a crosstab layed out exactly like you say.  Here is the XML.
FYI if I create the Current Week Member calculation using a MUN defined in a data item, I can then create the report and see the correct values in each column.  However, going forward, I want to try and avoid this because I have about 50 of these time periods to create plus I also want to try and feed in a selected day so that the periods become dynamic and not just based on today.  Basically trying to mimic the functionality that I have in my OLAP cube but using a DMR time dimension.  The first step is to be able to feed in a current day and produce the other levels (weeks/months/quarters etc) from this.  I was trying using "ancestor".

Thanks for looking

Regards



<report xmlns="http://developer.cognos.com/schemas/report/12.0/" useStyleVersion="10" expressionLocale="en">
            <modelPath>/content/folder[@name='Packages']/folder[@name='Development']/package[@name='2 Views - Time Calculations']/model[@name='model']</modelPath>
            <drillBehavior modelBasedDrillThru="true"/>
            <queries>
               <query name="Query1">
                  <source>
                     <model/>
                  </source>
                  <selection><dataItem name="Amount_in_Local_Currency" aggregate="total"><expression>[presentation].[Fact AP].[Amount_in_Local_Currency]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="2" output="no"/><XMLAttribute name="RS_dataUsage" value="fact" output="no"/></XMLAttributes></dataItem><dataItem name="Current_Day_BOD" aggregate="none" rollupAggregate="none"><expression>[presentation].[Current_Day_BOD]</expression><XMLAttributes><XMLAttribute name="RS_dataUsage" value="unknown" output="no"/></XMLAttributes></dataItem><dataItem name="Current_Week_BOD" aggregate="none" rollupAggregate="none"><expression>[presentation].[Current_Week_BOD]</expression><XMLAttributes><XMLAttribute name="RS_dataUsage" value="unknown" output="no"/></XMLAttributes></dataItem><dataItem name="Currency_Code" aggregate="none" rollupAggregate="none"><expression>[presentation].[Currency].[Currency_Code]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="3" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem></selection>
               </query>
            </queries>
            <layouts>
               <layout>
                  <reportPages>
                     <page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>
                        <pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>
                           <contents>
                              <crosstab refQuery="Query1" horizontalPagination="true" name="Crosstab1">
                                 <crosstabCorner><style><defaultStyles><defaultStyle refStyle="xm"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Amount_in_Local_Currency"/></dataSource></textItem></contents></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>
                                    <defaultStyles>
                                       <defaultStyle refStyle="xt"/>
                                    </defaultStyles>
                                    <CSS value="border-collapse:collapse"/>
                                 </style>
                              <defaultMeasure refDataItem="Amount_in_Local_Currency"/><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Current_Week_BOD" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Current_Day_BOD" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Currency_Code" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows></crosstab>
                           </contents>
                        </pageBody>
                        <pageHeader>
                           <contents>
                              <block><style><defaultStyles><defaultStyle refStyle="ta"/></defaultStyles></style>
                                 <contents>
                                    <textItem><style><defaultStyles><defaultStyle refStyle="tt"/></defaultStyles></style>
                                       <dataSource>
                                          <staticValue/>
                                       </dataSource>
                                    </textItem>
                                 </contents>
                              </block>
                           </contents>
                           <style>
                              <defaultStyles>
                                 <defaultStyle refStyle="ph"/>
                              </defaultStyles>
                              <CSS value="padding-bottom:10px"/>
                           </style>
                        </pageHeader>
                        <pageFooter>
                           <contents>
                              <table>
                                 <tableRows>
                                    <tableRow>
                                       <tableCells>
                                          <tableCell>
                                             <contents>
                                                <date>
                                                   <style>
                                                      <dataFormat>
                                                         <dateFormat/>
                                                      </dataFormat>
                                                   </style>
                                                </date>
                                             </contents>
                                             <style>
                                                <CSS value="vertical-align:top;text-align:left;width:25%"/>
                                             </style>
                                          </tableCell>
                                          <tableCell>
                                             <contents>
                                                <pageNumber/>
                                             </contents>
                                             <style>
                                                <CSS value="vertical-align:top;text-align:center;width:50%"/>
                                             </style>
                                          </tableCell>
                                          <tableCell>
                                             <contents>
                                                <time>
                                                   <style>
                                                      <dataFormat>
                                                         <timeFormat/>
                                                      </dataFormat>
                                                   </style>
                                                </time>
                                             </contents>
                                             <style>
                                                <CSS value="vertical-align:top;text-align:right;width:25%"/>
                                             </style>
                                          </tableCell>
                                       </tableCells>
                                    </tableRow>
                                 </tableRows>
                                 <style>
                                    <defaultStyles>
                                       <defaultStyle refStyle="tb"/>
                                    </defaultStyles>
                                    <CSS value="border-collapse:collapse;width:100%"/>
                                 </style>
                              </table>
                           </contents>
                           <style>
                              <defaultStyles>
                                 <defaultStyle refStyle="pf"/>
                              </defaultStyles>
                              <CSS value="padding-top:10px"/>
                           </style>
                        </pageFooter>
                     </page>
                  </reportPages>
               </layout>
            </layouts>
         <XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2017-10-19T18:38:02.587Z" output="no"/></XMLAttributes></report>

[CognosPaul]

it certainly looks right.

What happens if you do parent([presentation].[Current_Day_BOD]) instead of [presentation].[Current_Week_BOD]? If you put in the mun directly into the data item?

[ukflix]

Hi CognosPaul

If I directly put the MUN inside the calculation I get the same behaviour.  Seems I cannot get the parent or ancestor of a MUN if there is no data for it.  For example

parent([Week 42 2017]) gives me the October value as there is a value for Week 42
parent([Week 43 2017]) this returns nothing even though the Month is still October because there is no data for Week 43

Regards

[CognosPaul]

Is there any form of suppression going on? Are you possibly mixing relational and DMR data items?

[ukflix]

The package is Relational but we created a DMR Time Dimension to give us a Fiscal Calendar.  We hoped to use the dimensional functions to build time calculations for Current, Prior etc...  for Days, Weeks, Months etc...
Reason is for year-on-year comparison reports where users do not have to change the date filters every week/month.

So the rows and measure are coming straight from the Star Schema, but the Columns are coming from the DMR Time Dimension.  The dimension is created from a table that is part of the main star schema.

[CognosPaul]

That is probably what is causing it. In general the best practice is to have a full DMR model, mixing and matching would generally return a warning.

Try using the override dimensional data. Drag in the data items and try that.