Tag Archives: SSRS 2005

Reporting Services: Can’t Uninstall – The setup failed to read IIsMimeMap table. The error code is -2147024893

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.

Advertisements

SSRS and SSAS Formatting Dates

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.

Reporting Services Scripter: Sync Reporting Services Instances and Objects

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!

SSRS: RunningValue() to get Cumulative Totals

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.

SSRS Exporting Report to Excel – Keep Formatting on Export Round 2

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!

SSRS Exporting Report to Excel – Keep Formatting on Export

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

SQL Server 2005 – Reporting Services – Windows Vista

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!