Anyone else had this error situation?

So, for the past two weeks or so every morning Reporting services has come up
with the report error "Sorting cannot be performed on table1". For some odd
reason though it does the error on all reports with any sorting whatsoever.
I've checked and double checked my sorting expressions
(=iif(Parameters!Direction.Value = "Ascending",
Fields(Parameters!SortBy.Value).Value, 0) and =iif(Parameters!Direction.Value
= "Descending", Fields(Parameters!SortBy.Value).Value, 0)...
Direction is a drop down param which is either Ascending or Descending and
SortBy is a drop down param that contains all table columns) and have had
others look at it as well and the logic is good for it. What ultimately
remedies it is a restart of IIS on the server we run the reports on.
So has anyone else experienced this problem? If so I could use any
information as to the possible cause or fix to this.Please try this - note the CInt() function call:
=iif(Parameters!Direction.Value = "Ascending",
CInt(Fields(Parameters!SortBy.Value).Value), 0)
=iif(Parameters!Direction.Value = "Descending",
CInt(Fields(Parameters!SortBy.Value).Value), 0)
etc.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Timm" <Timm@.discussions.microsoft.com> wrote in message
news:2DD5CE58-A3DF-41EF-BD47-010A0770E2AE@.microsoft.com...
> So, for the past two weeks or so every morning Reporting services has come
> up
> with the report error "Sorting cannot be performed on table1". For some
> odd
> reason though it does the error on all reports with any sorting
> whatsoever.
> I've checked and double checked my sorting expressions
> (=iif(Parameters!Direction.Value = "Ascending",
> Fields(Parameters!SortBy.Value).Value, 0) and
> =iif(Parameters!Direction.Value
> = "Descending", Fields(Parameters!SortBy.Value).Value, 0)...
> Direction is a drop down param which is either Ascending or Descending and
> SortBy is a drop down param that contains all table columns) and have had
> others look at it as well and the logic is good for it. What ultimately
> remedies it is a restart of IIS on the server we run the reports on.
> So has anyone else experienced this problem? If so I could use any
> information as to the possible cause or fix to this.|||The column names in use are string values (non-numeric) so using CInt throws
back a
different error that will cause the report to not work 100% of the time. I'm
toying with
an idea that this is something to do with the reporting services and not the
reports
themselves since restarting IIS resolves the issue for a day (and the next
day the error
occurs again).
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:OOY8V1sZFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Please try this - note the CInt() function call:
> =iif(Parameters!Direction.Value = "Ascending",
> CInt(Fields(Parameters!SortBy.Value).Value), 0)
> =iif(Parameters!Direction.Value = "Descending",
> CInt(Fields(Parameters!SortBy.Value).Value), 0)
> etc.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Timm" <Timm@.discussions.microsoft.com> wrote in message
> news:2DD5CE58-A3DF-41EF-BD47-010A0770E2AE@.microsoft.com...
>> So, for the past two weeks or so every morning Reporting services has
>> come up
>> with the report error "Sorting cannot be performed on table1". For some
>> odd
>> reason though it does the error on all reports with any sorting
>> whatsoever.
>> I've checked and double checked my sorting expressions
>> (=iif(Parameters!Direction.Value = "Ascending",
>> Fields(Parameters!SortBy.Value).Value, 0) and
>> =iif(Parameters!Direction.Value
>> = "Descending", Fields(Parameters!SortBy.Value).Value, 0)...
>> Direction is a drop down param which is either Ascending or Descending
>> and
>> SortBy is a drop down param that contains all table columns) and have had
>> others look at it as well and the logic is good for it. What ultimately
>> remedies it is a restart of IIS on the server we run the reports on.
>> So has anyone else experienced this problem? If so I could use any
>> information as to the possible cause or fix to this.
>|||If they are strings, then this explains why it does not work. Use the
following expression then - note that 0 is replaced with an empty string:
=iif(Parameters!Direction.Value = "Ascending",
Fields(Parameters!SortBy.Value).Value, "")
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Timm" <tim@.gumdropbooks.com> wrote in message
news:%23F1YsdtZFHA.720@.TK2MSFTNGP15.phx.gbl...
> The column names in use are string values (non-numeric) so using CInt
> throws back a
> different error that will cause the report to not work 100% of the time.
> I'm toying with
> an idea that this is something to do with the reporting services and not
> the reports
> themselves since restarting IIS resolves the issue for a day (and the next
> day the error
> occurs again).
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:OOY8V1sZFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> Please try this - note the CInt() function call:
>> =iif(Parameters!Direction.Value = "Ascending",
>> CInt(Fields(Parameters!SortBy.Value).Value), 0)
>> =iif(Parameters!Direction.Value = "Descending",
>> CInt(Fields(Parameters!SortBy.Value).Value), 0)
>> etc.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Timm" <Timm@.discussions.microsoft.com> wrote in message
>> news:2DD5CE58-A3DF-41EF-BD47-010A0770E2AE@.microsoft.com...
>> So, for the past two weeks or so every morning Reporting services has
>> come up
>> with the report error "Sorting cannot be performed on table1". For some
>> odd
>> reason though it does the error on all reports with any sorting
>> whatsoever.
>> I've checked and double checked my sorting expressions
>> (=iif(Parameters!Direction.Value = "Ascending",
>> Fields(Parameters!SortBy.Value).Value, 0) and
>> =iif(Parameters!Direction.Value
>> = "Descending", Fields(Parameters!SortBy.Value).Value, 0)...
>> Direction is a drop down param which is either Ascending or Descending
>> and
>> SortBy is a drop down param that contains all table columns) and have
>> had
>> others look at it as well and the logic is good for it. What ultimately
>> remedies it is a restart of IIS on the server we run the reports on.
>> So has anyone else experienced this problem? If so I could use any
>> information as to the possible cause or fix to this.
>>
>|||I did that change and this morning we got that error still. After restarting
IIS it now works again.
Is this a known issue or is this something that may be unique to our system?
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:%23ggV6FvZFHA.3280@.TK2MSFTNGP09.phx.gbl...
> If they are strings, then this explains why it does not work. Use the
> following expression then - note that 0 is replaced with an empty string:
> =iif(Parameters!Direction.Value = "Ascending",
> Fields(Parameters!SortBy.Value).Value, "")
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Timm" <tim@.gumdropbooks.com> wrote in message
> news:%23F1YsdtZFHA.720@.TK2MSFTNGP15.phx.gbl...
>> The column names in use are string values (non-numeric) so using CInt
>> throws back a
>> different error that will cause the report to not work 100% of the time.
>> I'm toying with
>> an idea that this is something to do with the reporting services and not
>> the reports
>> themselves since restarting IIS resolves the issue for a day (and the
>> next day the error
>> occurs again).
>> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
>> news:OOY8V1sZFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> Please try this - note the CInt() function call:
>> =iif(Parameters!Direction.Value = "Ascending",
>> CInt(Fields(Parameters!SortBy.Value).Value), 0)
>> =iif(Parameters!Direction.Value = "Descending",
>> CInt(Fields(Parameters!SortBy.Value).Value), 0)
>> etc.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Timm" <Timm@.discussions.microsoft.com> wrote in message
>> news:2DD5CE58-A3DF-41EF-BD47-010A0770E2AE@.microsoft.com...
>> So, for the past two weeks or so every morning Reporting services has
>> come up
>> with the report error "Sorting cannot be performed on table1". For some
>> odd
>> reason though it does the error on all reports with any sorting
>> whatsoever.
>> I've checked and double checked my sorting expressions
>> (=iif(Parameters!Direction.Value = "Ascending",
>> Fields(Parameters!SortBy.Value).Value, 0) and
>> =iif(Parameters!Direction.Value
>> = "Descending", Fields(Parameters!SortBy.Value).Value, 0)...
>> Direction is a drop down param which is either Ascending or Descending
>> and
>> SortBy is a drop down param that contains all table columns) and have
>> had
>> others look at it as well and the logic is good for it. What ultimately
>> remedies it is a restart of IIS on the server we run the reports on.
>> So has anyone else experienced this problem? If so I could use any
>> information as to the possible cause or fix to this.
>>
>>
>|||I haven't seen this before. Are you sure you don't have any report execution
caching set up?
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Timm" <tim@.gumdropbooks.com> wrote in message
news:eHjfzC3ZFHA.1040@.TK2MSFTNGP10.phx.gbl...
>I did that change and this morning we got that error still. After
>restarting IIS it now works again.
> Is this a known issue or is this something that may be unique to our
> system?
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:%23ggV6FvZFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> If they are strings, then this explains why it does not work. Use the
>> following expression then - note that 0 is replaced with an empty string:
>> =iif(Parameters!Direction.Value = "Ascending",
>> Fields(Parameters!SortBy.Value).Value, "")
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Timm" <tim@.gumdropbooks.com> wrote in message
>> news:%23F1YsdtZFHA.720@.TK2MSFTNGP15.phx.gbl...
>> The column names in use are string values (non-numeric) so using CInt
>> throws back a
>> different error that will cause the report to not work 100% of the time.
>> I'm toying with
>> an idea that this is something to do with the reporting services and not
>> the reports
>> themselves since restarting IIS resolves the issue for a day (and the
>> next day the error
>> occurs again).
>> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
>> news:OOY8V1sZFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> Please try this - note the CInt() function call:
>> =iif(Parameters!Direction.Value = "Ascending",
>> CInt(Fields(Parameters!SortBy.Value).Value), 0)
>> =iif(Parameters!Direction.Value = "Descending",
>> CInt(Fields(Parameters!SortBy.Value).Value), 0)
>> etc.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Timm" <Timm@.discussions.microsoft.com> wrote in message
>> news:2DD5CE58-A3DF-41EF-BD47-010A0770E2AE@.microsoft.com...
>> So, for the past two weeks or so every morning Reporting services has
>> come up
>> with the report error "Sorting cannot be performed on table1". For
>> some odd
>> reason though it does the error on all reports with any sorting
>> whatsoever.
>> I've checked and double checked my sorting expressions
>> (=iif(Parameters!Direction.Value = "Ascending",
>> Fields(Parameters!SortBy.Value).Value, 0) and
>> =iif(Parameters!Direction.Value
>> = "Descending", Fields(Parameters!SortBy.Value).Value, 0)...
>> Direction is a drop down param which is either Ascending or Descending
>> and
>> SortBy is a drop down param that contains all table columns) and have
>> had
>> others look at it as well and the logic is good for it. What
>> ultimately
>> remedies it is a restart of IIS on the server we run the reports on.
>> So has anyone else experienced this problem? If so I could use any
>> information as to the possible cause or fix to this.
>>
>>
>>
>|||I am new to reporting services, so my answer may not be correct.
I had same error, when 2nd parameter was changed to cint, error went
away.
Something like this -
=iif(Parameters!Direction.Value = "Ascending",
CInt(Fields(Parameters!SortBy.Value).Value), cint(0))
=iif(Parameters!Direction.Value = "Descending",
CInt(Fields(Parameters!SortBy.Value).Value), cint(0))

Any Idea

If Table1 is going to be joined on 90% of the time on ID, and used to
generate lists 10% of the time, which of the following combinations of
indexes would provide the best performance to the most queries?
1.. Clustered unique inde on ID; non-clustered index on LastName,
FirstName
2.. Clustered index on LastName, FirstNamel non-clustered unque index on
ID
3.. Clustered index on FirstName, LastName; non-clustered unique index on
ID
4.. Non-clustered, unique index on ID; non-clustered index on Last Name
I think so it's B
On Mon, 13 Jun 2005 13:09:18 -0400, Roy wrote:

>If Table1 is going to be joined on 90% of the time on ID, and used to
>generate lists 10% of the time, which of the following combinations of
>indexes would provide the best performance to the most queries?
> 1.. Clustered unique inde on ID; non-clustered index on LastName,
>FirstName
> 2.. Clustered index on LastName, FirstNamel non-clustered unque index on
>ID
> 3.. Clustered index on FirstName, LastName; non-clustered unique index on
>ID
> 4.. Non-clustered, unique index on ID; non-clustered index on Last Name
>I think so it's B
>
Hi Roy,
Did you know that posting school asssignments on Usenet is generally
frowned upon?
However, the question above is so utterly bad, even for a school
assignment, that I can't hold still.
The correct answer is 5: not enough information to answer. You really
must know what columns are in Table1, which of these columns are
candidate keys (alone or in combination), and how many rows (approx)
there are in the table. You'll also have to know the same information
about the tables that Table1 will be joined to. And you'll have to know
the layout of the lists that will be generated. With that information,
you can take a stab at what the indexing strategies look promising - and
then you'll have to test, test, test, test, test them all in order to
know for sure.
Oh, and you might also want to inform your teacher that in the real
world, lists (aka reports) are usually not based on one table, but on
information from several tables. On a join, that is.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)