Microsoft - Joel Lipman .Com

Reminder on SSRS row separator

: Joel Lipman; SQL Server Reporting Services; 26 October 2011; Hits: 9430

My Setup

XP SP3 Workstation
Business Intelligence Development Studio 2008 (BIDS)
SQL Server Reporting Services 2008 R2 (SSRS)

What do I want?
I have a report displaying room bookings. Each row lists the day, date, room name, start/finish times, booking details and the staff contact. I want an empty row to appear between each day in the list, so I have:

: Joel Lipman; SQL Server Reporting Services; 26 October 2011; Hits: 13587

The Problem
I've set some tablix headers but when I request the same report in PDF format, the tablix headers only appear once on the first page. Every subsequent page simply displays the report header (which does not include the tablix header... obviously). I right-clicked on the header row of the tablix and checked the box "Repeat header columns on each page.

: Joel Lipman; Excel; 30 September 2011; Hits: 10969

The Situation
I have a Microsoft Excel 2007 file that connects to a SQL Server 2008 R2 database. The Excel file pulls data using lookup tables and displays the data in an Excel Spreadsheet.

The Problem
We can select all cells and set row height to be 30 for example, but everytime we refresh the data in the Excel spreadsheet, all the rows get re-adjusted to fit the data and lose that consistency.

A Workaround: New line inserted before and after
So this is where I am at the moment without VBCode and other suggestions. Instead I add a newline in front of and after the smallest data value (one that I know will never be two lines (or two words)) within the SQL query itself. We have a DEPT column that is an acronym of the departments so for example:

SSRS External Images don't display

: Joel Lipman; SQL Server Reporting Services; 19 September 2011; Hits: 53138

The Situation
We want some photos taken from another website of ours to display in our report. These are not stored locally on the Reporting Server because other services use these photos on our Intranet and we only want one location to upload the photos. These images are provided via a website address (http) or network share (file).

Before you ask, we have several MS Sharepoint sites but we didn't go down the route of uploading 10000+ images into a Sharepoint database though we are still considering it; so this article is more about just getting external images to display when they are provided over an authenticated URL (You shouldn't be having any issues with this if the image is available to anonymous users - eg. Google Logo).

Our Setup

Windows XP Workstation
Business Intelligence Development Studio 2008 (BIDS)
SQL Server 2008 R2 Reporting Server (SSRS) running on Windows Server 2003
Team Foundation Server 2010 (TFS)

Problem #1: Image does not appear within SSRS
When previewing the report in BIDS, the photo appears fine. If we deploy the report to our Reporting Server and attempt to view it using a Web-Browser, the image does not display:

Get report to see images hosted on an external URL
Check if image exists to display alternative placeholding image.

: Joel Lipman; Excel; 05 September 2011; Hits: 17614

So I googled this for a while and there are a lot of solutions out there, none of which applied to what we meant and lots of people in the same boat.

The Situation
We have an Excel report which summarizes for our guys at the top, all the activities and time spent by staff. There are several filters available on the report (only a few to keep it simple silly). When you click on the filter, a dropdown appears with all available values listed.

The Problem
The values are listed in alphabetical order at first. If any new values come along then they get added to the bottom of the list... This is the problem. For example, if the year dropdown has a list of 2010, 2011, 2013; then if you add an entry which has year 2012, then the dropdown list will be in the following order: 2010, 2011, 2013, 2012.

The Solution

: Joel Lipman; SQL Server Reporting Services; 02 September 2011; Hits: 16682

I'm storing a note here because it took a while to figure out and googling other solutions did not answer our questions but did lead us to a workaround.

So this is regarding an error when trying to add parameters to a Stored Procedure of an Oracle database from within Microsoft SQL Server Reporting Services.

The Error

copy raw

ORA-00911: invalid character 
ORA-06512: at "SYS.DBMS_UTILITY", line 114 
ORA-06512: at line 1 (System.Data.OracleClient)

ORA-00911: invalid character
ORA-06512: at "SYS.DBMS_UTILITY", line 114
ORA-06512: at line 1 (System.Data.OracleClient)

The Situation
I'm using Business Intelligence Development Studio 2008 to develop a Reporting solution on a Windows XP workstation. We are connecting to a SQL Server 2008 R2 Reporting Service (SSRS) with Team Foundation Server 2010. The database with our stored procedure is Oracle 10g.

The Stored Procedure
Rather than a returned dataset, our Stored Procedure updates a field in one of our Oracle tables and returns nothing. It accepts 3 parameters: The StudentID (reference) varchar2, Username (who's running the report) varchar2 and a JobID (request reference) number.

Category: SQL Server Reporting Services :: Article: 380

: Joel Lipman; SQL Server Reporting Services; 12 August 2011; Hits: 10888

What?
This article serves to describe several workarounds or proofs of concept.

Why?
We have a first report which acts like a search page. You enter the student you are looking for by reference or name and if there is only 1 result in the results page, then we want it to redirect to the details report for that student automatically.

How?
So there are various solutions out there, here's an overview of some:

Method #1

Create one report with all selectable reports as subreports - toggle visibilty based on parameter.

Method #2

Add a button below "View Report" linked to some script code (requires change to ASP pages on the ReportingServer, ie. affects all reports on that server).

Method #3

Use ASP instead of SSRS.

Method #4

Use a TimeInterval refreshing the page after a certain time.

Conclusion
In the end, this came down to a design decision. Time spent on trying to find a solution was not considered to be warranted. My reports use a parameter for the connection string to specify which server and database to connect to and the follow on link was enough as a solution. My seniors advised that if we had time in the future we could expand on this further.

Google Searches that got me nowhere:

ssrs process parameter after report execution
auto-redirect after are a report is run
ssrs vbscript post report processing
ssrs auto redirect based on a dataset value

Before and After, Highs and Lows

: Joel Lipman; SQL Server Reporting Services; 03 August 2011; Hits: 8005

So I'm looking for a SQL query that could do this all in one go and return all the results in one table.

With PHP & MySQL it's pretty simple: use individual SQL queries to get the count of yesterday, yesterweek, yestermonth, yesteryear and do the layout in PHP.

Now let's say I have one RDL or SSRS Solution. I could do a dataset per SQL query but it doesn't seem that ideal.

In Theory:

: Joel Lipman; SQL Server Reporting Services; 03 August 2011; Hits: 8601

So I find myself using date ranges endlessly as I've been working in SSRS. The below is derived from a collection of various sources across the web as well as some of my own. These are what worked in my environment: WinXP, BIDS (vs2008), TFS (vs2010), SSRS 2008 R2. The following examples assume today's date is Wednesday 03 August 2011 @ 11:46:

This Week:

copy raw

-- Start Date (US format - mm/dd/yyyy)
=DateAdd("d", -(WeekDay(Today(),2))+1, Today()) // yields: 8/1/2011

-- End Date (US format - mm/dd/yyyy)
=DateAdd("d", -1, DateAdd("d", 7-(WeekDay(Today(),2))+1, Today())) // yields: 8/7/2011

-------------------------------------------------------------------------

-- Start Date (european format - dd/mm/yyyy)
=Format(DateAdd("d", -(WeekDay(Today(),2))+1, Today()), "dd/MM/yyyy") //yields 01/08/2011

-- End Date (european format - dd/mm/yyyy)
=Format(DateAdd("d", -1, DateAdd("d", 7-(WeekDay(Today(),2))+1, Today())), "dd/MM/yyyy") // yields: 07/08/2011

-- Start Date (US format - mm/dd/yyyy)
=DateAdd("d", -(WeekDay(Today(),2))+1, Today()) // yields: 8/1/2011
-- End Date (US format - mm/dd/yyyy)
=DateAdd("d", -1, DateAdd("d", 7-(WeekDay(Today(),2))+1, Today())) // yields: 8/7/2011
-------------------------------------------------------------------------
-- Start Date (european format - dd/mm/yyyy)
=Format(DateAdd("d", -(WeekDay(Today(),2))+1, Today()), "dd/MM/yyyy") //yields 01/08/2011
-- End Date (european format - dd/mm/yyyy)
=Format(DateAdd("d", -1, DateAdd("d", 7-(WeekDay(Today(),2))+1, Today())), "dd/MM/yyyy") // yields: 07/08/2011

This Month:

Category: SQL Server Reporting Services :: Article: 373

: Joel Lipman; Windows OS; 28 July 2011; Hits: 1796

Just a quick note here. This is an article based on the REG file from Kelly's Korner (@www.kellys-korner-xp.com/xp_tweaks.htm). I'm not fond of downloading REG files and running them even if I have checked what it's doing. I'm putting a note here just for me:

Start > Run > Regedit > OK
Browse to HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Policies\Explorer
Create New DWORD Value
Name it "MaxRecentDocs"
Double-click on the new DWORD
Set value data to "19" ( equals 25 )
Leave Base as "Hexadecimal"
OK
Restart your computer

Sort order a dropdown list in MS InfoPath without programming

: Joel Lipman; Infopath; 21 July 2011; Hits: 32514

So this is for Microsoft Office Infopath 2007 (SharePoint 2007).

The situation is that I started creating a SharePoint List (datasheet) and when I made my form dropdown read from the list, it just put elements in the order that I entered them in the datasheet.

Googling this led me to browse MSDN for an hour before I realised all those experts were using programming solutions that seemed a bit over the top for something that should be so simple.

Hey presto, I found a cheat/workaround:

Click on the list to see your datasheet (has a MS Access icon in the top left to remind you what you're getting yourself into)
Go to Settings
Select Create View
Select Datasheet View (you could probably use a "Standard View", I just used the Datasheet one)
Give the view a name, select what columns you want the form to have (I included the IDs for functional purposes)
Further down the "Create View" page, there should be a Sort section, specify the column to sort by.
Save the view by clicking the OK button.

You'll be returned to your datasheet and it's possible it isn't in any different order. I re-checked out my InfoPath form, looked at the dropdown that was already configured to read off the sharepoint list, and it had re-ordered to what I set in the view!!! I don't really understand how it works only that it does. I think this could get complicated if you had two dropdowns reading off the same list but needed to list elements in different orders.

: Joel Lipman; SQL Server Reporting Services; 19 July 2011; Hits: 15633

The situation is that we are replacing a website-application with a single SQL Server Reporting Services (SSRS 2008 R2) report. We're using Business Intelligence Development Studio (BIDS VS2008) with Team Foundation Server (TFS VS2010) and connecting to an Oracle database (ie. "seamless integration because our setup is perfect and well thought through" not - note the DBMS is not hugely relevant for the purposes of this article).

The end-user must be able to search on EITHER the student's username or the student's ID (2 report parameters: @StudentADAccount [varchar] and @StudentReference [int] respectively). Most of the remaining datasets use the resulting @StudentReference number in their "where" clause. A student always has a "Student Reference" but not necessarily a student AD account (enquired/applied only).

Reminder on SSRS row separator

SSRS Repeat Headers in PDF Report

Stop Excel Row Height Self-Adjust on Refresh

SSRS External Images don't display

Excel PivotTable Filter List Ordering

SSRS Parameters in Oracle Stored Procedure

SSRS Redirect after a report is run

Before and After, Highs and Lows

SSRS / MDX Date Queries

Increase Recent Documents Number in Windows XP

Sort order a dropdown list in MS InfoPath without programming

SSRS Querying on either of 2 Parameters

Excel

Infopath

SharePoint

SQL Server

SQL Server Integration Services

SQL Server Reporting Services

Team Foundation Server

Windows OS

Credit where Credit is Due:

RSS Feed

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Donate & Support