previous year calculation from multi-select prompts

[dsg1]

Hi,

I have a tree-prompt (displaying the years: 2001, 2002...) where the user can select multiple values from it. I have to calculate a sales field for the chosen years(in the prompt) and their correspording previous year sales.
The columns are like these:
[Product] [Sales] [Sales previous period]

Need suggestions.

Thanks.

[rockytopmark]

Prompts are used to set Parameters... 

From your parameter, you can create a filter;
[Namespace].[Qquery_Subject].[Query_Item] in ?Parameter?

To combine two columns...  [Sales] + [Prev_Year_Sales]

I'm not sure this is what kind of answers you aree looking for, but I think we need more information for a more exacting solution idea.

[dsg1]

The user needs to see sales for the chosen year and previous year. Suppose if he selects the year 2006(in the prompt), we need to display the sales of 2006 and the previous year 2005(in two separate columns).

The "Sales" and the "Previous Year Sales" are two different columns and if I put a static filter its going to filter other columns as well.

[MFGF]

Hi,

Your latest post suggests that maybe the users can only select a single year from the prompt.  Is this the case, or can they really select multiple years?  If they do select multiple years, what would you wish to show in the report - all the years they select individually, or just an aggregated summary value for the selected years?  What would you want to show in this instance for the previous year?

Let's assume you just want your users to select a single year.  Probably the most efficient way to do this in a crosstab is to add a query calculation to your report called [selected year] with the expression
[year level from your hierarchy] -> ?yr?
You could then get hold of the previous year by adding a second query calculation called [previous year] with the expression
lag([selected year],1)

If you are using a list report, you will need to do a little more work.  Add the above calculations to your query (not your list), and also bring your measure value into your query.
Add a query calculation into your list called [selected year measure] with the expression
tuple([selected year],[your measure])
then add a second calculation called [previous year measure] with the expression
tuple([previous year],[your measure])

Best regards,

MF.

[dsg1]

Hi,

The user is selecting multiple values(year) from the tree prompt.
Also when I am calculating the value for the previous year column using the function: lag([query item for the selected year],1) ,  the output is not showing any data

[MFGF]

Hi,

My assumption was that a single year was being selected.  If the user selects multiple years, what would you wish to show in the report - all the years they select individually, or just an aggregated summary value for the selected years?  What would you want to show in this instance for the previous year?

MF.

[dsg1]

The report is a list report. The user selects multiple years in the prompt.

What I need to show in the report is in one column 'Sales' for the selected years. In another column , just adjacent to the 'Sales' column; 'Sales for the previous year'(i.e. calculate the sales of the previous years).

[MFGF]

Hi,

OK - try the following:

Create a new list report, and add a query calculation called 'Year' with the following expression:
set([your time hierarchy]->?yr?)

Add a second query calculation called 'Sales' with this expression:
tuple(currentMember([your time hierarchy]),[your measure value])

Finally, add a third query calculation called 'Prev Year Sales' with the expression:
tuple(lag(currentMember([your time hierarchy]),1),[your measure value])

Does this give you what you need?

Regards,

MF.

[dsg1]

Hi

when I am taking the function lag, it is not fetching any result. Also I think the function tuple is used, when the user is selecting only one value from the prompt.

I have a question that, will these functions (eg. lag,tuple,...) be giving consistent results when the data is huge in the production environment?

[MFGF]

Hi,

If your lag function is not working, then there must be something else going on in your report that I don't know about.  The steps I provided work fine for me.  My report from the Great Outdoors Company package is included below.  The use of the currentMember function isolates each member during the iteration through the set, so the tuple function is exactly the right function to use with this.  These functions should always return consistent results from any OLAP based package.  Lag may not provide appropriate results with a DMR package unless explicit ordering of the members has been done.

Best regards,

MF.

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/2.0/" expressionLocale="en-gb"><!--RS:8.1-->
<modelPath>/content/package[@name='Great Outdoors Company']/model[@name='model']</modelPath>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItem name="Year"><expression>set([great_outdoors_company].[Years].[Years] -&gt; ?yr?)</expression></dataItem><dataItem name="Sales"><expression>tuple(currentMember([great_outdoors_company].[Years].[Years]),[great_outdoors_company].[Measures].[Revenue])
</expression></dataItem><dataItem name="Prev Year Sales"><expression>tuple(lag(currentMember([great_outdoors_company].[Years].[Years]),1),[great_outdoors_company].[Measures].[Revenue])</expression></dataItem></selection>
</query>
</queries>
<layouts>
<layout>
<reportPages>
<page class="pg" name="Page1">
<pageBody class="pb">
<contents>
<list class="ls" refQuery="Query1">



<style>
<CSS value="border-collapse:collapse"/>
</style>
<listColumns><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Year"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="Year"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Sales"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="Sales"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle class="lt"><contents><textItem><dataSource><dataItemLabel refDataItem="Prev Year Sales"/></dataSource></textItem></contents></listColumnTitle><listColumnBody class="lm"><contents><textItem><dataSource><dataItemValue refDataItem="Prev Year Sales"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns></list>
</contents>
</pageBody>
<pageHeader class="ph">
<contents>
<block class="ta">
<contents>
<textItem class="tt">
<dataSource>
<staticValue/>
</dataSource>
</textItem>
</contents>
</block>
</contents>
<style>
<CSS value="padding-bottom:10px"/>
</style>
</pageHeader>
<pageFooter class="pf">
<contents>
<table class="tb">
<tableRows>
<tableRow>
<tableCells>
<tableCell>
<contents>
<textItem>
<dataSource>
<reportExpression>AsOfDate()</reportExpression>
</dataSource>
</textItem>
</contents>
<style>
<CSS value="vertical-align:top;text-align:left;width:25%"/>
</style>
</tableCell>
<tableCell>
<contents>
<textItem>
<dataSource>
<staticValue>- </staticValue>
</dataSource>
</textItem>
<textItem>
<dataSource>
<reportExpression>PageNumber()</reportExpression>
</dataSource>
</textItem>
<textItem>
<dataSource>
<staticValue> -</staticValue>
</dataSource>
</textItem>
</contents>
<style>
<CSS value="vertical-align:top;text-align:center;width:50%"/>
</style>
</tableCell>
<tableCell>
<contents>
<textItem>
<dataSource>
<reportExpression>AsOfTime()</reportExpression>
</dataSource>
</textItem>
</contents>
<style>
<CSS value="vertical-align:top;text-align:right;width:25%"/>
</style>
</tableCell>
</tableCells>
</tableRow>
</tableRows>
<style>
<CSS value="border-collapse:collapse;width:100%"/>
</style>
</table>
</contents>
<style>
<CSS value="padding-top:10px"/>
</style>
</pageFooter>
</page>
</reportPages>
</layout>
</layouts>
</report>

[dsg1]

in your xml code, when u r pointing year to ?yr?, how the following expression comes: "->".

[MFGF]

Hi,

Because XML uses the > character to denote the end of a tag, the '>' in my expression is being represented as '>' in the XML.

The expression is

set([great_outdoors_company].[Years].[Years] -> ?yr?)

All you should need to do is to copy this XML to the clipboard, then in Report Studio go to Tools/Open Report from Clipboard.

Regards,

MF.