Displaying Year & Month together in Cross Tab like ..Oct-2012,NoV-2012......

[84gopi]

Hi,

I want to display both year & month deatils in below format in Cross tab report in cognos 10.

format: Oct-12, Jan-2012,Feb-2013..........like this in a single row ....

I was able to get Month name in the report like Apr,May etc. But when I am trying to concat with year with '-' , i am getting parsing error. I have used below exprn to combine year & month.

Cast( Business View.Aggr Month.Merchandising Month Name, varchar(3) ) + Business View.Aggr Merchandising Year.MERCHANDISING_YEAR

I am getting output as Apr2010, but i need to get ouput
Apr-2010,May-2010 like this........

PFA format fYI.

How can I do this.........please assist me ASAP.

Thanks
Gopi

[MFGF]

Hi,

You could perhaps do this using two separate items - one for Year, one for Month, then unlock the structure of your report and drop them into the same heading with a text item of ' ' between them?

Just a thought?

MF.

[84gopi]

Hi,

Sorry i didn't get your point...

I require both year & month name in a single row, even though i have year column separately in the report.
So in one row i should get like Oct-2012, Nov-2012......like that.......since I am already getting output as Apr2012 I need to know how can I get Apr-2012 like in the same row?

Thanks
Gopi

[MFGF]

Have you used the padlock button on the toolbar before? This will lock/unlock the structure of the report. When the structure is unlocked, you have the ability to drag items into existing containers in your report. So if you already have, for example, a cell with just the Month in it, you can unlock the structure, drag a text item into the cell and drop it before the Month data item (and enter a '-' as the text), then drag your Year item into the same cell and drop it before the text item. You end up with a single cell that contains <year>-<month> without having to grapple with a calculation to concatenate them.

If you do this, remember to re-lock your structure afterwards to re-establish normal drag-and-drop behaviour!

MF.

[pricter]

Based on Go Sales Sample I tried the following and it worked

Code Select Expand

cast([Month], varchar(3)) + ' - ' + cast([Year], varchar(4))

You may need a "sort" data item to combine the year month like 201201 in order the
sorting to be in chronological order and not in alphabetical one.

[84gopi]

Hi,

Thanks for the info........I have tried with the code given by you with below expn

Cast([[Business View].[Aggr Month].[Merchandising Month Name]], varchar(3)) + ' - ' + Cast([[Business View].[Aggr Month].[Merchandising Year]], varchar(4))

I am getting parsing error, PFA for the same.

Thanks
Gopi

[wyconian]

Hi

You seem to have a strange number of []

Have you tried using the concatsep function?  Try something like concatsep('-',[Business View].[Aggr Month].[Merchandising Month Name],[Business View].[Aggr Month].[Merchandising Year])

Are you using a relational source or a cube?  If you have a relational source it might be worth adding this calculation into the framework model which gives you more flexibility on functions you can use, and makes it easier for users to reuse the calculation.  If you're uisng a cube you could add this as the description or long name property of the attribute and then drag it on to the report.

Good luck

[pricter]

You get an expn error since you put extra bucket

Cast([[Business View].[Aggr Month].[Merchandising Month Name]], varchar(3)) + ' - ' + Cast([[Business View].[Aggr Month].[Merchandising Year]], varchar(4))

The bold brackets causing the eprn error

Try this

Cast([Business View].[Aggr Month].[Merchandising Month Name], varchar(3)) + ' - ' + Cast([Business View].[Aggr Month].[Merchandising Year], varchar(4))

Usually eprn error caused because you missed something in writing for example a missed parenthesis, an extra parenthesis etc

[84gopi]

Hi,

I have used you expn like below

Cast([Business View].[Aggr Month].[Merchandising Month Name], varchar(3)) + ' - ' + Cast([Business View].[Aggr Month].[Merchandising Year], varchar(4))

I am getting output in a single row instead Apr - coming in 1 st row & year is coming under below row........

Apr -
2009 , PFA doc.

We are using data from package only not from cube.......

Thanks
Gopi

[pricter]

You can specify the weight of the cell in order your text it is not wrapped

[Rahul Ganguli]

Select the Crosstab cell. In properties you will find "white space" set it to no-wrap

[84gopi]

Hi,

Thank you all for your help its working now.........

Thanks
Gopi