cancel
Showing results for 
Search instead for 
Did you mean: 

Best Way To Set Up Report

Former Member
0 Kudos

I am having an issue with setting up a report that has the data coming from a stored procedure on SQL Express 2008R2. The data is being loaded fine into two tables placed into a dataset. Now the problem is with designing the report to display that data. I can't seem to attach the stored procedure in order to place the fields on the report. I have also been unsuccessful in using the unbound fields so far. Can someone provide some guidance?

Accepted Solutions (1)

Accepted Solutions (1)

0 Kudos

Hi Steve,

Moved to .NET SDK forum.

Once you have the Data into a DS then you should be able to see the DS in the Designer using ADO.NET.

Then you create the report off the DS and NOT the SP.

Here's one way of doing it using RAS:

private void SetData_Click(object sender, EventArgs e)

{

    string connString = "Provider=SQLOLEDB;Data Source=VMMSSQL2K8;Database=xtreme;User ID=sb;Password=pw";

    string sqlString = @"SELECT * from Xtreme";

    OleDbConnection oleConn = new OleDbConnection(connString);

    OleDbDataAdapter oleAdapter = new OleDbDataAdapter(sqlString, oleConn);

    DataTable dt1 = new DataTable("Query");

    oleAdapter.Fill(dt1);

    System.Data.DataSet ds = new System.Data.DataSet();

    ds.Tables.Add(dt1);

    ds.WriteXml(@"D:\Atest\xtreme.xml");

    ds.WriteXmlSchema(@"D:\Atest\xtreme.xsd");

    ISCRDataSet DS1 = (ISCRDataSet) CrystalDecisions.ReportAppServer.DataSetConversion.DataSetConverter.Convert(ds);

           

    rptClientDoc.DatabaseController.SetDataSource(DataSetConverter.Convert(ds), "Orders_Detail", "\"Orders Detail\"");

    MessageBox.Show("Data Source Set", "RAS", MessageBoxButtons.OK, MessageBoxIcon.Information);

    ds.Clear();

    ds.Dispose();

}

Don

Former Member
0 Kudos

I believe that it is almost there. I have the code below to build the data.

_Pack_WinDataSet pwds = new _Pack_WinDataSet();

rd.Load(@".\Reports\SumAccounts.rpt");              

dcPackWinDataContext pw = new dcPackWinDataContext();               

DataTable repdt = null;               

DateTime frdt, todt;

repdt = ToDataTable(pw.RoutePoundsSUM(Convert.ToDateTime("1/1/2012"), Convert.ToDateTime("3/23/2012")).ToList());

pwds.Tables.Add(repdt);

//pwds.RoutePoundsSUM = repdt;

repdt = ToDataTable(pw.RoutePctSUM(Convert.ToDateTime("1/1/2012"), Convert.ToDateTime("3/23/2012")).ToList());

pwds.Tables.Add(repdt);

rd.SetDataSource(fds);

I have also tried a regular dataset but neither displays data in the report. When I go to the report I do not see a dataset but I do get the ability to select the fields from ROUTEPOUNDSSUM and ROUTEPCTSUM. Am I missing something in making the dataset available in report design mode?

0 Kudos

Actually one of the easiest ways to do this is use my code to export to XML/XSD, create a new report off the XSD file ( data structure ) and then simply set your ds using the same line

rd.SetDataSource(fds);

Also, use CR Designer to convert dates, the structure needs to be the same as the designer expects.

Try a simply report and DS first to make it easier to debug.

Don

Former Member
0 Kudos

Thanks Don,

I have started with implementation of that approach and I am having trouble resolving ISCRDataSet. Is there a specific USING that I need for VS2008 with the integrated CR?

Former Member
0 Kudos

This is NET3.5 by the way.

0 Kudos

I always include them all.

Here's what I use:

using System;

using System.IO;

using System.Xml;

using System.Drawing;

using System.Drawing.Printing;

using System.Collections;

using System.ComponentModel;

using System.Windows.Forms;

using System.Data;

using System.Threading;

using CrystalDecisions.CrystalReports.Engine;

using CrystalDecisions.Shared;

using CrystalDecisions.ReportAppServer.ClientDoc;

using CrystalDecisions.ReportAppServer.Controllers;

using CrystalDecisions.ReportAppServer.ReportDefModel;

using CrystalDecisions.ReportAppServer.CommonControls;

using CrystalDecisions.ReportAppServer.CommLayer;

using CrystalDecisions.ReportAppServer.CommonObjectModel;

using CrystalDecisions.ReportAppServer.ObjectFactory;

using System.Data.OleDb;

using CrystalDecisions.ReportAppServer.DataSetConversion;

using CrystalDecisions.ReportAppServer.DataDefModel;

using CrystalDecisions.ReportSource;

using CrystalDecisions.Windows.Forms;

using System.Data.SqlClient;

using System.Runtime.InteropServices;

It does require fully qualifying the methods because both the Engine and RAS have the same.

Don

PS - for those using 4.0 frame work be suree to add this to your app.config file:

 

<

startup useLegacyV2RuntimeActivationPolicy="true"

>

Message was edited by: Don Williams

Former Member
0 Kudos

Whenever I try to add beyond

using CrystalDecisions.ReportAppServer

it fails to load or compile because it errors out. Is there something that needs to be added as a reference?

Former Member
0 Kudos

I see. I added the reference and all is well now.

Former Member
0 Kudos

I have followed this advice and used the XSD file as the data source. I use the code below and design the report using files from the two tables in the XSD but it still displays empty data. I see that there is data in the XML file.

System.Data.

DataSet ds = new System.Data.DataSet();

rd.Load(@".\Reports\SumAccounts.rpt");               

dcPackWinDataContext pw = new dcPackWinDataContext();               

DataTable repdt = null;               

DateTime frdt, todt;

repdt = ToDataTable(pw.RoutePoundsSUM(

Convert.ToDateTime("1/1/2012"), Convert.ToDateTime("3/23/2012")).ToList());                ds.Tables.Add(repdt);

//pwds.RoutePoundsSUM = repdt;

repdt = ToDataTable(pw.RoutePctSUM(

Convert.ToDateTime("1/1/2012"), Convert.ToDateTime("3/23/2012")).ToList());

ds.Tables.Add(repdt);

//rd.SetDataSource(fds);

ds.WriteXml(@"C:\prtsum.xml");

ds.WriteXmlSchema(@"C:\prtsum.xsd");

ISCRDataSet DS1 = (ISCRDataSet)CrystalDecisions.ReportAppServer.DataSetConversion.DataSetConverter.Convert(ds);

rd.SetDataSource(DataSetConverter.Convert(ds));

I will work more on this in the afternoon but I am having a hard time seeing what is wrong.

former_member183750
Active Contributor
0 Kudos

Empty report is typically the result of the report engine not understanding the data you are passing to it, so the engine strips out all data columns. To confirm this, export the report to RPT file format. Than view the report in the designer. If the data fields have ben stripped out, you are passing data to the report that do not agree "as far as format is concerned" with what the engine is expecting. To troubleshoot this, see the blog Troubleshooting Issues with VS .NET Datasets and Crystal Reports.

- Ludek

Former Member
0 Kudos

Progress has been made. The trouble was caused by the automatic linking that was left in place. I have two tables in the XML file with the same field names. One table has dollar amounts while the other table has percentages. I changed the field names so that they are not the same and it is still grouping the two sections together. I need them separate so that I can show the dollars together and total them. The next section should show the percentages. See below for the current display.

100     12     24     60
100     .11     .15     .04

101     333     431.52     31.92
101     3.04     2.78     2.12

It should show as

100          12          24          60
101        333     431.52     31.92
              345     455.52     91.92

100     .11     .15     .04
100     3.04     2.78     2.12

What am I doing wrong in the report setup? I have tinkered with relative positions and keep together. I don't see much more that I can modify in the sections.

0 Kudos

Using more than one table in a dataset is not very efficient, CR ONLY likes one table. If you can write a SQL to get all your data into the one table than use it. What CR tries to do with DS's with multiple tables is link all fields to all fields.

Create it manually in the Designer by adding the tables directly, then create your links. Then Show SQL and use that in your app as the SQL query.

Then post to the Report Design Forum for help in laying out your data.

Don

Former Member
0 Kudos

By jove, I believe that we are almost there. I used a MERGE instead of adding a second table and ended up with a single dataset of combined data with a function field with "Dollars" or Percentage" in it. I then grouped by this field and subtotaled the pounds, sales, and profit.

Now, for the final aggravation. Can I display the subtotal field based on the function field used? I remember something about using a function to determine whether a field displays but I can't find that at the moment. Thanks for all of the help.

Former Member
0 Kudos

And that solution was solved differentlt than expected, but it is solved. I used the highlight selector and changed the font to white if it is PERCENTAGES. Thanks for all of the help.

Answers (0)