Skip to Content
0

ReportDocument SetDataSource does not change SQL query

Apr 04, 2017 at 07:11 AM

120

avatar image
Former Member

VisualStudio 2016, a HelloWorld app. Using CrystalReport Engine v13.

A report template file. In CR Designer, go Database/ShowSQLStatement where it shows a rather massive SQL query, according to the table definitions inside the report. All correct here.

At runtime, my app loads a template from the file, then (for a sake of testing) does SetDataSource(new DataSet());

The displayed report still shows the result according to the 'embedded' SQL query. There appears to be no way to change/reset the query.

Help!!!

Thanks

Michael

10 |10000 characters needed characters left characters exceeded
Former Member

mysteriously, the code kind of works if we use SqlDataAdapter to initialise the DataSet.

........

var dbDataAdapter = newSqlDataAdapter(queryString, dbConnection);

dbDataAdapter.Fill(dataSet, "LOGDATA");

reportDocument.SetDataSource(dataSet);

-------------

Previously we initialized the DataSet like following:

dbTable.Load(dbCommand.ExecuteReader());

dataSet.Tables.Add(dbTable);

------------

The content of the dataset is the same, in both cases. Why one approach works and another does not? Is there a "proper" way?

0
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Best Answer
Don Williams
Apr 21, 2017 at 08:32 PM
0
Share
10 |10000 characters needed characters left characters exceeded
Don Williams
Apr 04, 2017 at 07:33 PM
0

Hi Michael,

I changed the Tag to CR for VS.

The reason why is Datasets use a different driver and doesn't always support full SQL. Using the MS to SQL Adapter does use full SQL.

Don

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Don

The SqlDataAdapter approach works when the report is based on a single table, but still struggling with a more complex multi-tables reports.

To describe the issue:

A report combines columns from 3 tables, T1 T2 and T3. The tables relationship is hardcoded by the template.

At runtime, we want the report to be initialised by an SQL query which is virtually identical to the 'embedded' query, but has extra parameters in its WHERE token.

Despite going through a lot of information from a lot of people providing hints and tricks on the same or related issues, we are not able to see how the SqlDataAdapter can be utilised for this scenario. Tried a few things but still no luck.

Any help is truly appreciated.

Thanks

M

0
Don Williams
Apr 11, 2017 at 02:42 PM
0

Work flow would be:

Open report:

Log onto the Database server or equivalent, to get the SQL, reason is CR DB connection and SQL generator is dynamic so the SQL will adjust depending on the DB Source.

Get the SQL, convert/copy the SQL from the Report

Make a connection to the DB Server and use the SQL from the Report to query for the Dataset.

Set location of the report to the Dataset.

KBA 2281780 has a sample app that can do all of this for you, run in debug mode and single step through it so you can see what is happening.

https://launchpad.support.sap.com/#/notes/2281780

Don

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Don

Can't get to the KB article because we don't have S-UserID.

[Spoke to local folks here in Aus, but as we are not SAP users they can't issue me S-UserID. Huge redtape.]

Any chance you can assist with accessing the KB info? Anything from my side I can do to ease the pain?

Appreciated

M

0
Don Williams
Apr 12, 2017 at 01:58 PM
0

That is why I included the KBA number, Search for the number and it will come up.

Share
10 |10000 characters needed characters left characters exceeded