Crystal Report Cross Tab Custom Total

[Former Member]

Scenario is,

  I want to make cross tab crystal report like below image

I made cross tab report shown below but it showing obtain marks total only, I want proper percentage at end ((ObtainMarks/TotalMarks)*100)

help me to sort out pls. im new to corss tab. Its my 4th day on seraching about this topiccr

[Former Member · Accepted solution]

Hi Zaid,

Right click on the total column and select the option Edit Summary then select the radio button "show as percentage of" Total field

[Former Member · Accepted solution]

I want to show Percentage bye this formula

(ObtainMarks/TotalMarks)*100),

but not sum of total

[abhilash_kumar · Accepted solution]

Hi Zaid,

Create a formula with this code:

{ObtainMarks} % {TotalMarks}

Add this formula field as the second summary under the Crosstab.

Format it to display a % sign from the toolbar.

-Abhilash

[Former Member · Accepted solution]

why need to take modulas ?

[abhilash_kumar · Accepted solution]

That's not modulas!

It's a shorter way of writing:

(ObtainMarks/TotalMarks)*100)

in Crystal Reports.

-Abhilash

[Former Member · Accepted solution]

check i did as u said. but it shows 100 100 100

[abhilash_kumar · Accepted solution]

Here's what I get using the sample data you posted and the formula I suggested:

-Abhilash

[Former Member · Accepted solution]

im using this formula, im getting number error without using tonumber()

[abhilash_kumar · Accepted solution]

Could you send me a copy of this .rpt file 'with saved data' to my e-mail?

-Abhilash

[Former Member · Accepted solution]

im making crystal report in visual studio 2013

[Former Member · Accepted solution]

I have done, but now it showing invidual percentages and suming the percentage on total field, but i want to show percentage on total field, {totalObtainMakrs/TotalTotalmarks}*100 on total field..

As you can see in my sample report image above

[abhilash_kumar · Accepted solution]

You'd need a calculated member for the Total column.

Please re-post that as a new discussion per SCN's rules.

-Abhilash

[Former Member · Accepted solution]

my this post is for that. check my sample  image in postm last column show total percentage

[abhilash_kumar · Accepted solution]

OK. See if this works:

1) Right-click the Total Column Header > Calculated Member > Insert Child. A blank column with zero values is added at the start

2) Right-click the top left corner of the Crosstab > Advanced Calculations > Calculated Member > Select the radio button labeled 'After'. This moves the new blank column just before the Total column

3) Right-click the 'Total' column header > Column Grand Totals > Suppress Grand Totals. You may also want to suppress the Row Grand Totals in a similar manner.

4) Go to the Crosstab Expert and remove the percent formula I suggested earlier from the Summarized fields. Instead, add the Total Marks field as the second summary field

5) Right-click the blank header cell in the new column > Calculated Member > Edit Header formula > Type in:

"Percentage"

6) Right-click the first zero value in this column > Calculated Member > Edit Calculation formula > Add this code:

gettotalvaluefor("Subject",0) % gettotalvaluefor("Subject",1)

Notice how the database field 'Subject' is enclosed in double quotes

7) Highlight the second summary field in the Crosstab > Suppress. This is the Total Marks field and we don't need it for display

😎 Reduce the size of this summary field so that it barely takes some space.

If you follow all steps correctly, you should get this:

-Abhilash

[Former Member · Accepted solution]

I Cant see option for calculated member

[abhilash_kumar · Accepted solution]

That probably means CR for VS does not have this feature,

As an alternative, you can download a trial version of the CR Designer > build the report > open it in CR for VS.

-Abhilash

[Former Member · Accepted solution]

gimme the link

[abhilash_kumar · Accepted solution]

It's available for download here.

-Abhilash

[Former Member · Accepted solution]

I hope this will not create issue opening standalone reports in VS

[abhilash_kumar · Accepted solution]

I don't think it would.

cc:

-Abhilash

[Former Member · Accepted solution]

I have made DataSet In VS how I can use that Dataset in Designer now:( i have installed it

[Former Member · Accepted solution]

I insert a Cross Tab and right click on total field but not seen any option for calclated member. there embedded summary option

[abhilash_kumar · Accepted solution]

That's probably because you're clicking a summary cell.

If you read the instructions carefully again, this is what I mentioned:

"Right-click the Total Column Header"

-Abhilash

[former_member183750 · Accepted solution]

You cannot use a dataset in the designer. A dataset is a .NET construct for .NET SDKs. However you have several options:

1) Build a Dataset Provider DLL and use that to create your report. See Using The .NET DataSet Provider in Crystal Ente... | SCN

2) Create an XML from the dataset and use that to build your report. Then in your .NET app point the report at the dataset:

   crReportDocument.SetDataSource(dataSet)

For more information see re datasets:

http://www.emoreau.com/Entries/Articles/2006/09/Feeding-Crystal-Reports-from-your-application.aspx

Crystal Reports for Visual Studio 2005 Walkthro... | SCN

- Ludek

Senior Support Engineer Product Support, Global Support Center Canada

Follow me on Twitter

Got Enhancement ideas? Use the SAP Idea Place

[Former Member · Accepted solution]

I Open Crystal report standalone designer,for the first time, Made a cross tab report and select VS made Dataset (as ADO.Net(XML)) that resides  in Visual studio project directry. and then I added that report in visual studio, and runs the report, its look strange that it accepted the VS dataset and Showing the data

[Former Member · Accepted solution]

Yeah Right clicked on header but I didnot find that option.however when I right clicked on top left corner of CrossTab, I saw Option "Advanced Calculation" >"Calculated Member"

Made Calculated member here. Set the insertion formula,Edit The Header, I run The report it shows me the Percentage column at runtime,not at design time. but now Im facing error when I Edit Column Value Formula

.May Be your trying to guide me acorrding to different look and feel of the UI of different version thats why Some options are here and there with different headings.

[abhilash_kumar · Accepted solution]

The UI is same for all versions from CR 2008 and higher.

Step 1:Highlight the 'Total Column' Header:

Step 2: Right-click this cell > Select Calculated Member > Insert Child

Step 3: Follow further steps from my previous elaborate reply.

-Abhilash

[Former Member · Accepted solution]

look there is not option for that. but in Advanced Calculation I have That Option and I have created Percentage column from Advance Calculations but that is not being shown at design time but it is showing it when I run the report

[abhilash_kumar · Accepted solution]

You're still right-clicking at the wrong place!

This needs to be done when the records are being displayed in the 'Preview Window'.

-Abhilash

[Former Member · Accepted solution]

Lemme tell you whole story, Im using Webservices(WCF Rest) Services return data in form of List<CustomClass> . My Reports are for client side,(No Need of DB Connection at client side), Report DataSource is List. Report dont have any data at design time so my Preview Window not showing any data. its showing empty rectangle of Cross Tab

[Former Member · Accepted solution]

and Column will not constant, there may be more than two Subject columns at different times

[Former Member · Accepted solution]

tell me what to do now Prewview tab not showing temperary data

[abhilash_kumar · Accepted solution]

OK. Go to the 'Advanced Calculations' < Calculated Member window again.

Edit the 'Insertion Formula' and use this code:

GetColumnGroupIndexOf(CurrentColumnIndex) = 0

-Abhilash

[Former Member · Accepted solution]

Using This Code,Percentage column Vanished on runtime too.

[abhilash_kumar · Accepted solution]

OK. see if this brings it back:

Right-click the top left corner of the Crosstab > Advanced Calculations > Calculated Member > Select the radio button labeled 'Before'.

-Abhilash

[Former Member · Accepted solution]

No. I used InsertColumnIndex(CurrentIndex) before that code you gave, It was showing, but now it vanished. Preview tab showing problem due to Dummy Dataset (ADO.NET(XMl) ) ? as I made it in Visual studio and used it in Crystal report designer?

[abhilash_kumar · Accepted solution]

The code works fine for me though.

-Abhilash

[Former Member · Accepted solution]

Can you help me on TeamViewer?

[Former Member · Accepted solution]

I Tried One thing, It Showing Preview tab properly when I Make Local DB connection and I can See Insert Child Option. But I dont want DB Connection as my reports are for client side,Data will be returned from web service

[abhilash_kumar · Accepted solution]

What is the insertion formula you've used?

-Abhilash

[Former Member · Accepted solution]

Look I added that Columns using "Advance Calculation">"Calculated Member".

I used this inertion formula there. CurrentColumnIndex=(GetNumColumns-2).
It shows calculated Col on only runtime ,not in preview tab as i told

[abhilash_kumar · Accepted solution]

That's fine. You can use the 'Calculation Formula' as well.

-Abhilash

[abhilash_kumar · Accepted solution]

I mean use the calculation formula to display the percentages as described in my reply above.

-Abhilash

[Former Member · Accepted solution]

now Im facing this error in "Edit Column Value Formula", Using formula which you gave me in most above replies

[abhilash_kumar · Accepted solution]

The field 'Subject' was just an example I used in my datasource.

You'd need to replace that with the name of that field from your report's datasource.

And you'd need to enclose it in double-quotes without the curly braces.

-Abhilash

[Former Member · Accepted solution]

Formula window mention this "

//The group value is used to identify this calculated row/column.

// This formula must return a/an String value.

"

and What integers with column name in below formula ?

gettotalvaluefor("Subject",0) % gettotalvaluefor("Subject",1) // 0  and 1 is for what?

[abhilash_kumar · Accepted solution]

Where are you using this code?

This is supposed to be the 'Calculation Formula'.

Are you using this as the 'Insertion Formula'?

-Abhilash

[Former Member · Accepted solution]

Im using it in Edit Column Formula (Calculation Formula)

look, Formula saying in comments that Formula should return string

[abhilash_kumar · Accepted solution]

Is the datatype of the summary field being used 'text'?

What's the summary function?

-Abhilash

[Former Member · Accepted solution]

no its Int Type in DataSet. it doing sum correctly its mean its not a text

[Former Member · Accepted solution]

im coding this formula for  newly Calculated Member which is of text type???