Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
Forums » .NET » ASP.NET »
formula fiels in reports
Posted Date: 24 Nov 2008 Posted By: nandhitha Member Level: Silver Points: 1
Responses:
1
|
Hi..
In reports.rdlc page....i cannot find field explorer...
im able to open document outline....where i dont find any formula fields or formula workshop...
can any body suggest me pls.....where to put the formulas...
|
Responses
|
| Author: Deepika Haridas 24 Nov 2008 | Member Level: Diamond | Rating:  Points: 6 |
make crystal reports like this
(1) Goto Project > Add New Item > Crystal Report which opens/creates a template window/filecalled CrystalReport1.Rpt (For this sample, retain this default filename CrystalReport1.Rpt). This template file is a format-report window much like the ReportViewer's Report1.RDLC template file, in fact both types enter the project as embedded resources.
The sections, as follows, on this CrystalReport1.Rpt template are just like the sections on a Report1.RDLC file:
REPORT HEADER section
PAGE HEADER section
DETAILS section
REPORT FOOTER section
You will see a window asking what type of report, you can choose USE THE REPORT EXPERT. This will open up a tabbed window/Wizard called the CREATE REPORT EXPERT wizard which, if you close it, you can reopen anytime by right-click > Report > Report Expert.
One difference is this. With ReportViewer you usually use a filename to access the template file called Report1.RDLC. For example:
reportViewer1.LocalReport.EmbeddedResource = "AppName.Report1.RDLC"
but with Crystal you access the template file, NOT as a file, and thus NOT by filename, but rather as an object because .Net treats the file CrystalReport1.Rpt as an object/class (precisely like it would treat any class file created by doing a Project > Add Class) and hence this object is visible in Intellisense. It only takes ONE LINE OF CODE to produce the report, all you have to do is drop a ReportViewer onto the form, well here's it's called a CrystalReportViewer and then write a line of code that intanciates the object/class.
//use "new" because it's an object/class.
CrystalReportViewer1.ReportSource = New CrystalReport1
I also add this line of code:
Me.CrystalReportViewer1.DisplayGroupTree = False
as to eleminate the annoying lefthand pane from the report display.
Probably, if you are porting the CrystalReport1.Rpt file to another project, you can just add a blank report to the new project and then manually replace the file, using the mouse, with the ported file. I haven't tried porting as yet.
Like ReportViewer, Crystal needs a list of column names. When you supply them (see below), they display in a lefthand pane entitled the Field Explorer window. What's incredibly stupid is that VS refers to the FIELD EXPLORER window as the DocumentOutline window. To open it go to View > Other Windows > Document Outline.
As stated, Crystal needs a list of colum names. Choose one of the following two sources of column names and data - either a real database table (let's call this Method-1) or a datatable (let's call this Method-2).
Method-1: Use a real database as the datasource
Reopen the CREATE REPORT EXPERT wizard (right-click > Report > Report Expert). In the first tab page ("Data"), select
OLE DB (ADO.NET)
which opens up a window with a list of providers from which you can select:
OLE DB Data Provider for SQL Server
Click Next. Checkmark the Integrated Security box at the bottom, and then, at the top, choose your SQL Server (typically ComputerName). Finally, choose a database from the dropdown such as Northwind. This will give you a list of tables as to get a list of column names - warning: EVERY SINGLE COLUMN NAME selected will be placed on the format-report template automatically, it is not as though you get a chance to drag only the selected/desired columns (as with ReportViewer). Of course you can remove unwanted ones. To restore a column once it has been removed, drag them from the lefthand pane which is called FIELD EXPLORER (its real name is the Document Outline window at View > Other Windows > Document Outline). By default a column existing in the Details section is also placed, as with ReportViewer, in the Header section (so that the column name will be visible). Again, it only takes one line of code to generate the report (all the column names on the Details section will be populated):
this.crystalReportViewer1.ReportSource = new CrystalReport1();
And that's all there is to it.
****************
Method-2: Using a datatable as datasource
I found this solution in the user-comments section located in the after-pages of this article:
http://www.dotnet-news.com/lien.aspx?ID=6265
We'll use this sub as our data:
private DataTable getTheData(string TableName)
{
string strConnection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Northwind.mdb";
System.Data.OleDb.OleDbDataAdapter da = new
System.Data.OleDb.OleDbDataAdapter("SELECT * FROM " + TableName, strConnection);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
Here's how to provide Crystal a list of column names. As a one-time task, store the datatable's XSD schema in an XML file and then point Crystal to the XML file as the source of column names. Actually use a dataset rather than a datatable.
DataTable dt = this.getTheData("Products"); //the sub above
dt.TableName = "Products";
DataSet ds = new DataSet();
ds.Tables.Add(dt);
ds.WriteXmlSchema("C:\\Products.xml");
The last line of code can be deleted once we have run the code once as to build the list of column names. Next, in the template window (the format-report window), goto right Click > DataBase > Add/Remove DataBase and look for the yellow node
More Data Sources
And underneath this node
Ado.Net(XML)
and underneath that you'll find
Make New Connection
Double click this MAKE NEW CONNECTION node and then browse to your "C:\Products.XML" file. In the wizard you will now see a new node
New DataSet
and underneath that the table Products (as read from the XSD file). Add the Products table, which will make the columns present in Field Explorer (at View > Other windows > Document Outline). A quick way to move all of them onto the template, if you like, is to open the CREATE REPORT EXPORT window at right click > Report > Report Export and then click Next and then goto the Fields tab and select column names. Or you can drag the columns one by one from the left hand pane.
Now that you dragged your column names onto the report, run the report as follows. First, build the dataset using the same code used to build the XML file:
DataTable dt = this.getTheData(); //the sub above
dt.TableName = "Products";
DataSet ds = new DataSet();
ds.Tables.Add(dt);
And then add the Crystal code:
Dim CrystalRep As New CrystalReport1
'One of the next two lines might be optional but I included both just to be sure.
CrystalRep.SetDataSource(ds)
CrystalRep.Database.Tables(0).SetDataSource(ds.Tables(0))
CrystalReportViewer1.ReportSource = CrystalRep
Or in C# (actually in this sample I used a CrystalReport2.rpt file).
CrystalReport2 objCrystalRep = new CrystalReport2();
objCrystalRep.SetDataSource(ds);
objCrystalRep.Database.Tables[0].SetDataSource(ds.Tables[0]); //probably optional
crystalReportViewer1.ReportSource = objCrystalRep;
******************************
ALTERNATIVE SYNTAX FOR METHOD-1 AND METHOD-2
It's good to be aware of the alternative syntax because some of the sample code found on the Web relies on it.
Although in Method-1 and Method-2 we used a CrystalReport1 object for the report (which uses an embedded resource file called CrystalReport1.rpt), you can, alternatively, use another kind of object called ReportDocument which looks to the C:Drive for CrystalReport1.rpt instead of accessing it as an embedded resource. I tried to load it as an embedded resource which would have been easier but it didn't seem to work. Here's the code:
string nameOfTemplateFile = "CrystalReport1.rpt";
//Loop through all embedded resources to get full name of CrystalReport1.rpt
System.Reflection.Assembly asm = System.Reflection.Assembly.GetExecutingAssembly();
foreach (string str in asm.GetManifestResourceNames())
if (str.ToLower().IndexOf(nameOfTemplateFile.ToLower()) > -1) nameOfTemplateFile = str;
CrystalDecisions.CrystalReports.Engine.ReportDocument Rpt = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
Rpt.Load(nameOfTemplateFile); //here I passed in the name of embedded resource but got an error.
Rpt.SetDataSource(dtProducts);
this.crystalReportViewer1.ReportSource = Rpt;
I get a load-failure error at Rpt.Load(nameOfTemplateFile), so instead I will first copy the embedded resource to the C:Drive using the following code.
private void extractThisEmbeddedTemplateFileToThisDest(string nameOfTemplateFile, string pathToDest)
{//extract the embedded CrystalReport1.Rpt file to a file on the hard drive.
//if an older version exists at destination, delete it first.
System.IO.FileInfo fi = new System.IO.FileInfo(pathToDest);
if (fi.Exists) fi.Delete();
//Loop through all embedded-resource names to get FULL name of CrystalReport1.rpt
System.Reflection.Assembly asm = System.Reflection.Assembly.GetExecutingAssembly();
foreach (string str in asm.GetManifestResourceNames())
if (str.ToLower().IndexOf(nameOfTemplateFile.ToLower()) > -1) nameOfTemplateFile = str;
System.IO.Stream file =asm.GetManifestResourceStream(nameOfTemplateFile);
System.IO.FileStream outFile = new System.IO.FileStream(pathToDest, System.IO.FileMode.Create);
int bufferLen = 1024;
byte[] buffer = new byte[bufferLen];
int bytesRead;
do
{
bytesRead = file.Read(buffer, 0, bufferLen);
outFile.Write(buffer, 0, bytesRead);
} while (bytesRead != 0);
outFile.Close();
}
Now we'll call the above sub to extract the template to the Cdrive and then instanciate a ReportDocument which will load the the template file fromt the C:drive and then display the report.
string pathWhereTemplateFileWillBe = "C:\\Rpt.Rpt";
extractThisEmbeddedTemplateFileToThisDest("CrystalReport1.Rpt", pathWhereTemplateFileWillBe);
//the file is now there so let's change the variable name for clarity.
string pathToTemplateFile = pathWhereTemplateFileWillBe;
CrystalDecisions.CrystalReports.Engine.ReportDocument Rpt = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
Rpt.Load(pathToTemplateFile);
Rpt.SetDataSource(ds.Tables[0]);
this.crystalReportViewer1.ReportSource = Rpt; //displays report
***********************************
FIELD EXPLORER
As stated earlier, VS refers to the FIELD EXPLORER window (the lefthand pane) as the DocumentOutline window. To open it go to View > Other Windows > Document Outline.
One REASON it's called Field Explorer is that it allows you to add special columns to your report - they should have called it the COLUMN Explorer. A RunningTotal column, for example, can be added (see below). A calculcated column can be added, this is called a Formula Field.
If for some reason you accidentally chose the wrong table, goto right-click > Report > Report Expert > Fields to rechoose, as I recall.
To remove a table from availability completely, right-click > DataBase > AddRemove/DataBAse
NETWORK PERMISSIONS
The above code could fail for lack of permissions. In that case, give permissions to to the NETWORK_SERVICES group to modify the folder:
C:\windows\temp
and the same for the exportation files if you need to export reports in diferent formats.
Thanks & Regards,
Deepika
Editor
If U want to shine like a SUN..First U have to burn like the SUN!!
Need a Guide? Join my mentor program..
|
| Post Reply |
|
|
|
 | | This thread is locked for new responses. Please post your comments and questions as a separate thread.
If required, refer to the URL of this page in your new post. |
|
|
|
|