Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-06-21
10
Medium Priority
?
722 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
[X]
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
  • 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

IP addresses can be stored in a database in any of several ways.  These ways may vary based on the volume of the data.  I was dealing with quite a large amount of data for user authentication purpose, and needed a way to minimize the storage.   …
Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

715 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