Creating reports and dashboards | Stimulsoft community forum

Hi!

I've created a code to add several sqlsources to my report, along as several relations between them, and I'm experiencig two problems:

1 - The relations don't show up in the dictionary unless I create a new relation manually, then the relation I added through code to that table is show.
2 - When I add any relation manually, the designer seems to refresh all the other relations and the memory consumption goes sky high. My machine has 2GB of physical memory and it crashes without finishing the refresh.

This leads me to conclude that I'm not using a necessary method so that the relations will be shown in the dictionary. As for the memory consumption, I don't see which can be the problem. My code adds 279 sources and 702 relations, as follows:

StiOptions.Designer.CodeTabVisible = false;
StiOptions.Designer.HtmlPreviewReportVisible = false;

this.Cursor = Cursors.WaitCursor;

StiReport Report = new StiReport();
StiSqlDatabase Conexao = new StiSqlDatabase("Labore","Server=RM0005522;Database=EXEMPLO;Uid=rm;Pwd=rm;");
Report.Dictionary.Databases.Add(Conexao);

string mySQL = "SELECT GCAMPOS.* " +
"FROM GCAMPOS " +
"WHERE GCAMPOS.APLICACOES LIKE '%P%' ";

DataTable GCAMPOS = dtOpen(mySQL,"GCAMPOS");
DataView GCAMPOSVIEW = GCAMPOS.DefaultView;

GCAMPOSVIEW.RowFilter = "COLUNA='#'";
GCAMPOSVIEW.Sort = "DESCRICAO";

mySQL = "SELECT DISTINCT GLINKSREL.* ";
mySQL = mySQL + "FROM GLINKSREL ";
mySQL = mySQL + "INNER JOIN GCAMPOS ON ";
mySQL = mySQL + "GLINKSREL.MASTERTABLE = GCAMPOS.TABELA ";
mySQL = mySQL + "AND GCAMPOS.APLICACOES LIKE '%P%' ";
mySQL = mySQL + "AND GCAMPOS.COLUNA = '#' ";
mySQL = mySQL + "INNER JOIN GCAMPOS FILHA ON ";
mySQL = mySQL + "FILHA.TABELA = GLINKSREL.CHILDTABLE ";
mySQL = mySQL + "AND FILHA.APLICACOES LIKE '%P%' ";
mySQL = mySQL + "AND FILHA.COLUNA = '#' ";

DataTable GLINKSREL = dtOpen(mySQL, "GLINKSREL");
DataView GLINKSRELVIEW = GLINKSREL.DefaultView;

DataSet Tabelas = new DataSet();
Tabelas.DataSetName = "Labore";

foreach (DataRowView Tabela in GCAMPOSVIEW)
{
DataView Colunas = GCAMPOS.DefaultView;
Colunas.RowFilter = "TABELA = '" + Tabela["TABELA"] + "' AND COLUNA '#' AND Relatorio = 1 ";
Colunas.Sort = "DESCRICAO";

if (Colunas.Count > 0)
{
mySQL = "SELECT ";

foreach (DataRowView Coluna in Colunas)
{
if (mySQL.Length > 7)
{
mySQL = mySQL + ", ";
}
mySQL = mySQL + Coluna["COLUNA"]; }

mySQL = mySQL + " FROM " + Tabela["TABELA"];

StiSqlSource Fonte = new StiSqlSource("Labore", (string)Tabela["TABELA"],(string)Tabela["DESCRICAO"], mySQL);
Report.Dictionary.DataSources.Add(Fonte);
Report.Dictionary.Synchronize();
Fonte.SynchronizeColumns();

foreach (StiDataColumn Coluna in Fonte.Columns)
{
Colunas.RowFilter = "TABELA = '" + Tabela["TABELA"] + "' AND COLUNA = '" + Coluna.Name + "' AND Relatorio = 1 ";
if ( Fonte.Columns.IndexOf(Fonte.Columns[(string)Colunas[0]["DESCRICAO"]]) < 0)
{
Coluna.Name = (string)Colunas[0]["COLUNA"];
Coluna.Alias = (string)Colunas[0]["DESCRICAO"];
}
}

}
}

stiDesignerControl1.Report = Report;
Report.Dictionary.Relations.Clear(); ;
Report.Compile();

foreach (StiSqlSource FontePai in Report.Dictionary.DataSources)
{
GLINKSRELVIEW.RowFilter = "MASTERTABLE = '" + FontePai.Name + "'";

foreach (DataRowView Linha in GLINKSRELVIEW)
{
string NomeRelacao = "FK_" + Linha["MASTERTABLE"] + Linha["CHILDTABLE"];
StiSqlSource FonteFilha = (StiSqlSource)Report.Dictionary.DataSources[(string)Linha["CHILDTABLE"]];
StiDataRelation Relacao = new StiDataRelation(NomeRelacao , FontePai, FonteFilha, Linha["MASTERFIELD"].ToString().Split(','), Linha["CHILDFIELD"].ToString().Split(','));
Report.Dictionary.Relations.Add(Relacao);
}
}

Report.Dictionary.RegRelations();
Report.Dictionary.Synchronize();
Report.Compile();

this.Cursor = Cursors.Default;

Thanks in advance!

Hello.

Please register that relations as it shown in the following topic:

This method will work, I just have checked it myself.

http://forum.stimulsoft.com/Default.aspx?g=posts&m=4644

And about memory consumption it is difficult to say something without a sample solution. Is it possible somehow?

Also please set CacheAllData property of StiReport object in true:

Report CacheAllData = true;

Thank you.

Following this example was my first try. The difference is that it takes longer to run, and the memory consumption raises as the relations are added, but it still crashes (without showing the designer) and if I interrupt the code, adding just a few relations, they still don't show in the dictionary.

My doubt is: When I add a relation manually, wich metods the are called after the addition? RegRelations, Synchronize? I'm sure I'm missing one.

For the memory consumption, I can send the sample solution, code and/or executable. The problem will be the data. I can export my database table structure to XML files and send along with the solution, will this be enough? I'll make a test with XML files anyway.

Yes, please send it to support[at]simulsoft.com with XML database.

Thank you.