SQL Expression Help

[Rich Hartnett]

I'm actually creating a report in SQL Report Builder 3.0, so I am not even sure if this question I allowed in here or not, but I figured I would give it a shot. I am very new at SQL so bear with me .............

I have a database that lists data related to several different police departments, but they are identified by a 4-digit code rather than by their name. For most agencies that is no problem, but some have more than one code, and I want the user to be able to filter the reports by a "user friendly" agency name rather than by the code. In the example below, I have the report builder filter parameter set to one specific agency and code, and it works fine.
Screen Shot 11-27-17 at 06.43 PM.JPG

Obviously I am filtering this report to a specific agency whose code in the database table is "0210." This works perfectly, but unfortunately this particular agency actually has records with two "agency codes." Some records are 0210 and some are 0211. As such, I want to write an expression that basically says Berwyn Heights PD = 0210 Or 0211, but that apparently is not the correct way to write it because it fails. (I also tried it as Berwyn Heights PD = "0210" Or "0211", along with Berwyn Heights PD = "0210" .or. "0211", but those didn't work either.)
Screen Shot 11-27-17 at 06.57 PM.JPG


I've searched the internet until my fingers are sore, and I can't find an answer, so I am wondering if anyone here knows the correct way to write this SQL expression?


Thanks,
Rich

[elkton_john]

Re: SQL Expression Help

Dear Rich,

From a SQL perspective, the best way to handle this is to create an intermediate table in SQL server that holds the values. The code column would be the primary key column and the 2nd column the department name. Then create a view joining the main table with the intermediate table on the code column. Optionally create a foreign key in the main table using the code column from the intermediate table as the source. To get the filtered records, run a SELECT statement on the department name column of the view (i.e. SELECT * FROM MyView WHERE DepartName = 'Berwyn Heights'). This will return all records with codes assigned to Berwyn Heights in the intermediate table.

John

ps:

Assuming that the report already has a defined filter that accepts a return of the 'code' values to fill the parameter, click the 'run a query' radio button, use the above select statement but instead of the asterick use the column name for the 'code'. (i.e. SELECT code FROM MyView WHERE DepartName = 'Berwyn Heights')

[Rich Hartnett]

Re: SQL Expression Help

I don't have access to the data tables other than to query the data through SSRS Report Builder for creating reports. The actual database belongs to another agency so I can't edit it or alter it in any way, which includes building any tables.

I thought it was a simple as an expression, but I guess not.

[GGandhi]

Re: SQL Expression Help

I thought it was a simple as an expression, but I guess not.

not that difficult.not using sql server nor report builder, just curious, to play with ( time on hand this morning).

go to the query builder and change the query to
SELECT ..... WHERE agency_code IN (@agency_code) -(meaning - change the equal sign to IN and fill in proper agency code field name)
then on the parameter properties select general and check allow multiple values
then run the report
at the parameter drop-down enter one agency code click return and then enter the second one. run the report.
hopefully it will work for you.

[Rich Hartnett]

Re: SQL Expression Help

Thanks but I'm not trying to run a report with multiple agencies, I want to report for a single agency, but that single agency may have multiple "codes" associated with it. In this particular data table they are called "BeatNames."

Maybe this will make it clearer, as I know I'm probably not explaining it correctly. If you look at the screen shot below, you will see that I set "Berwyn Heights PD" as the lookup list label twice, and each one searches for all records with a particular "BeatName" code of either 0210 or 0211. That works fine if the user selects either one, but what I want is for the user to only have one lookup option for Berwyn Heights, and have it look for either 0210 or 0211 at the same time.

ScreenShot013.jpg


I was hoping that could be done in the expression builder with an "OR" statement. Something like "=0210 OR 0211."

ScreenShot015.jpg

ScreenShot014.jpg

[GGandhi]

Re: SQL Expression Help

i will have to test tonight, since the report builder is at home.
can you post imaginary data at least three or four records and show what needs to be filtered. ( or desired result)
i can build that table into and try filtering that table.

[Rich Hartnett]

Re: SQL Expression Help

Thank you very much for trying to help me with this.

I have attached a small file with about 20 records.

You'll notice that one field is called "BeatName." That is the field I need to filter records on, but my problem is that one single agency can be associated with several "BeatNames."

For example, the Berwyn Heights Police Department handles calls for service in the 0210 or 0211 beat area. As such, when a user wants to filter a report by the Berwyn Heights PD (for example), the report would need to filter all calls with a BeatName of 0210 or 0211 in the record. The thing is that I can easily create a parameter filter right now that says "Berwyn Heights PD = "0210" or another one that says "Berwyn Heights PD = "0211," but that requires the user to perform two separate searches.

What I am trying to do is create an parameter expression on the BeatName field that says Berwyn Heights PD = "0210 OR "0211"

In Alpha 5 that type of expression would look something like this: BeatName = "0210" .or. BeatName = "0211" but apparently that doesn't work in SQL.




CFS Test Data.zip

[elkton_john]

Re: SQL Expression Help

Rich,

I understand what you are trying to do. The report definition has a query filter that uses the parameters that can then be filled at run-time. Do you have access to that filter? If so, I suggest that you copy it and post it here. Also, do you have permission to modify the filter?

John

[GGandhi]

Re: SQL Expression Help

i see the numbers (without the leading 0 ) but how would any one know those two are associated?
(for that matter, it may not be important at all, you just want the logic?)

[Rich Hartnett]

Re: SQL Expression Help

This reply is to both of you. (John & GGhandi)

John I think this is what you are talking about, and if so, yes that is where I am trying to create the expression. If you look at the first and second pics, you will see that as a test, I created two parameter filters, one to search for records with "0210" and a different one to search for "0211," as both of them are related to the same police department (Berwyn Heights).
Screen Shot 11-28-17 at 04.11 PM.JPG Screen Shot 11-28-17 at 04.11 PM 001.JPG

But I don't want the user to have to search twice, so in the pics below, I have only one parameter filter, and in the expression section, I have what I thought was the correct expression to search by both "0210" and "0211," but it gives me an error.
Screen Shot 11-28-17 at 04.19 PM.JPG Screen Shot 11-28-17 at 04.12 PM.JPG


GGhandi, I know the data I sent you only shows "210" or "211" and I'm not sure why, as the leading zero is definitely in the database records, but for testing I'm sure you can use it as is.

[GGandhi]

Re: SQL Expression Help

sorry, i keep going to my steps over and over again.
i tested with your data and added a parameter to allow multiple values and called the parameter select beat
then entered 210 and 211 the report seems to come out alright.
if something pops up i will let you know.

[Rich Hartnett]

Re: SQL Expression Help

I'm confused, did you say you were able to make it work with two different Beat numbers??

[GGandhi]

Re: SQL Expression Help

yes, i will post a screencast soon

[GGandhi]

Re: SQL Expression Help

https://www.screencast.com/t/dEJUMdkN

okay here is the start, I will work on the report itself to show how it looks when finalized, or you can beautify it.

here is a second attempt:
https://www.screencast.com/t/x0zqtGLz6hc


since I do not use my expertise in fashioning the report is rudimentary. should be able to do better given time.

[Rich Hartnett]

Re: SQL Expression Help

Take a look at this. It will help show what my ultimate goal is.

Thanks

Report_Builder_Demo_(11-28-17).swf