XSD File

If you open a DataSet object with the result of the query, you can save a XSD file with the schema for that query:

private void WriteSchemaWithXmlTextWriter(DataSet thisDataSet){
   // Set the file path and name. Modify this for your purposes.
   string filename="mySchema.xml";
   // Create a FileStream object with the file path and name.
   System.IO.FileStream myFileStream = new System.IO.FileStream
      (filename,System.IO.FileMode.Create);
   // Create a new XmlTextWriter object with the FileStream.
   System.Xml.XmlTextWriter MyXmlTextWriter =
      new System.Xml.XmlTextWriter(myFileStream, System.Text.Encoding.Unicode);
   // Write the schema into the DataSet and close the reader.
   thisDataSet.WriteXmlSchema(MyXmlTextWriter );
   MyXmlTextWriter.Close();
}
This code was taken from: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatasetclasswritexmlschematopic.asp

Then add the XSD file to your project, and designing the report select the XSD as your DataSource.

Good Luck

Thanks RuiSousa for the quick response, however, the way this is described won't work for me right now. The dataset will be changing based on a parameter. A user selects a date, a dataset is returned based on that date, and then the info in the dataset is used as the source for the report. The way the above method describes it is, I would need to know which request the user is going to make, and create the report for them...not on the fly...correct?

Thanks

I'm not sure if this is what you're after:

                  string connString = @"DRIVER=Microsoft Access Driver (*.mdb);UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;PageTimeout=5;MaxScanRows=8;MaxBufferSize=2048;FIL=MS Access;DriverId=25;DefaultDir=D:\my documents;DBQ=D:\my documents\db1.mdb";

                  string command = "SELECT * FROM Table1";

                  DataSet ds = new DataSet();

                  using ( OdbcConnection conn = new OdbcConnection( connString ) )
                  {
                        OdbcDataAdapter da = new OdbcDataAdapter( command, conn );

                        da.FillSchema( ds, SchemaType.Source );
                  }

                  MessageBox.Show( (ds != null ? ds.GetXmlSchema() : "oops" ) );


Let me know if this is what you're after...

Vascov, thanks for the reply. That's close. I've already got the dataset populated correctly with the information. My question is, how can take the dataset, and either turn it into a xsd file dynamically and tie that xsd file to a crystal report dynamically, or just tie the dataset to the report dynamically. Is this even possible. I would think this isn't an uncommon scenario, but I may be wrong.

I'm going after a crystal report that is generated dynamically, depending on what parameters are sent to a stored proecedure in Access.

Thanks for the help

But do you have a report previously generated ?
or what kind of dynamic report do you intend to produce ? (a simple tabular one ? )

Is the structure of your query in terms of returned info changing ? or is it just the data itself that changes according to some criteria ?

If all you want is to pass the DataSet into your report here's another sample:

                  string connString = @"DRIVER=Microsoft Access Driver (*.mdb);UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;PageTimeout=5;MaxScanRows=8;MaxBufferSize=2048;FIL=MS Access;DriverId=25;DefaultDir=D:\my documents;DBQ=D:\my documents\db1.mdb";
                  string command = "SELECT * FROM Table1";

                  DataSet ds = new DataSet();

                  using ( OdbcConnection conn = new OdbcConnection( connString ) )
                  {
                        
                        OdbcDataAdapter da = new OdbcDataAdapter( command, conn );

                        da.FillSchema( ds, SchemaType.Source );

                  }

                  MessageBox.Show( (ds != null ? ds.GetXmlSchema() : "oops" ) );

                  CrystalReport1 r = new CrystalReport1();

                  r.SetDataSource( ds );

                  r.ExportToDisk( CrystalDecisions.Shared.ExportFormatType.WordForWindows, @"C:\temp\myrep.doc" );

If using web, then you will probably want to use a reportviewer, and assign to ReportSource, the report we instantiaded just now (in this sample r )

HTH

I have a Web App that produces reports based on criterias the user inputs, like BeginDate, EndDate, etc.

The result of the query is allways diferent, but with the same columns. In this cenario this will work.

If the query outputs diferent columns depending on the paramaters, then you will need one report, and one XSD, for each columns layout. The report's design must be defined and saved as a .rpt file. I don't think you can generate a report dinnamicaly at runtime, but you can populate a report at runtime:

SqlConnection myConn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
myConn.Open();
SqlCommand myComm = new SqlCommand("MyStoredProcedure",myConn);
myComm.CommandType = CommandType.StoredProcedure;                           
SqlCommandBuilder.DeriveParameters(myComm);

DataSet MyDataSet = new DataSet();
myAdapter.Fill(MyDataSet,"MyStoredProcedure");
oRpt.Load(Server.MapPath("MyReport.rpt"));
oRpt.SetDataSource(MyDataSet);

crViewer.HasDrillUpButton = true;                        
crViewer.ReportSource = DaReport(myComm);
crViewer.DataBind();

myConn.Close();

Sorry!!
Were I wrote:
crViewer.ReportSource = DaReport(myComm);

Please read:
crViewer.ReportSource = MyReport;

Oops.

I'm copying and pasting the code from diferent places, and i'm not doing it right...

This time is for real:


SqlConnection myConn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
myConn.Open();
SqlCommand myComm = new SqlCommand("MyStoredProcedure",myConn);
myComm.CommandType = CommandType.StoredProcedure;                      
SqlCommandBuilder.DeriveParameters(myComm);

//Add parameters filling in here

SqlDataAdapter myAdapter = new SqlDataAdapter(myComm);
ReportDocument oRpt= new ReportDocument();

DataSet MyDataSet = new DataSet();
myAdapter.Fill(MyDataSet,"MyStoredProcedure");
oRpt.Load(Server.MapPath("MyReport.rpt"));
oRpt.SetDataSource(MyDataSet);

crViewer.HasDrillUpButton = true;          
crViewer.ReportSource = DaReport(myComm);
crViewer.DataBind();

myConn.Close();

Here's the code I have. objSet is successfully created. However, here's the error I receive: Unable to find the report in the manifest resources. Please build the project, and try again. DailyActivity.rpt exists in the directory.

                  Activity objAct = new Activity();
                  DataSet objSet = new DataSet();
                  DailyActivity objReport = new DailyActivity();
                  
                  ReportDocument objDoc = new ReportDocument();
                  objDoc.Load(Server.MapPath("DailyActivity.rpt"));
                  objSet = objAct.GetActivitiesForDay("12/2/2003","3");

                  objReport.SetDataSource(objSet);                  
                  
                  objCRViewer.ReportSource = objDoc;
                  objCRViewer.BestFitPage = true;
                  objCRViewer.BorderWidth = 1;
                  objCRViewer.DisplayToolbar = false;
                  objCRViewer.DataBind();

Try to instantiate the object directly

DailyActivity dr = new DailyActivity();

(this assumes the Crystal Assembly is acessible from within your project)

Did it help ?

Sorry, I was out of the office for the last few days. The line of code you suggested I implement already existed (line 3 of my pasted code). Unfortunately, it is still not working.

Hi Junoti,

You're right, i tried to point you to the problem, but didn't post the solution :(

In your code, you'll notice, that objReport is only used in 2 places. the 3rd line and the 9th. You're not passing objReport to the viewer.

So, here's part of your code "revised":
               Activity objAct = new Activity();
               DataSet objSet = new DataSet();
               DailyActivity objReport = new DailyActivity();
               
               objSet = objAct.GetActivitiesForDay("12/2/2003","3");

               objReport.SetDataSource(objSet);              
               
               objCRViewer.ReportSource = objReport;
               objCRViewer.BestFitPage = true;
               objCRViewer.BorderWidth = 1;
               objCRViewer.DisplayToolbar = false;


Let me know if this works

Thanks vascov, here's the scenario now. The above code appears to "work" by not returning an error...but...now I get, what appears to be an empty report. I get nice little scroll bars, but no data. I've put breaks into the code, and I've verified that objSet has valid data. Here are the steps I used to create the actual rpt file:

1) Created a dataset file called "DailyActivities.xsd" with the exact schema of the data returned from objSet
2) Created a report called "DailyActs.rpt" and used "DailyActivities.xsd" as it's data source. The schema appears fine.
3) After creating the rpt file, I implemented the above code.

Thanks for the help. This is the only part of my project left, so I'm increasing the points also.

TIA

Have you validated that objSet has the correct data ?

You can profile it as SQL using SQL Profiler, to make sure the data is getting out of SQL, and then use the Trace mechanisms from ASP.NET to make sure the data is getting set along the way.

Yep, data is being returned correctly. I use the same method in several other places, and it works fine.

Ok, I think I'm getting close. I have an XSD file called "DailyActivities.xsd". A code behind file name "DailyActivities.cs" is created from the XSD file, and inherits System.Data.DataSet. Rather than instantiating a new DataSet object (line 2 of above code), I think I should have been instantiating a DailyActivities object. My function called "GetActivitiesForDay(...,..)" returns a DataSet object. I try to cast/copy the DataSet returned from the function to a DailyActivities object, but get an error stating the the cast is invalid. Any ideas?

To the code vascov wrote:

               Activity objAct = new Activity();
               DataSet objSet = new DataSet();
               DailyActivity objReport = new DailyActivity();
               
               objSet = objAct.GetActivitiesForDay("12/2/2003","3");

               objReport.SetDataSource(objSet);              
               
               objCRViewer.ReportSource = objReport;
               objCRViewer.BestFitPage = true;
               objCRViewer.BorderWidth = 1;
               objCRViewer.DisplayToolbar = false;

You should add

               objCRViewer.DataBind();

I hope this is it.

Olá Rui,

Actually, there's no explicit need to databind.  (it wont hurt either :)
When you set the reportsource it "binds".

HTH

Alright guys, this is perplexing. Here's something I found:
http://groups.google.com/groups?q=%22unable+to+find+the+report+in+the+manifest+resources.+Please+build+the+project,+and+try+again.%22&hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=02fd01c31994%245d707090%24a501280a%40phx.gbl&rnum=9

I take the same exact code from my project, create a new project, follow the exact same steps of creating a dataset file and bind a report to it, and voila it works. According to this above post (and I hope someone can validate), reports do not like being in namespaces. For instance, my namespace for the report is Project.CompanyName.Reports, and this doesn't work. In the new project I created as a test, it was just Reports, and this works fine. Can anyone validate this and/or suggest a work around?

Thanks for all the help fellas/fella-ettes!

Woohoo! Thanks for the help! This line kinda lead me into a new direction:

In your GetActivitiesForDay code, are you creating a DataSet or a DailyActivities object internally ? If you are creating just a DataSet, than it's base type would be DataSet, and when you try to "copy" it to a DailyActivities object, it naturally complains, because it doesn't know how to do it.

I ended up creating a new method called ReportActivitiesForDay that returned a DailyActivities object (base class is DataSet). One key lesson I learned (off of CrystalDecisions.com) is that the name of the DataSet that is being filled HAS to match the name of the dataset in the XSD file. For instance, my XSD file had a dataset name of "ActivityReport". However, when using the dataadapter to fill the dataset object, the code was similar to objAdapter.Fill(objSet,"DailyActivity"). Once I changed the "DailyActivity" to "ActivityReport", my report showed up!! Albeit, it doesn't look pretty, at least I'm reporting off the database now!

Thanks again for the effort!

Junoti, so how did you pull it ?

Regarding the namespace, i don't have any authoritative answer, but i've developed the report for testing using the namespace vascov.problems.ee. And it works :)

Pull the data? Using a stored proc.

I'm not sure about the namespace stuff either, must have been an issue on the other machine I was developing on. The namespace I currently have the report sitting in has a somewhat deep nomenclature, and it works fine also. Hope it still works when it moves to the production server :)

Thanks for the help vascov.