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