Possible to programmatically create a command object?

[rod_weir]

Hi all,

Is it possible to programmatically create a command object into an existing report? (Visual basic .net)

I would like to do so as a filtering mechanism.

The report is based on a database query.

I would like to create a command object and inner-join it to the query, and use the SQL of the command object to do the filtering.

I want to use a command as the filtering because the SQL comes from an application that has it's own filtering mechanism that builds up SQL. It may include database fields that are not necessarily part of the original report table (query)

Is it possible? Any ideas?

Many thanks,

Rod

[Former Member · Accepted solution]

Rod,

I'm not 100% sure what you're trying to do... But I have an idea.

There is a very cool thing about Commands and their use of parameters. What CR does, it uses a simple parameter/value swap. This makes it very easy to write dynamic SQL without actually having to write dynamic SQL.

For example:

Say you have a report and you want to give the option of having FieldABC = 123 OR FieldXYZ = 789

You can set up your parameter like this:

Value1: FieldABC = 123

Description1: Filter on FieldABC

Value2: FieldXYZ = 789

Description2: Filter on FieldXYZ

You can then write you command like this...

SELECT * FROM TableName WHERE {?ParameterName}

So now when the the report runs and the user selects "Filter on FieldXYZ" as their parameter selection, the SQL that gets sent back to the database will look like this...

SELECT * FROM TableName WHERE FieldXYZ = 789

HTH,

Jason

[rod_weir · Accepted solution]

Hi Hoa and Jason,

Thanks for your replies. I'll look into both of these.

The reason why I'm looking into dynamically (progammatically) creating a command object is so that I can use it as a report filter.

The reports I'm making are for an application. The application has it's own search screen that is being used as a filter. I'm looking for a way to pass the SQL from this search screen through to the report. In the past, this search screen would transform the search parameters into Crystal Reports "RecordSelectionFormula" code and set these just before displaying the report. The problem with this approach is that the data source for the report needs to contain all of the fields that the search screen can generate filters for. This means having to use a datasource for the report that is overkill for the actual fields that are displayed on the report. In other words, you need to have the underlying query return (or at least expose) many database fields that are not actually used in the report - they are only there so the "RecordSelectionFormula" can work.

Why the command?

If I can dynamically create a command object and set its SQL, I can inner join it to the main report datasource and it will act as a filter. The main datasource contains all of the display fields, and the command is simply a filter mechanism.

At this stage I'm looking into hard-coding into every report an additional datasource that is based of an XML schema, with just one field - the primary key for the datasource. I can inner-join this at design time, and then programmatically asisgn it a dataset with the filtering data I require.

If anyone has a better way, I love to discuss it. I could be making a lot of extra work for myself here?

Thanks,

Rod

Edited by: Rod Weir on Feb 14, 2011 10:40 PM

[Former Member · Accepted solution]

Rod,

Kind of seems like you are taking a round about approach to this... And your approach may cause you some performance problems if your main query has any complexity at all.

The problem is that as soon as you add a Command to the mix, CR stops pushing any of the processing back to the server. So while the Command would limit the records shown on the report it will not limit the number of records being pulled from the database. Every record from every table will be brought in and all of the processing will happen client side.

If your application is capable of generating the necessary SQL to create a workable where clause it should also be able to generate an entire SQL statement. Just hard code the bulk of the SQL (the part you currently have) and make the rest dynamic as per your current plan.

From there you can simply pass the entire SQL statement as a single string type parameter.

Basically the Command itself would simply be 1 single parameter. And the value passed to the parameter would be an entire SQL statement.

You'll just want to make sure the field list is identical no matter what the user selects in the application... Which doesn't sound like an issue based on your last post.

HTH,

Jason

[rod_weir · Accepted solution]

Hi Jason,

Thanks for this info. I didn't realize the client/server processing ramifications of commands. That's a definite show-stopper for my plan.

The datasource that the reports are based off are basically unfiltered database views - they return everything! From what you're saying, adding a command will return the entire query client side, and then do the inner join - is this right?

By basing a report off of a dynamic command as you suggest (the parameter being an entire SQL statement supplied by the application), this means that at design time you have no record set to build and design the report from.

How do you build and design a report if the data required for it (field definitions etc) is only supplied at runtime?

Thanks,

Rod

[rod_weir · Accepted solution]

OK, I think I just answered my own question about 2 seconds after posting.

You would hard-code the SQL into the command at design time (or specify the SQL parameter) whenever the report is run to return all of the fields.

[Former Member · Accepted solution]

Hey Rod,

From what you're saying, adding a command will return the entire query client side, and then do the inner join - is this right?

Yes that is correct.

How do you build and design a report if the data required for it (field definitions etc) is only supplied at runtime?

When you click OK to create the command, you will be prompted to enter a parameter value before the Command is accepted. You would simply paste in a sample SQL query at that time. From there CR will test the SQL and pull in the results. That will give you the necessary data to build the report.

Jason

[Former Member · Accepted solution]

You don't necessarily want to

hard-code the SQL into the command at design time 

as in add the actual query to the Command. Just add the 1 string parameter. You would actually add the SQL by manually pasting the query into the prompt.

For example:

1) Start a new blank report based off of the SQL Server AdventureWorks database.

2) Add Command and create a new parameter called SQL

3) Double click it over to the Command window so that the command itself looks like this

{?SQL}

Nothing else...

4) Click OK and it will prompt you for a value for {?SQL}... type or paste in *SELECT * FROM Sales.SalesOrderHeader*.

The SQL will be accepted and you will see all of the rows from the SalesOrderHeader table available for use on the report.

Jason

Edited by: Jason Long on Feb 14, 2011 4:33 PM

[rod_weir · Accepted solution]

Thanks, working on this now.

Having trouble creating a command with no actual text and just a parameter. Won't let me save it until something is in the main command window.

Ah, thanks for the expanded edit. Makes sense now.

Edited by: Rod Weir on Feb 14, 2011 11:39 PM

[rod_weir · Accepted solution]

Getting this....

-

---

Crystal Reports

-

---

Failed to retrieve data from the database.

Details: ADO Error Code: 0x80040e10

Source: Microsoft OLE DB Provider for SQL Server

Description: No value given for one or more required parameters.

-

---

OK

-

---

Using CR 2008 Developer.

Seems like this method might have its quirks...

[rod_weir · Accepted solution]

OK, sorry, got it sorted. I misread your instructions.

Many thanks for your help Jason - really appreciated. Looks like this technique is a winner.

Best regards,

Rod

[Former Member · Accepted solution]

Actually I know what problem is... The parameter is only accepting the 1st line of your SQL. Try pulling out all of the line feed/carriage returns. Like you're writing the entire statment on a single line in notepad.

[rod_weir · Accepted solution]

Thanks again Jason. It's all working now, and I have a couple of hundred reports to update to this new data access method. Should be fun....

This is definitely the way to go when bundling CR with an application.

Best regards,

Rod

[Former Member]

Maybe you can insert a sub-report, and use sql command in that report which won't join with the main report.

Thx, Hao