Adding two datasources to a report

[jnb]

I have a report which should use two tables as datasources. In the report I have the two sources defined with a common source names but different name and alises; i.e. I have

Table 1 - name in source = "interim", name = "players", alias = "players"
Table 2 - name in source = "interim", name = "assess", alias = "assess"

which seems to be right as they appear as separate tables in the report dictionary.

In the C# source code I have

DataTable players = GetBasicPlayerData("118", out filtered);
DataTable assess = GetPlayerAssessmentData("118", out filtered);
StiReport interimreport = new StiReport();
interimreport.Load(appDirectory + "\\interim3.mrt");

interimreport.RegData("interim", "players", players);
interimreport.RegData("interim", "assess", assess);

Now only the last registered set of data works, i.e. in the above example the assess data appears but not the players data, as though one one data table can be registered in the report. I've tried registering this via a dataset, i.e.

DataSet ds = new DataSet("interim");
DataTable players = GetBasicPlayerData("118", out filtered);
DataTable assess = GetPlayerAssessmentData("118", out filtered);
ds.Tables.Add(players);
ds.Tables.Add(assess);

StiReport interimreport = new StiReport();
interimreport.Load(appDirectory + "\\interim3.mrt");

interimreport.RegData("interim", ds);

but that's even less successful. So what am I missing? how do I get two datatables into a report.

TIA

[Alex K.]

Hello,

In this case, please modify your code:

Code: Select all

DataTable players = GetBasicPlayerData("118", out filtered);
DataTable assess = GetPlayerAssessmentData("118", out filtered);
StiReport interimreport = new StiReport();
interimreport.Load(appDirectory + "\\interim3.mrt");

interimreport.RegData("players", "players", players);
interimreport.RegData("assess", "assess", assess);

Thank you.

[jnb]

Sorry that doesn't work either.

I've created a simplified test example. In my c# code I populate two tables one contains five rows of data and one contains 19 rows of data.

Code: Select all

DataTable data1 = GetData1();
DataTable data2 = GetData2();

StiReport report = new StiReport();
report.Load(appDirectory + "\\test.mrt");

report.RegData("test", "matches", data1);
report.RegData("test", "players", data2);
rpt_test.Report = report;

In the test.mrt I have

ColumnHeaderBand1
list of column headers and count of items in data source
DataBand1
Data source = "players"
list each field in players
ColumnHeaderBand2
list of column headers and count of items in data source
DataBand2
Data source = "matches"
list each field in matches


ColumnHeaderBand1, DataBand1 and ColumnHeader2 appear but the count in ColumnHeader2 refers to the datasource from DataBand1. DataBand2 is empty, I can put in a reference to a field from DataSource1 in DataBand2 and it appears 5 times with the last row's data from DataSource1.

If I change the data registration to;

Code: Select all

report.RegData("test", "matches", data1);
report.RegData("test", "players", data2);

Then the second data set appears but not the first,

If I use;

Code: Select all

report.RegData("players", "players", players);
report.RegData("matches", "matches", matches);

Then nothing appears

The datasources are defined in the report as;
--Data Sources
----test
------players
--------firstname
--------surname
--------id
------matches
--------date
--------opponent

If I redefine the datasources as;
--Data Sources
----test
------players
--------firstname
--------surname
--------id
----test2
------matches
--------date
--------opponent

and change the data registration to;

Code: Select all

report.RegData("test2", "matches", data1);
report.RegData("test", "players", data2);

Both tables appear, but by doing that I lose the ability to have master detail relationships.

[Ivan]

Hello,

Both tables appear, but by doing that I lose the ability to have master detail relationships.

Please check the following topic in our knowledgebase:
http://stimulsoft.helpserve.com/index.p ... ticleid=79

Description
How to render Master-Detail reports if two tables are located on another DataSet?

Solution
In this case you should enable caching all data in one DataSet. For this you should set the CacheAllData property of report to true.

Thank you.

[jnb]

Nope still can't get this to do what I want. CacheAllData may resolve the master detail issue but that's not what I want yet. For the moment I want to just list two tables on a report. In future I will want master detail relationships etc and that will all come from a single dataset, but for the moment that can wait.

So I have a very simple database

Code: Select all

CREATE TABLE IF NOT EXISTS `tempa` (
  `playerid` int(11) NOT NULL,
  `name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `tempa` (`playerid`, `name`) VALUES
(2, 'Adam'),
(3, 'Barbara'),
(4, 'Charles'),
(5, 'Daphne'),
(6, 'Edward');

CREATE TABLE IF NOT EXISTS `tempb` (
  `matchid` int(11) NOT NULL,
  `venue` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `tempb` (`matchid`, `venue`) VALUES
(1, 'Anywheresville'),
(2, 'Beside the seaside'),
(3, 'Charlestown'),
(4, 'Down in the valley'),
(5, 'East of eden'),
(6, 'Far far away'),
(7, 'Green green valley');

a very simple report;

Code: Select all



  True
  
    
    
    
      
        players
        
          name,System.String
          playerid,System.Int32
        
        
        players
        test
      
      
        matches
        
          matchid,System.Int32
          venue,System.String
        
        
        matches
        test
      
    
    
    
    
  
  EngineV2
  
  
  
    
      None;Black;2;Solid;False;4;Black
      Transparent
      
        
          Transparent
          0,0.4,19,0.6
          
            
              Transparent
              0,0,2.4,0.6
              
              Arial,12,Bold
              b800172b58c542e5ab083f48f9e547a7
              Center
              0,0,0,0
              Text2
              
              
              ID
              Black
              Expression
            
            
              Transparent
              2.4,0,2.4,0.6
              
              Arial,12,Bold
              0315091eaabf41609b20414237754d46
              Center
              0,0,0,0
              Text3
              
              
              Name
              Black
              Expression
            
            
              Transparent
              9.2,0,4.6,0.6
              
              Arial,12
              46a9319fa1d44328b11f8983a7d0211a
              0,0,0,0
              Text5
              
              
              {Count()} players
              Black
              Expression
            
          
          
          0aa4362ff3274ee1b2542a2357d457a0
          ColumnHeaderBand1
          
          
        
        
          Transparent
          
          0,1.8,19,0.6
          
            
              Transparent
              0,0,2.4,0.6
              
              Arial,12
              9eb8aa0ee4684d14b480f215c9b40d2e
              0,0,0,0
              Text6
              
              
              {players.playerid}
              Black
              Expression
            
            
              Transparent
              2.4,0,2.4,0.6
              
              Arial,12
              ec73275eb6c24ca889c2551914023218
              0,0,0,0
              Text7
              
              
              {players.name}
              Black
              Expression
            
          
          
          
          players
          
          d96f5d33a8ef4b47ad4d0c1c79fee360
          DataBand1
          
          
          
        
        
          Transparent
          0,3.2,19,0.6
          
            
              Transparent
              0,0,3.8,0.6
              
              Arial,12,Bold
              Center
              0,0,0,0
              Text9
              
              
              Match ID
              Black
              Expression
            
            
              Transparent
              4,0,7.8,0.6
              
              Arial,12,Bold
              Center
              0,0,0,0
              Text11
              
              
              Venue
              Black
              Expression
            
            
              Transparent
              12.6,0,5.6,0.6
              
              Arial,12
              8e55ed3099c14e40a2042672eff32690
              0,0,0,0
              Text13
              
              
              {Count()} matches
              Black
              Expression
            
          
          
          ColumnHeaderBand2
          
          
        
        
          Transparent
          
          0,4.6,19,0.6
          
            
              Transparent
              4.2,0,7.6,0.6
              
              Arial,12
              0,0,0,0
              Text10
              
              
              {matches.venue}
              Black
              Expression
            
            
              Transparent
              0,0,4,0.6
              
              Arial,12
              0,0,0,0
              Text8
              
              
              {matches.matchid}
              Black
              Expression
            
          
          
          
          matches
          
          DataBand2
          
          
          
        
      
      
      e7b3da22e4e24bbeb19c5ebd0ede701b
      1,1,1,1
      Page1
      29.7
      21
      
      
        Arial,100
        [50:0:0:0]
      
    
  
  
  
    System.Dll
    System.Drawing.Dll
    System.Windows.Forms.Dll
    System.Data.Dll
    System.Xml.Dll
    Stimulsoft.Controls.Dll
    Stimulsoft.Base.Dll
    Stimulsoft.Report.Dll
  
  Report
  2/2/2011 11:40:20 AM
  2/1/2011 2:03:21 PM
  C:\k\test2db\test2.mrt
  3ef457df254e49ff8f3678dc342963d4
  Report
  Centimeters
  2010.3.900
  using System;
using System.Drawing;
using System.Windows.Forms;
using System.Data;
using Stimulsoft.Controls;
using Stimulsoft.Base.Drawing;
using Stimulsoft.Report;
using Stimulsoft.Report.Dialogs;
using Stimulsoft.Report.Components;

namespace Reports
{
    public class Report : Stimulsoft.Report.StiReport
    {
        public Report()
        {
            this.InitializeComponent();
        }

        #region StiReport Designer generated code - do not modify
        #endregion StiReport Designer generated code - do not modify
    }
}

  CSharp
  

and a c# web application which should populate that report;

Code: Select all

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using MySql.Data.MySqlClient;
using Stimulsoft.Report;

public partial class _Default : System.Web.UI.Page 
{
    public static string dataBaseLocation()
    {
        string location = System.Configuration.ConfigurationManager.AppSettings["connectionString"];
        return location;
    }

    protected DataTable GetData1()
    {
        DataTable dt = new DataTable("players");
        MySqlDataAdapter dataAdapter = new MySqlDataAdapter("SELECT playerid, name FROM tempa ", dataBaseLocation());
        dt.Locale = System.Globalization.CultureInfo.InvariantCulture;
        dataAdapter.Fill(dt);
        return dt;
    }

    protected DataTable GetData2()
    {
        DataTable dt = new DataTable("matches");
        MySqlDataAdapter dataAdapter = new MySqlDataAdapter("SELECT matchid, venue FROM tempb ", dataBaseLocation());
        dt.Locale = System.Globalization.CultureInfo.InvariantCulture;
        dataAdapter.Fill(dt);
        return dt;
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        string appDirectory = HttpContext.Current.Server.MapPath(string.Empty);

        DataTable data1 = GetData1();
        DataTable data2 = GetData2();

        StiReport report = new StiReport();
        report.Load(appDirectory + "\\test2.mrt");
        report.RegData("test", "players", data1);
        report.RegData("test", "matches", data2);

        rpt_test.Report = report;
    }
}

Whichever RegData call takes place last wins out and no other data can be seen. Now I'm sure I'm making some very simple and very stupid mistake but I have no idea what it is.

[Alex K.]

Hello,

You can use the following code:

Code: Select all

protected DataSet GetDataSet()
{
    DataSet ds = new DataSet("Test");
    DataTable dtPlayers = new DataTable("players");
    SqlDataAdapter dataAdapter1 = new SqlDataAdapter("SELECT playerid, name FROM tempa ", dataBaseLocation());
    dtPlayers.Locale = System.Globalization.CultureInfo.InvariantCulture;
    dataAdapter1.Fill(dtPlayers);
    DataTable dtMatches = new DataTable("matches");
    SqlDataAdapter dataAdapter2 = new SqlDataAdapter("SELECT matchid, venue FROM tempb ", dataBaseLocation());
    dtMatches.Locale = System.Globalization.CultureInfo.InvariantCulture;
    dataAdapter2.Fill(dtMatches);
    ds.Tables.Add(dtPlayers);
    ds.Tables.Add(dtMatches);
    return ds;
}

protected void Page_Load(object sender, EventArgs e)
{
    string appDirectory = HttpContext.Current.Server.MapPath(string.Empty);
    DataSet ds = GetDataSet();
    StiReport report = new StiReport();
    report.Load(appDirectory + "\\test2.mrt");
    report.RegData("Test",ds);
    StiWebViewer1.Report = report;
}

Thank you.

[jnb]

Hello,

You can use the following code:

...

Code: Select all

    ....
    report.RegData("test",ds);
    StiWebViewer1.Report = report;
}

Shows nothing at all! Changing to

Code: Select all

    ....
    report.RegData("test", "players", ds.Tables["players"]);
    report.RegData("test", "matches", ds.Tables["matches"]);
    StiWebViewer1.Report = report;
}

show just the match data, while

Code: Select all

    ....
    report.RegData("test", "matches", ds.Tables["matches"]);
    report.RegData("test", "players", ds.Tables["players"]);
    StiWebViewer1.Report = report;
}

Shows just the players data.

So the data set is populated correctly. The report can look at both tables in the dataset but not both at the same time. What on earth is going wrong here?

(NB I have changed the dataset name from "Test" in your example code to "test" in my code so it is consistent with the case of the names in the report.)

[Alex K.]

Hello,

Can you please send us a sample test project, which reproduce the issue for analisys?

Thank you.

[fkmfkm]

Hello,

Can you please send us a sample test project, which reproduce the issue for analisys?

Thank you.

Any updates here...? i am also having the same problem....

[Ivan]

Hello,

We couldn't reproduce this issue, on our data all works correctly.
Can you please send us a sample test project, which reproduce the issue for analisys?

Thank you.