Cross Tab limitations, how to over come

[AurobindoSaha]

I am using Crystal Report XIR2, on Windows XP, using Sybase Database as my source.

I use a commmand object to the write a query to pull records from database tables

I am using a cross tab in Crystal Report to find the count (expressed as percentage) of phone calls in every month for last 13 rolling months.

Please note that there are months when no phone calls are made and data is not present in the database for those months.

Requirement is to show all the call counts in 2 decimal places, for each of 13 rolling months. For months where no call has been made, 0.00% should be displayed.

I have 2 questions -

1. How do I show the months under cross tab column heads and 0.00% in cells under those column heads where no data is present in database.

2. Why the count summary in cross tab is not showing more than 1 deimal place even I specify that in the field format?

You answers will be really helpful.

Thanks
Aurobindo

 

[lbass]

1-The only way you will get the month to appear in an inserted crosstab is if there is a corresponding record, so you will have to use a left join FROM a table that contains all dates to the other tables, with no selection on the other tables (unless you build these into the from clauses within your command).
2-There is a method for creating decimals in a crosstab. Please see thread767-1487372. Note that you do not have to have the totals at the top for this to work as long as you don't have more than one row field.

-LB

 

[chelseatech]

A rolling 12 month report is a better approach than a crosstab. Use 12 formulas to calculate each month, and then a summary of values for each month.

There are examples of this in our free newsletter.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm