rolling back one year

system · July 6, 2004, 6:16pm

Hi All…
can we create a cross tabreport which shows rolling back one year… I mean if you select feb 04 it should look like this

            feb04    jan04  dec03  nov 03...............................mar03

prod1       4040     894     549     899    ............................... 3433

prod2       4040     894     549     899    ............................... 3433

prod3       4040     894     549     899    ............................... 3433

any inputs are really appreciated…
thanks in advance
phani

phani (BOB member since 2003-11-24)

system · July 6, 2004, 6:23pm

Create a predefined condition in universe

Date <= @Prompt('Select Date','D','LOVs',,,)
and 
Date > Add_Months(@Prompt('Select Date','D','LOVs',,,),-12)

This should solve your purpose.
You need to check the syntax for Add_Months function. But this should give the direction to follow.

[Used bbc CODE formatting for better readability - Andreas]

mkumar (BOB member since 2002-08-26)

system · July 6, 2004, 6:38pm

Hi kumar thanks for the reply… I have doubt…
should i create the second prompt or ur referencing the first prompt??
can you throw some light on the second prompt…
wot I understood is first condition gets all the dates lesser than the selected month. and i dint understand the second one… do we need to select values in the second prompt also… can you please explain the second one plz…
thanks and regards
phani
Create a predefined condition in universe

Date <= @Prompt(‘Select Date’,‘D’,‘LOVs’,)
and
Date > Add_Months(@Prompt(‘Select Date’,‘D’,‘LOVs’,),-12)

This should solve your purpose.
You need to check the syntax for Add_Months function. But this should give the direction to follow.

phani (BOB member since 2003-11-24)

system · July 6, 2004, 6:51pm

Both the conditions use the same prompt.
As the prompt name is same, it will appear only once but will be used twice in the Where clause.

First condition is to fetch data for all dates less than entered date.
Second condition is to fetch data Greater than (Entered date - 12 months).

So if you select ‘Feb 2004’ you will see data Greater Than Feb 2003 and <= Feb 2004.

Hope it makes you clear.

mkumar (BOB member since 2002-08-26)

system · July 6, 2004, 7:04pm

Hi Kumar…
thanks very much for the help… will try that
Iam using MS access looking for a similar function in access…
thanks and regards
venkat

phani (BOB member since 2003-11-24)

system · July 6, 2004, 9:13pm

Hi Kumar…
every thing is working fine but its getting multiple columns of the month…
like if i have 4 records in the month of feb its getting 4 columns in the cross tab…
i want to see all the records of the particular month in one column… do you have any idea how do i do that???
to be more clear i have four records in the month of february…
the out put is some thing like this… I want to see the feb recs in one column can you plz help me out with this…
thanks and regards
phani

 
               feb04    feb04  feb04  jan04    dec03    nov03...............mar03 

prod1           4040                    899      123      345...............3433 

prod2                   894    549      899      456      567............   3433 

prod3           4040           549      899    ..............................3433

phani (BOB member since 2003-11-24)

system · July 6, 2004, 9:14pm

What is the object you are displaying in the body of cross tab?
It should be a measure not a dimension.

mkumar (BOB member since 2002-08-26)

system · July 6, 2004, 9:15pm

yeah its the count of the product and is a measure

phani (BOB member since 2003-11-24)

system · July 6, 2004, 9:18pm

Two things.

In universe, the aggregate function for this object should not be NONE.
In the report, right click on cross tab and go to Format Crosstab.

In the General Tab, Avoid Duplicate Row Aggregation should not be checked.

See if this solves your problem.

mkumar (BOB member since 2002-08-26)

system · July 6, 2004, 11:26pm

Or to say this another way – your measure objects should usually USE an aggregate function, such as SUM. That is, in this case, the SELECT defined in Designer for the measure object should probably be defined as SUM(measure_column).

Anita Craig (BOB member since 2002-06-17)

system · July 7, 2004, 12:35pm

To emphasize:
I wish Business Objects would force Designers to use SQL Group by functions such as SUM, COUNT, MAX etc. whenever creating measures.

A measure without a SQL Group By function should not be a measure in the first place - IMHO.

Andreas (BOB member since 2002-06-20)

system · July 7, 2004, 5:09pm

Well, I don’t feel as strongly about it – I’ve seen a universe where it made sense to be able to set the projection to SUM, but to retrieve the rows unsummed. The individual user might want to see the individual rows on one report but not another, and the Reporter option to Format - Table - “Avoid Duplicate Rows Aggregation” was what was needed.

However, I would concede that it might be nice for a measure to default to SUM() in the SELECT, which the designer could choose to use or not.

Anita Craig (BOB member since 2002-06-17)

system · July 7, 2004, 8:16pm

Hi Kumar…
I dont see either of the options u were mentioning…

In universe, the aggregate function for this object should not be NONE.
I dont see this option in the porperties of the object
In the report, right click on cross tab and go to Format Crosstab.

In the General Tab, Avoid Duplicate Row Aggregation should not be checked.
I couldnt find this option also?? iam using 5.1.2

can you help me reg this??

phani (BOB member since 2003-11-24)

system · July 7, 2004, 8:26pm

In universe, Double click on Object
Go to Properties Tab.
In the Qualification Section, you will see a dropdown

Choose how this measure will be projected when aggregated

Function:                DROPDOWN BOX HERE

The function selected in that dropdown box should be SUM.

Just noticed that the option to “Avoid duplicate Row Aggregation” is available only for Tabular format not for crosstabs.

mkumar (BOB member since 2002-08-26)

system · July 7, 2004, 8:45pm

Hi Kumar…
I have tried the option you just mentioned…
but still i seen 3 feb columns but the values are being summed for every feb column…
by the way one more thing the months in the cross tab are records they are not individual columns…
-phani

phani (BOB member since 2003-11-24)

system · July 7, 2004, 8:50pm

If possible, Can you email the report to me?

mkumar (BOB member since 2002-08-26)

system · July 9, 2004, 12:31pm

This is one of the reasons I wish Business Objects Designer would force you to use SQL Group By statements whenever creating measures: Two Fact Tables

If the user for example wanted to see detailed transactions he/she could either pull in the Transaction ID or the designer could create a Dimension object instead of a Measure.

Andreas (BOB member since 2002-06-20)

system · July 9, 2004, 9:58pm

Again, I just don’t feel that strongly about it. I guess I’m more of a Libertarian!

Anita Craig (BOB member since 2002-06-17)