crosstab custom calculation

[mprof]

Hi,

i have a crosstab of fruits/vegetables :

                                                         
                  oranges                              apples                     tomatoes                               
Day1          100                                       30                          50                       
Day2          150                                       40                          60                               
..
Total           1000                                    350                         400             

I have a Total to summarize the  data until yesterday.
I want to add a row that calculates the average multiply the number of days left in the month.

please advise on how can i do so.

thanks

[BigChris]

The days left in the month can be calculated along the lines of:

_days_between(getdate(),_last_of_month(getdate()) - obviously it depends on your platform etc. You should then be able to multiply that by your average

[mprof]

thanks but the problem is not with the calculation itself.
i have a problem to add it to the crosstab.
I can only add the given functions (like total, average etc.) and not custom.

[MFGF]

Hi,

Go to the toolbox and drag in a query calculation below the existing rows in the crosstab. You can code the expression as whatever you need it to be.

Cheers!

MF.

[mprof]

I have tried this, but all i get is an empty line

[MFGF]

What expression did you use?

MF.

[mprof]

query calculation:
total([sql1].[orange])/3

[MFGF]

What is [Orange] - is it the default measure of the crosstab?

[mprof]

it's a query result.
aggregation of 'orange' by day

[MFGF]

Is there a default measure, or are "Oranges", "Apples" and "Tomatoes" all separate items?

If you have a default measure, what happens if you try an expression like total([your default measure] for [day item in your rows]) / 3

The other thing that occurred to me is that previously you indicated you have an item [sql1].[orange] - I'm hoping this doesn't indicate you have hard-coded SQL in the query? Can you clarify?

MF.

[mprof]

I do have sql query.
it's something i inherited and cannot change it now :/
and yes each item is sperated

[MFGF]

Having an SQL query will severely limit what you can do in the report. It means only locally processed items can be added unless you amend the SQL. I'd seriously consider replacing the SQL with a "normal" query if possible.

If each value in columns is a separate item, then things get quite complicated...

MF.

[mprof]

I see, thanks, but unfortunately I cannot change to *normal*  query.

[navissar]

Hey,
If (And for the life of me I cannot think of a fitting scenario) you cannot build the report on top of a package, then you'll have to amend the SQL - that is, add the calculation you need on the SQL level.
I would, however, recommend strongly that you find a way to create a framework package and build the report on top of that, for two reasons:
A. It's a crosstab. Cognos creates a small cube on server for Crosstabs, and it pulls the SQL results into it. It does this better when it can affect the SQL.
B. It will make for better performance and easier maintenance.