Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How to bind dataset object directly to crystal report at run time

Posted on 2008-06-21
10
717 Views
Last Modified: 2013-11-26
Hi,

I went through an article by emoreau: and created my report and I am able to view the data. These r the steps I followed.  I created an xsd schema file with the same column names as returned by my dataset object.

myobj_Dataset is populated with data returned by the function from business myobj_getQuery.QueryDataGet( val1 val2,  )  where QueryDataGet is the method in the business layer that retuns dataset.

My xsd schema file is as follows:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:od="urn:schemas-microsoft-com:officedata">
<xsd:element name="Table">
<xsd:complexType>
<xsd:sequence>

<xsd:element name="Commodity" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="255"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="year_2001" minOccurs="0" od:jetType="double" od:sqlSType="float" type="xsd:double"/>
<xsd:element name="year_2002" minOccurs="0" od:jetType="double" od:sqlSType="float" type="xsd:double"/>
<xsd:element name="commodity_Type" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="255"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="commodity_Code" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="255"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>

I use this code to bind my dataset to rpt
            crpt = New ReportDocument
            crpt.Load(Server.MapPath("~/Report/test1.rpt"))
            'Pass the dataset to the report
            crpt.SetDataSource(obj_Dataset)
            'Pass the Report Document object to the viewer
            CrystalReportViewer1.ReportSource = crpt
            CrystalReportViewer1.DataBind()


but my problem is that even though the number of columns returned by the dataset and the datatype will remain the same but the field names will be different for eg my report start year is this year -7 years so my end year is 2008 the start year is 2001 but next year it will be 2002  to 2009 so the field names returned by the dataset will be
value_2002 , value_2003 and so on.  wheras my data definition on field explorer is value_2001, value_2002 and so will my page header change. My question is in this scenario is the approach I am following will work or throw error ?


This is very urgent since I am confused at this point but i am sure there should be a solution for this.
0
Comment
Question by:PNKJ
  • 4
  • 4
  • 2
10 Comments
 
LVL 5

Expert Comment

by:JuckMan
ID: 21839604
Update your stored procedure to return fixed names. when you are returning data use alias to change the original column name to fit your names.
In this way you report is bound to the exact column names (aliased names)  and it will work no matter what year data you return..

Another option is, you can map the columns using crystalreport editor. I would not recommend this step since it is a code change and requires buld..

hope this helps
0
 

Author Comment

by:PNKJ
ID: 21840578
Can you please give me an example using the alias names . I am new to crystal reports

supose my dataset object returns  year_2001, year_2002 and so on and my xsd file also has element year_2001 and year 2002  in that case wil I have to modify my xsd file too?



0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 21840712
You won't be able to change your column name at run time and having CR to reflect that.

You will need to name your columns something like year_1, year_2, ... and feed them with the data you want.
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:PNKJ
ID: 21841380
Hello Emoreau,
I followed the exact steps and what I have understood is the following line was only used to create the structure so that we can drag and drop the fields names to the report. The xml that was generated matches the field name from the database returned. But I am not clear if next year my start year and end year will be different in that case the field names returned from my dataset object that is populated will not match the schema on rpt.  Sorry but may be I am not getting the concept.

mdsData.WriteXmlSchema("c:\Temp\DemoSchema.xsd")
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 500 total points
ID: 21841884
Whatever the start year is, it must always fill a field name year_1.
0
 

Author Comment

by:PNKJ
ID: 21842019
making sense so it means that whatever my start year may be  my stored procedure should always return column names as  year_1, year_2 year_3  and my xmlschema should also be year_1 year_2 , year_3 and so on .  Perhaps in that case my report header will have to be manipulated somhow to reflect the names as 2001, 2002 for the year_1 and year_2 . Currently when I drag the field year_2001 my repor theader also shows year_2001
0
 
LVL 5

Expert Comment

by:JuckMan
ID: 21842388
for a complete solution you need to do the following.

1) In crystal reports display the column headers from data returned from database (this is to change the year from 2001 to 2001 or what ever it is depend on your current year)
2) create an additional table in your databaes which will store the current year. You do not need this table, if your current year matches with the calendar year, in this case you can use getdate() to extract out the current year
3) based on your logic alias your database column title as well as the values. so that crystal report can work without any code changes and display the years as you are expecting.

hope this helps
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 21842515
Your headers could be ITextObject. See the section titled "Passing a value to a report object" from my article (http://www.emoreau.com/Entries/Articles/2006/09/Feeding-Crystal-Reports-from-your-application.aspx)
0
 

Author Comment

by:PNKJ
ID: 21842656
Thanks Emoreau  I think my first step would be to change the sql in store dprocedure to return alia s names . Is it possible to do that in your code behind page after the dataset is populated since my stored procedure is used at many places in application.

I f yes is there any sample for that ?
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 21842749
I am not sure. You may try to change the column name of the datatable but they might be read only.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question