Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2008-06-21
10
Medium Priority
?
724 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

972 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