Articles - Joel Lipman .Com

SSRS Redirect after a report is run

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

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

The ReportServer Database

: Joel Lipman; Databases; 08 August 2011; Hits: 7468

Amazing, I have just spent all morning on Microsoft websites to determine what number is the TimeDataRetrieval column displaying. Thank you I know it's an INT. There is just a serious lack of documentation as to what this database is and how it populates its data.

View: ExecutionLog

InstanceName	nvarchar(38)	NOT NULL	Name of the report server instance that handled the request. Usually YOURSERVERNAME\MSSQLSERVER
ReportID	uniqueidentifier	NULL	The ID of the report (looks like a hexadecimal SSID). It's the unique ID of the report but not unique in the table (can be referenced many times).
UserName	nvarchar(260)	NULL	Windows authenticated username and domain of the person running the report (eg. MYDOMAIN\myusername)
RequestType	bit	NOT NULL	User or System. Can be 1 or 0. This was zero "0" when I would run a report as a user.
Format	nvarchar(26)	NULL	This is the rendering format. Mostly RPL if viewed in MS Internet Explorer.
Parameters	ntext	NULL	Parameters and the values they were submitted with.
TimeStart	datetime	NOT NULL	Time report started to run.
TimeEnd	datetime	NOT NULL	Time report finished running? Need to check what finished?
TimeDataRetrieval	int	NOT NULL	Milliseconds spent retrieving the data.
TimeProcessing	int	NOT NULL	Milliseconds spent processing the report.
TimeRendering	int	NOT NULL	Milliseconds spent rendering the report.
Source	int	NOT NULL	Source of the report exection (1=Live, 2=Cache, 3=Snapshot, 4=History)
Status	nvarchar(32)	NOT NULL	either rsSuccess or an error code; if multiple errors occur, only the first error is recorded
ByteCount	bigint	NOT NULL	Size of rendered reports in bytes.
RowCount	bigint	NOT NULL	Number of rows returned from queries.

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

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: 8630

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: 1821

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: 32547

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: 15672

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

: Joel Lipman; AutoHotkey; 08 July 2011; Hits: 13641

What's this all about? Well I want the basic hover effect: when my mouse cursor hovers over a link, I want that link to turn blue and display an underline. When I move the cursor away from the link, I want the link returned to black without an underline.

How?

: Joel Lipman; Linux; 05 July 2011; Hits: 10928

I could just remember to search wikipedia for "Date (Unix)" or search my site for "Unix". This was easier.

Source: Wikipedia: Date (Unix)

: Joel Lipman; AutoHotkey; 05 July 2011; Hits: 20665

Apologies for copying and pasting this but this is information that is worth NOT losing. Tutorial on how to use COM objects in AutoHotkey.

Source: Jethrow @ Autohotkey Forums

: Joel Lipman; AutoHotkey; 05 July 2011; Hits: 12519

Apologies for copying this from another source but with lots of OpenSource developers disappearing and reappearing, the content is what I don't want to lose.

: Joel Lipman; Windows OS; 05 July 2011; Hits: 6285

What?
This is an article intended for IT Support. It is a list of the system tools available in the Microsoft Windows OS that can be run from the "Start > Run" option.

How?

Credit where Credit is Due:

RSS Feed

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Donate & Support