Display YTD in Report

[Steve]

Hi Guys:

I have a report where I have to show YTD, QTD and MTD for Products along with images. So I created a list report. 1st of all, is there a better way of getting it instead of a list report?

If list report is Ok, how do I get to display the value of a measure in the YTD column. Current it displays like this:

Product       YTD              QTD                 MTD
--------------------------------------------------------

Computer  YTD                QTD                MTD          <-- Displays text

Instead of text, I need to display the values of the measure.

Thanks

[Blue]

What is your data source, relational or dimensional?  If the latter directly against the database or an OLAP data source?  If the former have you created a relative time dimension table?

[Steve]

Sorry, I should  have mentioned that earlier.

The data source is a cube and I have the YTD Grouped. QTD Grouped in the cube in the time dimension.

[Blue]

If you create a new hierarchy in the Time dimension and create the categories YTD, QTD, and MTD in that hierarchy then you can drag that hierarchy directly into the columns of the crosstab and you will get what you want.

Bluey

[MDXpressor]

A topic near and dear to my heart!Ã,  Read on with caution, I may Geek out on you...

I have attached a sample report (against the Great Outdoors Company package on C8 MR2).Ã,  Note the calculation to identify the Current Month.Ã,  This creates a single member from which to reference several slices of relative time.Ã,  This includes Prior Month (same month 1 year ago), Last 3 months, Same 3 months for last year, YTD, and Prior YTD.

The periodsToDate calc creates my YTD, but by changing the level it points at, this could easily have been a QTD, or MTD, or even WTD if you have sufficient levels to support it.

Relative Time Analysis is where the true strength of C8 shines...Giddy Up!

[nonooo]

I do not have the sample on my computer but it seems the "calculation" is a prompt, isn't it?

so I guess user have to enter the  current month... Do you know a way to get this member from the current date ?

I have made something in the same spirit but it is far from perfect as I am using the category code to get the last date..... here is the query (in bold this is the static member that I would have loved to get automatic)


<RSClipboardFragment version="2.0"><query name="G4_graph"><source><model/></source><selection><dataItem name="previous3years_niveaumonth" aggregate="none" rollupAggregate="none"><expression>descendants(tail(children([SAP_Sales].[All Times].[Life To Date]-&gt;:[PC].[@MEMBER].[529397]) ,3),2)</expression></dataItem><dataItem name="Sales_Euros" aggregate="none" rollupAggregate="none"><expression>[SAP_Sales].[Mesures].[Sales].[Sales_Euros]</expression></dataItem><dataItem name="Sales_Euros_MAT" aggregate="none" rollupAggregate="none"><expression>moving-total([SAP_Sales].[Mesures].[Sales].[Sales_Euros],13)</expression></dataItem><dataItem name="Sales_Euros_YTD" aggregate="none" rollupAggregate="none"><expression>running-total ([Sales_Euros] for [year])</expression></dataItem><dataItem name="Goal_Cust_Euros" rollupAggregate="none" aggregate="none"><expression>[SAP_Sales].[Mesures].[Goals].[Goal_Cust_Euros]</expression></dataItem><dataItem name="Goals_Cust_Euros_YTD" aggregate="none" rollupAggregate="none"><expression>running-total ([Goal_Cust_Euros] for [year])</expression></dataItem><dataItem name="categorycode" sort="ascending" aggregate="none" rollupAggregate="none"><expression>roleValue('_businessKey' , [previous3years_niveaumonth])</expression></dataItem><dataItem name="year" aggregate="none" rollupAggregate="none"><expression>substring (caption([previous3years_niveaumonth]),1,4)</expression></dataItem><dataItem name="13_mois" aggregate="none" rollupAggregate="none"><expression>tail ([previous3years_niveaumonth] ,13)</expression></dataItem></selection><dimensionInfo><dimensions><dimension name="previous3years_niveaumonth"><levels><level name="year"><keys><key name="year" refDataItem="year"/></keys><attributes/><calculatedMembers/></level><level name="previous3years_niveaumonth"><keys><key name="categorycode" refDataItem="categorycode"/></keys><attributes><attribute name="previous3years_niveaumonth" refDataItem="previous3years_niveaumonth"/></attributes><caption refDataItem="previous3years_niveaumonth"/><calculatedMembers/></level></levels><hierarchies><levelHierarchy name="Level Hierarchy1"><levelRef refLevel="year"/><levelRef refLevel="previous3years_niveaumonth"/></levelHierarchy>
                  </hierarchies></dimension></dimensions><factList><fact name="Sales_Euros" refDataItem="Sales_Euros"/><fact name="Sales_Euros_MAT" refDataItem="Sales_Euros_MAT"/><fact name="Sales_Euros_YTD" refDataItem="Sales_Euros_YTD"/><fact name="Goal_Cust_Euros" refDataItem="Goal_Cust_Euros"/><fact name="Goals_Cust_Euros_YTD" refDataItem="Goals_Cust_Euros_YTD"/></factList></dimensionInfo><detailFilters><detailFilter><filterExpression>[SAP_Sales].[All Sales Views].[All Sales Views].[SalesView].[SalesView - Nom complet] = 'Orders'</filterExpression></detailFilter></detailFilters></query></RSClipboardFragment>

[MDXpressor]

Normally, I prompt for the Current Period, but you could use a calc like that. 

Just be careful since the cube may pre-populate the members for months in a started Quarter.  For instance if we have data to populate [Year].[Quarter1].[Month1], you may find that [Month2] and [Month3] members of that Quarter are also available, even though they have no measures against them yet.  Therefore, using your calc, the member that would actually be returned is [Year].[Quarter1].[Month3], unless you add a filter() statement to you calc.

[nonooo]

I will check that when there will be more data in the cube... thanks for the advice

[bdybldr]

To get current month...

EXTRACT (MONTH FROM ({sysdate}))

[nonooo]

select ... from dual;

to be correct.



And the point of this topic was to do that against a cube datasource.