CommandText issue - SQL Reporting Services

I'm having a ton of trouble with a dataset. It builds at design time,
but fails at runtime, saying:

--------
Processing Errors
--------
An error has occurred during report processing.
Cannot set the command text for data set 'ds_Legal_Entity'.
Error during processing of the CommandText expression of dataset
'ds_Legal_Entity'.
--------
OK
--------

Below is the CommandText for ds_Legal_Entity that gives me the error:

="SELECT DISTINCT dbo.t_d_legal_entity.legal_entity_desc FROM
dbo.t_d_legal_entity INNER JOIN dbo.t_f_month_summary ON
dbo.t_d_legal_entity.legal_entity_key =
dbo.t_f_month_summary.legal_entity_key WHERE
(dbo.t_f_month_summary.acctg_mth_key = " &
Parameters!acctg_mth_key.Value & ")" &
IIF(Parameters!BusUnitKey.Value = 0,""," AND
(dbo.t_f_month_summary.bus_unit_key = " & Parameters!BusUnitKey.Value &
")") & " ORDER BY dbo.t_d_business_unit.legal_entity_desc"

If I delete everything after " & Parameters!acctg_mth_key.Value & ")",
I won't get the error, so I assume that's where the problem lies. I
just need another pair of eyes to see it.

Thanks!
Mike(Michael.EJ.Reynolds@.gmail.com) writes:
> Below is the CommandText for ds_Legal_Entity that gives me the error:
>="SELECT DISTINCT dbo.t_d_legal_entity.legal_entity_desc FROM
> dbo.t_d_legal_entity INNER JOIN dbo.t_f_month_summary ON
> dbo.t_d_legal_entity.legal_entity_key =
> dbo.t_f_month_summary.legal_entity_key WHERE
> (dbo.t_f_month_summary.acctg_mth_key = " &
> Parameters!acctg_mth_key.Value & ")" &
> IIF(Parameters!BusUnitKey.Value = 0,""," AND
> (dbo.t_f_month_summary.bus_unit_key = " & Parameters!BusUnitKey.Value &
> ")") & " ORDER BY dbo.t_d_business_unit.legal_entity_desc"
>
> If I delete everything after " & Parameters!acctg_mth_key.Value & ")",
> I won't get the error, so I assume that's where the problem lies. I
> just need another pair of eyes to see it.

The best way to sort this out, is to grab the generated SQL and then
copy and paste into Query Analyzer to run it there for hopefully a
better error message.

The one error I can see is that the ORDER BY clause includes a column
which is not in the SELECT list, which is required when you have DISTINCT.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp