Issue adding new formula to Cross-Tab

[Former Member]

I'm not too bad creating formulas but I've never had the need to add them to a Cross-Tab report. I can see the new formulas in the Report Fields section when in the Formula Editor and Field Explorer, but not in the Report Fields section when creating or updating a Cross-Tab.

I've done everything I can think of. I'm guessing that it's because of some issue with CR 8.5, but I don't have a choice right now. I can't upgrade due to compatibility issues.

Any ideas?

[Former Member · Accepted solution]

Thanks for your reply.

I wasn't trying to get a count specifically, I was just using that code as an example. I need a column that is going to show the percentage of the grand total of tickets submitted. That formula would be something along the lines of (count this month/grand total submitted)*100, but I can't even get the count part of the formula to show as a formula field to choose from in the Cross-Tab format section because apparently the count function doesn't work in cross-tabs.

Any ideas?

[Former Member · Accepted solution]

Insert the summary field as count(field) in the crosstab and then again insert the same count(field) in the summary.

Now in the design select the second summary field>go to format field>Commaon Tab>Display String (X+2)>write the formula like

(currentfieldvalue/grand total) * 100

Regards,

Raghavendra

[Former Member]

That's sad.

I looked all over and couldn't find anything that looks like what you describe.

Thanks so much for your quick replies.

On the bright side we expect to update to CR XI before the end of the year.

If anyone else knows of a way to do this before then, I'd appreciate your advise.

[Former Member]

Great. That's exactly what I think I need to do.

I have one small problem.

I don't see that option in the Common Tab of the Format Editor.

There are many X+2 buttons.

They correspond to the following:

Suppress

Horizontal Alighnment

Keep Object Together

Close Border on Page Break

Can Grow

Tool Tip Text

Suppress if duplicated

I don't see "Display String" or anything like that.

I figure either it's me needing new glasses or the fact that I'm at CR 8.5.

[Former Member]

I'm not sure that you have this option in 8.5 or not but I can see it in Crystal XI

Regards,

Raghavendra

[Former Member]

So if I do a new formula of:

count(ticketID)

I can save the new formula but it doesn't show-up as an available formula in the cross-tab.

If I update it to read:

whilereadingrecords;

count(ticketID)

I get an error stating:

"Function can not be used because it needs to be evaluated later"

So I can't save the formula.

Any column in a cross-tab showing percentage information would involve the count function but apparently that function's just not available no matter what I do.

Any ideas how to get around this?

[Former Member]

If you want to add the Count(TicketID) in the crosstab then insert the field TicketID in summary field in crosstab expert and select the summary as count.

If you want to add this formula as a column you can directly add in crosstab. I have tested using formula count(field) and it is working fine. If you want to print this count for each group as a column then try this

count(,{gorup field})

Regards,

Raghavendra

[Former Member]

Thanks for your quick replies.

I did just find this post that I think has to do with my issue.

Post titled: Selecting Formulas for use with Graphs?

I'm starting with a simple cross-tab that looks at a database of tickets, counts how many submitted by month and displays 1 column of data with the month and total submitted that month. Then a grand total submitted at the bottom.

I wanted to add a formula that would be another column for each month showing the percent submitted for that month. ie) In 9/08, 10 new tickets were submitted out of a grand total of 200 tickets submitted to date which would mean that 5% of all tickets were submitted in September.

The row would look like:

9/08 10 5%

I start with an elementary formula:

count(ticketID)

It saves the formula but that new formula doesn't show as an available

In playing around, I do see that some formulas I create will show as available in the Cross-Tab format section. It seems to depend on what function commands I use.

Would anyone be able to assist?

Thanks.

[Former Member]

To make the formula work in crosstab you have to use

Whilereadingrecords;

YOUR FORMULA

That way it will be available in crosstab. I am not sure if it is available or not in your version but there is an option in CR 2008 which says "Calculated members" that adds another column to the crosstab.

Regards

Jehanzeb

[Former Member]

Can you paste the formula here? You cannot add formulas that doesn't contain any databse fields.

So I just want to check your formula once

Regards,

Raghavendra

[Former Member]

Barbara,

Could you please post your formula which you are trying to use and also what exactly is the problem you having?

Is it that you are using "Whileprintingrecords;" instead of using "Whilereadingrecords;" in your formula? I mean when you use printingrecords, it will not work.

Rest I can't say until I see the formula.

Regards

jehanzeb