Solved

Creating a SQL Command in Subreport that references multi text parameter

Posted on 2009-04-06
8
953 Views
Last Modified: 2012-05-06
I have an application where the user can multi-select a list of items to pass to the Crystal Report file.

Say the user selects Choice1, Choice2, and Choice3.

That is passed to crystal in a formula as "Choice1 | Choice2 | Choice3".

I have that formula as the subreport link to a parameter within my SQL command statement.

I can't get an IN statement to return any results.  I've only been able to get this working with a single select with an =.

Any ideas?  I've tried just about everything.

My latest attempt was taking the original string that crystal receives and converting that using a formula to:
('Choice1','Choice2','Choice3')

The line in the SQL statement of interest is currently written as
COLUMN.FIELD IN {?ParameterName}

I have my share of experience with Crystal syntax quirks...just can't figure this one out.  Thanks for the help!
0
Comment
Question by:kenagy18
  • 3
  • 3
  • 2
8 Comments
 
LVL 34

Expert Comment

by:James0628
ID: 24083435
Well, the problem is that SQL just sees "Choice1 | Choice2 | Choice3" as a single string value, not a list of values.  You can write some SQL code to break the string down into separate values and put those in a temp table or table variable.

 But there may be a simpler option.  Let's say that you have CR produce a string like:
"Choice1","Choice2","Choice3"

 In your WHERE, you could have something like:
CHARINDEX ('"' + TABLE.FIELD + '"', {?ParameterName}) <> 0


 What that does is put double-quotes around the field and then look for that in the parameter.  By putting each value from CR in quotes, and putting quotes around the field in the db, you're isolating the values, so you don't have to worry about incorrect matches (assuming that the field will never contain double-quotes).
 So, for example, if the field said Choice1, it would look for "Choice1" in the parameter string and return its starting position (1), which is not 0, so the test passes.  But if the field was Choice, it would look for "Choice" (including the quotes), and that is not in the parameter, so the test would not pass.  If we did not add the quotes and just looked for Choice, it would find that and think that it had a match.

 I used double-quotes in the string produced by CR so that you don't have to deal with trying to get single-quotes into a string literal in SQL.

 James
0
 

Author Comment

by:kenagy18
ID: 24083503
I tried what you suggested and got the error in Crystal Reports that CHARINDEX is an invalid identifier.

Here is part of my WHERE statement

WHERE
      SAMPLE.sample_number = TEST.sample_number
      AND TEST.test_number = RESULT.test_number
      AND CHARINDEX ('"' + SAMPLE.FORMULATION + '"', {?FORMULATION}) <> 0

My other concern is that CHARINDEX is a substring search?  So a user selection of CHOICE1 would return data that has a value of CHOICE10.  Do I understand that correctly?
0
 
LVL 34

Expert Comment

by:James0628
ID: 24083568
CHARINDEX is an MS SQL function.  I don't use CR "commands", but if you're actually writing SQL code (which the use of WHERE would seem to suggest), then I would expect functions like CHARINDEX to work.  I'm really not sure why you got the error.


 Yeah, it's a substring search.  CHOICE1 would not match CHOICE10, because of the quotes that we're adding.  CR would pass CHOICE1 as "CHOICE1", and the test adds quotes around the field, so it would end up looking for "CHOICE10", including the quotes, which does not match "CHOICE1".  By putting quotes around the values from CR and around the fields, we are forcing the test to only find exact matches (again, assuming that the values will never contain quotes).

 But this may all be moot if we can't get CHARINDEX to work in the command.

 Can you post your command?  Maybe it will help if I can see the whole thing (Maybe :-).

 James
0
 

Author Comment

by:kenagy18
ID: 24083623
Thanks for the clarification on the CHARINDEX function.  Here is my command statement:

SELECT DISTINCT

SAMPLE.sample_number S_Sample_Number,
SAMPLE.LOT_NAME S_Lot_Name,      
SAMPLE.CONDITION S_Condition,
SAMPLE.TIMEPOINT S_Timepoint,
SAMPLE.X_COMPOUND S_Compound,
SAMPLE.PROTOCOL S_Protocol,
SAMPLE.PROTOCOL_LEVEL S_Protocol_Level,
SAMPLE.FORMULATION S_Formulation,
SAMPLE.product S_Product,
SAMPLE.product_version S_Product_Version,
SAMPLE.product_grade S_Product_Grade,
SAMPLE.sampling_point S_Sampling_Point,
SAMPLE.stage S_Stage,
SAMPLE.spec_type S_Spec_Type,
                                   
TEST.analysis T_Analysis,
         
RESULT.NAME R_Name,
RESULT.ANALYSIS R_Analysis,
RESULT.ORDER_NUMBER R_Order_Number,            
RESULT.UNITS R_Units,
RESULT.FORMATTED_ENTRY R_Formatted_Entry,
RESULT.ENTRY R_Entry,
RESULT.REPORTABLE R_Reportable,
RESULT.REPORTED_NAME R_Reported_Name,  
RESULT.RESULT_NUMBER R_Result_Number,
                                   
condition.display_string C_Display_String,

timepoint.display_string T_Display_String,
DECODE(TIMEPOINT.CALENDAR_DAYS, 'T', TIMEPOINT.NUM_MONTHS, (TIMEPOINT.INTERVAL/86400))  As T_Timepoint,
DECODE(TIMEPOINT.CALENDAR_DAYS, 'T', 'MONTHS', 'DAYS') As T_Units,

stability_level.display_string SL_Display_String,

Dense_Rank() OVER (Partition By Sample.X_Compound, Sample.Protocol, Sample.Lot_Name, Sample.Formulation, Sample.Protocol_Level, Sample.Condition Order By
(DECODE(CALENDAR_DAYS, 'F', (INTERVAL/86400), (NUM_MONTHS * 30.5)))
) As TimepointRank


FROM SAMPLE, TEST, RESULT, CONDITION, TIMEPOINT, STABILITY_LEVEL

WHERE
      SAMPLE.sample_number = TEST.sample_number

      AND TEST.test_number = RESULT.test_number

--      AND SAMPLE.FORMULATION in {?FORMULATION}
        AND CHARINDEX('"' + SAMPLE.FORMULATION + '"', {?FORMULATION}) <> 0

      AND SAMPLE.CONDITION = CONDITION.NAME(+)
      AND SAMPLE.TIMEPOINT = TIMEPOINT.NAME(+)
      AND SAMPLE.PROTOCOL_LEVEL = STABILITY_LEVEL.NAME(+)
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 500 total points
ID: 24084014
A week or two ago, I wrote an example report that takes a multi-value string parameter from the main report, formats the selected values and passes them to the SQL Command-based subreport.  Here's the gist of it:

1)  Create a container report with no data source.  In the container report,  create a multiple value string parameter.
2)  In the container report, create a formula that collects the array of parameter values into a formatted string.  Here's the formula I used:

"'"&Join({?Territory},"','")&"'"

In my case, the parameter values I entered were 1, 2, 3 and 4.  The formula above formats them into a single string as '1','2','3','4'

3)  Create a subreport based on a SQL Command.  In my example, I used the AdventureWorks database that came with SQL Server 2005.  My command is very simple (since it's an example):

SELECT
  *
FROM
  SALES.CUSTOMER
WHERE
  TerritoryID  in ({?Territory})

Please note, when you create a parameterized command, it will ask you for sample parameter values.  They don't need to be valid database values, but they do need to match the datatype and syntax used.  So, in my case, when I was prompted for the sample parameter value, I entered '4'.  Normally, when I need to use a string parameter in a SQL Command, I encapsulate the entire parameter within single quotes.  A common example in many of my current client's reports is: EFFECT_DATE = TO_DATE('{?EFFECT_DATE}','MM/DD/YYYY') .  This doesn't work, however, when you're passing an array of string values into a parameter, which is why I use the formula listed above.

4)  Once the SQL Command has been successfully created, link the subreport to the main report on the Formula field from the main report to the actual Command parameter in the subreport.  Make sure to choose the SQL Command parameter, not the parameter Crystal tries to create during the link process (screen print attached).

I hope this helps!

~Kurt
LinkCommandbyParameter.jpg
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 24084033
FYI - CHARINDEX is a SQL Server Keyword and you're obviously using Oracle (DECODE and Analytic Functions are a giveaway). The comparable Oracle keyword, if you want to try it, is INSTR is the correct keyword.

~Kurt
0
 

Author Closing Comment

by:kenagy18
ID: 31567329
Thank you, I just needed to add parentheses around the parameter within the command statement which I gathered from the way you wrote your command.  
0
 
LVL 34

Expert Comment

by:James0628
ID: 24094601
Kurt,

 Thanks for the part about which SQL he was using.  That was presumably why CHARINDEX didn't work.  He didn't specify so I had to go with the one I was familiar with.  :-)

 James
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now