- datetrunc(‘Month’,[Order Date]) will not work
- datetrunc(‘month’,[Order Date]) will work!
In Reporting Services 2008, it is possible to export to Excel using the Visual Studio interface and the Reporting Services browser. However, what about the case where you have a complex report with numerous tablixes, matrices and so on, and want to preserve these in the target Excel Workbook? Here is a nifty trick for doing this:
1. Take the example where you have a report with two tablix or tables; for illustrations sake, we will call them Table1 and Table2./
2. To do this, go to ‘Properties’ of Table1 in question. Look for the property called ‘PageBreak’. Set this to ‘end’.
3. Save your changes
4. Run the report, and export it to excel.
Open the report in Excel. You will see that Excel has two worksheets, one for Table1 and the other for Table2.
If you want to name your Excel worksheets to something other than ‘Sheet 1’ etc., then there isn’t an easy way to do this. One option is to schedule your report, and then run an SSIS job that opens, renames and saves the Excel worksheets on a shared drive.
This issue affects both the execution of the report in Visual Studio and in the Reporting Services browser. If you have one sheet, and export the SSRS report to Excel using the Reporting Services browser, then you’re in luck: the sheet will be called the name of the report. If you then add in another table to the same report, and try to create a multi-tabbed Excel workbook, then you are not in luck: the workbook sheets will be called ‘Sheet 1’ etc. It seems as if the tab name matches the report name, unless you try to export the data to different tabs. In this case, it loses it… and sticks with the default name, ‘Sheet 1’.
Note that other reporting software also has this issue; exporting to Excel from Tableau, for example, will also keep to the default ‘Sheet 1’ worksheet name.
I hope that the Tablix names will be preserved in Excel in later versions: we will have to wait and see.
I have decided to write about SSAS 2008 errors that I come across occasionally, either by my own hand or things that I see my customers doing from time to time. In particular, if I’ve had trouble finding the resolution anywhere else on the Internet, I think it’s important to share my resolution to save other people some time!
Issue: One bugbear is the following error message:
Errors in the back-end database access module. The size specified for a binding was too small, resulting in one or more column values being truncated.
This is my most un-favourite error message of all time. If you get this error message, it can be difficult to identify where the issue is. This error seems to arise if you do any changes to a view or table underlying the DSV, which can arise in a mismatch.
The error message gives you details of the offending column. The fix seems to be to re-open the DSV, close it again, then reprocess dimension, then cube. If not, you can try to wade through the code version of the DSV and see where the mis-match is.
It might be worth having a look at the health of the server. I have noticed that I don’t get this error message on some machines. However, I have had this error message repeatedly on one machine that belonged to one of my customers, which was a server that’s slowly grinding to a halt. Since it wasn’t my machine, I wasn’t in a position to do very much except try and get their IT support to look at it.
It seems that prevention is the best way to get rid of this error message. Having had this error message at the worst times, I now try and prevent it by using Views in the DSV. For every column in the view, I specifically cast it as a particular type in the view.
Sometimes it’s been near impossible to work out where the issue is, so I’ve simply had to start again by casting every column in the cube as a type, and then re-creating a new cube using the dimensions etc that I’ve already created. It’s not great but it can sometimes be quicker just to do this.
I hope that this helps!