Query and Adding a Value to the Query in DMR

[gosoccer]

Hi guys,
I have a Query that I have to eliminate certain value like 'Junk' from the Drop Down List. So, I setup the filter in the Query and hided 'Junk' so it won't show. Unfortunately, 'Junk' has other links/relationships with other Queries.
So, for example,
Q1:
REGION        Name
1                   BUI
2                   TOW

State
1            MD
2            TOW

So, since the Q1 is a combination of the REGION and State at the Model level, in the Report, if I filter out 'TOW' the TOW for Region DOESN't SHOW as welll which makes sense.

So. how can I hide TOW from the State Drop Down List the Region will still show the TOW. Since we have join between the Region and State, I had to add 2  TOW in order to show the Regions but don't need to show TOW for the State.

I was thinking a way I can create a separate Query in Report Studio and have the main Query to be Joined with the second Query so the value of TOW will populate as a part of the REGION even if I filter it out from State.

Any ideas would be greatly appreciate it.  :o :o

[MFGF]

If this is DMR, and you have a prompt for State, the prompt would usually be fed via its own query? Wouldn't you simply omit the TOW state member from this query via either an Except() function or by simply defining a set of all other State members?

MF.


Sent from my iPhone using Tapatalk

[MFGF]

PS. Sounds like you're using a detail filter? With a dimensional package? Refer back to the FAQ post to see why this is not a good strategy :)


Sent from my iPhone using Tapatalk

[gosoccer]

I'm not sure but I'll try. just the State NOT IN ('TOW') didn't work and caused the region to drop it as well due to the join at FM level.

[MFGF]

I'm not sure but I'll try. just the State NOT IN ('TOW') didn't work and caused the region to drop it as well due to the join at FM level.

State NOT IN ('TOW') looks like a detail filter expression? As above, don't use detail filters with dimensional packages...

MF.

[gosoccer]

Yes, that makes sense. It's Dimensional. Thank you for your time.

[MFGF]

Yes, that makes sense. It's Dimensional. Thank you for your time.

As above, either omit the TOW state member from the prompt query via either an Except() function or by simply defining a set of all other State members. That's how I would do it.

Cheers!

MF.

[gosoccer]

Yeah! I'll try that with the Except. It should work and it shouldn't eliminate the other Queries values for other Drop Down Lists that are depending on this value that we are taking off. Thanks. :) :)