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))