Calculation in Crosstab

[yogeswari]

Hi,
I am attaching the screen shot.  Please look at this and anyone tell how to achieve this?

I have taken Go Datawarehouse(query) package. Taken "Sales and Marketing(query)-->Sales(query).

"Quantity" and "Unit Cost" are the 2 measures included in column.
"Quarter" is included below those measures.
Product Name is included in rows.

(In screen shot, I have filtered out the products to see results in a page).

For any information, kindly revert me.

Since i am fresher i am unable to achieve this :( :( :(, Please provide suggestions.


thanks,
Yogeswari.

[yogeswari]

Sorry,


missed out to attach the files.
sorry for inconveniences, if so..

[charon]

hi

you might want to try two different approaches
1) create two crosstables, and integrate the calculation logic into the rows. you have to format the crosstabs in order to achieve the layout/ simulation it would be one crosstab only though..

2) open the cells of the crosstab and place singletons...because you have only 4 calculations, it is not that big kind of a deal.
in each singleton insert the calculation of one measure/ quarter.

good luck :P
cheerz

[yogeswari]

thank you for your quick turnaround.

Since i am fresher to this(less than 1yr exp) i could not get your point. I am using cognos 10.1

How to open the cells of the crosstab.  Is there any property to change??  When i click on the cell getting 'This selection does not have default selection'(attaching the screen shot for your references)

Kindly help me i would consider you help as great.

thanks,
Yogeswari.

[charon]

Hi,

in the menu bar there is a little "lock". clickin on it will "open the lock", and by doing so you enable writing in individual crosstab cells.

as next step, create a query like "Q_Singletons" and create as many data items as calculations you need.
open the data item and put in the calculation logic (X*Y/Z e.g.).
Then, in Report Studio, drag a singleton object from the object tool pane into the opened cell of your crosstab. change the query of your singleton in the property pane to "Q_Singletons" and move the needed data item calculation in this singleton.
DO so for every one, and you should be good to go.
Good luck :P
cheerz charon

[yogeswari]

Thank you again for your immediate responses and detailed explaination..thanks again.

i am using CROSSTAB.

The issue which i am facing is, when i unlock, i am able to modify values of ENTIRE rows and columns.  But i need to modify specific rows and column intersection point.

But i need to modify the set of values under "subtotal" ROW for "unit cost" COLUMN (highlighed in green color in screen shot attached here.)

Moreover the calcualtion is specific for that particular row and column[Eg: (total(unitcost) for quarter)].  so i cant be applied for first set of column values(quantity).

I tried many ways, unable to get it :( :( :( :(

Please revert me, if my point is not clear.


thanks,
yogeswari.

[blom0344]

I hate to bring it to you  ;) , but you are not working with a spreadsheet.  In Excel you can reference an individual cell (cause a spreadsheet is exactly that: a collection of cells).  A report crosstab is dynamic, it sizes depending on the data to be displayed. [You can of course influence the dataset for certain values]

[charon]

I have a dream...one day, Cognos BI will become as "powerfull" as excel  ;D

[yogeswari]

Thanks for your comments.

Please provide What is the solution for my issues?? Is it able to resolve??  Or Is it drawback in cognos crosstab?

Please tell your suggestions as i am searching for a months more and still unable to resolve it :( :( :( :(.


thanks,
yogeswari.

[blom0344]

I have a dream...one day, Cognos BI will become as "powerfull" as excel  ;D

What a brain-whacking  nerve to post this on Cognoise  !!
It is not about empowerment though.  A spreadsheet  <>  BI reporting tool, even though Microsoft feels otherwise. The beauty of Cognos is partially about data consistency, whereas the spreadsheet allows for manual  manipulation, or in other words : The Hell of Excel   ;)

But no-one on Cognoise will keep you from going the Excel way; you'll have to learn a different set of skills.

[make note: additional entry for BI-basics Blog]


I used to explain this issue 10-15 years ago with Business Objects client reporting: "No you cannot change an individual cell in the report crosstab output; it's about data presentation, not about data-manipulation"

[Lynn]

Attached is a report spec based on Charon's idea of using multiple crosstabs to simulate what you are trying to achieve. Really an ugly approach, but something to consider.

You cannot, as Blom eloquently explained, manipulate individual intersections.

[yogeswari]

Thanks for your workaround.

I tried your approach and got worked but "YTD" column of "Unit Cost" displays wrong data.

Here, YTD displays summation of all quarters but NOT to the corresponding products. In other words YTD(Bear Edge) is shown as YTD(Auto Pilot).I am also attaching the screen shot for your references.

Please resolve this as i am unable to do this and revert me if i am not clear.

thanks,
yogeswari.

[yogeswari]

could anyone provide solutions for my issues??

Is is possible to achieve?? :( :( :( :( :(


thanks,
yogeswari.