Parameter drop-down

I've got two reports that are almost identical. They both allow clicking on
the column headers to sort the report so there is a parameter called SortBy
which appears as a drop-down list of fields that are available for sorting.
Both reports have the same default value defined for this parameter because
they both use the same datasource, however, one report shows this default
value in the drop-down list by default, the other report shows <Select a
Value> and the user must select a value before the report can be generated.
Any ideas? I've even compared the .rdl files and they appear identical with
regards to this parameter.
Help, I'm losing it over this one.
Thanks in advance.Hi Danno,
perhaps you need a second pair of eyes. I assume you have set up values as
Non-Queried and that the Value in the list that you want as the default is
exactly the same (case and all) as what you have set as your default value -
a space or a capital in the odd place may throw it off - I'm not sure how
exact this has to be.
A question of my own, if I may, how do you set up the report such that the
user can sort by a column by clicking on the column header?
thanks
Matt
"Danno" <Danno@.discussions.microsoft.com> wrote in message
news:CF5CB8D2-56A1-4C85-ADCF-AEBF6F53715B@.microsoft.com...
> I've got two reports that are almost identical. They both allow clicking
on
> the column headers to sort the report so there is a parameter called
SortBy
> which appears as a drop-down list of fields that are available for
sorting.
> Both reports have the same default value defined for this parameter
because
> they both use the same datasource, however, one report shows this default
> value in the drop-down list by default, the other report shows <Select a
> Value> and the user must select a value before the report can be
generated.
> Any ideas? I've even compared the .rdl files and they appear identical
with
> regards to this parameter.
> Help, I'm losing it over this one.
> Thanks in advance.|||Thanks Danno,
Bit disappointing that it requires two reports to achieve this.
regards
Matt
"Danno" <Danno@.discussions.microsoft.com> wrote in message
news:D320BE8F-0F79-4BBA-90E6-97174E704B09@.microsoft.com...
> Never mind, I fixed it. I just deleted the report in Report Manager and
> re-added it and now it works...go figure.
> To allow column-header clicking, check out this sample:
>
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=6a347b53-5594-40f9-861d-876beabeda16
> "Matt" wrote:
> > Hi Danno,
> >
> > perhaps you need a second pair of eyes. I assume you have set up values
as
> > Non-Queried and that the Value in the list that you want as the default
is
> > exactly the same (case and all) as what you have set as your default
value -
> > a space or a capital in the odd place may throw it off - I'm not sure
how
> > exact this has to be.
> >
> > A question of my own, if I may, how do you set up the report such that
the
> > user can sort by a column by clicking on the column header?
> >
> > thanks
> >
> > Matt
> >
> >
> > "Danno" <Danno@.discussions.microsoft.com> wrote in message
> > news:CF5CB8D2-56A1-4C85-ADCF-AEBF6F53715B@.microsoft.com...
> > > I've got two reports that are almost identical. They both allow
clicking
> > on
> > > the column headers to sort the report so there is a parameter called
> > SortBy
> > > which appears as a drop-down list of fields that are available for
> > sorting.
> > > Both reports have the same default value defined for this parameter
> > because
> > > they both use the same datasource, however, one report shows this
default
> > > value in the drop-down list by default, the other report shows <Select
a
> > > Value> and the user must select a value before the report can be
> > generated.
> > > Any ideas? I've even compared the .rdl files and they appear
identical
> > with
> > > regards to this parameter.
> > >
> > > Help, I'm losing it over this one.
> > >
> > > Thanks in advance.
> >
> >
> >|||Never mind, I fixed it. I just deleted the report in Report Manager and
re-added it and now it works...go figure.
To allow column-header clicking, check out this sample:
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=6a347b53-5594-40f9-861d-876beabeda16
"Matt" wrote:
> Hi Danno,
> perhaps you need a second pair of eyes. I assume you have set up values as
> Non-Queried and that the Value in the list that you want as the default is
> exactly the same (case and all) as what you have set as your default value -
> a space or a capital in the odd place may throw it off - I'm not sure how
> exact this has to be.
> A question of my own, if I may, how do you set up the report such that the
> user can sort by a column by clicking on the column header?
> thanks
> Matt
>
> "Danno" <Danno@.discussions.microsoft.com> wrote in message
> news:CF5CB8D2-56A1-4C85-ADCF-AEBF6F53715B@.microsoft.com...
> > I've got two reports that are almost identical. They both allow clicking
> on
> > the column headers to sort the report so there is a parameter called
> SortBy
> > which appears as a drop-down list of fields that are available for
> sorting.
> > Both reports have the same default value defined for this parameter
> because
> > they both use the same datasource, however, one report shows this default
> > value in the drop-down list by default, the other report shows <Select a
> > Value> and the user must select a value before the report can be
> generated.
> > Any ideas? I've even compared the .rdl files and they appear identical
> with
> > regards to this parameter.
> >
> > Help, I'm losing it over this one.
> >
> > Thanks in advance.
>
>

Paging and dynamic sort order (ASC/DESC)

Hi all,

I have a SQL statement that allows paging and dynamic sorting of the
columns, but what I can't figure out without making the SQL a dynamic
string and executing it, or duplicating the SQL statement between an
IF and ELSE statement.

Following is the SQL statement;

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_search]
@.search VARCHAR( 80 )
, @.startRow INT = 1
, @.endRow INT = NULL
, @.postcode AS CHAR( 4 ) = NULL
, @.suburb AS VARCHAR( 40 ) = NULL
, @.stateIdentity AS TINYINT = NULL
, @.fromLatitude AS REAL = NULL -- latitude the user is located in
, @.fromLongitude AS REAL = NULL -- longitude the user is located in
, @.sort TINYINT = 1
AS
BEGIN

SET NOCOUNT ON;

DECLARE @.calculateDistance BIT;
SET @.calculateDistance = 0;

-- get the longitude and latitude if required
IF ( NOT @.postcode IS NULL )
BEGIN
SELECTDISTINCT
@.fromLatitude = latitude
, @.fromLongitude = longitude
FROMtbl_postalcode
WHERE(postalcode = @.postcode)
SET @.calculateDistance = 1
END
ELSE IF ( NOT @.suburb IS NULL AND NOT @.stateIdentity IS NULL )
BEGIN
SELECTDISTINCT
@.fromLatitude = latitude
, @.fromLongitude = longitude
FROMtbl_locality
WHERE(locality = @.suburb)
AND(stateIdentity = @.stateIdentity)
SET @.calculateDistance = 1
END
/*
ELSE IF ( @.fromLatitude IS NULL AND @.fromLongitude IS NULL )
BEGIN
RAISERROR( 'You need to pass a valid combination to this stored
procedure, example: postcode or suburb and state identity or longitude
and latitude', 18, 1 );
END*/

SELECT D1.[row]
, D1.[totalRecordCount]
, D1.[classifiedIdentity]
, D1.[title]
, D1.[summary]
, D1.[price]
, D1.[locality]
, D1.[state]
, D1.[postcode]
, D1.[addedLast24]
, D1.[dateStamp]
, D1.[t2Rank]
, D1.[t3Rank]
, D1.[tRank]
, D1.[distance]
, F.[originalName]
, F.[extension]
, F.[uniqueName]
FROM(
-- derived table
SELECT ROW_NUMBER() OVER ( ORDER BY CASE @.sort WHEN 0 THEN
CAST( COALESCE( t2.RANK, 0 ) + COALESCE( t3.RANK, 0 ) AS CHAR( 5 ) )
WHEN 1 THEN C.title WHEN 2 THEN CAST( CEILING( [dbo].
[fn_calculateDistance] ( @.fromLatitude, @.fromLongitude, L.latitude,
L.longitude ) ) AS CHAR( 9 ) ) WHEN 3 THEN ( C.locality + ' ' +
C.state ) WHEN 4 THEN CAST( C.price AS CHAR( 10 ) ) END ASC ) AS row
, COUNT( * ) OVER() AS totalRecordCount
, C.[classifiedIdentity]
, C.[title]
, C.[summary]
, C.[price]
, C.[locality]
, C.[state]
, C.[postcode]
, CASE WHEN ( C.[dateStamp] >= DATEADD( day, -1, GETDATE() ) )
THEN 1 ELSE 0 END AS addedLast24
, C.[dateStamp]
/* , t1.RANK AS t1Rank */
, t2.RANK AS t2Rank
, t3.RANK AS t3Rank
, /* COALESCE( t1.RANK, 0 ) + */ COALESCE( t2.RANK, 0 ) +
COALESCE( t3.RANK, 0 ) AS tRank
, CASE @.calculateDistance WHEN 1 THEN CEILING( [dbo].
[fn_calculateDistance] ( @.fromLatitude, @.fromLongitude, L.latitude,
L.longitude ) ) ELSE 0 END AS distance
FROM [tbl_classified] AS C
INNER JOINtbl_locality L
ONC.localityIdentity = L.localityIdentity
/* LEFT OUTER JOINCONTAINSTABLE( tbl_category, title, @.keyword ) AS
t1
ON FT_TBL.categoryIdentity = t1.[KEY] */
LEFT OUTER JOINCONTAINSTABLE( tbl_classified, title, @.search ) AS
t2
ON C.classifiedIdentity = t2.[KEY]
LEFT OUTER JOINCONTAINSTABLE( tbl_classified, description,
@.search ) AS t3
ON C.classifiedIdentity = t3.[KEY]
WHERE ( /* COALESCE( t1.RANK, 0 ) + */COALESCE( t2.RANK, 0 ) +
COALESCE( t3.RANK, 0 ) ) != 0
) AS D1
LEFT OUTER JOINtbl_classified_file CF
OND1.classifiedIdentity = CF.classifiedIdentity
LEFT OUTER JOINtbl_file F
ONF.fileIdentity = CF.fileIdentity
WHERE( row >= @.startRow )
AND( @.endRow IS NULL OR row <= @.endRow )

END

The part I'm having trouble with is making the sort order in the
following line dynamic

ORDER BY CASE @.sort WHEN 0 THEN CAST( COALESCE( t2.RANK, 0 ) +
COALESCE( t3.RANK, 0 ) AS CHAR( 5 ) ) WHEN 1 THEN C.title WHEN 2 THEN
CAST( CEILING( [dbo].[fn_calculateDistance] ( @.fromLatitude,
@.fromLongitude, L.latitude, L.longitude ) ) AS CHAR( 9 ) ) WHEN 3 THEN
( C.locality + ' ' + C.state ) WHEN 4 THEN CAST( C.price AS
CHAR( 10 ) ) END ASC

any help would be greatly apprecaited.

ThanksGot this one sorted thanks...

Page totals in last page

Friends,
I am working on a report where I have to print all the page totals, in the last page.
Could you please give us some sort of solution.
Regards
JCYou need to use Three formulae
Formula1 Reset having the code

Whileprintingrecords;
numbervar x:=0;
Formula2 Sum having the code
whileprintingrecords;
numbervar x:=Tonumber(x+DatabaseField);
Formula3 Display having the code
whileprintingrecords;
numbervar x;
Place Formula1 at Page Header
Place Formula2 at Details section
Place Formula3 at Page Footer

Now suppress Formula1 and Formula2

Page Not Found, Sort Of

System: Win2k3, SQL2k5, Sharepoint (all on same box)
Note: default website port is 8000 (I mentioned this because it's non
standard)
I had this system all setup and running properly then all of the sudden
some brilliant support person at our data center decided to change the
I.P. of the server. This wreaked havoc on most of the code on that box
as I had to change all of the config files, DNS, and HTTP headers.
I finally got most of the pieces running again, but I have a couple an
issue. Currently there are two instances of SSRS running on the box. I
have the "old" one's service stopped, so it is not running, but when
browsing to http://localhost:8000/reports it is still trying to go to
the stopped instance. How can I change that? I've tried the rsconfig
utility, but I may not have the correct parameters.
thanks,
.britOh, one more thing. The "Page Not Found" error mentioned in the subject
refers to a very strange problem that I feel is resulting from having
two instances of SSRS running.
I have a domain name pointing to the default IP for the box. If I use
[domain url]/reports everything works great in Report Manager until I
actually go to view a report and then I get a Page Not Found error
within Report Manager. The error does not take over the entire screen,
just the portion in Rpt Mgr that would show the report.
If I go straight to the report using [domain url]/reportserver/[path of
report] they work great.
What gives?
thanks,
.brit