Select curent month?

[Jeka]

Hello Guys,

please help out!

I'm creating a report, (in report studio with a relational model) need to have current month, prior month and YTD.
[FPNL].[Period].[ Period Description] has  values (Mar, May, Jun...etc) but also has some values that are not relevant to a period (ex. 'period73738' or 'not available' or 'period1-1-2000' )
I only need current month, prior month and YTD!

The package doesn't have Period hierarchy, Period is in the package as an  Query subject, which represents a table in the database.

I'm trying to create a query [FPNL].[Period].[ Period Description]->?Month?
but this gives me a prompt before running a report!
or
periodsToDate([FPNL].[Period].[ Period Description]
does the same thing!
what should i do in order to pull only current month, prior month and YTD?

Thank-you for your input!

Steve

[blom0344]

elements:

1. _add_months(current_date,-1) = [date one month back]

2. extract(month,[date one month back])

3. a range for the ytd, i.e. between to_date('1-jan-2008') and current_date

[Jeka]

Thank-you Bloom!

Can you please explain, I'm confused regarding elements!

Thank-you!

[blom0344]

A description field that holds random values that indicate a month (in an unknown number of formats) is not a candidate for selecting the proper time-elements. You need a regular field like a date for the proper definitions.
How can you expect proper results if the description fields holds values like 'not available' ?

[Jeka]

Bloom,

what if i have two fields with Start_date and Finish_date.
mm/dd/yyyy
Now i can bring the descreption that will tell me the name of the month.

What would i write in query that will bring me the current , prior and ytd?

Thank-you for your help!

[blom0344]

Maybe showing some of the data will make your issue a bit clearer.
Usually a certain date-field is used to determine in which bucket (say a month level) data should fall.
Hence, the examples I showed you to determine Current month , Previous month and YTD.

What are the start and end date? Is this from the fact or field that is associated with a calendertable?

[Jeka]

Hi Bloom ,

Little bit off Topic.

The model that I'm working with is Relational. From what i see there are no calculations done in FM.
If i have to calculate: prior month, current, TD. than also calculate variances between actual. Set filters to not pull unwanted data from the table, for some reasone the data in the table is not clean.

Would that be much easier to do in the FW than in Report Studio?

Thank-you!

[blom0344]

Your answers simply do not clarify what you are really after. I you can show a bit of the data you are working with , then it might be easier to come up with ideas.

If your data is  not ' clean' enough for reporting ,then Cognos is not the tool that will allow intensive cleansing. There are other tools for that.

However you mention   start_date / finish date. If these are true dates then they should qualify for setting up time-based calculations.

[Jeka]

Im after building a report :) that will be fast!

For all  the joins i will be  using SIDs.

The questions is: by building a report with calculations like: current, ytd and prior. than devinding totals by certnt entities.

is better to do it the report or in the model?

thank-you!

[blom0344]

Your using a relational model, so whatever you build will result in a SQL statement created by Cognos that will be recompiled at the server for the database where it will be executed.
Calculations and other business logic will - mostly - be translated to the source database as well.

So ,the greatest impact:

1. Fresh statistics on the source database
2. Efficient join- strategy (SID's would see to that)
3. Returning the smalles possible dataset to be processed by the cognos server.

If you define this in the model, then you can reuse it, but it will probably not be 'better' performance-wise..

[Jeka]

Hello Bloom,

So I builded a cross tab report, ([FPNL].[Period].[ Period Description]) that shows me: July, jun, May, etc.

SID are used for all the joins.

From what i understand I need to create three queries:
1. [FPNL].[Period].[ Period Description] that will show me only current month
2. [FPNL].[Period].[ Period Description] that will show me prior month
3. YTD
What should I do now in order to see  Current Month (July), Prior (June) and YTD.
thank-you for your help!

[blom0344]

For a continuous crosstab I would use 3 queries and use a fourth (union) to create the final set. Now base the crosstab on this union and you'll have a relatively simple crosstab.
Obviously the queries need to have the same output in number and datatype of dataitems. The query conditions (filters) should make sure that each subset fetches the proper data..

[Jeka]

I created filters for current year and month, [year] = extract(year, current_date), same for the month!

but now im stuck for prior and ytd!

Or filters is the wrong way of doing this?

Thank-you!

[blom0344]

Current month query {{FILTER1}}:

Code Select Expand


extract(year,[somedate]) = extract(year,current_date) and
extract(month,[somedate]) = extract(month,current_date)


Prior month query {{FILTER2}}:

Code Select Expand


[somedate] between _first_of_month(_add_month(current_date,-1)) and
_last_of_month(_add_month(current_date,-1))


YTD query {{FILTER3}}:

Code Select Expand


extract(year,[somedate]) = extract(year,current_date) and
[somedate] <= current_date


These are used in detail filters in the respective queries.

In SQL notation (the union construct):

Code Select Expand


SELECT 'CURRENT MONTH',.........................
FROM .................
WHERE  {{FILTER1}}
UNION
SELECT 'PRIOR MONTH',.........................
FROM .................
WHERE  {{FILTER2}}
UNION
SELECT 'YTD',.........................
FROM .................
WHERE  {{FILTER3}}