Solved

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

Posted on 2008-06-21
10
704 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 69

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
 

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 69

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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 69

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 69

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Combobox, SelectedIndexChanged Get Index 2 22
Total in textboxes 9 36
Wpf develop 5 35
Code enhancement 5 14
Flash (http://en.wikipedia.org/wiki/Adobe_Flash) has evolved over the years to where it has become a masterful tool for displaying content screen.  It has excellent layout placement, UI precision as well as rendering capabilities. This, along with t…
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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now