cancel
Showing results for 
Search instead for 
Did you mean: 

get and set sql query on Crystal 2008

Former Member
0 Kudos
62

Hi,

i'am developing an application that uses Crystal 2008 reports and i need to filter results using any SQL Query...

Ex:

select * from products where productID in (select distinct produtctID from sales)

I have tried to change the sql query but that solution doesn't work...

Dim objDS As System.Data.DataSet

Dim objSqlConn As System.Data.SqlClient.SqlConnection

Dim lngTimeOut As Long

Dim SQLAdapter As System.Data.SqlClient.SqlDataAdapter

objSqlConn = New System.Data.SqlClient.SqlConnection(m_strConnectionStringNET)

objDS = New System.Data.DataSet()

SQLAdapter = New System.Data.SqlClient.SqlDataAdapter(strSqlQuery, objSqlConn)

SQLAdapter.Fill(objDS)

m_objReport.SetDataSource(objDS)

Anyone can help me to sql and get SQL Query?

Thanks

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

HI,

well I need some help here:

Do you want to say that the report is designed and now you are trying to change the sql query to get different data in report?

If that is so, then you can get the SQL query that the report is using/ referring to, using RAS sdks but cannot change it.

If you mean that, do you need to pass the query to the report through code then its not required as Crystal would do it internally.

I really apologize if I misunderstood you!

Regards,

AG.

Former Member
0 Kudos

Hi,

i am trying to get the SQL Query, change it, and send it back to the report...

i am using VB.net...

Thanks

former_member183750
Active Contributor
0 Kudos

You say:

"I have tried to change the sql query but that solution doesn't work..."

- but what happens? Errors? Behavior? Etc.?

You should be able to create a dataset off of the SQL as you are doing, but the resulting dataset must absolutely match the data the report is expecting. E.g.; if you change a field type from number to text, the report will fail. See [this|https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/13270] [original link is broken] [original link is broken] [original link is broken]; blog re. troubleshooting issues with CR and .NET datasets.

Ludek

Former Member
0 Kudos

Hi,

thank for the answer.

I changed the line:

SQLAdapter.Fill(objDS,"MyTable")

and the reports now filter the data...

But i have a lot of reports that are using more than one table...

Can i keep some tables and specify only the one i need to filter in dataset or i have to fill dataset with them all?

0 Kudos

Hello,

There is no option to alter the SQL in code now. You can get the SQL but you can not write it back. Using record sets is one way but there are limitations as to the amount of data the system can handle. More than 20,000 rows of data is about the max but this of course all depends on the size of the report and other functions. Using one record set is recommended so it would mean having to re-build your reports.

You best option is to use the Record Selection Formula API's to add data filtering to the report. Test it in the Report Designer and then emulate the same steps in code to add filtering. Or convert them all to use Stored Procedures, CR simply runs the SP and waits for the data to return, what you do in the SP can be all controlled by you, again the fields must not change or the reprot will fail but filtering can be modified.

Another option is to re-build all your reports and use Command Tables, you can alter the SQL of those in code but if fields are used in the report then those fields must stay in the SQL or the reports will have problems.

Thank you

Don

Former Member
0 Kudos

Thanks,

I'm affraid my problem doesn't have a solution...

I have a report... have 2 tables:

SALES PRODUCTS

Now i need to filter using another table, but the report doesn't have this table...

COLOUR

select * from SALES inner join PRODUCTSon (PRODUCTS.productID = SALES .productID)

where PRODUCTS.COLOR in (select colour from COLOURwhere colour.name like 'a')

this is my big problem!

how can i filter using a table not present in the report...

and this can be any table!

i tryed

-> datasets (works, but 20.000 rows limit)

-> get/set Query (not possible)

-> selection formula (table not exists, error)

-> SQL conditions (only returns one row)

-> ....

Very Thanks!

former_member183750
Active Contributor
0 Kudos

You are correct, this will never work as is.

What about creating a dataset off of your SQL and passing the dataset to the report? Now, you have to remeber that the report will have to be built and the dataset will have to provide the data in the format the report expects (field types, etc), but this may be the way to go. For more info on CR and datasets , see the following:

https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/401c4455-a31d-2b10-ae96-fa57af5a...

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/2091d0c3-da1d-2b10-22be-a3426b18...

[This|https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/13270] [original link is broken] [original link is broken] [original link is broken]; blog may also be of help eventually.

Ludek

0 Kudos

Hello,

You may want to post your report design problem to the Report Design forum also.

How about if you add a subreport and use the COLOR table as it's data source? You can then link on the subreport fields and using a formula get the subreport field info and use it in the main reports record selection formula.

Even easier is why don't you just add the COLOR table to the main report? You don't have to drop any fields from it into the report just use it in the selection formula as a parameter.

I highly suggest you post your question to the Design forum and then can help you sort your data source needs out.

I would move this post but it's getting long so better for you to post anew.

Thank you

Don

Former Member
0 Kudos

Thanks for yout answers.

Don says about use of dataset that: "More than 20,000 rows of data is about the max", so that is not a solution for me.

I can't add the table COLOR to the report because it's an example... It can be one or more tables (SIZE, HEIGHT, CATEGORY), and i have more than 300 reports on our application...

Thanks again

former_member183750
Active Contributor
0 Kudos

Perhaps the RAS SDK is for you. You can add tables at runtime using the RAS report creation APIs. But if that is not good either, as much as I don't want to, I'd suggest that it may be that Crystal Reports is not the solution for your present needs(?). I'm basing this on the fact that we've provided a number of options, none of which appears to be fulfilling the requirements of your project.

[This|https://www.sdn.sap.com/irj/scn/wiki?path=/display/bobj/netBusinessObjectsEnterpriseSDKSamples] wiki list sample RAS applications, which may be a good place to start. Other RAS resources:

https://boc.sdn.sap.com/developer/library

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/10b840c0-623f-2b10-03b5-9d191386...

https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/b050afe0-2fa5-2b10-658d-98f214ba...

Ludek

daniel_paulsen
Advisor
Advisor
0 Kudos

You can design your report off of a command table.

the RAS SDK has access to this object and you can completely customize the query using:

CommandTable.CommandText

this should work providing the resultset matches the schema of the table(s) in the report. if not, you can also add/remove columns (or tables) using the RAS SDK.

ie:

ReportClientDocument rcd = new ReportClientDocument();

rcd.Open(pathtoreport);

CommandTable tbl = (CommandTable)rcd.DatabaseController.Database.Tables[0];

tbl.CommandText = "Select * from Customer";

Dan

Former Member
0 Kudos

Thanks for yout answers,

i'm using VB.net and crystal 2008...

i'm having some problems casting

Dim ct As New CrystalDecisions.ReportAppServer.DataDefModel.CommandTable

ct = DirectCast(m_objReport.ReportClientDocument.DatabaseController.Database.Tables(0), _

CrystalDecisions.ReportAppServer.DataDefModel.CommandTable)

I get the error :

Unable to cast COM object of type 'System.__ComObject' to interface type 'CrystalDecisions.ReportAppServer.DataDefModel.CommandTable'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{EC2F9A25-E94D-40C0-BF21-3C9D748CC983}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

Thanks for your help

daniel_paulsen
Advisor
Advisor
0 Kudos

Is the first table in your report a commandTable? (ie did you redesign your report using a command or did you simply add the table from the list in your datasource connection. You cannot cast a table object as a commantable unless it is acutally a commandtable.

Dan

Former Member
0 Kudos

Hi,

You are right... this is not a command table...

I Have a lot of repots and i need to keep them with no changes, and alter the sql query when the report is being generated...

Very thanks

Former Member
0 Kudos

Hi All,

Please forgive me if I am wrong or this post is irrelevant.

It seems that we are using datasets. If that is true then when canu2019t we just populate the dataset with data and save a schema of the dataset, use it to create reports and then pass the same dataset to the report?

I understand that we are referring to a table which does not exist. But we can get the same in dataset right?

Thanks,

AG.

0 Kudos

Hello,

The final option you have is to update your reports or go back to Crystal Report XI R2 and use the RDC, which still allows you to alter the SQL. RDC is no longer shipped as of CR 2008. There is no way to alter the SQL in any of our SDK's now other than using a command table. Which is not our SQL, CR simply passes what's in that UI to the database. CR just expects the resulting data set to be what the reprot was designed with. There is no API to map fields from a database to a Command table nor can it be done in the Report Designer either. Re-creating reports to use Command tables is the only option.

As much as you don't want to make changes to the reports there is no other option. You can not and we will not be adding the functionality to alter the SQL directly back into the reports or in our SDK's.

Your only options are to use filtering in the Record Selection formula to update the SQL WHERE clause. Use RAS to add those "unknown" tables to the report so the record selection formula can filter on those new tables and fields you just added.

Thank you

Don

Answers (0)