Attempting a manual crosstab - Crystal XI 1

[ReillyC]

I'm creating a manual cross tab. I have grouped my report by a field called product. From that field(product) I created 3 GROUPS (digital, analog and others) using the group expert. In the select expert I placed the date range 1/1/2005 - 12/31/2005.

I was trying to create a formula to count the number of applications received in each group by quarter.
This is the formula I came up with
if Calendar1stQtr = {VMIS_TABDATE}then count ({VMIS_TABAPPNO})
The application number is alphanumeric.
I kept getting zeroes. What am I doing wrong?




31-MAR-2005 30-JUN-2005 30-SEP-2005 31-DEC-2005

DIGITAL 0 0 0 0

ANALOG 0 0 0 0

OTHERS 0 0 0 0

 

[lbass]

The formula should be like this:

if {VMIS_TABDATE} in Calendar1stQtr then 1

Place this in the detail section. Use similar formulas for your other quarters. Then insert a summary on each formula (Sum, NOT Count), and then suppress the detail section. Drag the groupname into the group footer.

-LB

 

[ReillyC]

Brilliant.It works wonderfully. Could I ask another question.

I would like to add the results of the 1st quarter to the 2nd quarter. This goes back to the running total issue.

Any help is much appreciated.

Cheers
R

 

[lbass]

Create a formula like:

sum({@Qtr1},{table.product}) + sum({@qtr2},{table.product})

Or you could use a formula like the following at the detail level:

{@Qtr1} + {@Qtr2}

And then insert a summary on this.

-LB

 

[ReillyC]

I used your same procedure but changed Calendar1stQtr to Calendar1stHalf (if {VMIS_tabdate} in Calendar1stHalf then 1 ) to account for the first 2 quarters.

But how can I easily add 3 quarters when I tried
if {VMIS_TABDATE} in (Calendar1stQtr,Calendar2ndQtr,Calendar3rdQtr )then 1
I get an error message.

Help
Thanks

 

[lbass]

You need to use brackets instead of parens:

if {VMIS_TABDATE} in [Calendar1stQtr,Calendar2ndQtr,Calendar3rdQtr] then 1

-LB

 

[ReillyC]

Got it. This is working great. Yet again you've help me tremendously.

Many thanks

 

[ReillyC]

Okay now I've made an error. The calendar function works great if I specify the year in the select expert. I now realize that I have to include the YTD data from 2004. Is there any way to slightly change the formula to indicate the year.

 

[lbass]

I believe the CalendarNthQtr functions only apply to the current year, so you could instead hard code the quarter ranges.

-LB

 

[TechiUser]

Hi ReillyC,

It may seem strange to you that someone is coming back on this thread. I am new to Crystal reports and need to build a report similar to your example here.

I am struggling to get the columns right on my report. In your example, you have 31-March-2005, 30-Jun-2005 etc. as column headers which (as per what I understood from the post) are probably coming from the dynamic data. How do you do that? My report looks like,

Oct-00 (group level1)
Oct-00 10
Nov-00 20
Dec-00 30
Nov-00 (group level1)
Nov-00 40
Dec-00 50
Dec-00 (group level1)
Dec-00 60

I need the output to be
Oct-00 Nov-00 Dec-00
Oct-00 10 20 30
Nov-00 40 50
Dec-00 60

I can't do this using cross tab as the values (10,20,30...) are actually arrived at by using customized formula not available in summarize option.

Can you give me some idea on how to get the dates on the row as columns?
Thanks

 

[lbass]

Please explain your report structure using field names, e.g., we cannot tell what field the dates under the month groups are coming from. Explain your report structure, how you are linking your tables, etc. Also show the contents of the formula used for your summary values.

-LB