Call today 0845 643 6463

Blog

  • Clearing SSRS Query cache

    April 7th, 2010 in .Net, Blog, Reporting Services

    When developing SQL Server Reporting Services (SSRS) reports, BIDS caches the query results when you preview the report. This cache is then used next time you run a preview. This has the benefit of speeding up report development, but it does cause a problem when you want to test changing data. A simple way of forcing the cache to refresh is to open the folder containing the .rdl report files, and delete the corresponding .rdl.data files. The…

    » Read more

  • Excel Cube Pivot drillthrough limited to 1000 rows

    March 25th, 2010 in Analysis Services, Blog

    When browsing a cube using Excel 2007, you can drillthrough the measures to display up to 1000 rows of the transaction level source data. I often get asked whether this limit of 1000 rows is configurable – well the good news is yes it is. There is an option in the actions tab of the BIDS cube designer which allows you to specify the maximum rows, but helpfully this is ignored by Excel. Instead, you have to set it in Excel when you create a…

    » Read more

  • Loan Amortisation in SQL Server (PMT, FV, IPMT, PPMT)

    March 17th, 2010 in .Net, Blog, SQL Server

    Whilst designing a data warehouse for a banking client recently, I needed to calculate projected future loan payments (including breaking this down by interest and capital payments) for every customer throughout the life of the loan. In Excel this is a pretty simple job, as Microsoft helpfully provide a number of functions to do just that (namely PMT, FV, IPMT and PPMT). In SQL Server however we do not have the luxury of having ready made…

    » Read more

  • SQL Bits 6 – Registration Open

    March 17th, 2010 in Blog, SQL Server

    For anyone interested in SQL Server, the unmissable SQL Bits conference is returning for round six. “SQL Bits – The 6th Sets” is being held in central London for the first time, and unlike previous conferences it will be for one day only on Friday 16th April 2010. It’s being held as part of the SQL Server 2008 R2 release, also happening that week in London. Registration is now open, you can find out more at the SQL Bits…

    » Read more

  • MDX Sub select Vs WHERE clause

    March 2nd, 2010 in Analysis Services, Blog

    I’ve just read an interesting thread on the SQL Server Developer Center forum, regarding how to filter results. Specifically the difference in MDX between using a subselect SELECT x on COLUMNS, y on ROWS FROM ( SELECT z on COLUMNS FROM cube)) or using a where clause SELECT x on COLUMNS, y on ROWS FROM cube WHERE z In a simple query they produce the same results, but what is the actual difference? You can read the full thread here, but to…

    » Read more

  • Data Warehouse Presentation

    February 23rd, 2010 in Blog, SQL Server

    Purple Frog is presenting a session on data warehouse design concepts at the South Wales SQL Server User Group on Thursday 25th February 2010. If you’re in the area and want to come along you can register for free here. Eversheds are hosting the event in their Cardiff office. Adam Morton will be demonstrating an ETL control framework in SSIS, and Alex Whittles will be discussing the concepts of data warehousing and the fundamental…

    » Read more

  • Scope Problems with MDX Calculated Members

    November 18th, 2009 in Analysis Services, Blog

    We were recently investigating a problem for a client regarding the use of Scope within MDX calculated members. The code in question was similar to this: CREATE MEMBER CURRENTCUBE.[Measures].[Test Measure To Date] AS "NA", VISIBLE = 1; Scope([Date].[Calendar].MEMBERS); [Measures].[Test Measure To Date] = SUM(NULL:[Date].[Calendar].CurrentMember, [Measures].[Test Measure]); End Scope; Scope([Date].[Fiscal].MEMBERS); [Measures].[Test Measure To…

    » Read more

  • Excel 2007 and SSAS 2008 Error

    October 30th, 2009 in Analysis Services, Blog

    I was working on a new SSAS 2008 cube today, and came across an error which Google was unable to help with. I thought I’d post the solution here to help anyone else who may encounter it. The cube in question will be primarily be accessed using Excel 2007, so I’d been dutifully testing it along the way to ensure all was well. And then, after a number of changes the following error appeared when connecting to the cube from Excel to…

    » Read more

  • Style Sheets with Reporting Services

    June 14th, 2009 in Blog, Reporting Services

    Firstly sorry for not posting anything for a while, I can only blame spending too much time working and on my uni assignments – not much time free these days! I stumbled upon a great post by Barry King, demonstrating a way of applying database driven style configuration to a set of reporting services reports, in the absence of any style sheet support in SSRS. It doesn’t rely on custom code (most people’s favourite way of…

    » Read more

  • Dynamic MDX in Reporting Services

    September 18th, 2008 in Analysis Services, Blog, Reporting Services, SQL Server

    After a couple of days off work last week with SQL Bits III and SQL 2008 Unleashed, it’s back to the grindstone this week; however before I get onto the serious stuff I’d like to say thank you to the organisers of both events. Watching you on stage headbanging to Rockstar – legendary! (You can see for yourself here and here…). Anyway, back to MDX… This post explains how you can build a dynamic MDX query in Reportins…

    » Read more