Error Msg : Cannot set the commandtext for dataset. Help !

I am doing a report which shows rows from a table 'tblAccounts'. The
tblAccounts table is related to the tblUsers table by the userid. The
2 datasets I built are dsAccounts and dsUsers
The commandtext for dsUsers is
select 0 as userid, '(All)' as username from tblemployeeprofile UNION
select userid, username from tblemployeeprofile
I wanted to have a parameter for the user on the report, so when the
user is selected (the userid is the value, the username is the label)
the rows of all that selected user in the tblAccounts are shown in the
report. Also there is the '(All)' item (which has a value 0) in the
users dataset which will shows the rows of all the users.
Typically I would do a query for the dataset for tblAccounts as
follows
="select * from tblaccounts " & IIf("Parameters!userid.Value=0","","
where userid = Parameters!userid.Value")
But this gives me an error message.
"An error occured during report processing. Cannot set the commandtext
for dataset dsAccounts. Error during processing of the commandtext
expression of the dataset 'dsAccounts'"
I tried simplifying the commandtext by bringing down the commandtext
of dsAccounts
="select * from tblaccounts " & IIf("1=2",""," where userid = 72")
But the error message still says
But this gives me an error message.
"An error occured during report processing. Cannot set the commandtext
for dataset dsAccounts. Error during processing of the commandtext
expression of the dataset 'dsAccounts'"
Helppp!
Anand Sagar
anandsagar@.gmail.comRemove double-quotes around the first parameter to iif().
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Anand Sagar" <anandsagar@.gmail.com> wrote in message
news:3f76a771.0408122236.4430d93a@.posting.google.com...
> I am doing a report which shows rows from a table 'tblAccounts'. The
> tblAccounts table is related to the tblUsers table by the userid. The
> 2 datasets I built are dsAccounts and dsUsers
> The commandtext for dsUsers is
> select 0 as userid, '(All)' as username from tblemployeeprofile UNION
> select userid, username from tblemployeeprofile
> I wanted to have a parameter for the user on the report, so when the
> user is selected (the userid is the value, the username is the label)
> the rows of all that selected user in the tblAccounts are shown in the
> report. Also there is the '(All)' item (which has a value 0) in the
> users dataset which will shows the rows of all the users.
> Typically I would do a query for the dataset for tblAccounts as
> follows
> ="select * from tblaccounts " & IIf("Parameters!userid.Value=0","","
> where userid = Parameters!userid.Value")
> But this gives me an error message.
> "An error occured during report processing. Cannot set the commandtext
> for dataset dsAccounts. Error during processing of the commandtext
> expression of the dataset 'dsAccounts'"
> I tried simplifying the commandtext by bringing down the commandtext
> of dsAccounts
> ="select * from tblaccounts " & IIf("1=2",""," where userid = 72")
> But the error message still says
> But this gives me an error message.
> "An error occured during report processing. Cannot set the commandtext
> for dataset dsAccounts. Error during processing of the commandtext
> expression of the dataset 'dsAccounts'"
>
> Helppp!
> Anand Sagar
> anandsagar@.gmail.com|||Yo! I removed the extra spaces and carriage returns. It worked now. I
realised after seeing the documentation that you shouldnt give more
than 1 extra space and you shouldnt give enter hits while writing the
sql query
it works now. phew !
anandsagar@.gmail.com (Anand Sagar) wrote in message news:<3f76a771.0408122236.4430d93a@.posting.google.com>...
> I am doing a report which shows rows from a table 'tblAccounts'. The
> tblAccounts table is related to the tblUsers table by the userid. The
> 2 datasets I built are dsAccounts and dsUsers
> The commandtext for dsUsers is
> select 0 as userid, '(All)' as username from tblemployeeprofile UNION
> select userid, username from tblemployeeprofile
> I wanted to have a parameter for the user on the report, so when the
> user is selected (the userid is the value, the username is the label)
> the rows of all that selected user in the tblAccounts are shown in the
> report. Also there is the '(All)' item (which has a value 0) in the
> users dataset which will shows the rows of all the users.
> Typically I would do a query for the dataset for tblAccounts as
> follows
> ="select * from tblaccounts " & IIf("Parameters!userid.Value=0","","
> where userid = Parameters!userid.Value")
> But this gives me an error message.
> "An error occured during report processing. Cannot set the commandtext
> for dataset dsAccounts. Error during processing of the commandtext
> expression of the dataset 'dsAccounts'"
> I tried simplifying the commandtext by bringing down the commandtext
> of dsAccounts
> ="select * from tblaccounts " & IIf("1=2",""," where userid = 72")
> But the error message still says
> But this gives me an error message.
> "An error occured during report processing. Cannot set the commandtext
> for dataset dsAccounts. Error during processing of the commandtext
> expression of the dataset 'dsAccounts'"
>
> Helppp!
> Anand Sagar
> anandsagar@.gmail.com