Parameter Driven SQL

[baycolor]

Hi,

I'm using CR XI, XI server and a Teradata database.

I know I could do this with a stored procedure but I'm wondering if there's another way to do this in CR.

This system I'm reporting from has the following five tables account, team, invoice, associate and internal opportunity.

All of the tables have the following tracking columns in common: creation_timestamp, created_by, last_modified_timestmap, last_modified_by.

I'm constantly getting asked...
- When this account record created and by who.
- When this team record created and by who.
- When was this invoice record last modified and by who.
- Etc...

I would like to create a report that has a prompt that asks for what table they want to query. And prompt for (in the case of the account table) an account number. If they had selected the team table the user would have provided a team number in the team prompt.

Depending on what table the user specified a different query would have to be run.

In the case of the account table:
SEL
account_no
,creation_timestamp
,created_by
,modified_timestamp
,modified_by
FROM account_table
WHERE account_no = <prompt account_no value>;

In the case of the team table:
SEL
team_no
,creation_timestamp
,created_by
,modified_timestamp
,modified_by
FROM team_table
WHERE team_no = <prompt team_no value>;

Is there a way that I can setup a CR to run different completely different SQL based upon the parameters that were filled in?

NOTE: These tables don't necessarily join together so its not like I can create one SQL view with a bunch of joins(example internal_opportuinty doesn't join to any of the other tables mentioned).

Thanks in advance.

 

[synapsevampire]

Sure, use the Add Command and 2 parameters.

You can do as many as required, replace whatever granularity is required with the parameters within the SQL.

-k

 

[lbass]

I think you could set this up using a union query in the add command area, like:

SELECT 'Account' as type, account_table.`account_no` as ID,account_table.`creation_timestamp`,account_table.`created_by`,account_table.`modified_timestamp`,account_table.`modified_by`
FROM `account_table`account_table
WHERE account_table.`account_no` = {?ID} and
'Account' = '{?Table}'
UNION ALL
SELECT 'Team' as type, team_table.`team_no` as ID,team_table.`creation_timestamp`,team_table.`created_by`,team_table.`modified_timestamp`,team_table.`modified_by`
FROM `team_table`team_table
WHERE team_table.`team_no` = {?ID} and
'Team' = '{?Table}'
UNION ALL //add next table info, etc.

You would create the parameters in the Add Command screen. I tested this and it worked. You would need to adapt the syntax to your datasource, of course.

-LB

 

[bmarks]

There is another option. I don't generally like using subreports when I can avoid them, but the client I'm working for currently, their previous Crystal developer used them in some interesting ways.

In this case, you could setup a subreport for each of those choice. Set up each subreport in it's own section and suppress the section if it's not asked for in the parameters.

 

[baycolor]

Thanks everyone for the info I'm going to try it a couple different ways.

Synapsevampire,
Regarding your response I used Add Command to create two parameters and they show up in my main report. Do you have any tips on how these are used? Or can you point me to something? I've looked in the CR help, this site, and the BO site and haven't been able to find a lot of documentation on this functionality. I see the Commands but when the report is run how does one SQL run instead of the other? And for that matter how do you make any of them run?

lbass,
Regarding your response the only additional thing I believe I will need to do is CAST some values to the same type - some of the tables don't have the same type of keys. account_no is an integer, team_no is CHAR field. I guess the only questions I have are the same ones that I documented above to synapsevampire. Any info would be great.

bmarks,
Thanks for the info. I actually implemented this way temporarily - I posted trying the questions above to try and avoid the subreports.

 

[lbass]

The parameters in a command work the same way they do in a main report, except that command parameters only accept one value (although with a string parameter you could respond to a prompt with a string of values enclosed in parens). String parameters in a command should be enclosed in sinqle quotes (unless the intent is to respond to a prompt with a string of multiple values).

Note that the service pack #1 for XI corrected a problem with multiple command parameters where if they were added or edited at different times, the command failed.

You are correct that the corresponding fields in the union all statement must be of the same datatype.

bmarks' solution is a good one, also, although you are essentially setting up all of the reports separately.

-LB

 

[bmarks]

baycolor,

I think the solutions from synapsevampire and lbass are basically the same idea. Where I think you might have some confusion (based on your questions), is that you only do one "Add command" (if you do more, then they get linked together after, as opposed to running one or the other). This command will then run whenever you refresh the report. The unions will mean all the statements will run - but only those where the parameter choice of ?Table match will yield data.

The code from lbass in the "Add command" is probably the best solution. I would say do a cast/convert on all fields that are different (that you are then going to treat in th same variable) to CHAR and then work with them that way. Then you can setup a single report with a minimum amount of fields to display.

lbass - thanks for your comments on my solution - but I think I prefer yours, as it's more elegant and should be faster as well.

 

[baycolor]

Thanks everyone for the help!!! I finally got around to playing with this and used the add command option. It worked great!!! Using Add Command with parameters is a really slick feature. I have a ton of other places where I'm going to use this. Thanks again.