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
  1. Create one report with all selectable reports as subreports - toggle visibilty based on parameter.

Method #2
  1. 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
  1. Use ASP instead of SSRS.

Method #4
  1. 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

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:

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:
copyraw
-- 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
  1.  -- Start Date (US format - mm/dd/yyyy) 
  2.  =DateAdd("d", -(WeekDay(Today(),2))+1, Today()) // yields: 8/1/2011 
  3.   
  4.  -- End Date (US format - mm/dd/yyyy) 
  5.  =DateAdd("d", -1, DateAdd("d", 7-(WeekDay(Today(),2))+1, Today())) // yields: 8/7/2011 
  6.   
  7.  ------------------------------------------------------------------------- 
  8.   
  9.  -- Start Date (european format - dd/mm/yyyy) 
  10.  =Format(DateAdd("d", -(WeekDay(Today(),2))+1, Today()), "dd/MM/yyyy") //yields 01/08/2011 
  11.   
  12.  -- End Date (european format - dd/mm/yyyy) 
  13.  =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

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:

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

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:

  1. 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)
  2. Go to Settings
  3. Select Create View
  4. Select Datasheet View (you could probably use a "Standard View", I just used the Datasheet one)
  5. Give the view a name, select what columns you want the form to have (I included the IDs for functional purposes)
  6. Further down the "Create View" page, there should be a Sort section, specify the column to sort by.
  7. 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.


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

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?

This must have been so obvious to everyone else that nobody bothered to write an article on it... till now.

Situation
I have a report that returns room bookings based on a user and given a date range. The problem is that there are a few thousand users and Microsoft's SQL Server Reporting Services interface isn't the most fun to scroll endlessly down. Advanced users can type the name really fast for it to auto-scroll down to the desired name. Our advanced users are exceptions to the rule.

Problem

  1. Open a Windows Explorer and make a duplicate of the report that you want to use as a template.
  2. Rename the copy (suffix with template?)
  3. Remove objects unique to the report and leave elements for all reports.
  4. File > Save selected item as...
  5. For SSRS 2008: Save in the folder: "C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject"
  6. Done!

Update August 2011
So I don't know how many people were just saying why don't you do the following:
  1. Bring up the "Tablix Properties" of the dataset
  2. Look for the section "No Rows"
  3. Put in a value for "NoRowsMessage".
This solution hides the entire Tablix which kinda defeats the purpose of having anything there. I want my end-users to have faith in my report. The report just returning blank can also mean "I don't know". I want the report to either return the correct value or say "No data found". My end-users would otherwise ask is the data value reliable if it's blank?


The long way of doing this
I would have called this article "Iteration within SQL Server Reporting Services Business Intelligence Development Studio (BIDS) version 2008 through combining Transact-SQL and MDX expressions" but boy what a mouthful, and it's not really iterating anymore. So it's "SSRS Hide results table if empty" though I will add that if you wanted to put a message instead of hiding the table then following the below will also let you do this (requirements: common sense or the IQ of a duck).

It's what I've been searching for for the past hour and although you may think I'm just adding to the cyberspace pile of useless info, at least I'm not just copying and pasting from other sites to add content to my own. And I'm not just adding content, the way I'm doing the below is nothing similar to what I googled (probably a bad sign but time is ticking and no one has forever).


Re: Business Intelligence Development Studio 2008

Often happens to me and not sure why. You open the project and the window panel for the Report Data containing the parameters and datasets isn't there. I spent a bit of time enabling/disabling toolbars before I found this.
  1. Open a Report solution (or have one open already?)
  2. The menu link is "report data" and it's right at the bottom of the "View" menu.
Report Data at the bottom of the View menu when a report is loaded in the editor

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

RSS Feed

Related Articles

Joes Revolver Map

Joes Word Cloud

source   function   form   page   google   field   where   creator   uploaded   note   date   need   find   file   table   data   list   error   using   joomla   server   used   parameter   following   system   mysql   display   added   create   order   name   would   first   report   script   files   code   time   database   version   deluge   client   website   windows   work   zoho   user   case   value   license   JoelLipman.Com

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.