Using push AND pull methods to get data

[Former Member]

Hello,

I would think that there is an answer to my question in the forum, but I could not find it... also, please be patient, I am not a cr expert!

We are developing reports using cr2008 editor and connecting to a local sqlserver database.

The report consists of several tables from the db, a calls table and a couple more tables.

Then, in our app (VS2005, c#) I use the push method to get data to the Calls table to have the report display the relevant data (no way around that):

m_dataSet = new DataSet();                
m_oleAdapter = new OleDbDataAdapter();                
m_oleAdapter.SelectCommand = new OleDbCommand(selectString, m_oleConn);
m_oleAdapter.Fill(m_dataSet, "Calls");
m_reportDocument.Database.Tables["Calls"].SetDataSource(m_dataSet);

What I would like to do with the other tables is not use the push method to avoid as much overhead as possible, just change the db connection info since I want the report to pretty much have access to all the records in the table.

I tried numerous things:

1- changing all the available data sources:

m_reportDocument.DataSourceConnections<i>.SetConnection(server, database, username, password);

2- changing the connection info at the table level:

IEnumerator er = m_reportDocument.Database.Tables.GetEnumerator();
while (er.MoveNext())
{
        Table tb = (Table)er.Current;
        if (tb.Name != "Calls")
        {
                        TableLogOnInfo tableLogonInfo = tb.LogOnInfo;
                        tableLogonInfo.ConnectionInfo = connectionInfo;
                        tb.ApplyLogOnInfo(tableLogonInfo);                                                
        }

Does not work, the tables act as if they are empty...

To make it work now, I need to create a dataset for each additional table and I hope to find a way around since my dataset is a 'select * from tableName'

Now, as soon as I don't pass the dataset to my calls table, the other tables work and conenct to the right db. Almost looks like if the push and pull are exclusive.

So, I am wondering if I can use the push method (see above) for my Calls table and just change the connection info for the other tables? Hope it makes sense, and if you need any more info, please let me know.

Any help much appreciated.

Thanks

Cheers

[Former Member · Accepted solution]

Come on, could it be that nobody has naything to say about this issue?

Am I making an obvious error?

Should I post more information about the isuue?

Thanks in advance for the daring ones!

[former_member183750 · Accepted solution]

There is a number of good threads on these forums re. passing of datasets to a report at runtime.

As an example see:

https://forums.sdn.sap.com/click.jspa?searchID=18774692&messageID=6485631

where you will find [this|https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/401c4455-a31d-2b10-ae96-fa57af5aec20] link particularly useful.

There a re also a number of good troubleshooting threads in these forums, so again do try to search the forums, should you run into issues such as no data appearing in your report once you've passed a dataset to it.

Ludek

[Former Member]

Thank you for the posts, I read the doc, it is very informative but I still don't see the solution to my problem.

btw, you are right Ludek, I want to 'get data for two tables from a database and from a dataset to the third table', with one caveat, all tables are linked.

Since I am in no way a CR expert, let me paddle back a bit.

In our app, we let the user define a query on our main Calls table.

What I want is to run a report against this query.

So, what I do is push a DataSet to the report's Calls table

Now, the report also has other tables that are linked to this Calls table (hence, the sub report option does not work!) and I would like not to push (to get the best performance) but pull the data for the other tables.

From what I read, this seems to be the right way to do it, am I right?

Also, I tried already to set the connections at the Table level without success, see below:

// 1- Extract Connection info
                // server, database etc...

                // 2- Create and load the report doc
                m_reportDocument = new ReportDocument();
                m_reportDocument.Load(tmpFileName);                
                // 3- Create an oleConnection
                m_oleConn = new OleDbConnection("Provider=SQLOLEDB;" + conStr);                
                // 4- Create the DataSet and fill it with the oleAdapter and oleConnection
                m_dataSet = new DataSet();
                m_oleAdapter = new OleDbDataAdapter();
                m_oleAdapter.SelectCommand = new OleDbCommand(selectString, m_oleConn);
                m_oleAdapter.Fill(m_dataSet, "Calls");                
                // 5- Set the report's data source to the newly created and filled DataSet             
                m_reportDocument.Database.Tables["Calls"].SetDataSource(m_dataSet);

                ConnectionInfo connectionInfo = new ConnectionInfo();
                connectionInfo.ServerName = server;
                connectionInfo.DatabaseName = database;
                connectionInfo.UserID = username;
                connectionInfo.Password = password; 

                foreach (Table tb in m_reportDocument.Database.Tables)
                {
                    if (tb.Name != "Calls")
                    {
                        TableLogOnInfo tableLogonInfo = tb.LogOnInfo;
                        tableLogonInfo.ConnectionInfo = connectionInfo;
                        tb.ApplyLogOnInfo(tableLogonInfo);                        
                        bool cn = tb.TestConnectivity();
                    }                   
                }

Of course, I only know the user query at runtime.

Is my approach of using a dataSet for Calls reflecting the query flawed? Is there a better way?

And... still, why does the above code results in my 'other' tables being empty !?

Again, if instead of changing the ConnectionInfo for the 'other' tables I push the right dataset, it all works.

Tigrane

[Former Member]

Thank you for you response, I read the documentation and researched the threads, but... could not find the answer to my particular problem.

So I wrote the simplest code to come up with an example.

I have a rpt file with 3 tables: Calls, Phones and Subscribers.

In my report preview project, I need to push data into the calls table and I just need the other tables to access the db.

If I run the code below, just changing the DataSourceConnections for the report document, it works, but I get my whole table into Calls.

When I say it works, I mean the Phones and Subscribers tables also have data.

Note the line that is commented out on step 5 ? Well, if I uncoment this line, then my Calls get the right subset.

However Phones and Subscribers are then empty unless I push data to them using a DataSet.

Why is that? Shouldn't I be able to push data to Calls and have Phones and Subscribers pull data using the DataSourceConnections?

It is like if once I push data to one of my table, the pull does not work anymore, could it be?

I tried to build my rpt with an xsd schema for Calls and a different datasource for the other tables, no difference...

Thank you for taking a look!

Cheers

// 1- Extract Connection info
                // server, database etc...

                // 2- Create and load the report doc
                m_reportDocument = new ReportDocument();
                m_reportDocument.Load(tmpFileName);                
                // 3- Create an oleConnection
                m_oleConn = new OleDbConnection("Provider=SQLOLEDB;" + conStr);                
                // 4- Create the DataSet and fill it with the oleAdapter and oleConnection
                m_dataSet = new DataSet();
                m_oleAdapter = new OleDbDataAdapter();
                m_oleAdapter.SelectCommand = new OleDbCommand(selectString, m_oleConn);
                m_oleAdapter.Fill(m_dataSet, "Calls");                
                // 5- Set the report's data source to the newly created and filled DataSet             
                //--------> below is the line...
                //m_reportDocument.Database.Tables["Calls"].SetDataSource(m_dataSet);

                // Change my connections
                for (int i = 0; i < m_reportDocument.DataSourceConnections.Count; i++)
                {
                    m_reportDocument.DataSourceConnections<i>.SetConnection(server, database, username, password);
                }                
            

[former_member183750]

Hmm, so if I understand this, you want to get data for two tables from a database and from a dataset to the third table(?).

If that is the case, you may want to think about using subreports. E.g.; say you have a table that has logos and that is the one you want to use a dataset for, create a subreport and pas the dataset to it.

If you do need to actually have a dataset and two tables, you will get a performance hit in that this will force Crystal Reports to perform the SQL locally. E.g.; no server side data processing will take place. The code would be something like this:

'pass in the dataset instead of a db connection

crReportDocument.Database.Tables(0).SetDataSource(dataSet.Tables("NAME_OF_TABLE"))

Then you will have to set the logon at the table level for the other two tables:

crReportDocument.Database.Tables(1).Location = "DatabaseB.dbo.Customers";

crReportDocument.Database.Tables(2).Location = "DatabaseB.dbo.stuff";

Ludek

[former_member183750]

The solution in this thread may also help: