Solved

XML schema for crystal report

Posted on 2008-06-20
10
3,954 Views
Last Modified: 2010-08-05
I had this question posted before and there was a good article by  emoreau.
I am little confused as I did the following steps to create my report
 My reports get populated but I realize my column header will change every year since the defaults for the start year and the end yearwill change and my xml schema is static.  

crpt = New ReportDocument
            crpt.Load(Server.MapPath("~/Reports/commodity.rpt"))
            'Pass the dataset to the report
            crpt.SetDataSource(myobj_Dataset)
            'Pass the Report Document object to the viewer
            CrystalReportViewer1.ReportSource = crpt
            CrystalReportViewer1.DataBind()
 
           myobj_Dataset is populated by calling a method from the dataaccess layerin asp.net code behind page.
            My dataset object returns only one table named (Table)


Mystored procedure has dynamic sql so I cannot bind the report to the stored procedure directly.

I created an xsd file to create schema and added the file from add new datasource
When I created the crystal report I used the following option
 Crystal report ---> databaseexpert--> createnew connection-->databasefile and selected the xsd file.
NOw In my crystal report I dragged and dropped the field names from the Field explorer.

<?xml version="1.0" encoding="UTF-8"?>
<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="year_2003" 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>
0
Comment
Question by:PNKJ
  • 5
  • 3
  • 2
10 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 21836262
Not sure what you are asking.  

If what you want is to be able to change the start and end year without changing the report, you can add a parameter to the report and use that to set the start and end year.

Another idea depending on how the years are determined you may be able to use a formula to do the calculation based on the current year.

mlmcc
0
 

Author Comment

by:PNKJ
ID: 21836395
All I want to know is since I cannot directly see the field names as I am using dynamic sql in stored procedure so i created the xml file to define schema and at run time I am passing my dataset object directly
 'Pass the dataset to the report
            crpt.SetDataSource(myobj_Dataset)
When I created the xml file I ran the stored procedure from backend to see the column names it returned and used the same field names for creating schema eg year_2002 as the second column. Stored procedure handle the start and end year. but next year the second column will be year_2003 (Start year being current year minus 5 years). In that case will my report fail since year_2003 will not match with the field name year_2002 on the rpt file field explorer.
I know my dataset object retuns only one table Is there any other way to bind the dataset object programmatically directly to the report and be able to view the field names in the field explorer that can be dragged and dropped to the rpt file.
Also my headers needs to display years like 2001, 2002 and so on
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 21836623
So you are concerned about the field names from the dataaset not headings on the report?

mlmcc
0
 

Author Comment

by:PNKJ
ID: 21837430
Yes since the report is bound to dataset file that is no longer used and the schema information is saved within the report file. I am assuming that field names returned from dataset should match exactly the schema or may be I am not clear how the schema works with the dataset object.  xsd file is not saved anywhere in my project.

 created an xsd file to create schema and added the file from add new datasource
When I created the crystal report I used the following option
 Crystal report ---> databaseexpert--> createnew connection-->databasefile and selected the xsd file.
This gave me the whole structure and I dragged and dropped the field names from Field explorer.


Thanks
0
 

Author Comment

by:PNKJ
ID: 21838986

So far what I have understood is that
Schemas files are XML files that describe the columns in your datatable and can be created by  to call  dataset's WriteXmlSchema method:
obj_Dataset.WriteXmlSchema("c:\Temp\DemoSchema.xsd")
once the schema is created u can call the xsd file from field explorer that will list the table but what if the columns that are generated will not be the same every time. In that case what is the best solution to bind the daset object to crystal report.

obj_Dataset.WriteXmlSchema("c:\Temp\DemoSchema.xsd")
            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()

 should this line be commented once u have dropped the fields on rpt from your code?
obj_Dataset.WriteXmlSchema("c:\Temp\DemoSchema.xsd")
I am bit confused since my dataset is going to generate different columns based on user input and will not be the same every time.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 69

Expert Comment

by:Éric Moreau
ID: 21840584
>>but what if the columns that are generated will not be the same every time. In that case what is the best solution to bind the daset object to crystal report

Crystal Report cannot be your reporting tool if your columns are changed every time. Crystal Report requires that you have a fixed set of tables and columns on which you create reports file and then the only thing that can change at run time is the data (not the schema).
0
 

Author Comment

by:PNKJ
ID: 21841851
I see my stored procedure runs dynamic sql and and when i run it generates the first column name as year_2001, second column as year2002 and so on. No of columns will not change it is just that the field names since next year the query will return year_2002 as my first column and year_2003 as second column and so on.

Since the schema needs to be same it means the field names returned by the stored procedure should be same as the schema. Is there any other solution like having alias names or something else.
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 21842002
not year_2001, but year_1 whatever year it contains!

>Since the schema needs to be same it means the field names returned by the stored procedure should be same as the schema.

Yes

>>Is there any other solution like having alias names or something else.

Sure you can use alias (select xx as year_1, yy as year_2 ...)
0
 

Author Comment

by:PNKJ
ID: 21845505
thanks
How can i change the column names for eg my dataset has one table and i want to rename the column name 3 with alias name and then do data bind I wan to change the names in my code behind file than in stored procedure
 crpt.SetDataSource(myobj_Dataset)
0
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 500 total points
ID: 21845595
Try this:

YourTableObject.Columns(YourColumnNameOrNumber).ColumnName = "new name"
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.

760 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

19 Experts available now in Live!

Get 1:1 Help Now