2/19/2011

Microsoft SSRS Cross Browsers

I'm on board with Microsoft as my main development platform, it's the direction I've taken.  The software is user friendly, there's a lot of info on the web for researching, you can get up to speed on new tech pretty fast, the community support is vast, the only thing I can't figure out is:


Why doesn't Microsoft play nice across all browsers? 

How is a Microsoft developer supposed to respond to users when asked why SSRS Reports and Share Point do not display properly in Firefox or Chrome?

I'm sure the roots go deep: Microsoft vs. Java; MS dos vs. PC dos; MS not compatible on Unix/Linux.


To assume that all users on the web will be using a version of Internet Explorer is kind of silly, so shouldn't a product intended to be distributed to as many users as possible be compatible with most of the browsers available?

I realize there are plug ins for Firefox that will allow users to render the reports as if they were viewing IE and some of the blame should be aimed at IE competitors.

I'm just saying...

2/17/2011

Limit Report Results Based on User Logon

Sometimes there's a need to add security to your SSRS report.  You can limit what values your users can see by adding a new parameter and modifying your "where" clause to equal the user - that way they can only see what they have rights to:

Create a parameter called User 
     (String)
     (Hidden)
     (Non-Queried)
     with a Default Value:  

=Right(User!UserID, Len(User!UserID)-InStr(User!UserID,"\"))

And in your DataSet add Parameter:
     Name: @User
     Value: =Parameters!User.Value

Add a WHERE Clause to your Dataset:
     WHERE n.LoginID=@User

Should be good to go!

Troubleshooting Subscriptions Link

Here's a good post on Troubleshooting Subscriptions...

Click here for link...

New Territory - Recent Questions

Working with SSRS 100% now (no more Java, Oracle, Crystal Reports, etc) I find myself in new territory each day.

My latest path has brought me to some new questions:

Does SSRS Data Driven Subscriptions run Concurrently or Sequentially?
Why do some SSRS DDS "Run Once" for the following day never execute?
Will isolating the ReportServerTempDB to its own drive increase performance?
Will increasing the SSRS server Virtual memory to 1.5 x Physical Memory increase performance?
Is defragging the SSRS server recommended periodically?

Just a few of my latest concerns.

I'm sure there will be more to come.

2/09/2011

PASS Data Warehousing and Business Intelligence Virtual Chapter

Here's the link for PASS Data Warehousing and Business Intelligence Virtual Chapter:

http://bi.sqlpass.org/

This what I like about programming / reporting with Microsoft, they have the best user community!!!

2/07/2011

SQL Server 2008 R2 Parallel Data Warehouse

Tonight's Tampa Bay SQL Server BI Users Group meeting was about:

SQL Server 2008 R2 Parallel Data Warehouse


Interesting topic. Deals with buying pre-assembled hardware/software which you plug in and load your data warehouse. It's designed to handled "a-lot" of data in the terabytes on up. The processor dynamically load balances the data into 8-10 servers all running "vanilla" SQL-Server. Each server is then split up into 8 file structures so you can split a 10 terabyte database into 10 server of 1 terabyte into 8 file structures of about 120 gigs. It's quite powerful, but it works for just data warehouses (no OLTP at this time). The warehouse can be accessed directly through JDBC, ODBC, ADO.net as well as SSRS, SSAS through native code.

The presenter, Brian Mitchell, did a wonderful job keeping us entertained for 2+ hours. And the free pizza was delicious (Pizza Hut).

I think it was worth taking the night off from work (Part Time) to attend the meeting. Can't wait for next months meeting.

And that's about it.

Link SSRS Subscriptions to SQL-Server Agent Jobs

Here's a link I stumbed upon today while searching for a way to identify SSRS Subscriptions to SQL-Server Agent Jobs

http://stackoverflow.com/questions/1362722/how-can-i-link-a-sql-server-job-name

2/03/2011

SSRS Data Driven Subscriptions - Just one instead of many

This article describes how to run just one SSRS Data Driven Subscription which reads from a database table or even a giant UNION statement instead of writing a seperate DDS for each combination. Saves time and controlled at the table level for adding / subtracting reports:

http://msdn.microsoft.com/en-us/library/aa237784(v=sql.80).aspx

Pass in Multiple Parameters - Data Driven Subscriptions (DDS)

I was looking to pass in multiple parameters for a given parameter using SSRS data driven subscriptions.

It took some investigation and a lot of trial and error.

However, the following link pointed me in the right direction:

http://www.bigresource.com/Tracker/Track-ms_sql-JMW7Arql/

It turns out you have to set the parameter's "Available Values" to NON-QUERIED as well as your "Default Values = to NON-QUERIED.  And be sure to enter some dummy default values or it won't take.

Once you get the report uploaded and setting the values, you can simply select multiple values from the drop down list under Specify a static value:

Hope that helps!

Dynamic Date Generated File Name

When creating an SSRS Data Driven Subscription, the customer requested a need for adding the date (not time) to the exported file name.

The way this is done is to append the Year + Month + Day in SQL

Select

'Report_Name_' +
(
CONVERT(CHAR(4), YEAR( GETDATE() ) ) + '-' +
RIGHT('00' + CONVERT(VARCHAR(2), MONTH( GETDATE() ) ), 2) + '-' +
RIGHT('00' + CONVERT(VARCHAR(2), DAY( GETDATE() ) ), 2)
)
as 'File name'

Microsoft SSRS Data Driven Subscription Error

In trying to setup a new Data Driven Subscription, I ran across an error:

Status: Failure writing file HS_AllCourses_NoDups_Mo_2011-02-03.xls : A logon error occurred when attempting to access the file share. The user account or password is not valid.

Apparently the userid must be prefixed with the domain:

<domain>\<user name>

I found this posting on the web.

http://www.developmentnow.com/g/115_2007_3_0_0_945500/Delivery-to-FileShare-on-Different-Domain.htm

2/01/2011

Application Administrator

My new role is: Application Administrator starting this week.

I now administer the SQL Server Reporting Services Server but more important, I prioritize the work load for two programmers. I also get to converse with the customers, create some of the new complex reports in SSRS and set up subscriptions on the server.

Today I was tasked with converting some of the Matrix reports into SQL Server Analysis Services (SSAS). That's been my goal for the past year, to work specifically in the SQL Server BI stack and finally get to do some SSAS.

I'm pretty excited with the recent change. Had my first meeting today with the team to get to know each other and find out what it is they do. I plan to leave things in place but I would like to implement some standards around report naming conventions, clean up the SSRS folder structure as well as Visual Source Safe.

When I applied for the position, I didn't realize fully that it was a supervisor position, thought it was just mentoring. But now that its come to fruition, I kind of like the new role.

And that's the update!

Mountain Living