SSRS: Export to Excel 2007-2010 Format

On versions previous to Reporting Services 2012 (even Reporting Services 2008 R2), you could only export a maximum of 65536 rows when you chose to export the data in a report to Excel.     If this is a showstopper, then Reporting Services 2012 is your saviour.   However if you are using Reporting Services 2008 R2 you also have another option before you consider going down this path.  Anything 2008 or earlier, you are really out of luck, or perhaps you could aggregate the data in your report before exporting to reduce the number of rows.

First, the new Reporting Services 2012 Excel rendering extension, renders a report as an Excel document that is compatible with Microsoft Excel 2007-2010 but also provides Excel 2003 support. This new Excel-rendering extension removes limitations of the earlier version, meaning the amount of rows you can export lifts from 65536 to a maximum of 1,048,576 rows per worksheet – Amazeballs!   Other improvements are introduced as follows :

  • Maximum columns per worksheet is 16,384
  • Number of colors allowed in a worksheet is approximately 16 million (24-bit color)
  • ZIP compression provides smaller files sizes
  • Support for Microsoft Word .docx

Read more here - If you really can’t upgrade to Reporting Services 2012 just now but you are running Reporting Services 2008 R2, then you can export more than 65536 rows by setting a page break on your report and splitting your data into groups, 1 worksheet per group.  The one catch is that each group needs to be less than 65536 rows per group (per worksheet). To get this happening, you need to edit your original report, then setup your page break in your report Tablix and set the PageName property to an expression that identifies how you will split or group the data.   An example expression shows how to group by the data in your ProductCategory field :

"Product Category : " & Fields!ProductCategory.Value

Read more here

Tagged with:
Posted in Report (SSRS Report Builder Power View)

AlwaysOn: Configuring NTFS Permissions for the Quorum File Share

The female and male penguin…such a dreamy couple….after the egg is made…the female transfers the egg to her mate, then leaves to spend winter in the open ocean.  The quorum between the egg and parent is maintained throughout this changing of guard.

SQL Server 2012 AlwaysOn can be configured in a number of different quorum voting configurations.  Each aims to ensure the quorum of the nodes in the primary data centre are not comprised by outages in the Disaster Recovery data centre, or loss of connectivity between the two data centres.

In the Node and File Share Majority quorum model, a file share provides an additional vote to establish quorum.   The file share requires full permissions granted to the Cluster’s Virtual Computer Object (VCO) Account.   The VCO account will be the same name as the cluster and by default gets created in the Computers container in your Domain.

A given file share can also act as a witness to one or more WSFCs so you would need to grant permissions to more than one VCO account, one for each cluster.   The file share should ideally exist in a 3rd data centre, however you could in theory configure the share within the Primary or Disaster Recovery data centres, even utilising other WSFC nodes if you are really stuck for resources.

Tagged with: ,
Posted in Manage (SQL Server Admin)

PowerPivot for Sharepoint – Locating Analysis Services on a Different Server

PowerPivot for Sharepoint 2013 supports a variety of different installation architectures – one of which allows you to run the PowerPivot Analysis Services instance on a server other than your Sharepoint 2013 server.   You still need to install PowerPivot software on your Sharepoint server, however only the spPowerpivot.msi package (or on all Sharepoint servers if you have a multi node Sharepoint farm) – this can be found on your SQL Server install media or located on the SQL 2012 feature pack   Make sure the same version of msPowerPivot.msi is used on all servers in farm.

Make sure you specify the Excel Services Data Model settings in the configuration of your Excel Services Service Application – this is the instance name of your PowerPivot instance e.g. HOSTNAME\POWERPIVOT.

For Kerberos configuration you will need to get the Port number of your POWERPIVOT instance (as well as the port number of the browser service), create your MSOLAPsvc.3 and MSOLAPDISCO.v3 SPNs then add these to your constrained delegation configuration.

Finally, you need to run the PowerPivot configuration tool to complete your installation – because you installed msPowerPivot.msi on the Sharepoint server, you can run the PowerPivot configuration tool and finish the PowerPivot integration.

Screen Shot 2014-01-19 at 7.08.40 am

Posted in Analyse (SSAS, Excel and Powerpivot), Share (SharePoint 2013, 2010 and 2007)

Word: This document contains links that may refer to other files

If Thor had an office job, his desk would be a pile of splinters….Possibly one of the most annoying warnings in Microsoft Word is the warning “This document contains links that may refer to other files” when you open a document. It doesn’t matter if you choose Yes or No, you will continue to be prompted every time you open the file.



You can turn off the warning altogether…by going to the File menu, Options, Advanced, then scroll down to the General section and untick the box beside “Update automatic links at open”.



Whilst this won’t resolve the cause, it will stop the warning from popping up next time, for any Word document.  The gotcha is every user must set the same option in their installation of Microsoft Word.

To identify links used in your document you can expose the “Edit Links to Files” function.  You can expose this by going through File, Options, Quick Access Toolbar, select “File Tab” in the “Choose Commands From” dropdown, find the function “Edit Links to Files” and select “Add”.


This will explose the following icon in the Quick Launch Menu where you can view links to other files.





Tagged with:
Posted in Uncategorized

SSRS : Use the same colour Palette in different charts in the same report

In the following example you have two pie charts – each chart is set to default colours – you want to make sure that the charts are coordinated, meaning they use the same fill colour for each category – so the blue always aligns with Category 1, the green always aligns with Category 2 and so on.

The following reference highlights how to do it however some additional tips.

Use the sample code below (adapted from the above technet article) but substitute your colours with either the colour name or the hex colour with the # e.g. CadetBlue or #5F9EA0.  You can use the following colour reference for help choosing  Make sure you choose enough colours to cater for the maximum number of categories in your dataset.

Once you have created code within your Report properties Code module, you just need to call the code….right click on your pie chart, select Series properties and under fill, set your expression under the Color property. Make sure your expression refers to the field value e.g. =Code.GetColor(Fields!YourCategoryField.Value)

Private colorPalette As String() = {"#5F9EA0", "Gold", "Gray"}
    Private count As Integer = 0
    Private mapping As New System.Collections.Hashtable()
    Public Function GetColor(ByVal groupingValue As String) As String
        If mapping.ContainsKey(groupingValue) Then
            Return mapping(groupingValue)
        End If
        Dim c As String = colorPalette(count Mod colorPalette.Length)
        count = count + 1
        mapping.Add(groupingValue, c)
        Return c
    End Function
Tagged with:
Posted in Report (SSRS Report Builder Power View)

SSRS : Report fails – RdlObjectModel is not a member of ReportServices

You run a report in Reporting Services which subsequentially fails with error RDlObjectModel is not a member of ReportServices. You investigate the suspect textbox and find an expression in the textbox contains the following code Microsoft.ReportingServices.RdlObjectModel.ExpressionParser.VBFunctions.  This appears to happen when you copy and paste a text box that contains and expression.

A connect case is available here but for now you will need to manually strip the expression of this code. Either do this manually within your textbox expression or open the report in View Code mode and search and replace.

Tagged with:
Posted in Report (SSRS Report Builder Power View)

SSRS: User does not have required permissions…windows user account control uac restrictions

You run Report Manager and you get a user does not have required permissions / uac error, yet you know you have permissions, in fact you would put money on it…except you stand there locked out of your Report Manager.

A combination of the following is likely to resolve :

1. Disable UAC (Open Control and change User Access Control settings to Never Notify – may require a reboot)
2. Run Internet Explorer as administrator then open the report manager
3. If all else fails, add the url of the report manager to the Local Internet Zones in Internet Explorer, not Trusted Sites.
4. Once logged in, if you are required to me an adminstrator, verify you have the System Adminisator role within the Site Settings link in Report Manager. Additionally set yourself Content Manager permissions at the Home folder level.

Tagged with:
Posted in Report (SSRS Report Builder Power View)
1.#INF 70-461 70-462 70-463 70-464 70-465 70-466 70-467 access 97 excel 2010 ssis add foreign key add primary key alter column information_schema.columns case senstive search tsql certification certs convert string to number cross join sample cte parent description datatypes dependsondimension property displaying ssrs multi select parameter values in a report drillthrough duplicate attribute key Error: Subreport could not be shown format ssrs column headers with line breaks formatting 000's indexed views for reporting Learn Excel management studio status bar colours many to many relationships ssas mdx for starters MDX Member Does not Exist mdx query designer mdx top items and other items mdx top topcount over() performance point 2010 external access performance point deployment performance point error 11861 performance point security primer performance point unattended account planning ssrs security pps 2010 time intelligence functions processing cube job step publish report parts from report designer rds removing all member reportserver catalog reportserverdb reportservertempdb rsds select into vs insert into sql 2008 r2 to sql 2012 sharepoint integrated sql 2012 feature comparison sql server 2012 Denali Exam sql server service packs and cumulative updates sql server upgrade advisor 2012 2008 R2 sql server virtualisation vsphere srss textbox value expression SSAS Reporting Actions ssis cache lookup ssis casting ssis timestamp expression ssrs catalog report list ssrs integrated mode feature comparison ssrs parent child charts ssrs pie percentages strtomember Subreport tablix inside a list tablix inside a list - custom heading The LocaleIdentifier property error There is an error in XML document winlogon.exe windows 8 shutdown error your first power view

Get every new post delivered to your Inbox.

Join 76 other followers