criteria to used in an impromptu report 1

[damzi]

Hi everyone - am looking for a solution to an urgent issue I have been trying to resolve in an Impromptu report. I have several - almost 20 prompts - in my impromptu report. All of the criteria, IF ENTERED in the prompts should be satisfied - else it should be ignored in the report. I cannot use power prompts or SPs in this reports, so my options are very limited here.

I have been trying several combinations of the conditions, but am just not able to get anywhere - since all the rows get filtered out (with the AND clause used to include all conditions) if any one prompt is ignored.

Thank you

 

[iguanadon]

Maybe I'm off track and thinking too simplistically, but if you use the LIKE command and default in the wildcard "%" allowing the users to fill in only the prompts they wish to use. If some of them are date fields or dollar values, you can set them to extreme dates for >= or <= so that everything fits that particular criteria item.

I have a similar report and that's how I got around it.

Scott

 

[DoubleD]

20 Prompts?!?, sounds like you should be rethinking your design. Are you trying to answer every question in your environment with only one report? If so, you actually end up making it more cumbersome for your users. I realize the users probably told you they need a report that does x, y, z and the kitchen sink. Your job as a report designer is to help them understand how to use their data more intelligently.

Pain is stress leaving the body.

DoubleD

 

[iguanadon]

This is a sample of the prompt for a report I have. I feel inferior, it only has 13 prompts in all.

Enter Start Date Less Than or Equal To:
2006-10-01

Enter End Date Greater Than or Equal To:
2001-10-01

Contract Type:
%

Project Number:
%

Customer Number:
%

Funding Source:
%

Project Manager Last Name:
%

Contract Value Greater Than or Equal To:
0

Contract Value Less Than or Equal To:
500000000000

Funding Value Greater Than or Equal To:
0

Funding Value Less Than or Equal To:
500000000000


Then based on which criteria (or multiple criteria) they want to search on, they can. For instance all projects managed by Smith with a Funding value greater than $100,000. Or all projects for customer number 12345 with a contract value less than $250,000. etc, etc

 

[damzi]

Scott,

So what you are saying is that if I default the prompt to % and code it to OR in my script so it is not included in a report, if the user has not specified the value for the same. Cld you please send me the script used for the same. I guess I am wondering too if this solution is too simple - I have 12 criteria in my filters and the user have the option to select from any of these. So if I have to inlude the same in a filter - I need an AND clause if the user has filled it out, and a OR clause in the filter if the user has left it blank.

Thanks so much for your fast response. Have a wonderful a swell weekend !!!

- Di

 

[damzi]

Hey am sorry - did I say 12 prompts - well there are 25 in all - did not wish to shock anyone here initially. This report isnt going to be too preety! - Di

 

[damzi]

Thanks a ton Scott - that almost looks as if you were anticipating my reply - Thanks so much for the lightening response !!!

 

[iguanadon]

Damzi, actually they are all AND clauses connected in the filter and by using LIKE with the wildcard "%" it flows smoothly. I wish I could cut and paste the filter, but it won't let me.

Here is part of the sql code after I ran it for a single project.


where T1."PROJ_F_TOT_AMT"<=500000000 and T1."PROJ_F_TOT_AMT">=0 and T1."PROJ_V_TOT_AMT"<=500000000 and T1."PROJ_V_TOT_AMT">=0 and substring(T1."PROJ_MGR_NAME",1,20) like '%' and T5."UDEF_ID" like '%' and T1."CUST_ID" like '%' and T1."PROJ_ID" like '08784' and T1."PROJ_TYPE_DC" like '%' and T1."ORG_ID" like '%'

 

[damzi]

DoubleD - I understand your anticipation. But in this application my hands are tied - I guess that isnt too difficult to follow - we all go through those circumstances.

 

[iguanadon]

One last piece of advice. Build the filter one element at a time, testing after each one. Depending on the use of wildcards, greater than or less than items, dates, etc, you may build one incorrectly or filled in improperly and if you try to build all 25 at once you won't know which one blew it up and will have a heck of a time figuring it out.

Good luck to you.

Scott