How to calculate running total on diemensional data at report level?

[shrini]

Hi Frns,

   How can we calculate running total on diemensional data at report level as source from cube.
Pls let me know if any,we dont have the running total function for diemensional data.So if any possible ways pls let me know.Its urgent


Thanks,
Shrini. 

[CognosPaul]

The running functions are strictly relational. You can try the following though:

Code Select Expand

total([Measure] within set periodsToDate ([Cube].[Dates].[Years].[All],currentMember ([Cube].[Dates].[Years])))

Let's say your crosstab has months on the rows and the measure on the columns. Using this as the default measure should be exactly what you need. For each row it will generate a set of all of the months up to the current row with the periodsToDate function. It will then total the [Measure] for that set.

The referenced level in the periodsToDate, Year, defines where the months begin for that set.
An example:


Corner    |  Measure  |  Running  |
----------+-----------+-----------|
January   |     1     |      1    |
February  |     2     |      3    |
March     |     3     |      6    |
April     |     4     |     10    |
May       |     5     |     15    |
June      |     6     |     21    |


If you selected Quarters instead of years you'd get something like:

Corner    |  Measure  |  Running  |
----------+-----------+-----------|
January   |     1     |      1    |
February  |     2     |      3    |
March     |     3     |      6    |
April     |     4     |      4    |
May       |     5     |      9    |
June      |     6     |     15    |


The draw back is that this is not a true running. You can't select a range of members and expect it to only run against that range, it will always run against the level.

[shrini]

HI MFGF,

     I have worked around for the solution you gave me but its showing a parsing error.Let me tell you what i have done--

total([PP_ACC_RCV_AMNT] within setPeriods ToDate([YM],currentMember([YM])))

This is what i have placed in the expression but unable to get and got a parsing error- QE-DEF-0459 CCLException.

Can u please sort me out of this issue.

Thanks,
Shrini

[MFGF]

HI MFGF,

     I have worked around for the solution you gave me

Hi Shrini,

Whoa!!  Nope - not me.  :)  I take no credit for this elegant technique whatsoever.  It's our resident guru PaulM who posted this reply for you.

Looking at the expression you posted:

1. Can you check that [PP_ACC_RCV_AMNT] is a valid measure.
2. "within setPeriods ToDate()"  should be "within set periodsToDate()"
3. [YM] shoukd be a fully qualified level from your hierarchy - I would expect to see [Namespace].[Dimension].[Hierarchy].[Level] as the structure.

MF.

[shrini]

HI MFGF<

    Sorry for that mistake i jus thought u bcoz u used to respond to everyone so i thought you can tell me any ways to calculate running total at report level for DMR data as data from cube.


Thanks,
Shrini

[MFGF]

Hi Shrini,

Just took another look at your expression, and there is another probable issue - the second [YM] in your expression should be the fully qualified hierarchy name - [Namespace].[Dimension].[Hierarchy]

Check through these suggestions and correct the expression where necessary, and you should find Paul's solution works fine.

Good luck!

MF.

[shrini]

Hi MFGF,

      I have gone thru n implemented the syntax said by Paul.The prob is that its not showing running total when i have a set of values for an yearmonth(ex-199002) n if i have a single value for an yearmonth(199004) its showing me the running total.Let me give you a give you a clear pic of my report output:-

--------------------------------------------------------------------------
                    199002                                                199004

            PP  Rcv     Running Total                       PP  Rcv     Running Total
-------------------------------------------------------------------------
Prod    271485.00                                             30141.00    30141.00

Prod1  30141.00                                               30141.00    30141.00 

Prod2  30147.00

Prod3  30153.00

Prod4  30159.00

Prod5  30165.00

Prod6  30171.00

Prod7  30177.00

Prod8  30183.00

Prod9  30189.00

   And its summimg up all the values of the year months and displaying on the top PPRcv Column
The above way i am getting the output.So no running total for 199002

So pls consider my example and pls guide me out thru this issue


Hoping result from you asap.

Thanks,
Shrini

[CognosPaul]

Are you using the same level in the function as in the columns?

In your example if YM is the month level, you need to specify the year level. Remember, the running-total in this function will reset for each new member referenced in the level.

By using this function ...

Code Select Expand

total([PP_ACC_RCV_AMNT] within set periodsToDate([YM],currentMember(hierarchy([YM]))))

you would get this:

Month     | Month    |  Measure  |  Running  |
----------+----------+-----------+-----------|
January   |January   |     1     |      1    |
February  |February  |     2     |      2    |
March     |March     |     3     |      3    |
April     |April     |     4     |      4    |
May       |May       |     5     |      5    |
June      |June      |     6     |      6    |



because you're explicitly telling it to restart the numbering for each row in YM for the current row.

Try this instead:

Code Select Expand

total([PP_ACC_RCV_AMNT] within set periodsToDate(ancestor(currentMember([YM]),2),currentMember(hierarchy([YM]))))

[MFGF]

Lol.  Thanks Paul.  I just this second replied to a PM from Shrini asking the same question, but my suggestion was slightly different, albeit the same.  Just for fun (and for completeness of the thread, because PMs are not a good way of sharing ideas with the community), here was my stab:

total([PP_ACC_RCV_AMNT] within set periodsToDate([Your Namespace].[Your Time Dimension].[Your Time Hierarchy].[Your Year Level],currentMember([Your Namespace].[Your Time Dimension].[Your Time Hierarchy])))

I hadn't originally twigged that [YM] was the month level not the year level, but the lightbulb came on when I saw the results.

My belated effort is exactly the same concept as you suggest Paul, but using the Year level in the expression rather than using the ancestor() function, and using the hierarchy rather than using the hierarchy() function.  It just goes to show there's more than one way to end up with the same result :)

All credit to you for coming up with the idea in the first place, by the way - it's a very neat solution, sir!

MF.

[shrini]

HI Paul,

    I have jus gone thru ur expression syntax:-

total([PP_ACC_RCV_AMNT] within set periodsToDate(ancestor(currentMember([YM]),2),currentMember(hierarchy([YM]))))

Hierarchy[YM] and currentMember[YM] how should we specify them

In my report i have for

Name Space:-DM
Dimension- [MonthTime]
Hierarchy- [Month Time]
Level- [YM]

and  have framed it as -----

total([pp_acc] within set periodsToDate(ancestor(currentMember([DM].[Month Time].[Month Time].[YM]),2),currentMember([DM].[Month Time].[Month Time])))

So i f any changes let me know and i have error as invalid coercion from level to hierarchy

Thanks,
Shrini                           

[CognosPaul]

My mistake! get rid of the currentMember in the ancestor function. the currentMember only accepts hierarchies as a parameter.

[shrini]

Hi Paul,

  As u said i have done it but getting error,removing of currentMember from ancestor and my expression framed is below ---


total([PP_ACC_RCV_AMNT] within set periodsToDate(ancestor([DM12_SSS].[Month Time].[Month Time].[YM],2),currentMember([DM12_SSS].[Month Time].[Month Time])))

And i am getting an error --

QE-DEF-0459 CCLException
QE-DEF-478 Invalid coercion from 'level' to 'member'


Thanks,
Shrni

[MFGF]

Hi,

Just a quick sanity check - your time dimension does have a year level, doesn't it?

If so, instead of using the ancestor() function on [YM] to obtain the Year level, why not just drag in the Year level itself?

total([PP_ACC_RCV_AMNT] within set periodsToDate([DM12_SSS].[Month Time].[Month Time].[Your Year Level],currentMember([DM12_SSS].[Month Time].[Month Time])))

If you don't have a year level, can you tell us what the structure of the Month Time hierarchy is (ie what levels you have?)

MF.

[shrini]

Hi MFGF,

   why do u get errors like invalid coercion from 'level' to 'hierarchy'

Pls let me know frm yesterday i was jus sorting for getting rid of it for my expression

Thanks,
Shrini

[MFGF]

Hi,

This is telling you that you have used a dimensional level in your expression in a place where a dimensional hierarchy was expected to be found.  Typically you will get this error when using functions like currentMember(), which will only accept a hierarchy as the argument, but where you have placed a level as the argument.

A couple of questions, if I may (which may help us to understand your structures a little better and help you get this working).

1. What levels exist in the [Month Time] hierarchy if you expand it in the package tree on the left?
2. Is it correct thet the [Month Time] hierarchy exists within a dimension also called [Month Time]?

Thanks,

MF.

[shrini]

Hi MFGF,

MY Strucutre is like this --

Name Space:-DM
Dimension- [MonthTime]
Hierarchy- [Month Time]
Level- [YM]
         [Month Time]
         [FSCL_Y]
         TRM_CTGRY]
         [QTR_TRM_CTGRY]
          [YM]

this is my strucutre, so pls let me know how frame the expression.

Thanks,
Shrini
 

[MFGF]

Hmmm - I'm confused.  You seem to have the same level [YM] in your hierarchy twice - at the top and at the bottom.  This doesn't make sense - you can't have the same named level in two different places in the same hierarchy.  Could you double-check this?  Also, do you definitely have a second level called [Month Time]?

If I ignore the first two levels you included, it seems to make more sense:

FSCL_Y              your Fiscal Year level?
TRM_CTGRY        your Term Cagegory level?
QTR_TRM_CTGRY your Quarter Term Category level?
YM                    your Month level?

Is this your hierarchy structure, or are there really two more levels?

Thanks,

MF.

[LDJB81]

Hi

Old topic I know but any idea how I apply the same thing to a 12 month cycle that doesn't start in January?

Thanks
Lloyd

[CognosPaul]

The easiest way to handle this would be to create a fiscal calendar time dimension/hierarchy. That would make things significantly easier for other requests as well, such as End-Of-Period reporting. You could do a simple lastChild(year), instead of specifically selecting the correct month.

But, let's say that it's impossible for whatever reason and you're stuck with a standard hierarchy.

In the following, I'm assuming that your fiscal year is June to May.

To start, create your xtab with Months nested inside Years. Drag a measure into the columns.

Create the new calculation with the following expression:

Code Select Expand

total([Revenue] within set except(union(periodsToDate([sales_and_marketing].[Time].[Time].[Year],lag(currentMember([sales_and_marketing].[Time].[Time]),
if(count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])))>5) then (0) else (count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time]))))
)),

head(periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])),if(count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])))>5) then (0) else (count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])))))
)
,

head(descendants (ancestor(
lag(currentMember([sales_and_marketing].[Time].[Time]),if(count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])))>5) then (0) else (count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])))))
, [sales_and_marketing].[Time].[Time].[Year]
), [sales_and_marketing].[Time].[Time].[Month]),5)

))

It's a little bit more complex then the standard periodsToDate, but it gets the job done. Writing it was painful enough, explaining it would take a bit of time. Read through that and let me know if you have any questions.

In the meantime, here's the full XML:

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/9.0/" useStyleVersion="10" expressionLocale="en-us">
<modelPath>/content/folder[@name='Samples']/folder[@name='Cubes']/package[@name='Sales and Marketing (cube)']/model[@name='2008-07-25T15:28:38.072Z']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItemLevelSet name="Year"><dmLevel><LUN>[sales_and_marketing].[Time].[Time].[Year]</LUN><itemCaption>Year</itemCaption></dmLevel><dmDimension><DUN>[sales_and_marketing].[Time]</DUN><itemCaption>Time</itemCaption></dmDimension><dmHierarchy><HUN>[sales_and_marketing].[Time].[Time]</HUN><itemCaption>Time</itemCaption></dmHierarchy></dataItemLevelSet><dataItemLevelSet name="Month"><dmLevel><LUN>[sales_and_marketing].[Time].[Time].[Month]</LUN><itemCaption>Month</itemCaption></dmLevel><dmDimension><DUN>[sales_and_marketing].[Time]</DUN><itemCaption>Time</itemCaption></dmDimension><dmHierarchy><HUN>[sales_and_marketing].[Time].[Time]</HUN><itemCaption>Time</itemCaption></dmHierarchy></dataItemLevelSet><dataItemMeasure name="Revenue"><dmMember><MUN>[sales_and_marketing].[Measures].[Revenue]</MUN><itemCaption>Revenue</itemCaption></dmMember><dmDimension><DUN>[sales_and_marketing].[Measures]</DUN><itemCaption>Measures</itemCaption></dmDimension><XMLAttributes><XMLAttribute name="RS_dataType" value="9" output="no"/></XMLAttributes></dataItemMeasure><dataItem name="running"><expression>total([Revenue] within set except(union(periodsToDate([sales_and_marketing].[Time].[Time].[Year],lag(currentMember([sales_and_marketing].[Time].[Time]),
if(count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])))&gt;5) then (0) else (count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time]))))
)),

head(periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])),if(count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])))&gt;5) then (0) else (count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])))))
)
,

head(descendants (ancestor(
lag(currentMember([sales_and_marketing].[Time].[Time]),if(count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])))&gt;5) then (0) else (count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])))))
, [sales_and_marketing].[Time].[Time].[Year]
), [sales_and_marketing].[Time].[Time].[Month]),5)

))</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/></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><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Month" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Year" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Revenue" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="running" edgeLocation="e4"><style><defaultStyles><defaultStyle refStyle="cl"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="running"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="cv"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns></crosstab>
</contents>
</pageBody>


</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="false" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2008-07-25T15:28:38.133Z" output="no"/></XMLAttributes></report>

[MFGF]

The easiest way to handle this would be to create a fiscal calendar time dimension/hierarchy. That would make things significantly easier for other requests as well, such as End-Of-Period reporting. You could do a simple lastChild(year), instead of specifically selecting the correct month.

But, let's say that it's impossible for whatever reason and you're stuck with a standard hierarchy.

In the following, I'm assuming that your fiscal year is June to May.

To start, create your xtab with Months nested inside Years. Drag a measure into the columns.

Create the new calculation with the following expression:

Code Select Expand

total([Revenue] within set except(union(periodsToDate([sales_and_marketing].[Time].[Time].[Year],lag(currentMember([sales_and_marketing].[Time].[Time]),
if(count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])))>5) then (0) else (count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time]))))
)),

head(periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])),if(count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])))>5) then (0) else (count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])))))
)
,

head(descendants (ancestor(
lag(currentMember([sales_and_marketing].[Time].[Time]),if(count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])))>5) then (0) else (count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])))))
, [sales_and_marketing].[Time].[Time].[Year]
), [sales_and_marketing].[Time].[Time].[Month]),5)

))

It's a little bit more complex then the standard periodsToDate, but it gets the job done. Writing it was painful enough, explaining it would take a bit of time. Read through that and let me know if you have any questions.

In the meantime, here's the full XML:

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/9.0/" useStyleVersion="10" expressionLocale="en-us">
<modelPath>/content/folder[@name='Samples']/folder[@name='Cubes']/package[@name='Sales and Marketing (cube)']/model[@name='2008-07-25T15:28:38.072Z']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItemLevelSet name="Year"><dmLevel><LUN>[sales_and_marketing].[Time].[Time].[Year]</LUN><itemCaption>Year</itemCaption></dmLevel><dmDimension><DUN>[sales_and_marketing].[Time]</DUN><itemCaption>Time</itemCaption></dmDimension><dmHierarchy><HUN>[sales_and_marketing].[Time].[Time]</HUN><itemCaption>Time</itemCaption></dmHierarchy></dataItemLevelSet><dataItemLevelSet name="Month"><dmLevel><LUN>[sales_and_marketing].[Time].[Time].[Month]</LUN><itemCaption>Month</itemCaption></dmLevel><dmDimension><DUN>[sales_and_marketing].[Time]</DUN><itemCaption>Time</itemCaption></dmDimension><dmHierarchy><HUN>[sales_and_marketing].[Time].[Time]</HUN><itemCaption>Time</itemCaption></dmHierarchy></dataItemLevelSet><dataItemMeasure name="Revenue"><dmMember><MUN>[sales_and_marketing].[Measures].[Revenue]</MUN><itemCaption>Revenue</itemCaption></dmMember><dmDimension><DUN>[sales_and_marketing].[Measures]</DUN><itemCaption>Measures</itemCaption></dmDimension><XMLAttributes><XMLAttribute name="RS_dataType" value="9" output="no"/></XMLAttributes></dataItemMeasure><dataItem name="running"><expression>total([Revenue] within set except(union(periodsToDate([sales_and_marketing].[Time].[Time].[Year],lag(currentMember([sales_and_marketing].[Time].[Time]),
if(count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])))&gt;5) then (0) else (count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time]))))
)),

head(periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])),if(count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])))&gt;5) then (0) else (count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])))))
)
,

head(descendants (ancestor(
lag(currentMember([sales_and_marketing].[Time].[Time]),if(count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])))&gt;5) then (0) else (count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])))))
, [sales_and_marketing].[Time].[Time].[Year]
), [sales_and_marketing].[Time].[Time].[Month]),5)

))</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/></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><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Month" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Year" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Revenue" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="running" edgeLocation="e4"><style><defaultStyles><defaultStyle refStyle="cl"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="running"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="cv"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns></crosstab>
</contents>
</pageBody>


</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="false" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2008-07-25T15:28:38.133Z" output="no"/></XMLAttributes></report>

Wow! That's a very neat technique, Paul! Very impressive!!!

MF.

[rcamarda390]

The easiest way to handle this would be to create a fiscal calendar time dimension/hierarchy. That would make things significantly easier for other requests as well, such as End-Of-Period reporting. You could do a simple lastChild(year), instead of specifically selecting the correct month.

But, let's say that it's impossible for whatever reason and you're stuck with a standard hierarchy.

In the following, I'm assuming that your fiscal year is June to May.

To start, create your xtab with Months nested inside Years. Drag a measure into the columns.

Create the new calculation with the following expression:

Code Select Expand

total([Revenue] within set except(union(periodsToDate([sales_and_marketing].[Time].[Time].[Year],lag(currentMember([sales_and_marketing].[Time].[Time]),
if(count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])))>5) then (0) else (count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time]))))
)),

head(periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])),if(count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])))>5) then (0) else (count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])))))
)
,

head(descendants (ancestor(
lag(currentMember([sales_and_marketing].[Time].[Time]),if(count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])))>5) then (0) else (count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])))))
, [sales_and_marketing].[Time].[Time].[Year]
), [sales_and_marketing].[Time].[Time].[Month]),5)

))

It's a little bit more complex then the standard periodsToDate, but it gets the job done. Writing it was painful enough, explaining it would take a bit of time. Read through that and let me know if you have any questions.

In the meantime, here's the full XML:

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/9.0/" useStyleVersion="10" expressionLocale="en-us">
<modelPath>/content/folder[@name='Samples']/folder[@name='Cubes']/package[@name='Sales and Marketing (cube)']/model[@name='2008-07-25T15:28:38.072Z']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItemLevelSet name="Year"><dmLevel><LUN>[sales_and_marketing].[Time].[Time].[Year]</LUN><itemCaption>Year</itemCaption></dmLevel><dmDimension><DUN>[sales_and_marketing].[Time]</DUN><itemCaption>Time</itemCaption></dmDimension><dmHierarchy><HUN>[sales_and_marketing].[Time].[Time]</HUN><itemCaption>Time</itemCaption></dmHierarchy></dataItemLevelSet><dataItemLevelSet name="Month"><dmLevel><LUN>[sales_and_marketing].[Time].[Time].[Month]</LUN><itemCaption>Month</itemCaption></dmLevel><dmDimension><DUN>[sales_and_marketing].[Time]</DUN><itemCaption>Time</itemCaption></dmDimension><dmHierarchy><HUN>[sales_and_marketing].[Time].[Time]</HUN><itemCaption>Time</itemCaption></dmHierarchy></dataItemLevelSet><dataItemMeasure name="Revenue"><dmMember><MUN>[sales_and_marketing].[Measures].[Revenue]</MUN><itemCaption>Revenue</itemCaption></dmMember><dmDimension><DUN>[sales_and_marketing].[Measures]</DUN><itemCaption>Measures</itemCaption></dmDimension><XMLAttributes><XMLAttribute name="RS_dataType" value="9" output="no"/></XMLAttributes></dataItemMeasure><dataItem name="running"><expression>total([Revenue] within set except(union(periodsToDate([sales_and_marketing].[Time].[Time].[Year],lag(currentMember([sales_and_marketing].[Time].[Time]),
if(count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])))&gt;5) then (0) else (count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time]))))
)),

head(periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])),if(count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])))&gt;5) then (0) else (count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])))))
)
,

head(descendants (ancestor(
lag(currentMember([sales_and_marketing].[Time].[Time]),if(count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])))&gt;5) then (0) else (count(1 within set periodsToDate([sales_and_marketing].[Time].[Time].[Year],currentMember([sales_and_marketing].[Time].[Time])))))
, [sales_and_marketing].[Time].[Time].[Year]
), [sales_and_marketing].[Time].[Time].[Month]),5)

))</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/></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><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Month" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Year" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Revenue" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="running" edgeLocation="e4"><style><defaultStyles><defaultStyle refStyle="cl"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="running"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="cv"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns></crosstab>
</contents>
</pageBody>


</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="false" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2008-07-25T15:28:38.133Z" output="no"/></XMLAttributes></report>

Paul, This is brilliant and it work! however I need it just for one level, not Year and Month. An example would be just month.
I'm writing to get clairfication on the parts:
You want to total up the revenue but only on members above the 'current' member. IE dont include members that happen after the current member.
You created a set for exception, "set1" and "set2". "Set1" is the union of two sets: Q: union(periodsToDate()) 'set' is members before the current member, unioned to members after the current member??
Second set is the top X members from the current member?
Head( ,5) returns top 5 members before descendants of the ancestor of the prior 5 members (lag) from the currentmember?

How much simpler would this be if it would be just running total of last 12 months?

TIA
Rob

[Lyly]

Dear Paul!

I tried to do the same you described, but i still can't do  . :'(
Although test result is  successful   but The error in FM still  appears  when i validate measure :
BMT-MD-0003 GEN-ERR-0016 The 'currentMember' function is not supported in the context in which it is used.
GEN-ERR-0009 Data source type(s) '' - Function 'realDataMode' is not supported in 'NoDataModeProvider'.
GEN-ERR-0009 Data source type(s) 'OL' - Function 'currentMember' is not supported in 'RelationalQueryProvider'.
GEN-ERR-0009 Data source type(s) 'OL' - Function 'currentMember' is not supported in 'RelationalQueryProvider'.
GEN-ERR-0009 Data source type(s) '' - Function 'realDataMode' is not supported in 'NoDataModeProvider'.
GEN-ERR-0009 Data source type(s) 'OL' - Function 'currentMember' is not supported in 'RelationalQueryProvider'.
GEN-ERR-0011 The pattern 'providerQuery' is not supported in 'OlapQueryProvider'.
GEN-ERR-0009 Data source type(s) 'OL' - Function 'currentMember' is not supported in 'RelationalQueryProvider'.
GEN-ERR-0009 Data source type(s) '' - Function 'realDataMode' is not supported in 'NoDataModeProvider'.
GEN-ERR-0009 Data source type(s) 'OL' - Function 'currentMember' is not supported in 'RelationalQueryProvider'.
GEN-ERR-0011 The pattern 'providerQuery' is not supported in 'OlapQueryProvider'.
GEN-ERR-0009 Data source type(s) 'OL' - Function 'currentMember' is not supported in 'RelationalQueryProvider'.
GEN-ERR-0009 Data source type(s) '' - Function 'realDataMode' is not supported in 'NoDataModeProvider'.
GEN-ERR-0009 Data source type(s) 'OL' - Function 'currentMember' is not supported in 'RelationalQueryProvider'.
GEN-ERR-0011 The pattern 'providerQuery' is not supported in 'OlapQueryProvider'.
GEN-ERR-0009 Data source type(s) 'OL' - Function 'currentMember' is not supported in 'RelationalQueryProvider'.
GEN-ERR-0009 Data source type(s) '' - Function 'realDataMode' is not supported in 'NoDataModeProvider'.
GEN-ERR-0009 Data source type(s) 'OL' - Function 'currentMember' is not supported in 'RelationalQueryProvider'.
GEN-ERR-0011 The pattern 'providerQuery' is not supported in 'OlapQueryProvider'.

And my expression : total ([Dimension view].[Sales Invoice].[Quantity] within set periodsToDate ([Dimension view].[Time].[Year_Quarter].[Year(All)] , currentMember ([Dimension view].[Time].[Year_Quarter] )))
with :
[Dimension view].[Sales Invoice].[Quantity] is measure
[Dimension view].[Time].[Year_Quarter].[Year(All)]  is level ,
currentMember ([Dimension view].[Time].[Year_Quarter] is  hierachy
and  dimension Time has  many hierachy.
Thanks a lot!

[CognosPaul]

Hi Lyly,

Framework has a lot of trouble dealing with custom members like this, especially when validating. FM will automatically attempt to validate expressions in a relational context, which won't work at all here. What happens if you publish the package and try using your measure in a crosstab?

Hi Rob,

I'm sorry I missed your post. It looks like it was shortly after the IOD, and I was still going through my post vacation rush.

The issue presented was showing the a running total from the beginning of the fiscal year, which was in June, to the end in May. Since the time dimension wasn't set up to accommodate that type of query, cheating is necessary. The head function was effectively shifting the start and end months for the year.

A rolling total of 12 months would be easier, as long as the cube only contains a single year. total(currentMeasure within set lastPeriods(12, currentMember())) wouldn't reset itself at the start of each year.

[Lyly]

Dear Paul!
Thanks for responding.

I do not get any error when I use these DMR functions in Report Studio, the result displays exactly, But i calculated with  that measure in FM still has error.
I don't know method to fix error :'(

I read on web, he say that: '' Yes you can use the dimensional functions on a DMR package. No it does not matter which database you are using for your DMR package when using the dimensional functions.

When processing a dimensional query on a DMR package a series of operations will take place to process the query. Roughly, these are:
1. Evaluate the query for syntax
2. Translate the dimensional query into a Cognos MDX statement
3. Translate the MDX into SQL statements which can be processed by the database
4. Retrieve the data from the database
5. Perform any relational local processing required on the data at the Cognos server
6. Perform any dimensional functions/operations on the data at the Cognos server

What is happening in your case is that some operation in your query is triggering database-only relational processing of the data which would have to be applied after the dimensional operations. This is out of sequence so the dimensional operation inside the relational operation cannot be pushed down to the database. Exactly what the source of the problem is is not clear from just the expression you have provided. "

Any help will be highly appreciated.