calculating CM, PM, YTD?? stuck with YTD

[Jeka]

Hi,
This is what i have done so far to get Current Month, and Prior Month. Working with cross tab, relational package.

2. Brought in [Begin Date] (FORMAT: YYYY-MM-DD 00:00:00) From a Period table
3. need to loose time so cast([Business View].[Period].[Begin Date],date) tried trunc([Business View].[Period].[Begin Date])...didnt work?
4. Created a filter [Begin Date]= ?p_month? or [Begin Date] = _add_months(?p_month?,-1)
5. Created 1st data item [CM] with :case when [Begin Date] = ?p_month?    then [Net Amount] else 0 end 
6. Created  2nd data tiem [PM] with: case when [Begin Date] =_add_months(?p_month?,-1)   then [Net Amount] else 0 end
This is where im stuck
8. Created 3rd data item [YTD] with: case when [Begin Date] between Not sure what i have to put here in order to have xxxx-01-01 and (?p_month?)then i need the sum of [Net Amount]for all the months??? else 0 end

Thank-you for your Help!
Nichole

[blom0344]

Code Select Expand


CASE
WHEN
EXTRACT(YEAR,BEGINDATE) = EXTRACT(YEAR,CURRENT_DATE)
AND BEGINDATE <= CURRENT_DATE
THEN NET_AMOUNT
ELSE 0
END
[CODE]

There is no point in trying to loose the time portion if that is 00:00:00[/code]

[Jeka]

Bloom thank you!

But I should have mentioned  that the user will have a prompt where he will choose for what period he wants to see the report. as an example: today is August 2008, user might want to see the report for August 2006.

So the report has to show August 2006   then July 2006 and YTD which will be 2006-01-01 to 2006-08-01 and see the total Net_AMOUNT  for YTD period

I wanted to loose 00:00:00 so the user wont see the time part!

Thank-you!

[blom0344]

Lose the time portion by setting the format of the date in the prompt as : short or long (play with all the options)

first day of year based on some input:

_make_timestamp(extract(year,[somedate]),1,1)   , name this   [Start]

The yeardiff would be calculated as : extract(year,current_date)-extract(year,somedate)   [Yeardiff]

The current date equivalent of  26-8-2008 in 2006 would then be:

_add_years(current_date,-1*[Yeardiff])    =  26-8-2006

With these elements you should be able to work out a construction..

[Jeka]

Hi Bloom,

I think (cant test it I have no data, etl team is reloading it )If i just create _make_timestamp(extract(year,[somedate]),1,1)   , name this   [Start]
and
and then for YTD i will create a data item with (#4 from my first post)

case when [Begin Date] between [start] and ?p_month? then [Net Amount] else 0 end

the only question is I need to have [Net Amount] be summed from [start] and ?p_month?.......??

Thank-you!

[blom0344]

case when [Begin Date] between [start] and ?p_month? then [Net Amount] else 0 end

What is wrong with this expression? Obviously ?p_month? has to be a date as well, but this is the proper way to use for time/date buckets.

You DO need to set the aggregate behavior in the properties of the calculated item to ' total'  and then Cognos will automatically transfer this to the sum you need..

[In Cognos setting the aggregate behavior is done through the item properties and not in the hard-coded expression]

[Jeka]

Nothing is wrong, it auctualy helped me.
I created (Please tell me if Im wrong) using Relational package.....CrossTab

1. Created a filter [Begin Date]= ?p_month? or [Begin Date] = _add_months(?p_month?,-1)
2. Created 1st data item [CM] with :case when [Begin Date] = ?p_month?    then [Net Amount] else 0 end 
3. Created  2nd data tiem [PM] with: case when [Begin Date] =_add_months(?p_month?,-1)   then [Net Amount] else 0 end
4. created a 3rd data item with _make_timestamp(extract(year,[somedate]),1,1) named [Start]
5. created 4th data item with case when [Begin Date] between [start] and ?p_month? then [Net Amount] else 0 end
6. Now Im setting up 4th data item aggregate behavior to 'total'? I got QueryReuse: the generated sql is not supported locally.

[blom0344]

What is the version you are working with  8.1/8.2/8.3/8.4  ?
What database are you using as a source?

The KB gives no clue about this error message.

If you apply the aggregate behavior of total to the 1st or 2nd dataitem, does this yield a valid SQL?

[Jeka]

8.3
peoplesoft
if i do the same thing and i go to aggregate function and set it to 'Total'. i get the same error!

Should i just create 3 differed queries set filters  and then Union them  into 4th query?
One for CM
2nd for PM
3rd for YTD

Thank-you!

[blom0344]

Your design is sound!

Create a test report , like with one identifier and a single fact and use the CASE construct with total as aggregate on the fact.

Something else should be the matter with your installation settings

[Jeka]

Bloom,
If im building a crosstab report then all the calculated  data items wont work in my report?
ex:
Created a data item: case when [Begin Date] = ?p_month?    then [Net Amount] else 0 end
but  if i drop Account into   Rows then i wont get any data!

Is my only option is union?

[blom0344]

AFAIK Cognos crosstabs that work with standard measures in the body NEED an aggregate type defined, otherwise the report will run, but the crosstab will not show anything in the body..

[Jeka]

Hi Bloom,

So i tried to do this:
I created 3 different queries and then do a JOIN, I couldnt figure out how can i do all this in one query

Filters in each query
CM   [Calendar Begin Date] = ?Time?
and query also have  Net_Amount and Account so total 3 data items
PM [Calendar Begin Date]= _add_months(?Time?,-1)
and query also have  Net_Amount and Account so total 3 data items

YTD [Calendar Begin Date] between _make_timestamp(extract(year ?Time? ),1,1) and ?Time?and query also have  Net_Amount and Account so total 3 data items


Where I hit the wall is, when i do JOINS to the 4th query, how can i brake it down into CM, PM and YTD.
4th query only has Net_Amount and Account and [Calendar Begin Date]???

So it can be
                                    NetAMount   CM    PM     YTD
                                    Account        $$    $$      $$
Thank-you!

[blom0344]

You add a dataitem to all 3 queries and call it 'Period'

Now for the respective 3 queries use the following values for this dataitem:

'CM'
'PM'
'YTD'

Remember that you will use UNION constructs , not joins

Union 1 and 2  (results in Q3)

Union Q3 and 4 (results in final query Q5)

You need to do this in 2 steps, as the Cognos interface does not allow unioning 3 queries in one go.

The general idea is that you will end up with (simplified):

Code Select Expand



SELECT 'CM' ,Account,sum(Net_Amount)
from .............
where
[filtercondition for CM bucket]
Group by Account
union
SELECT 'PM' ,Account,sum(Net_Amount)
from .............
where
[filtercondition for PM bucket]
Group by Account
union
SELECT 'YTD' ,Account,sum(Net_Amount)
from .............
where
[filtercondition for YTD bucket]
Group by Account



The filter themselves need not be fetched as dataitems, they are only there to retrieve the proper bucket

[Jeka]

Thank-you Bloom!

Can you please explain little more:
I understand that i have to create a 4th data item  and call it Period.
I tried to figure it out but Im confused with....."Now for the respective 3 queries use the following values for this dataitem"
what am i putting in expression definition?

Im using 8.3 cognos and from what i see i think union between three queries might work!

Thank-you!

[blom0344]

You put in the expressions the literal strings as such (3x):

'CM'
'PM'
'YTD'

The whole point is that you need to have a dataitem to use as x-axis in the crosstab and it will show you 3 distinct values :  CM,PM,YTD

The expression assigned to a dataitem need not be a function, you can assign constant values without a problem

[Jeka]

Thank Bloom,
I was doing exactly the same thing, but it was not working. So i just started from scratch and everything worked.
Thank-you Bloom!

[blom0344]

Great, congratulations!

_{Hard to resist, but tell me why is it that people keep adding an  'o' to my name?}