Cross tab issue

[Former Member]

Hi experts,

I created a crosstab report with Material , , division , fiscal year.Quantity,and revenue.

I Created a report filter for fiscal year for the year 2008,2009,2010,2011,2012. 

For division i created the section.

the report look like below.

*********************************************

D26(division)

fiscal year     2009 qty    2009 reven    

Material    

A                      1                   10

B                       2                  50       

C                       3                  60

*******************************************

because there is no values for the 2008,2010,2011,2012.

But i need to show the year which also has blank means there is no sales on those years.

for this i tried with show/hide properties of the table but didn't work.

So i created another query with adding just Fiscal year object and with query filter for the years 2008-2012.

Now i drag the Fiscal year from query2. then i got the following report.

*********************************************

D26(division)

fiscal year     2009 qty  !  2009 reven  !   2008 qty  !  2008 reven ! 2010 qty !   2010 reven !2011 qty   ! 2011 reven ! 2012 qty  !  2012 reven

Material    

A                      1                   10

B                       2                  50       

C                       3                  60

*******************************************

Here the problem is 2008  should come first.which fiscal year has values its showing first. I tried the sort option but no use.

i'm expecting

*********************************************

D26(division)

fiscal year     2008 qty  !  2008 reven  !   2009 qty  !  2009 reven ! 2010 qty !   2010 reven !2011 qty   ! 2011 reven ! 2012 qty  !  2012 reven

Material    

A                                                              1                   10

B                                                               2                  50  

C                                                               3                  6

Regards,

Sree

[Former Member · Accepted solution]

Are 2008 qty, 2008 rev, 2009 qty, 2009 rev all measures?

If they are, then you just drag them to change the order.

Or do you have one dimension "year", and two measures "rev" and "qty"?

[Former Member · Accepted solution]

i  have one dimension only  "year", and two measures "rev" and "qty". for illustration purpose i written as 2008 qty ..like that.

Regards,

Sree Ranganath

[Former Member · Accepted solution]

Hi Sree,

Merge the Year Dimension, and try using the Merged dimension Object.

Thanks,

Kirthi

[Former Member · Accepted solution]

Hi kirthi,

I tried what you said, still its showing 2009 data first.if we use the Merged Object Custom Sort option will be in enable state.

i tried custom sort also , instead of 2008 data ,2009 data its showing first.

Regards,

Sree

[Former Member · Accepted solution]

Hi Sree,

Change Fiscal Year to Date Format and then Try Displaying the Year.

eg : if your Fiscal year is Jan 2012

FormatDate(Todate([Fiscal Year];"Mmm yyyy");"yyyy")

Thanks,

Kirthi

[Former Member · Accepted solution]

Hi kirthi,

its fiscal year so yyyy. and its numeric.

there is no month ..in my case.

Regards,

Sree

[Former Member · Accepted solution]

Sounds like you might have had another sort on the block and this is still being applied, or maybe a hidden dimension. Try re-creating the block, just fiscal year and material and one measure. Does that sort correctly? Drag in the second measure, still working?

[Former Member · Accepted solution]

Hello david,

The following image show the problem i'm facing,

there is query filter based on fiscal year = 2008,09,10,11&12

and also prompt in query filter based on Customer..

the report has section based on Division..

and cross tab based on Material , yFiscal Year and Total Qty Invoice base UOM , GC net value.

for the customer i selected there is only one Divison i.e Auto..

So, i create one more query by taking fiscal year  with the same filter as fiscal year = 2008,09,10,11&12.

In the report i taken the Fisal year for Quey 3.

But still the problem .. there is no sort used based on other dimension.

For me 2008 qty and netvalues has to come first.

Regards,

Sree

[Former Member]

Thanks to all,

I recreated the total report again , and the i drag the fiscal year from second query as i did earlier.

Thanks David as you said there may be hidden dimension or something.

Now its working fine.

Regards,

Sree

[former_member661332]

Hi Sree,

Please convert the fiscal year as Numeric and try the sorting. It should work fine. You can add a header row to display the qty and revenue.

Thanks

Suresh

[Former Member]

Fiscal Year is already Number.i try the sorting but not working.

Regards,

Sree

[former_member661332]

In your example, I can see the fiscal year is displaying with text (qty/reven). Please remove this and try.

Thanks

Suresh

[former_member661332]

In your example, I can see the fiscal year is displaying with text (qty/reven). Please remove this and try.

Thanks

Suresh

[Former Member]

I tried what you suggested before itself..but no use..

Thanks & Regards

Sree