Wednesday, March 19, 2008

SSRS - SQL Server Reporting Services tutorials

Creating Module

Overall Tutorial:

Calculating Total on specific conditions CR.NET Crystal Reports

Creating Running Totals

The best method available to you to learn how to do this is to look over our Running Total Whitepaper. It gives lots of examples and explains in full detail the methods that you will need to use to create the running totals in your report. To get our running total whitepaper, please download it from our website at the following link:

Once there, please do a search and download the following documents:

With Crystal Reports you cannot create a summary field base on a formula with a summary field. What you will need to do is use-running totals. There are three formulas you are to create. One is to reset the variable you are going to create to contain the total of your running total. One is to do the actual calculation and the last one is to display the running total.

The Reset Formula:

The following formula is to be place in your group header so it will reset the variable on the change of each new group. X is the name of the variable
Numbervar X := 0

The Calculation Formula:

Place this formula in the detail section.

Numbervar X:=X +{fieldnametoaddup}

***For duplicate records the formula would look like this***

If onfirstrecord or previous({fieldname})<>{fieldname} then
Numbervar X:= X + 1

***For Conditionally Suppressed Records***

You will have to include the suppression condition in the formula for example:
Lets say you have record #51 suppressed then the formula should look like this:

If {fieldname} <> 51 then numbervar X:=X+1

This means the running total will only add one for every record as long as it is not 51.

The Display Formula

Place this formula in the group footer to display the result

Numbervar X;

Original Article at :

Tuesday, March 18, 2008

Select Case in CR.NET Crystal Reports

Numbervar numUnAllocatedAmount;

select {vwRptClaimPrint.ClaimStatus}

case 'PartAllocated':(numUnAllocatedAmount:={vwRptClaimPrint.TotalVouchedClaimAmount}- {vwRptClaimPrint.TotalAllocatedClaimAmount})

default: (numUnAllocatedAmount:= {vwRptClaimPrint.TotalVouchedClaimAmount} -{vwRptClaimPrint.TotalAllocatedClaimAmount})

If statement in CR.Net Crystal Reports

You are allowed to assign values to two variables in one formula, simply by separating the assignments with semicolons. This gets a little more confusing when you are within nested If-Then logic. The formula below shows you how to do four assignments if the condition is TRUE and a different four assignments if the condition is FALSE. This formula will increment each of the 4 variables with one amount if the order is under $1,000. The increment will be 10 times that amount if the order is over $1,000.
Three things to notice: 1) The set of assignments after THEN or ELSE are contained inside a pair parentheses. 2) Within each pair of parentheses the assignments are separated by semicolons. 3) The If - Then - Else steps themselves are NOT separated by semicolons.
The last line is not necessary, but it allows the formula to display the contents of all four variables at once on the report. Without this last line the formula would simply show the value of the last assignment. Remove the two comment marks (leaving the semicolon) for this line to activate.
Numbervar one;
Numbervar two;
Numbervar three;
Numbervar four;
if {Orders.Order Amount} < 1000
then (one := one + 1;
two := two + 2;
three:= three+ 3;
four := four + 4 )
else (one := one + 10;
two := two + 20;
three:= three+ 30;
four := four + 40 )
// ; Totext(one,0,'') + '-' + Totext(two,0,'') + '-' + Totext(three,0,'')+ '-' + Totext(four,0,'')

Wednesday, March 05, 2008

CR.NET Format field as percentage

To format a number as a percentage in CR.NET:

Right-click on the numeric field and select Format.

Go to the Number tab and click Customize.
  • Click the Currency Symbol tab.
  • Click Enable Currency Symbol.
  • Change the Currency Symbol to '%'
  • Change Position combobox to '-123%'
  • Click OK, OK to exit and save.

CR.NET Calculate Percentage

Create New Unboundfiled



( {Field} / Sum ({Field}) ) * 100

Tuesday, March 04, 2008

CR.NET Landscape paper orientation

Design tip of the sporadic time interval:
If you need to change the size or orientation of a Crystal Report, you can do this in the VS IDE by right-clicking your report and navigating Designer >> Printer Setup. On the first tab, you can choose the paper size and orientation. Hit OK to return to the IDE, and the report canvas will automatically resize to what you just specified.