How to make crosstab report in crystal report

[Former Member]

Hi all ,

I want to make a crosstab report in crystal report

my query is like this

select YearDate.year ,
(currentasset.currentaset / currentliability.currentliability  ) as CurrentRatio
,(currentasset.currentaset - Inventory.Inventoryaset / currentliability.currentliability ) as QuickRatio

from
(
select distinct YEAR(JDT1.RefDate) as year from JDT1
)YearDate left join
(
select YEAR(JDT1.RefDate) as year,sum(JDT1.Debit - JDT1.Credit ) as currentaset from JDT1,OACT 
where JDT1.Account = OACT.AcctCode 
and OACT.AcctCode = '11' 
group by YEAR(JDT1.RefDate) 
)currentasset on YearDate.year = currentasset.year left join
(
select YEAR(JDT1.RefDate) as year,sum(JDT1.Debit - JDT1.Credit ) as Inventoryaset from JDT1,OACT 
where JDT1.Account = OACT.AcctCode 
and OACT.AcctCode = '11.05' 
group by YEAR(JDT1.RefDate) 
)Inventory on YearDate.year = Inventory.year left join
(
select YEAR(JDT1.RefDate) as year,sum(JDT1.Debit - JDT1.Credit ) as currentliability from JDT1,OACT 
where JDT1.Account = OACT.AcctCode 
and OACT.AcctCode = '21' 
group by YEAR(JDT1.RefDate)
)currentliability on YearDate.year = currentliability.year  left join 


order by YearDate.year

So if i view the data in sql server it should be like this

Year   CurrentRatio   quickRatio
2006               501                 100
2007              600                  200
2008             700                   300
2009             900                   400

I want to make a crosstab with the column is year . the Year is dynamic according to the selection

So if i select the year from 2007 to 2009

It should be like this in report

2007                2008              2009
CurrentRatio                 600                  700                  900
QuickRatio                    200                   300                 400

How can i make it in Crystal report ?

I have use the template in crosstab crystal report, but there is not suitable.

Can i make manually. ?

Thanks in advance

Regards

KK

[Former Member]

Hi,

Please Open your .rpt file

Menu Bar > Insert > Cross Tab

and drag - drop on your report.

Now drag your column to Cross tab and check it.

Thanks

Kevin

[Former Member]

Hi kevin,

thanks for your respond . Actually, it's work.

But i have some issue again.

How can i make a grouping

So the grouping per Item

For example

                                                      2006                 2007                 2008
Group 1               Item A                5                        6                       7
                            Item B                7                        8                       9

Group 2              Item C                  9                       0                      0
                           Item D                 8                        8                      9

And another question, Is there always have row summary ? It seems that after the 2008 column, there is a total column, Can I remove this ?

Thanks in advance

regards

KK

Edited by: kah kian on Jun 2, 2011 4:17 PM