Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2008-06-21
10
Medium Priority
?
730 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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 

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 2000 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

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

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

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Loops Section Overview
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

580 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