hello -- we are building a report, it has two parameters that are being pulled from a view.
the view looks like this
facility sectiontheater1 zone1theater1 zone2theater1 zone3theater1 zone4theater2 zoneAtheater2 zoneBtheater2 zoneCAll NULLfor the table name i have the viewthe display is facility and the data column is also facility for parameter 1parameter 2 is built similarly the display/data columns are both called section and the where clause states <<p1>> = facility and the disable clause states <<p1>> = 'All'this works but it has two major issues and i know one isn't fixable but maybe someone has a work around the other must be fixable because it would be silly to have data display like this anyway what I want to appear in my drop downs is thisfor facility theater1theater2All when i specify the facility i.e. theater2, zone should display ZoneA, ZoneB and ZoneC (this functionality works).
but what is happening is that its displaying this:facility theater1theater1theater1theater1theater2theater2theater2All
how do i distinct it (and we only have the one view pulling all the data). but i can't seem to use the word distinct anywhere as it breaks the report. (this error has to be fixable in set up somehow).
the second issue that keeps happening is if i select multi select for parameter 1 then parameter 2 doesn't display any data. I have to turn off multi select for facility to get zone to show anything at all! I don't understand why we can't show me the multi select for the connected parameters? i say theater1 and theater2 and i get all the applicable zones to those facilities? I know there is probably no solution to this but I wanted to ask anyways. Because it seems like a system flaw.
thank you-Lisa
This sounds totally doable. I would recommend using a separate view for parameter 1, and use the facility_no as the data value. (Then modify your parameter 2 view to include the facility_no.) When you do multi-select, the value is treated as a string, which means when you use it as a filter for parameter 2, you can't just do WHERE facility_no = <<p1>> or even WHERE facility_no IN (<<p1>>). Instead, you have to do something like WHERE CHARINDEX(',' + CAST(facility_no AS varchar) + ',', ',' + <<p1>> + ',') > 0. This is basically composing a string using the parameter value and then searching for the facility_no within it.
You may also be able to find a canned report that does multi-select filtering a use its parameter setup as a model.
Thanks, the second part is a great idea -- and if all else fails we will go the two view option, i was just not sure why/how to select a distinct field from the view since i'm selecting a single column, why can't it be distinct. thanks again.