Ran into this error tonight trying to uninstall SQL Server Reporting Services. Not sure if it is just Vista, or XP and other OS’s as well, but the fix is to stop IIS and then re-run the Uninstall.
Tag: SSRS 2005
Ok, add this one to the annoying bin. If you are writing SSRS (SQL Server Reporting Services) reports of a SSAS (SQL Server Analysis Services) cube, and there is a time dim, you need to format the date parameters in the SSRS report to match the SSAS date format, as a string.
I started out doing this a while ago, like so:
=”[Time].[Calendar Date].&[“& Year(DateAdd(“D”,-1,DateTime.Now())) &”-“& IIf(Len(Month(DateAdd(“D”,-1,DateTime.Now()))) < 2,Month(DateAdd(“D”,-1,DateTime.Now())),”0″&Month(DateAdd(“D”,-1,DateTime.Now()))) &”-“& IIF(Len(Day(DateAdd(“D”,-1,DateTime.Now())).ToString()) < 2,”0″ & Day(DateAdd(“D”,-1,DateTime.Now())).ToString(),Day(DateAdd(“D”,-1,DateTime.Now())).ToString()) &”T00:00:00]”
Wow.. Ugly, hard to read, hard to debug. Just ran into issues, etc.
I decided to finally just write a function to do the same thing and make sure it is correct once and for all..
Function GetSSASDate(ByVal DateToRun As DateTime) As String
Dim result As String = “[Time].[Calendar Date].&[”
result = result & Year(DateToRun).ToString() & “-”
If Month(DateToRun).ToString().Length < 2 Then
result = result & “0”
End If
result = result & Month(DateToRun).ToString() & “-”
If Day(DateToRun).ToString().Length < 2 Then
result = result & “0”
End If
result = result & Day(DateToRun).ToString() & “T00:00:00]”
Return result
End Function
then, I go to my report properties, Custom Code and add that function, then in my parameter I call it like so:
=Code.GetSSASDate(DateAdd(DateInterval.Day,-1,DateTime.Now()))
Much cleaner, easier and just all around good. If I wanted to take this a step further, I would actually just make an assembly with some useful functions and deploy to my SSRS server, but I will save that for a later time.
Technorati tags: SSRS, SSAS, SQL Server Reporting Services, SQL Server Analysis Services, SSAS Dates, Dates, VB.NET, Code
The other day, I was tasked with moving all objects from one SQL Server Reporting Services instance to another. I know you can move the database itself, but then you run into issues with encryption keys etc. I just wanted to move the objects. I know that pretty much everything in SSRS is extensible, so I knew I could write something to do it, but before I went out and coded it, I Googled first to see if someone else had something. Turns out there is an awesome utility: Reporting Services Scripter. Works like a charm!
Technorati tags: Reporting Services, SQL Server 2005 Reporting Services, Reporting Services Scripter, SSRS
If you have a SSRS (SQL Server Reporting Services) report, and you want to aggregate values on a column, the function RunningValue() is what you would use.
The function takes 3 parameters, the field you want to “run the value” on, the function you want to run on it (Sum for example), and then the scope. The scope can be “Nothing” ex: RunningValue(Fields!Blah.Value,Sum,Nothing) but where the scope really comes into play is when you want to group by given fields in your row.
So if you want to group by say, salesperson, and run their values by day of their sales for a month to date total, you would have
Salesperson Date Daily Sales Month To Date Sales
——————————————————————————-
Homer Simpson
07/01/2007 50 50
07/02/2007 43 93
Bart Simpson
07/01/2007 5 5
07/02/2007 8 13
How you achieve this is: on the row with the salesperson, there is a group defined if you right click. Look at the group name, by default it is usually table1_detailsGroup or something similar (if you used the wizard).
Then, the column for the Month to Date Sales would have the expression
=RunningValue(Fields!DailySales.Value,Sum,”table1_detailsGroup”)
Where you can see the scope is now defined as the table group name as a string.
Technorati tags: SSRS, SQL Server 2005, SQL Server Reporting Services, RunningValue, Scope, Reporting Services, Cumaliative Value
Earlier I blogged about exporting SSRS (SQL Server Reporting Services) Reports to Excel and keeping formatting. Well, my previous solution works, but not always (doh!)
I found in a new report that I created that the formatting was taking on all columns. The solution? Wrap those expressions in CDec() function and it works. It was for some currency and percentage formatted columns.
Nothing can ever be easy!
Technorati tags: SSRS, SQL Server 2005, SQL Server 2005 Reporting Services, Excel, Reporting Services, Number Formatting, CDec
When creating Reporting Services (SSRS) reports, I usually use FormatCurrency() and FormatPercent() around values to make the report look good. Today I found out that when you use those, and export to Excel, you lose the ability to sum the values, because everything is exported as text.
The right way (or at least the way that I have found that works) is this. Remove all the FormatCurrency() and FormatPercent() functions. On all your currency fields, highlight them, then in the properties area, there is a “Format” property. I set that to C2 (C for Currency, 2 for the number of digits after the decimal, so if you wanted zero, it would be C0). Again for Percentages, same thing, highlight all the fields, and set “Format” to P2 (P for Percentage, 2 for the number of digits after the decimal).
Technorati tags: SSRS, SQL Server 2005, SQL Server 2005 Reporting Services, Excel, Reporting Services, Number Formatting
I just got my new laptop, and I am installing Visual Studio 2005 and SQL Server 2005. Ran into a snag, that took me a little bit to find the answer.
First, SQL Server 2005 setup is just ghetto. There are two setups, “server” and “tools” and sometimes it just doesn’t work right. Anyways, only SQL Server 2005 with SP2 is supported on Vista. You would think that there would be a slipstream download with the RTM and SP2? Nope. Not that I could find at least.
Other thing I found. By default, you cannot install Reporting Services (SSRS). Reporting Services depends on IIS to run the report web server. Even though I had IIS installed on Vista, it still wasn’t finding it. Searching Google for “SQL Server 2005 Reporting Services Vista” didn’t really give me much luck (easily).
Eventually I found this KB article http://support.microsoft.com/default.aspx/kb/920201
Basically you need to turn more things on in IIS. I turned pretty much everything on but classic ASP and CGI, reinstalled SQL 2005 and no warnings – great! 🙂
So, I go to connect to my instance, and no AdventureWorks DB. I installed everything I thought? WTF? Well, I found this article on reinstalling AdventureWorks sample OLTP and DW databases
http://msdn2.microsoft.com/en-us/library/ms143758.aspx
Because you know, once you install SP2 on SQL Server 2005, you can’t really run the RTM setup without it complaining, and then, it says you already have a newer version installed – bah!
So, in the end, SQL Server 2005 with all the fixings is doable on Vista. Just make sure you have IIS configured correctly!
In your Query Builder, you can filter a dimension using MDX, here is how you get the TOP N
TOPCOUNT({Dimension Member},N,{Measure.Value})
Comes in pretty handy for building reports.
If you ever have written any Reporting Services reports off a cube (Analysis Services), and want to have a drop down that auto picks a day, this is for you. There is probably a more elegant way to do this, but it works.
=”[Time].[Calendar Date].&[“& Year(DateAdd(“D”,-1,DateTime.Now())) &”-“& IIf(Len(Month(DateAdd(“D”,-1,DateTime.Now()))) < 2,Month(DateAdd("D",-1,DateTime.Now())),"0"&Month(DateAdd("D",-1,DateTime.Now()))) &"-"& IIf(Len(Day(DateAdd("D",-1,DateTime.Now()))) < 2,"0"&Day(DateAdd("D",-1,DateTime.Now())),Day(DateAdd("D",-1,DateTime.Now()))) &"T00:00:00]"
Remember, months and days need to have a zero in the front of them.
SQL Server Reporting Services is sort of an add on to Microsoft SQL Server. It allows you to develop reports within Visual Studio (or any other editor that can create report definition language files (RDL))
and then the reporting services talk to IIS with web services to publish reports. You can also set up subscriptions to reports, etc.
At first glance it looks like a panacea for anyone who has ever had to write reports. It allows you to go through some wizards, and create some very complex reports, and then publish them in html, xls, xml, pdf, text, etc. This is all great, but where it starts to break down is with the security.
If your organization is set up in a perfect world (which I am sure it isn’t) then reporting services will probably work for you. The main way to set up security is with NT authentication, and that just doesn’t work for some organizations. Also, parameters are passed through the querystring, so they are easily manipulated, so you can’t force a report to be ran the exact way you want to.
Overall, reporting services is pretty cool, but I just don’t think I would use it in what I do. If I was making an application for one organization where every user had the same level of rights/viewabilty, then I think it would work well.
I also saw a presentation on reporting servers at the SQL Users Group in the Twin Cities a few months back where they went into more detail then the MSDN event, and I still came to the same conclusion. I just don’t think it would work for me, but It is still a cool product, that will (hopefully) squash Crystal Reports 😉
Steve Novoselac's Blog 