Dynamic Query

I use IIF in the dynamic query to dynamically change the Select, Group By, and Order By statements.

In the table grouping properties, i also use IIF to change the grouping Field.

There are no errors, the report processes OK, but the report is not grouped or shows any Field values for which i have to use dynamic query. What could be the problem? Can anybody help please.

Thanks

Can you post the dynamic query you are using, as well as the group expression? Also if you enable tracing on the database side, is the query being executed correct?|||

Thank you for answering to my problem. I did get over it after much trying. The dynamic query looks like this:

="SELECT SUM(BASE_UNIT) AS BASE_UNIT "
& IIF(Parameters!Type.Value = "Location", ", Location", IIF(Parameters!Type.Value = "Admit_Source", ", Admit_Source", ", Provider_Name")) &
" as grouping FROM TBL_EOM
WHERE (MONTH(ENTRY_DATETIME) =@.RepMonth) AND (YEAR(ENTRY_DATETIME) = @.RepYear)" &
IIF(Parameters!Pract.Value = "*** ALL ***", " ", " AND (NAME = @.Prac) ") &
" GROUP BY Provider_Name, NAME" & IIF(Parameters!Type.Value = "Location", ", Location", IIF(Parameters!Type.Value = "Admit_Source", ", Admit_Source", " "))

In layout view i have this strig for field, grouping and sorting:

IIF(Parameters!Type.Value <> "Provider", Fields!grouping.Value, " ")

Thank you

Dynamic Filter Operator

Here is what I am trying to do in SSRS 2005.

Setting up filters based on parameters with an expression like this:

=Iif(Parameters!Company.Value = "", "", Fields!company.Value)

In the wizard we are creating, we are also letting the user choose the operator for each parameter that they choose. My question is how can I change the filter dynamically based on the user choosing a specific parameter and also choosing an operator to associate with that parameter?

Example 1: User 1 chooses the Company parameter to filter their report, and they choose the parameter to equal (=) a specific value. So the filter expression would be like the one previously mentioned and the operator would be an equal sign.

Example 2: User 2 chooses the Company parameter to filter their report, and they choose the parameter to be LIKE a specific value. So the filter expression would be like the one previously mentioned and the operator would be LIKE.

How can I do this?

Thanks in advance for your help.

You indicate you have a parameter named "Company"

first:
Create a parameter named "operator" and give it values "equals" and "like"

label value

equals equals
like like

Set the default value if you choose.

then:
Create a parameter named "filter" and leave it blank

Open the "table properties" and select "filter"

1st expression -
for the filter expression like:
=Iif(Parameters!Operator.Value = "equals", "", Fields!company.Value))
for the operator:
select the "like" operator
for the value:
=Iif(Parameters!Operator.Value = "equals", "", Switch(Parameters!Filter.Value = Parameters!Filter.Value, Parameters!Filter.Value & "*", Parameters!Filter.Value = nothing, "*"))

2nd expression -
for the filter expression equals:
=Iif(Parameters!Operator.Value = "like", "", Fields!company.Value))
for the operator:
select the "=" operator
for the value:
=Iif(Parameters!Operator.Value = "like", "", Parameters!Filter.Value)

This only covers one data field so when you select "like" you can enter the leter "A" in the filter parameter and all fields that start with "A" will be returned.
However if you select "equals" you need to know exactly what to type in other wise a dropdown would be great here.
If you leave the filter parameter blank and select "like" it will return all the data.
The options are endless. . .

|||

That is exactly what I was looking for!

Thanks very much

Dynamic Filter Operator

Here is what I am trying to do in SSRS 2005.

Setting up filters based on parameters with an expression like this:

=Iif(Parameters!Company.Value = "", "", Fields!company.Value)

In the wizard we are creating, we are also letting the user choose the operator for each parameter that they choose. My question is how can I change the filter dynamically based on the user choosing a specific parameter and also choosing an operator to associate with that parameter?

Example 1: User 1 chooses the Company parameter to filter their report, and they choose the parameter to equal (=) a specific value. So the filter expression would be like the one previously mentioned and the operator would be an equal sign.

Example 2: User 2 chooses the Company parameter to filter their report, and they choose the parameter to be LIKE a specific value. So the filter expression would be like the one previously mentioned and the operator would be LIKE.

How can I do this?

Thanks in advance for your help.

You indicate you have a parameter named "Company"

first:
Create a parameter named "operator" and give it values "equals" and "like"

label value

equals equals
like like

Set the default value if you choose.

then:
Create a parameter named "filter" and leave it blank

Open the "table properties" and select "filter"

1st expression -
for the filter expression like:
=Iif(Parameters!Operator.Value = "equals", "", Fields!company.Value))
for the operator:
select the "like" operator
for the value:
=Iif(Parameters!Operator.Value = "equals", "", Switch(Parameters!Filter.Value = Parameters!Filter.Value, Parameters!Filter.Value & "*", Parameters!Filter.Value = nothing, "*"))

2nd expression -
for the filter expression equals:
=Iif(Parameters!Operator.Value = "like", "", Fields!company.Value))
for the operator:
select the "=" operator
for the value:
=Iif(Parameters!Operator.Value = "like", "", Parameters!Filter.Value)

This only covers one data field so when you select "like" you can enter the leter "A" in the filter parameter and all fields that start with "A" will be returned.
However if you select "equals" you need to know exactly what to type in other wise a dropdown would be great here.
If you leave the filter parameter blank and select "like" it will return all the data.
The options are endless. . .

|||

That is exactly what I was looking for!

Thanks very much