Crystal Reports And Formula

Quick Start Crystal Reports And Formula

There are several different kinds of formulas in Crystal Reports: report, conditional formatting, selection, search formulas, and running total condition. The majority of formulas in a report are report formulas and conditional formatting formulas.

To Start Up With Basics Of  Formulas In Crystal Report

1)If You Are Very Unknown To Crystal Report Or Not Understanding What I am Talking About You Can View My Previous Blog Which Will Help You To Quick Start With Quick Start With Crystal Report

And If You Understand Keep Continue With This......

1)First Of All,Open Your Crystal Report File In Visual Studio,Now You Have A Field Explorer Like Below

2)Now Right click On Formula field ,And Click New,You Will get A Window Like Below One.

3)Now Type The Name You Want To Give Your Formula,(Which You'll Further Just Drag On Your report And It Will Show The Value Coming From The Output Of The Formula Execution).

Below Is The Screen You'll Get After Clicking Use Editor.Now Understand The Below Image And Apply Your Self Defined Formulas In Report

Below Are Sample Which You Can Use To Understand Formula Rules For Crystal Report.
Some Thing You Note Written Under {} are field of particular table or view.

Formula Field Is Easy To Use

1)

"FY " +  Totext(ToNumber({wsp_DSSRptPerfCrit;1.startyear})+ 1)

will give an date(eg.) FY 2,009.00

2)

"FY " +  Totext(ToNumber({wsp_DSSRptPerfCrit;1.startyear})+ 1,0)

 will give u the same date(abv eg.) FY 2,009

These above called basic syntax

But this one called crystal syntax

Sum Up A Column Could Be Done As

local numberVar a;

a:=a+ ToNumber({vw_PackingInvoicePart2Merge.totalpcs});

ToText(a);

To 'SUM' up you can also right click on report and go for this
insert>summary
choose your column name from summary window...and select calculate summary as SUM it will sum up that particular coloumn

Discount Could Be Done As
local numberVar a;


 local numberVar b;

a:=sum({VIEWINVOICEFRESH.multy});
b:=ToNumber({VIEWINVOICEFRESH.Discount})*a/100;

ToText(b);

Number To Word Could Be Done As

local numberVar a;

 local numberVar b;
 local numberVar c;
 local numberVar d;
 local numberVar e;
a:=sum({VIEWINVOICEFRESH.multy});
b:=ToNumber({VIEWINVOICEFRESH.Discount})*a/100;
c:=ToNumber({VIEWINVOICEFRESH.Fright});
d:=ToNumber({VIEWINVOICEFRESH.ET});
e:=a+c+d-b;
ToText(e);

ucase(ToWords(e,0)+" and "+{VIEWINVOICEFRESH.Small_Unit}  +" " + towords((Round(e,2)-int(e))*100,0)+" Only");

And What if there is a condition that write 25.00 as twenty five but 23.89 as twenty three Cents eighty nine only(Dynamically in Crystal report)??

In Such Case Use This:

if Round({@Total Amount},2) - Int({@Total Amount}) = 0 then
"Ringgit Malaysia: " + ToWords ({@Total Amount},0) + " only"
else
"Ringgit Malaysia: " + {@NumToWords1}  + " and " + "Cents " + ToWords ((Round({@Total Amount},2) - Int({@Total Amount})) * 100, 0) + " only"



Now The Things You Must Ask Me Are....
what is?
ucase()-To Write Words In UPPER CASE

towords()-use to understand an integer as word

ToText(e)-Convert Any Thing as String

ToText(e,0)-Remove decimal from end like 25.00 to 25

sum()-Sum Up Selected Column

(Round(e,2) -Use To Round Off Figure of Integer.

To Fit Size Of Detail Section 

print at bootom of page for group footer

fit section for detail section

Web Services Basic tutorial In Dot Net

Basic Tutorial Of Web Service

The Two Main Thing About Web Services Are Creating & Consuming!

Below Example Will Let you Know How To Create A Basic Web service.

And How To Consume Web services On server!




Web Service Basic Example Just In 10 Steps


1.Open Website template then select ASP.net Web Service Application

2)You'll see a method name hello world,now add two more method as below

3)Run the programme,you 'll see this kind of screen in your browser,with three method.

(Note :you can click and check any one that are they working properly by inserting any value and invoking it)

Now You Have Completed 50% Work i.e you've Created Your Web Service.

Which Have Few Methods.
But How Any One Can  Make Use Of/Consume Your Web Service On Server??

For That Don't Close The Above Window Of Web Service In Browser.

And Open A New Instance Of Visual Studio

4)Add service Refrence Like This

5)Click On Advance

6)Click Add Web Refrence

 7)Now Paste The WebServie URL In the URL Section
    and name webservice  testingvijayservices

 8)Now You Must Add Namespace Of The testingvijayservices like below

 9)Something Like This

 10)Type Below Code In It And Execute And See What Happen

You May Also Like To Know That

What are Web Services?

Web Services are the applications that run on a Web Server and communicate with other applications. It uses a series of protocols to respond to different requests. The protocols on which Web Services are built are summarized below:
UDDI: Stands for Universal Discovery and Description Integration. It's said to be the Yellow Pages of Web Services which allows Businesses to search for other Businesses allowing them to  search for the services it needs, know about the services and contact them.
WSDL: Stands for Web Services Description Language, often called as whiz-dull. WSDL is an XML document that describes a set of SOAP messages and how those messages are exchanged.
SOAP: Stands for Simple Object Access Protocol. It's the communication protocol for Web Services.
XML, HTTP and SMTP: Stands for Extensible Markup Language, Hyper Text Transfer Protocol and Simple Message Transfer Protocol respectively. UDDI, WSDL and SOAP rely on these protocols for communication. 

Definition of WPF is : Windows Presentation Foundation (WPF) is Microsoft's

development tool for Web applications and rich client applications.

With WPF, developers can use XAML, the Extensible Application Markup Language, to create custom controls, graphics,

3D images and animations that are not available in traditional HTML implementations.

Definition of WCF is : Windows Communication Foundation (WCF)

is Microsoft's programming model for using managed code to build unified Web services and other distributed systems that can talk to each other.

WCF focuses on connecting XML to programs that are built using development languages supported by Microsoft, such as VB.NET and C#.

To support this cross-language communication, WCF uses the extensible Simple Object Access Protocol (SOAP).

JQuery Basic Tutorial

Basic tutorial of JQuery

As The Logo Itself Suggest 'Write less do more'

jQuery is a JavaScript Library.

jQuery greatly simplifies JavaScript programming

So Proceeding Further One Should Have Clear Basics Of Javascript




A Quick Look To An Example Of JQUERY

<html>
<head>

<script type="text/javascript" src="jquery.js"></script>
<script type="text/javascript">
$(document).ready(function(){
  $(".flip").click(function(){
    $(".panel").slideDown("slow");
  });
});
</script>

<style type="text/css">
div.panel,p.flip
{
margin:0px;
padding:5px;
text-align:center;
background:#e5eecc;
border:solid 1px #c3c3c3;
}
div.panel
{
height:120px;
display:none;
}
</style>
</head>

<body>

<div class="panel">
<p>This Portion Visible Only When You Click Show Button.</p>
<p>Its a basic sample,if you understand it you can explore yourself with jquery in any style.</p>
</div>

<p class="flip">Show Panel</p>

</body>
</html>
 -------------------------------------------------------------------------------------------------------
You Often See Such Kind Of Code In JQuery Coding Part.If You Didn't Understand What they Actual Means Then You Browse Right Page To Find Out The Solution.

  

So Lets Start With Very Basic Things.

JQuery has many basic syntax as we use few in above example you should know what they mean!

1)Basic syntax is: $(selector).action()

• A dollar sign to define jQuery
• A (selector) to "query (or find)" HTML elements
• A jQuery action() to be performed on the element(s)

Examples:

$(this).hide() - hides current element

$("p").hide() - hides all paragraphs

$("p.test").hide() - hides all paragraphs with class="test"

$("#test").hide() - hides the element with id="test"

2)Now You Are Ready To Understand A Very Basic JQuery Implementation Programming.

Look At The Image Below And Take An Observation.

Selection With CheckBoxes In GridView

CheckBoxes Wise Selection In GridView

When You Require To do selectio of your data using checkboxes




Add This To HTML

<script language="javascript" type="text/javascript">

    function SelectAll(Id) {

        var myform = document.forms[0];

        var len = myform.elements.length;

        document.getElementById(Id).checked == true ? document.getElementById(Id).checked = false : document.getElementById(Id).checked = true;

        for (var i = 0; i < len; i++) {

            if (myform.elements[i].type == 'checkbox') {

                if (myform.elements[i].checked) {

                    myform.elements[i].checked = false;

                }

                else {

                    myform.elements[i].checked = true;

                 }

            }

        }

    }

</script>

And This To Your Server Side

  <asp:GridView runat="server" ID="GridView1" DataKeyNames="pck_id"

                             BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px"

                             CellPadding="1">

                             <FooterStyle BackColor="White" ForeColor="#000066" />

                             <RowStyle ForeColor="#000066" />

<Columns>

    <asp:TemplateField HeaderText="Select">

        <HeaderTemplate>

            <asp:CheckBox ID="chkSelectAll" runat="server" onclick="SelectAll(this.id)" />

        </HeaderTemplate>

        <ItemTemplate>

            <asp:CheckBox ID="chkSelect" runat="server" />

        </ItemTemplate>

    </asp:TemplateField>

    <asp:TemplateField HeaderText="PI temp">

        <ItemTemplate>

            <asp:Label ID="Label24" runat="server" Text='<%# Eval("pi_no") %>'></asp:Label>

            <asp:Label ID="LabelRUENO" runat="server" Text='<%# Eval("rue_no") %>'

                Visible="False"></asp:Label>

            <asp:Label ID="Labelcalid" runat="server" Text='<%# Eval("cal_id") %>'

                Visible="False"></asp:Label>

            <asp:Label ID="Labeltotalamt" runat="server" Text='<%# Eval("cal_id") %>'

                Visible="False"></asp:Label>

        </ItemTemplate>

    </asp:TemplateField>

    </Columns>

                             <PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />

                             <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />

                             <HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />

</asp:GridView>

Crystal Report Details Values Conflicts

Crystal Report Showing Wrong Values On Pressing Print Button?

or

Crystal Report Details Section Value Changes On Run Time When You Click Print Button?

A Major Hurdel Of Crytal Report Is Solved Now!

Did You Ever Notice The Values In Details Section Changes Automatically When You Press Any Button From The Crystal Report Toolbar On Top.

In Simple Words Its Just Because Crystal Report Saying That Please Learn The Life Cycle Of ASP.Net Page Again!

By Means Of This I Want To Say That Crystal Report Showing Confliction In Values Because Of Loading All Values On Page Load Every Time!

Basically you are addicted to love using thing like this..

Writing your reprt binding function in page load by making sure that page is not post back

 protected void Page_Load(object sender, EventArgs e)  
    {                 
           if (!Page.IsPostBack)
            { 

               bindingfunction();    
            }

    }

Why Don't You Try Put You Binding function In Page_Init Step of Page life cycle

protected void Page_Init(object sender, EventArgs e)  
    {                 
           if (!Page.IsPostBack)
            {

               bindingfunction();    
            }

    }

And You'll See Nothing Is Sorted Out Because You Are Still Calling your page.ispostback here

just remove it and see the problem is no more existed in your crystal page.

protected void Page_Init(object sender, EventArgs e)  
    {                 
          

              bindingfunction();    
           

    }

Ask Me If You Didn't Understand any thing  from above or you have further more question

case in SQL Server

 CASE in SQL Server

 a small examle to explain what we do with case in SQL

below is a condition where i need to show cratefrom & crate_to in such a way

so that they will look like this

1-15  (means cratefrom - crate_to)

but i also need to assure my self that if any where the cratefrom & crate_to are same then just show sigle one for eg cratefrom

below is code how you can do this!

SELECT [pck_id] ,[pi_no] ,[cal_id],[rue_id] ,[rue_no] ,[p_date] ,[product_type]
       ,[product_name],[size] ,[qty],[crateFrom],[crate_to],
    case when [crateFrom]=[crate_to] then [crate_to]
    else 
    ltrim(rtrim([crateFrom]))+'-'+ltrim(rtrim([crate_to])) 
    end as crate  
  FROM [dbo].[tbl_packinglist] GO

if you didnt understand the ltrim & rtrim,its nothing but just triming the values

like if the execute query gives you this

1    -12

then

ltrim & rtrim(lefttrim & rightrtrim(by side))

will give you this 

1-12

Delete All Tables,Views,Functions or Stored Procedure In SQL

Delete All Tables,Views,Functions or Stored Procedure In SQL

Some time you require to modify your database,but you don't know either they are tables,Views,functions or stored procedure.and you just want to run Delete all command for particular database objects.So You Can Use Curser To Execute The Requirement !

Delete All Tables

DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR
SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = 

'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON 

tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql
WHILE (@@FETCH_STATUS = 0)
BEGIN
Exec SP_EXECUTESQL @Sql
FETCH NEXT FROM @Cursor INTO @Sql
END
CLOSE @Cursor DEALLOCATE @Cursor
GO
EXEC sp_MSForEachTable 'DROP TABLE ?'
GO

Delete All Stored Procedures

declare @procName varchar(500)
declare cur cursor
    for select [name] from sys.objects where type = 'p'
open cur

fetch next from cur into @procName
      while @@fetch_status = 0
      begin
            if @procName <> 'DeleteAllProcedures'
                  exec('drop procedure ' + @procName)
                  fetch next from cur into @procName
      end

close cur
deallocate cur

Delete All Views

declare @procName varchar(500)
declare cur cursor
    for select [name] from sys.objects where type = 'v'
open cur

fetch next from cur into @procName
      while @@fetch_status = 0
      begin
                  exec('drop view ' + @procName)
                  fetch next from cur into @procName
      end
close cur
deallocate cur

Delete All Functions

declare @procName varchar(500)
declare cur cursor
    for select [name] from sys.objects where type = 'fn'
open cur

fetch next from cur into @procName
      while @@fetch_status = 0
      begin
                  exec('drop function ' + @procName)
                  fetch next from cur into @procName
      end

close cur
deallocate cur

But What if you want just table entries should be truncate 

follow this its works fine with SQL server R2

EXEC sp_MSForEachTable "TRUNCATE TABLE ?"

Diffrent kind of rows in crystal report asp.net

Generating Rows With Logic Of 'Group By'

There were some condition where you require to filter your data accordingly with a single value,for eg. you want to show a product one side(lets say right side) and its all rows of its processing  in other.

For Such Cases You Have To Use Grouping Concept Of Crystal Reports.

Below Is An Sample Of It..

1)Create table table_Product,as i 've created below

 

2)Create  Another Table With Name table_Details,as i 've created below

3)Lets says you've entered or the values under those table are like Below..

table_Product
 

table_Details

4)Now Add A Crystal Report And A Asp.net Page with name CrystalReport.rpt and CReport.aspx

respectively as Below

5)Now Proceed Step 1  to  7 From My Previous Example Creating Basic Example Of Crystal Report And Make Your Tables Attached To Your Crystal Report As Below.

6)Now Here Is The Thing Comes!
Right Click to The Group Name Field And Click On Insert Group As Above,

After that a pop up window will appear,Now Select the Column Name By Which you Need To do 'Group By' as below.

7)By Default The A Group #1 Name Object Is Now Created With Group header section and Group footer section,as below.

8)Now Add Details By Draging To Section3, Group header section & Group footer section and you'll get the result

I've done changes as below two images you can change as per your requirements.

I)

II)

9)Finally When You Generate The Out Put Will Be Like This...

Download Sample From Here