SSRS turn off paging

by ebarcza 5/14/2013 10:45:00 AM
You set the report's InteractiveHeight to 0 to disable paging.

Tags: ,

SQL Server Reporting Services

Report Viewer - 404 Error - File or directory not found

by ebarcza 1/11/2013 11:01:00 AM
When utilizing ASP.NET Report Viewer you may receive a 404 File or directory not found error. This is because the managed handler is missing in IIS for the domain serving your .NET application.

To resolve this issue:

  1. Open IIS 7 or 7.5
  2. Select the site to modify by double clicking, revealing all options.
  3. Double click on Handler Mappings
  4. Click Add Managed Handler
  5. Request path: Reserved.ReportViewerWebControl.axd
  6. Type: Microsoft.Reporting.WebForms.HttpHandler
  7. Name: Reserved-ReportViewerWebControl-axd
  8. Click OK to complete


Refresh the application or web page using the Report Viewer, it should work properly now.

Tags:

SQL Server Reporting Services

The permissions granted to user domain/username insufficent SSRS Report - rsAccessDenied

by ebarcza 8/6/2012 10:16:00 AM

NOTE: Even though the user was in the admin group, I had to do this anyway...

How to Fix: 

  1. Make sure Report Manger is running by going to the URL http://localhost/reports using the SQL Reporting Services Configuration. To do this:
    1. Open Reporting Services Configuration Manager -> then connect to the report server instance  -> then click on Report Manager URL.
    2. In the Report Manager URL page, click the Advanced button -> then in the Multiple Identities for Report Manager, click Add.
    3. In the Add a Report Manager HTTP URL popup box, select Host Header and type in: localhost
    4. Click OK to save your changes.
  2. Now start/ run Internet Explorer using Run as Administator...  
    • NOTE: If you don't see the 'Site Settings' link in the top left corner while at http://localhost/reports it is probably because you aren't running IE as an Administator or you haven't assigned your computers 'domain\username' to the reporting services roles, see how to do this in the next few steps.
  3. Now browse to: http://localhost/reports  You will most likely have to login
  4. You'll be directed to the Home page of SQL Server Reporting Services here: http://localhost/Reports/Pages/Folder.aspx
  5. From the Home page, click the Properties tab, then click New Role Assignment
  6. In the Group or user name textbox, add the 'domain\username' which was in the error message (in my case, I added: ebarcza for the 'domain\username', you can find the domain\username for your computer or application in the rsAccessDenied error message).
  7. Now check all the checkboxes; Browser, Content Manager, My Reports, Publisher, Report Builder, and then click OK.
  8. You're domain\username should now be assigned to the Roles that will give you access to deploy your reports to the Report Server.  If you're using Visual Studio or SQL Server Business Intelligence Development Studio to deploy your reports to your local reports server, you should now be able to.
  9. BOOM! Works on my machine :)

Tags:

SQL Server Reporting Services

SQL Server validating overlapping dates, date ranges

by pencilvac 4/11/2012 5:55:00 AM

When saving records to your database you may find that you cannot allow users to save multiple similar records with overlapping date ranges. The way I went about doing this was to check for the overlap in a SQL stored procedure in SQL server.

ALTER PROCEDURE [dbo].[UpsertGroupFieldValues]
    @GroupFieldValueID int = -1,
    @TargetEntityID int,
    @TargetPKID int,
    @PKID int,
    @EntityID int,
    @GroupFieldID int,
    @StartDate DateTime,
    @EndDate DateTime,
    @UserID int,
    @GroupValueIDReturned int OUTPUT

Out of all of these parameters If these parameters match a record that is already in the database, but without overlapping dates then we are good to save it. If however, the fields do match a record already in the database WITH overlapping dates, then we cannot save the record and we need to alert the user with an error.

To check for overlap I used an IF statement with a NOT EXISTS:

                //Check if there is a similar record (matching fields) with overlapping dates.//

                IF NOT EXISTS(select * from GroupFieldValues
                            WHERE TargetEntityID = @TargetEntityID
                            AND TargetPKID = @TargetPKID
                            AND PKID = @PKID
                            AND EntityID = @EntityID
                            AND GroupFieldID = @GroupFieldID
                            AND (
                                    (@StartDate BETWEEN StartDate AND EndDate)
                                    OR (@EndDate BETWEEN StartDate AND EndDate)
                                    OR(@StartDate <= StartDate AND @EndDate >= EndDate)
                                )


                   )                           

    --Basically we have a similar record. If we do have a similar record lets see if their dates overlap. If the parameter startdate is between the records start and end then it must be overlapping. If the parameter endDate is between the records start and end then it has to be overlapping. Otherwise the only other situation I can think of is if the parameter startdate is before the records startdate and the parameter @EndDate is after the records EndDate which means overlap. If this does not exist then we can go ahead and save the record.


                         
                BEGIN
                           YOUR INSERT STATEMENT
                END
                If it does exist ( an overlap) then we raise an error.
                ELSE
                    BEGIN
                        RAISERROR('Your new grouping was not saved because a similar group already exists within the same date range.',18,1)
                        --Print'Date Confliction'
                    END

ANOTHER TOPIC OF INTEREST: If you are doing an upsert stored procedure aka you are either inserting a new record or updating an already exisitng record with the same SPROC you will have to do a separate Date overlap test. Think of this situation: Your user goes into edit mode of a record in the front end of your application or website. The user changes the end date value to be November instead of October. When you check for overlap with the process above, the check will fail because there is a record with overlapping date ranges and your parameters (The record itself you are trying to update!). A way to get by this is performing separate checks based on whether you are trying to update or insert. Example:

 

IF @GroupFieldValueID = -1 --(our primary key) aka you are trying to insert a new record since its -1 (otherwise this will be the ID of the record you want to update)
    BEGIN --run our check for the new record
                IF NOT EXISTS(select * from GroupFieldValues
                            WHERE TargetEntityID = @TargetEntityID
                            AND TargetPKID = @TargetPKID --The company
                            AND PKID = @PKID --specific Value of contract, Location, Pipeline, Pricing Area from Drop downs.
                            AND EntityID = @EntityID --Entity were grouping on IE Pipeline or Contract
                            AND GroupFieldID = @GroupFieldID
                            AND (
                                    (@StartDate BETWEEN StartDate AND EndDate)
                                    OR (@EndDate BETWEEN StartDate AND EndDate)
                                    OR(@StartDate <= StartDate AND @EndDate >= EndDate)
                                )
                           
                            )   
                BEGIN
                        --PRINT'Start if no date confliction'

                               YOUR INSERT STATEMENT
                       
                END
               
                ELSE
                    BEGIN
                        RAISERROR('Your new grouping was not saved because a similar group already exists within the same date range.',18,1)
                        --Print'Date Confliction'
                    END
                   
                   
    END   
    ELSE--if @GroupFieldValueID != -1 meaning we are trying to update a record
        BEGIN --run a slightly different check where we specify that we are looking for an overlapping record that is NOT this record (AND GroupFieldValueID != @GroupFieldValueID)
            IF NOT EXISTS(select * from GroupFieldValues
                            WHERE TargetEntityID = @TargetEntityID
                            AND TargetPKID = @TargetPKID --The company
                            AND PKID = @PKID --specific Value of contract, Location, Pipeline, Pricing Area from Drop downs.
                            AND EntityID = @EntityID --Entity were grouping on IE Pipeline or Contract
                            AND GroupFieldID = @GroupFieldID
                            AND (
                                    (@StartDate BETWEEN StartDate AND EndDate)
                                    OR (@EndDate BETWEEN StartDate AND EndDate)
                                    OR(@StartDate <= StartDate AND @EndDate >= EndDate)
                                )
                            AND GroupFieldValueID != @GroupFieldValueID
            )
                BEGIN
                      YOUR UPDATE STATEMENT
                END
            ELSE
                BEGIN
                    RAISERROR('Your Group was not updated because there is another group with overlapping date ranges. ',18,1)
                END
        END   
END

 

BOOOM no more conflicting date ranges.

SQL Deleting Duplicate Rows

by ebarcza 4/5/2012 5:35:00 AM

Here is a SQL Example for deleting duplicate rows in a database.

  1. find them using appropriate group by and having clauses
    Select OperatorID, LocationID, ProdMonth, ProdYear, Count(*) as cnt   from CrudeDeals 
    where ProdMonth = 3 and ProdYear = 2012
    Group by OperatorID, LocationID, ProdMonth, ProdYear
    Having Count(*) > 1
  2. Isolate the first PKID of each of the duplicate Records sets
    Select MIN(CrudeDealID),OperatorID, LocationID, ProdMonth, ProdYear, Count(*) as cnt   from CrudeDeals 
    where ProdMonth = 3 and ProdYear = 2012
    Group by OperatorID, LocationID, ProdMonth, ProdYear
    Having Count(*) > 1
  3. Now Delete the first record of each of the duplicate sets
    Delete CrudeDeals where CrudeDealID in(
    Select MIN(CrudeDealID)  from CrudeDeals 
    where ProdMonth = 5 and ProdYear = 2012
    Group by OperatorID, LocationID, ProdMonth, ProdYear
    Having Count(*) > 1)

    BOOM! no more dups
Note that the first two SQL statement are really just affirming the data sets that you are about to delete. All the work could be done with Step 3 if you are cocky enough in your coding ability to pull the trigger without first inpsecting what records it will delete. So if you've got the balls, skip right to step 3!

Tags:

SQL Server Reporting Services

SUBSTRING in SSRS

by ebarcza 11/28/2011 11:43:00 AM
simple really... it's called the MID function in SSRS


MID(str as string, Start as int32, End as int32)


=Mid(Fields!CommodityType.Value, 2, Len(Fields!CommodityType.Value))

Tags: ,

SQL Server Reporting Services

New Line in RDLC

by ebarcza 8/18/2011 3:55:00 AM
Adding a new line to a SSRS report RDL textbox is simple =Fields!PeriodStart.Value + vbCrLf + "test"

Tags:

SQL Server Reporting Services

Finding Latest Objects Modified in SQL Server

by ebarcza 6/7/2011 12:20:00 PM
Simple little script to find all objects modified in the last 5 days

SELECT name AS object_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
,create_date
,modify_date
FROM sys.objects
WHERE modify_date > GETDATE() - 5
ORDER BY modify_date;
GO

Tags:

SQL Server Reporting Services

the edition of reporting services that you are using requires that you use local sql server

by ebarcza 5/5/2011 7:07:00 AM

When using Reporting Services in SQL Server Express you will most likely see this error at some point in time "The feature: the edition of reporting services that you are using requires that you use local sql server relation databases for report data sources and the report server database". is not supported in this edition of Reporting Services"

This error rears its ugly head when trying to view the report in Report Manager. You can, however, run the report in the Business Intelligence Studio just fine.

This is because SQL Express installs with an instance name of ServerName\SQLExpress. Visual Studio seems to ignore this instance name if there is only one instance running. When you deploy to the Server and try to run it through the report manager however you will get the "local sql server" error.

To fix this all you have to do is go into the Datasource and modify the connection string to use the instance name... like this - Data Source="ServerName\SQLEXPRESS";Initial Catalog=YourDatabaseName... I hope this helps!

Tags: , , ,

SQL Server Reporting Services

SSRS Blank page at the end of a report

by ebarcza 4/15/2011 7:42:00 AM
I always seem to get a blank page at the end of a report. It starts off just fine but then at some point a blank page will appear at the end. SSRS has a propery called ConsumeContainerWhite space that is set to False by default. Changing the value to true elimates the blank page in most cases. Note that this is a report level property. If it doesn't then make sure the size of the body isn't too big. Reduce the width of the body until the blank page goes away... Hope this helps!

Tags: , ,

SQL Server Reporting Services

Powered by BlogEngine.NET 1.5.0.7
Theme by Mads Kristensen

About the author

Name of author Author name
Something about me and what I do.

E-mail me Send mail

Calendar

<<  November 2017  >>
MoTuWeThFrSaSu
303112345
6789101112
13141516171819
20212223242526
27282930123
45678910

View posts in large calendar

Recent comments

Tags

Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2017

Sign in