Easy Landscape Printing in SSRS -- No programming required.

I had the same headaches as a lot of you. Check out my post on my blog
here:
http://binaryswitch.blogspot.com/2005/09/easy-landscape-printing-in-sql-server.html
This also solves the problem of printing directly out of the web
browser.While this takes care of the obvious printing problems, it doesn't take of
many others.
for instance, if you set the report layout properties to legal (8.5 x14) in
VS.NET, none of this is translated on the server-side. we still need to
change the layout properties in the print button dialog (and usually preview,
just to make sure things are well)
rob
"Mark Milley - BinarySwitch" wrote:
> I had the same headaches as a lot of you. Check out my post on my blog
> here:
> http://binaryswitch.blogspot.com/2005/09/easy-landscape-printing-in-sql-server.html
>
> This also solves the problem of printing directly out of the web
> browser.
>

Dynamically change report grouping

Currently our users can filter data but they cannot manually group data
differently without us having to create them a new report with their
required group options.
Is there a way perhaps to hard code maybe 1 or 2 group levels in a reports
and then turn them off or turn them on dynamically depending on how the user
wanted to view the results.
Any help is appreciated.We use a 'Group By' parameter that the stored proc uses to do the
grouping. The results come back in the same format each time of
course, they're just grouped differently. This can sometimes lead to
some icky VB.NET conditionals inlined into the RDL, but in general it
has worked very well.|||Alternatively, group on an expression that is based on the value of a
parameter.
In our case, the client's requirements needed 6 reports but this
technique allowed us to give them one.
The stored proc must return a result set at a grouping level just above
the two alternative groupings.
BrianK
www.bolign.com

dynamic sql help

I have a search procedure tied to my .net GUI. One value is required out of the 3 parameters I send it. The other 2 could be null, not null etc or any combination

The problem is the parameters are placed into the where clause. Is there a way to do an if statement of sorts in the where clause so that only the values that are not null end up in that part of the statement? I want to handle this in the sproc instead of reverting to a dynamically created where clause statement in my .net code.

Is this possible? Below is my sproc

CREATE PROCEDURE [dbo].[usp_SearchSpecialistCase]

@.LastName varchar(50)=NULL,
@.HLCI varchar(50)=NULL,
@.SSN char(9)=NULL
AS

SELECT
[PersonID],
[Case].[CaseID],
Person.[SSN],
Person.[HLCI],
UPPER(Person.LastName + ', '+ Person.FirstName + ' ' + ISNULL(Person.MiddleName, '')) as

FullName,
Person.[LastName],
Person.[MiddleName],
Person.[FirstName],
Person.[BirthDate],
Person.[HeadofHousehold],
SecurityUsers.SecurityOfficeID
FROM
[Person] INNER JOIN [Case] ON Person.CaseID = [Case].CaseID
LEFT JOIN CaseAssignedToHistory ON [Case].CaseID = CaseAssignedToHistory.CaseID
LEFT JOIN SecurityUsers ON CaseAssignedToHistory.UserID = SecurityUsers.UserID
LEFT JOIN SecurityOffice ON SecurityUsers.SecurityOfficeID = SecurityOffice.SecurityOfficeID
WHERE (

Person.LastName LIKE @.LastName + '%' AND
Person.HLCI LIKE @.HLCI + '%' AND
Person.SSN LIKE @.SSN + '%'

)
GO

mmmmm what about this?

select * from authors
where au_fname like 's%'

select * from authors
where au_fname like 's%'
and au_id like '%'

that is the same right?

then you could do this

CREATE PROCEDURE [dbo].[usp_SearchSpecialistCase]

@.LastName varchar(50)=NULL,
@.HLCI varchar(50)=NULL,
@.SSN char(9)=NULL
AS
SELECT
[PersonID],
[Case].[CaseID],
Person.[SSN],
Person.[HLCI],
UPPER(Person.LastName + ', '+ Person.FirstName + ' ' + ISNULL(Person.MiddleName, '')) as
FullName,
Person.[LastName],
Person.[MiddleName],
Person.[FirstName],
Person.[BirthDate],
Person.[HeadofHousehold],
SecurityUsers.SecurityOfficeID
FROM
[Person] INNER JOIN [Case] ON Person.CaseID = [Case].CaseID
LEFT JOIN CaseAssignedToHistory ON [Case].CaseID = CaseAssignedToHistory.CaseID
LEFT JOIN SecurityUsers ON CaseAssignedToHistory.UserID = SecurityUsers.UserID
LEFT JOIN SecurityOffice ON SecurityUsers.SecurityOfficeID = SecurityOffice.SecurityOfficeID
WHERE (
Person.LastName LIKE coalesce(@.LastName,'') + '%' AND
Person.HLCI LIKE coalesce(@.HLCI,'') + '%' AND
Person.SSN LIKE coalesce(@.SSN,'') + '%'
)
GO

Just check the execution plan for table scans etc, for the 2 queries above the execution plan is the same

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Please check out the link below for various suggestions/examples:

http://www.sommarskog.se/dyn-search.html