Using one parameter to select multiple values

Hi,

I am trying to get values within a database column to display through one parameter that is selected on my report. I have been searching the internet for help, and there is mention of a loop array. I have now idea on how to go about using an array.

If anyone could offer me some advice, I would really appreciate it.

Thank you.

Sorry, but I don’t understand. Perhaps it’s a lack of caffeine ingested this morning but I can’t make hide nor hair of what you’re trying to do.

Perhaps a sample of the data you’re searching through as well as the parameter you’re passing, and we can figure out what exactly you need…

what does the table look like? which column are you looking at? how do you want the parameter to be used?

as for “loop array” please put all thoughts of this out of your mind

Hi All,

Sorry about the vagueness of my questions.

I need my report to display particular data stored in the database under a particular location, and the parameter I would use is based on that locations number, so in this case, the locationtype column = 21, which is for the location of Hertfordshire storage.

So when the parameter is used it will select any column data that had the locationtype of 21. But for my case the location of Hertfordshire storage as been added 4 times under four different locationtype numbers, so what I am trying to do is, when the parameter is selected, it displays all data related to those four locationtypes.

Does this make more sense?

how is the query going to know what those four numbers are?

I think what he’s trying to do, Rudy, is a cascading search.

(I’m going to spitball this, both because I dont have the table schema… cough , and because my brain hasnt fully engaged at 5:30 in the morning… :P)

Something along the lines of

SELECT somefields FROM mytable WHERE mytable.name IN (SELECT name FROM mytable WHERE locationtype = 21)

Hi

Thanks for your responses.

I know what the different numbers are, ie 23 56 89 154, so all I want to be able to do is have it so when a location parameter for any of these numbers form the locationtype column are selected, i.e. 56, it displays data from the other three numbered columns too. Does that makes sense?

You’ve got a few options as I see it:

  • hard-code it in whatever code calls the query
  • have a table that links the multiple site codes together
  • fix the data so that one location has one code.

It… sort of makes sense. But are these numbers fixed, or is it a case of ‘In THIS case the numbers are…’?

Again, it would be helpful to see the schema and/or the data/expected output

It is just a case of:

Select {tablevalue} where locationtype =[23, 56, 89, 154]

But it doesn’t work like this in crystal reports, so this is why I think I need to create a looping array, but just don’t know how to write it.

This is the IN keyword in most SQL interactions.

SELECT {tablevalue} FROM table WHERE locationtype IN (23,56,89,154)

yes, but Crystal report doesn’t allow that. This is why I am stuck and was looking into arrays, which can look through each value but I just don’t get how to write it

https://stackoverflow.com/questions/6788743/in-operator-in-crystal-report ?

Certainly seems to. But i’m afraid i’m not familar with crystal reports…

1 Like

I think i did try that. I’ll have another go, and see where I get to and let you know.

Thank you for your assistances, much appreciated