DMR model How to calculate next month

[Chandrasekar]

Hi Everyone,

I try to calculate next month in DMR model. The selected month query is "[SalesReporting].[Month].[Month]->?Month?".
Next month query is "NextMember(SalesReporting].[Month].[Month]->?Month?)".

But i get the result is same month from next month query. How to solve this?

Thanks & Regards,
Chandrasekar.P

[cognos810]

Hello Chandrasekar,
Can you expand the month level to look at the members in report studio and make sure you do not have repeating members. Also, in FM while defining the hierarchy, member sort is of utmost importance in case of a time dimension. Please make sure that the members are sorted correctly.

-Cognos810

[Chandrasekar]

Hi Cognos810,

Thanks for your reply. I don't have FM access from client side. The hierarchy level for full year,
              [SalesReporting].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[FY]
Next level is
               Quarter 1 -> January, February, March
               Quarter 2 -> April, May, June
               Quarter 3 -> July, August, September
               Quarter 4 -> October, November, December
You can get any idea from this?

Thanks & Regards,
Chandrasekar.P

[MFGF]

Hi Everyone,

I try to calculate next month in DMR model. The selected month query is "[SalesReporting].[Month].[Month]->?Month?".
Next month query is "NextMember(SalesReporting].[Month].[Month]->?Month?)".

But i get the result is same month from next month query. How to solve this?

Thanks & Regards,
Chandrasekar.P

When you refer to "month query" and "next month query", do you mean you have separate queries in your report for the months, or are they each query calculations in the same query?

Is this really DMR or is it a package based on a TM1 cube? You mentioned a DMR package, but the MUN you posted ia a TM1 MUN. Which are you using?

Here is an example of what (I think) you are trying to achieve - works fine for me!

MF.

<report xmlns="http://developer.cognos.com/schemas/report/11.0/" useStyleVersion="10" expressionLocale="en-gb">
            <modelPath>/content/folder[@name=&apos;Samples&apos;]/folder[@name=&apos;PowerPlay&apos;]/package[@name=&apos;great_outdoors_sales_en&apos;]/model[@name=&apos;2010-06-18T15:38:44.109Z&apos;]</modelPath>
            <drillBehavior/>
            <queries>
               <query name="Query1">
                  <source>
                     <model/>
                  </source>
                  <selection><dataItemLevelSet name="Product line"><dmLevel><LUN>[great_outdoors_sales_en].[Products].[Products].[Product line]</LUN><itemCaption>Product line</itemCaption></dmLevel><dmDimension><DUN>[great_outdoors_sales_en].[Products]</DUN><itemCaption>Products</itemCaption></dmDimension><dmHierarchy><HUN>[great_outdoors_sales_en].[Products].[Products]</HUN><itemCaption>Products</itemCaption></dmHierarchy></dataItemLevelSet><dataItemMeasure name="Quantity sold"><dmMember><MUN>[great_outdoors_sales_en].[Measures].[Quantity sold]</MUN><itemCaption>Quantity sold</itemCaption></dmMember><dmDimension><DUN>[great_outdoors_sales_en].[Measures]</DUN><itemCaption>Measures</itemCaption></dmDimension><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="9"/></XMLAttributes></dataItemMeasure><dataItem name="Month"><expression>[great_outdoors_sales_en].[Years].[Years].[Month] -&gt; ?Month?</expression></dataItem><dataItem name="Next Month"><expression>NextMember([great_outdoors_sales_en].[Years].[Years].[Month] -&gt; ?Month?)</expression></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="Quantity sold"/></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>
                              <crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Product line" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><defaultMeasure refDataItem="Quantity sold"/><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Month" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="cl"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Month"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="cv"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Next Month" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="cl"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Next Month"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="cv"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns></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 output="no" name="RS_CreateExtendedDataItems" value="true"/><XMLAttribute output="no" name="listSeparator" value=","/><XMLAttribute output="no" name="RS_modelModificationTime" value="2010-06-18T19:38:44.127Z"/></XMLAttributes></report>

[Chandrasekar]

Hi MFGF,

Sorry for miss understanding, This package is TM1, I am using same query for both month queries.

The scenario is Crosstab have Month, YTD, YTG from actual, YTG and Full Year columns. If month selection is January means YTG take month is February, Other columns take January.

nextMember function worked in DMR model. Once again sorry.

Thanks & Regards,
Chandrasekar.P

[MFGF]

Hi MFGF,

Sorry for miss understanding, This package is TM1, I am using same query for both month queries.

The scenario is Crosstab have Month, YTD, YTG from actual, YTG and Full Year columns. If month selection is January means YTG take month is February, Other columns take January.

nextMember function worked in DMR model. Once again sorry.

Thanks & Regards,
Chandrasekar.P

Hi - so it's TM1? Ok.

If your Month column is a query calculation with the expression [SalesReporting].[Month].[Month]->?Month? it obviously results in a Month prompt. If a month is selected, then adding another calculation with the expression NextMember([SalesReporting].[Month].[Month]->?Month?) should return the following month from your month level. Are you saying this doesn't work?

Cheers!

MF.

[Chandrasekar]

Hi MFGF,

Create new report page for testing purpose, that have selected Month & YTG month(Next month) data item in list. If i select a January it specifies empty page(like no data found). So i guessed the query will not work. After a long time i moved to(page down) next report page. It works in cross-tab.

What is the issue / What actually done here? Please clear me.

Thanks & Regards,
Chandrasekar.P

[MFGF]

Hi MFGF,

Create new report page for testing purpose, that have selected Month & YTG month(Next month) data item in list. If i select a January it specifies empty page(like no data found). So i guessed the query will not work. After a long time i moved to(page down) next report page. It works in cross-tab.

What is the issue / What actually done here? Please clear me.

Thanks & Regards,
Chandrasekar.P

A list won't work, because under the covers it uses the RelationalQueryProvider, and then it's the equivalent of having a filter that ANDs the two months - no single row contains both months, so you end up seeing nothing. Avoid lists on dimensional sources (unless they are REALLY simple).

A crosstab is the way to go, and should give you the results you need.

MF.

[Chandrasekar]

Hi MFGF,

Thanks a lot. Now i am clear.

Regards,
Chandrasekar.P