Business Intelligence Geeky/Programming SQLServerPedia Syndication

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


Where you can see the scope is now defined as the table group name as a string.

Technorati tags: , , , , , ,
Business Intelligence Geeky/Programming SQLServerPedia Syndication

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!

Technorati tags: , , , , , ,
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SSAS 2005 – Named Sets – TopCount – Static/Dynamic and Ordering

Named Sets in SSAS 2005 (SQL Server Analysis Services 2005) are a good thing. You can set up named sets for things like “Last 3 months” or “Yesterday” which is really nice for end users. The nature of dates is that you can make the MDX dynamic but yet the named set is still “static”.

You can also create sets when running MDX queries, and use them later in queries you run. These are dynamic named sets. The problem is, when you are working on your cube, you cannot define dynamic named sets in the “Calculations” tab. The named sets there are static. If you try to define a named set that say, for instance, tries to get the “Top X Salespeople by Revenue” or something, you can create it, but what happens is when you deploy your cube, the named set gets run for one slice of the cube, usually just the default (   and

I did read on a site, that in SSAS 2008, you will be able to create dynamic named sets in the cube editor, so that is good.

One other thing with named sets, if you create a named set that is using TopCount, when you bring it into an Excel pivot table, you will see the Top X values you are looking for (sliced incorrectly, as I stated above, but still there). Thing is, they are sorted by the SET Expression, not the Numeric Expression. I haven’t found a way to get them to sort by the numeric expression. Example from above, you have the Top 3 Salespeople by Revenue. You want to see that sorted by revenue. If you run a MDX query using TopCount

{[Measures].[Revenue]} ON COLUMNS,
    [Measures].[Revenue])} ON ROWS

you will see it ordered in the query results correctly

John Smith           $1000
Amanda Jones      $950
Homer Simpson     $500


Now if you take the TopCount out –


and create a named set in your MDX Script of the cube editor

CREATE SET CURRENTCUBE.[Top 3 Salespeople by Revenue]
 AS TOPCOUNT([Employees].[Salespeople].MEMBERS,3,[Measures].[Revenue]) ;        

or even like this as the forums above say

CREATE SET CURRENTCUBE.[Top 3 Salespeople by Revenue]
 AS TOPCOUNT(Existing [Employees].[Salespeople].MEMBERS,3,[Measures].[Revenue]) ;  

When you pull that named set into your pivot table in Excel, they wont be ordered by Revenue, instead, by name

Amanda Jones      $950
Homer Simpson     $500
John Smith           $1000

Funky stuff. My recommendation – if you are going to be needed dynamic named sets like the ones created above, just create a SSRS (SQL Server Reporting Services) report instead, or wait until SSAS 2008 comes out.


Now, the above is just what I have found. There might some crazy way that I am unsure of in MDX or somewhere to get this to work, but I can’t find anything that would let me do what I want.

Technorati tags: , , , , , , , , , , , , , , ,
Geeky/Programming SQLServerPedia Syndication

SQL Server: SELECT Machine, Computer, Instance Names

to get the instance name: SELECT @@ServerName

to get the machine name of the server: SELECT SERVERPROPERTY(“MachineName”)

to get the machine name of the computer running the query: SELECT host_name()

one tip: cast the serverproperty as a varchar if you want to use it in a string or you will get errors in your stored procs/queries.

Technorati tags: , , , , , ,
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SSAS: AverageOfChildern and Semi-additive measure requires a time dimension

In SSAS (SQL Server Analysis Services), if you have a measure group, with some measures as AggregateFunction of Sum and some of AverageOfChildern, you will see a red squiggly line on the measure group and if you highlight it (or build the cube), you will get this error:

Semi-additive measure requires a time dimension.

The problem starts with the AggregateFunction property of your measures, where some are Additive, some are Semi-Additive. You can see which are which by looking at MSDN here. What I found will get rid of the error, but still might not help you is this: if you go to the properties of your Time dimension, and check its type. By default dimensions are created with type “Regular”. If you change your Time dim to type “Time”, then rebuild, you won’t get any errors, and you can deploy.

Now, this might be all good, but in the case I ran into, it still didn’t work for me. AverageOfChildern only applies to the Time dim. Not what you would expect from looking at it. Say you have a measure, that you want the Average (AVG) instead of Sum, you would think that picking AverageOfChildren as a AggregateFunction property would do the trick, well it doesn’t. If you do this, deploy, and browse your cube, you will still see that the values are Sum. WTF right? Well like it sparsely says in the docs, it only applies to the Time dim.

In order to get the Average of a measure, here is the best way to go about it that I have found.

1) Create a Measure from the field for Sum
2) Create a Measure from the field for Count
3) Create a Calculated Member (Measure) for the field, taking Sum/Count from step 1 and 2.
3a) you can check for 0’s in the count by using IIF()
3b) for best performance, check your NON_EMPTY_BEHAVIOR and if it applies to you, it should speed up your results tremendously. Usually you can just choose the fields you are using in your calculation and it should be fine from what I have seen (I am no expert though)
4) make sure to hide your Sum and Count measures if you don’t want your clients to use them by setting the Visible property to false.
5) Build, Deploy, Process, Report


Technorati tags: , , , , , , , ,
Business Intelligence Geeky/Programming SQLServerPedia Syndication

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

Technorati tags: , , , , ,
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SSAS: Deploying Cube OLE DB Error – Login Failed

If you are deploying/building a cube in SQL Server Analysis Services (SSAS), and you get tons of errors, and littered throughout you see

OLE DB error: OLE DB or ODBC error: Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’.; 28000.

You should take a look at your data sources, and on the impersonation tab, play around with the different options. Usually it is set to “Use the service account” or “default”. I have tried “Use the credentials of the current user” but got errors back when deploying saying that the impersonation method was invalid for processing. I have found that “Use a specific user name and password” works. As I sit and wait for the cube I just finally got to deploy to finish, I figured I would write this up in a post. Your mileage may vary.

Technorati tags: , ,
Business Intelligence Geeky/Programming SQLServerPedia Syndication

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

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

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!

Technorati tags: , , , , , , , , , , , , ,