12 August 2015

100 reasons I hate ssrs

A slightly tongue-in cheek hit-list of nasty things and vague hand-waving opinions on what makes microsoft's sql server reporting services (ssrs) such a pig to work with.

I don't really know of anything better so this is mostly just pointless ranting; but I'll justify it to myself by saying at least you'll know what you're getting into if you've read this before you start. SSRS seems to be more "death by one thousand paper cuts" than completely broken, so it's not so easy to say "it's shit, shalln't use it" like any good prima-donna developer would. Sorry I mean rock-star (recruiter speak). It also offers a few features that would be pretty hard to code from hand cost-effectively in something like asp.net mvc, such as user editing, multiple export formats, scheduled emails, and some of the ways you can cut-and-shut the data in the reports.

I make no apologies for the colourful language, it's a representation of the emotional side of having to use this heap of crap.


  1. http://www.allenkinsel.com/archive/2013/01/adventures-in-ssrs
  2. doesn't bind to a port like a normal fukcing service
    1. http://blogs.devhorizon.com/reza/2008/10/20/say-goodbye-to-iis-say-hello-to-httpsys/
    2. http://www.iis.net/learn/get-started/introduction-to-iis/introduction-to-iis-architecture#Hypertext
    3. https://social.technet.microsoft.com/Forums/sqlserver/en-US/f2586aca-78fe-40d6-9bcd-5151bac7136f/role-of-httpsys-in-ssrs-2008-?forum=sqlreportingservices
    4. http://blogs.technet.com/b/andrew/archive/2007/12/04/sql-server-2008-reporting-services-no-longer-depends-on-iis.aspx
    5. http server api (aka http.sys)
      1. https://msdn.microsoft.com/en-us/library/aa364510%28VS.85%29.aspx?f=255&MSPPError=-2147217396
    1. list reservations:
      1. `netsh http show urlacl`
  3. auth in reporting https://msdn.microsoft.com/en-us/library/ms152899.aspx
  4. reports in VS
    1. http://curah.microsoft.com/22200/create-ssrs-reports-using-visual-studio
    2. https://msdn.microsoft.com/en-us/library/ms173745.aspx
  5. "Explicity add new role assingment for the account you are using and check every box in sight" ~ a.n. colleague.  lol
  6. ignore the .rdl.data files with git.
    1. http://stackoverflow.com/questions/3424928/in-ssrs-is-there-a-way-to-disable-the-rdl-data-file-creation#3425429
  7. no folders.
    1. https://connect.microsoft.com/SQLServer/feedback/details/487106/allow-sub-folders-in-ssrs-projects
    2. http://stackoverflow.com/questions/3309002/visual-studio-for-ssrs-2008-how-to-organize-reports-into-subfolders-in-solutio
  8. renamed a dataset, nothing fucking broke!!!!!!!!!!!!!!!!!!!!!!!!!!! even though there are reports that depend on it. On editing the report's dataset list you can see clearly "not found", but yet it still runs. what in the blazes is that all about?
    1. caching in the report editor http://stackoverflow.com/q/3424928/10245
    2. kill the .data cache files http://stackoverflow.com/questions/3424928/in-ssrs-is-there-a-way-to-disable-the-rdl-data-file-creation
    3. git clean -xfd
    4. fuck
    5. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0aa81692-352f-4c1f-a0e3-95fe6c0797ca/cachedataforpreview-in-rsreportdesignerconfig-not-honored
    6. https://connect.microsoft.com/SQLServer/feedback/details/468482
    7. it's the `bin\` folder, not the .data files. Still, fuckkkk.
  9. to get from a report to a db you go, report > report dataset > shared dataset > db, but db is defined in the shared dataset with another name, which can be pointed to a shared data source, which is also named. and *that* data source actually has a connection string
  10. committing to tfs failed half way through because vs had locked a bunch of files I didn't even have open
  11. found a param with <Value>=Microsoft.VisualBasic.Strings.Join(Parameters!Stages.Label, ", ")</Value> - wtf.
  12. function overload matching warning wouldn't go away till I closed the sln
  13. localisation is a bitch
    1. http://stackoverflow.com/questions/10953629/how-to-change-ssrs-2008-locale etc
    2. http://blog.ponytailbob.com/2007/10/multi-language-tips-in-ssrs.html
    3. http://blogs.msdn.com/b/sriram_reddy1/archive/2012/01/09/localization-in-ssrs-reports.aspx
    4. https://support.microsoft.com/en-gb/kb/919153
    5. http://www.keepitsimpleandfast.com/2011/09/localization-of-your-ssrs-reports.html
    6. why you no use User!Language??
  14. Visual Studio 2013 crashed. hard. while cancelling new report param
  15. adds 00:00:00 to date fields from sql server. duuuuuuuuuuuuh (goes via .net datetime internally, but even so, not friendly)
  16. changed date format, looks fine in VS, but no change in report server. wuh? deploy all
  17. no auto-sizing of cols http://stackoverflow.com/questions/7851045/ssrs-tablix-column-cangrow-property-for-width
  18. no nulls in multi-value http://www.keepitsimpleandfast.com/2012/03/how-to-pass-null-value-to-multi-value.html
  19. need dirty hack to show "all" rather than full list
    1. http://www.mssqltips.com/sqlservertip/2844/working-with-multiselect-parameters-for-ssrs-reports/
    2. http://stackoverflow.com/questions/12917261/optional-multi-valued-parameters-in-ssrs
    3. http://www.bi-rootdata.com/2012/09/efficient-way-of-using-all-as-parameter.html
    4. http://blog.ponytailbob.com/2007/10/2-shortcomings-of-multi-valued.html
  20. some fucking horror I've yet to encounter (querystrings) http://stackoverflow.com/questions/512105/passing-multiple-values-for-a-single-parameter-in-reporting-services
  21. it has a fucking canvas size that will push over to 2 pages
  22. the font kerning on a print is massively different to on web / design view
  23. sorting
    1. http://stackoverflow.com/questions/9254604/why-does-my-sql-server-reporting-service-ssrs-report-appear-to-re-sort-the-d
  24. "Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index " from editing xml. thanks for the error info. fuckkkkkers
  25. the ssrs gui editor is a flaky piece of shit
    1. doesn't select the right fucking textbox in the props window
    2. had to restart visual fuckigjn studio
    3. grrr
  26. the underlying xml is fucking horrific
  27. <rd:Selected>true</rd:Selected> ----- what in the fucking blazes is that doing in there?
  28. how do you deploy without connecting visual studio to production server? you fucking don't hahahahahaa
  29. powerhell https://gist.github.com/timabell/7e3019bd2de802f0b259
  30. ssbi install croaked - https://support.microsoft.com/en-us/kb/2800050?wa=wsignin1.0
  31. [09:44:53] john doe: Tim can I have a .bak file of ReportServer$MSSQL2012TempDB which the stupid software seems to be unable to operate without even though it has Temp in the database name implying it will rebuild itself (at least that's what it implies to me)
    1. [09:47:48] Tim Abell: (facepalm)
    2. [09:47:49] Tim Abell: sure
    3. [09:48:08] Tim Abell: I did wonder, and then I thought, no they couldn't possibly need that
  32. dropdown doesn't work in firefox
  33. no debugging http://stackoverflow.com/a/14068447/10245
  34. Warning : The text box ‘appliedFilters’ and the image ‘urLogo’ overlap. Overlapping report items are not supported in all renderers.
  35. the only options for DRY in reports suck balls http://www.3pillarglobal.com/insights/tips-tricks-ensure-consistency-sql-server-reporting-services-reports
  36. http://harmful.cat-v.org/software/xml/ xml is a terrible format anyway
  37. layout is in inches
  38. you can change the layout to cm
  39. it stores different metrics (cm/in) for each element, wtf, pick a unit
  40. reflowing nicely is impossible
  41. layout engine is as intelligent as a piece of paper and a pen. x,y is all you get.
  42. http://www.webapplicationsuk.com/2010/07/word-html-renderer-ndash-the-road-to-hellhellip/
  43. https://connect.microsoft.com/SQLServer/feedback/details/540183/supported-rdl-object-model-rdlom
  44. this is the kind of bullshit that counts for helpful content on the net https://social.msdn.microsoft.com/Forums/en-US/86205ca4-13d0-4ca6-84f1-79797616f0f4/exclude-null-values-from-sum-and-avg-calculation?forum=sqlreportingservices - =sum(forum_format * quality_of_community) = errorrrrrrrrr
  45. multiple rdl xml schema in the same fucking project, completely different xml structure
    1. 2005 generated with "new report wizard" in VS 20-fucking-13: <Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
    2. 2008 <Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
    3. 2009 from https://technet.microsoft.com/en-us/library/cc627465%28v=sql.105%29.aspx - <Report xmlns:rd=http://schemas.microsoft.com/SQLServer/reporting/reportdesigner xmlns="http://schemas.microsoft.com/sqlserver/reporting/2009/01/reportdefinition">
    4. 2010 <Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns:cl="http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition">
    5. how many fucking versions??!
    6. http://stackoverflow.com/questions/15539859/what-is-the-difference-between-rdl-2008-schema-and-rdl-2010-schema-feature-wise
    7. http://stackoverflow.com/questions/9974179/is-there-a-new-version-of-rdl-schema-for-sql-server-2012-denali
    8. http://stackoverflow.com/questions/29951653/ssrs-2008r2-visual-studio-2008-and-2008-and-2010-schemas - how to not end up with old schema?!
    9. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f4d14548-c592-4d8d-8185-ca683c421649/2010-schema-with-visual-studio-2010?forum=sqlreportingservices
  46. how do you upgrade a report schema? install a massive chunk of fucking sql server http://stackoverflow.com/questions/13170608/upgrade-my-rdlc-schema-from-2008-01-to-2010-01
  47. [17:54:48] john doe: Btw have you noticed that in Print Layout view the header doesn't expand if any of the textboxes have auto-grown?
[17:55:14] Tim Abell: that's because ssrs is a piece of shit from 1990
[17:55:28] Tim Abell: and it thinks A4 is the ultimate display format
[17:55:47] Tim Abell: you just have to guess how much space you'll need
  48. the ordering of the xml in the proj file is unstable causing diff noise
  49. subreports, icky http://bhushan.extreme-advice.com/subreport-in-ssrs/
    1. or nested tables http://stackoverflow.com/questions/11335655/filtering-nested-data-regions-in-ssrs
  50. some things can only be achieved with subreports, and they have to be deployed separately from the main report, meaning they can get out of sync. enjoy the fear of not knowing if you'll break something else when you upload your new version of the subreport you depend on
  51. no support for "time" data type http://stackoverflow.com/questions/3846378/displaying-time-in-reporting-services-2008
  52. The "View Report" button next to the parameters when running a report in VS is *always* greyed-out, even though it actually works.
  53. wow that's mental, hidden reports show in details view and not in tile view in the ssrs web ui
  54. the report editor has a copy option for report items, but no paste, so you can't duplicate reports
    1. actually you can, but only if you know the keyboard shortcuts. 0_o - ctrl-c ctrl-v
  55. duplicating a report on the filesystem, and then using "add existing item" to include it puts it at the end of the list... until you rename it and then it's moved into alphabetical order causing a spurious diff. should have put in the right place in the first place. grr.
  56. using the cursor keys to move textboxes around is so laggy that I overshoot every single time
  57. the editor popups in visual studio are modal, so you can't refer to anything else
  58. and there's no maximise button so you have to drag the fiddly border to make it bigger
  59. the report editor hasn't heard of ctrl-c or ctrl-v, have to use ctrl-Ins / shift-Ins instead
  60. border rendering / precedence is a fucking mess. set some borders, your report will look like a two-year-old coloured it in, and how it looks changes depending on the zoom level.
  61. you have to use VB to do alternate row colours - http://stackoverflow.com/questions/44376/add-alternating-row-color-to-sql-server-reporting-services-report
  62. if anything goes wrong with an expression all you get is "#Error". Helpful. E.g. http://stackoverflow.com/q/9144312/10245
  63. this one time, at band camp, I edited a report definition in VS and it refused to save the new definition to disk (ctrl-shift-s, ctrl-shift-s!!). wtf. Restarted VS and all the changes were gone.
  64. report editor silently adds new parameters to the report when you add new parameters to the sql. seriously. fuck off.
  65. RSI-inducing UI for editing the reports. click click click clickity click
  66. the sql editor has only a single undo. like ye olde notepad.
  67. in the editor, you can right-click copy, you can't right-click paste. wtf. ctrl-v does paste though. wtf again. I know, I already said it, but it's reaaaaaly shit
  68. the field list on a dataset is ordinal, allowing you to mismatch the select in the sql from the list of fields in the dataset and not notice
  69. how do you align a textbox on the page?
                  <Top>0.82546cm</Top>
                  <Left>0.07309cm</Left>
                  <Height>0.88964cm</Height>
                  <Width>2.3025cm</Width>
    fuck you!!!!
  70. one goddam cell in the underlying format:
                    <TablixCell>
                      <CellContents>
                        <Textbox Name="qty">
                          <CanGrow>true</CanGrow>
                          <KeepTogether>true</KeepTogether>
                          <Paragraphs>
                            <Paragraph>
                              <TextRuns>
                                <TextRun>
                                  <Value>=Sum(Fields!qty.Value)</Value>
                                  <Style />
                                </TextRun>
                              </TextRuns>
                              <Style />
                            </Paragraph>
                          </Paragraphs>
                          <rd:DefaultName>qty</rd:DefaultName>
                          <Style>
                            <Border>
                              <Color>LightGrey</Color>
                              <Style>None</Style>
                            </Border>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                            <PaddingBottom>2pt</PaddingBottom>
                          </Style>
                        </Textbox>
                      </CellContents>
                    </TablixCell>



I know I can't count, but if ssrs is going to make so little effort I don't see why I should.

Fnuh.


Doesn't mean I won't use it again mind, just don't promise to like it.


12 January 2015

LibreOffice mail merge - "data source 'SOURCE' was not found"

So another year on, LibreOffice 4.2.7.2 (via Linux Mint 17.1) still has a dog's breakfast of a mail merge feature, hey ho, hopefully it might actually get fixed following the fork from OpenOffice and the change in contribution methods.

Anyways

So I've moved machines, copied my files across and for some reason my mail merge has soiled itself and now bleats "data source 'SOURCE' was not found" which is as unhelpful as it is infuriating, especially given that the "check connections" button is exactly the wrong place to look for an answer.

Turns out you actually get this if even just a single field in your document is 'broken'. How do you tell which ones are broken? Well you have to change them all to just be sure. Sigh.

The fix for me today was as follows (though with such a messy feature there's unlimited ways it can break):

  1. Hit F4 and check that your connection to the spreadsheet actually exists and works, and unbreak anything you find therein. While you're in there you can marvel at how it requires a whole other file (.odb) just to remember how to get to a spreadsheet. (See below for fixing this)
  2. Turn on the field names so you can see what the f*** is actually going on with "View > Field Names (ctrl+f9)" which will show you the fully qualified field name, which might even be completely wrong. You can now see that for whatever reason (insanity?) it embeds more than just the field name at the field place-holder.
  3. And finally the way you actually fix the broken fields it's failing to tell you about actually lies under the menu item "Edit > Fields", where you can change all the broken references one at a time to the correct place.
  4. For bonus points, if it the field looks right but is silently broken somehow then you have to change the field to something else, hit okay, and then change it back again for anything to actually change, which is annoying if you have a lot of fields.
Fragile much?

Another fix I've just discovered is you can rename your data source to match the name defined in the fields (assuming they're all the same) and it'll start working again.

Fixing the .odb file

If you're stuck on point 1, here's how you fix it, also completely non-obvious and full of apparent dead-ends and dubious information.

  1. Give up on trying to do this in writer, it doesn't seem possible, in spite of false hope from the data sources tool, it only allows you to select .odb (database) files, not spreadsheets.
  2. Open up "libreoffice base", which pops open the database wizard
  3. Choose "connect to an existing database"
  4. In the dropdown choose "Spreadsheet"
  5. Next
  6. Browse for your spreadsheet
  7. Next
  8. Leave "register database for me" selected
  9. Leave "open the database for editing" checked
  10. Finish
  11. It prompts to save the new database (.odb), I suggest saving it in the same folder as the spreadsheet to save future confusion.
  12. You now have the database open in "base", you should see your spreadsheet sheets listed as tables
  13. Open a table (i.e. a sheet) and check you can see the spreadsheet contents
  14. Close "base", saving changes
  15. Return to your writer document
  16. Open the data sources again (F4), you should now be able to browse your spreadsheet via your newly created database.

Simpler than getting planning permission out of a vogon. :-/


Hope that helps some other poor open source die-hard who has work to do.


Useful refs:

10 May 2014

throw vs throw ex vs wrap and throw in c-sharp

I've come across the throw vs throw ex 'debate' a few times, even as an interview question, and it's always bugged me because it's never something I've worried about in my own c# code.

background

So here's a typical example of the throw vs throw ex thing: https://stackoverflow.com/questions/730250/is-there-a-difference-between-throw-and-throw-ex

Basically it revolves around either messing up the line numbers in your stack trace (throw ex;) or losing an chunk of your stack entirely (throw;) - exception1 and 2 respectively in this nice clear answer http://stackoverflow.com/a/776756/10245


the third option

I've just figured out why.

Because in my own code, whenever I catch and re-throw I always wrap another exception to add more context before rethrowing, and this means you don't have either of the above problems. For example:

private static void ThrowException3() {
try {
DivByZero(); // line 43
} catch (Exception ex) {
throw new Exception("doh", ex); // line 45
}
}

Exception 3:
System.Exception: doh ---> System.DivideByZeroException: Division by zero
  at puke.DivByZero () [0x00002] in /home/tim/repo/puker/puke.cs:51 
  at puke.ThrowException3 () [0x00000] in /home/tim/repo/puker/puke.cs:43 
  --- End of inner exception stack trace ---
  at puke.ThrowException3 () [0x0000b] in /home/tim/repo/puker/puke.cs:45 
  at puke.Main (System.String[] args) [0x00040] in /home/tim/repo/puker/puke.cs:18 

Obviously 'doh' would be something meaningful about the state of that function ThrowException3 in the real world.

Full example with output at https://gist.github.com/timabell/78610f588961bd0a0b95

This makes life much easier when tracking down bugs / state problems later on. Particularly if you string.format() the new message and add some useful state info.

08 March 2014

Why publish open source when you are commercial?

Why open source your commercial projects?
  • Forces you to decouple them from other internal systems.
  • Encourages thinking in terms of reusable modules, which is better for internal reuse just as much as public reuse.
  • Possibility of contributions to systems useful to your business by others.
  • Easier reuse within your organisation (the public internet is a better search and sharing system than any internal systems).
  • Reputation advantages, the best coders often like to work in open and forward-thinking companies, and having public shared code is a great sign of such an organisation.
Do it early
  • Preferably push your very first commit straight to github.
  • Do it before it has a chance to be tightly coupled to internal systems, otherwise you'll have to unpick it and it will be less decoupled from day one, and inertia might mean that in spite of the best intentions you then never publish it.
  • You'll have it in mind that every commit is public from day one, avoiding adding internal config etc and forcing you to factor it out into config which is all round a good thing.
  • Don't wait for your code to be perfect, there are compromises in all code and sharing something imperfect is better than sharing nothing.

Worried about the brand?
  • Commit under personal email addresses and push to personal github accounts. You can always setup a corporate github account later when you are feeling more confident.

Of course I'm not saying you should open source everything, for example your core product's codebase should probably not go on github if you are a product company!

--

Be brave, be open.
Props to Tom Loosemoore

10 February 2014

Bash command line editing cheat sheet

https://www.gnu.org/software/bash/manual/bashref.html#Readline-Interaction
  • ctrl-a/e start/end of line
  • alt-f/b forward/back a word
  • ctrl-w/alt-d delete to start/end of word
  • ctrl-shift-_ undo (i.e. ctrl-underscore)
  • ctrl-y paste (yank) deleted text
    • alt-y paste older deleted text instead
  • prefix with alt+digit (0-9) to do multiple, e.g. delete two words
    • start with alt-minus to go backwards

Just a few notes I threw together for my own benefit. I finally got around to learning a bit more about editing commands on the Linux shell / terminal.

03 February 2014

Converting kml to gpx with python

Today I wanted to geo-code some of my photos.

I have an SLR digital camera (no gps of course), and an android phone. I recorded a track with My Tracks from google on the phone. (Not entirely recommended but works). I then fired up digikam to run the geo-correlation and add lat-long to the exif of the files only to discover digikam doesn't know how to read kml. Fooey.

 

I looked to gpsbabel, but it apparently can't handle this style of kml file, as differentiated by the coordinates being in the following style of markup:

<gx:Track>
<when>2014-01-25T18:00:13.955Z</when>
<gx:coord>-1.885348 50.769434</gx:coord>
<when>2014-01-25T18:00:14.565Z</when>
<gx:coord>-1.885193 50.769328 53.20000076293945</gx:coord>
<when>2014-01-25T18:00:58.566Z</when>


So I wrote a python script to munge it into gpx shape:

https://gist.github.com/timabell/8791116

This can be run as follows:

./kmlToGpx.py "25-01 12-48.kml" > "25-01 12-48.kml.gpx"

And worked a treat for me.

After I'd done this I discovered my pet tool gpsprune can open the new style kml. (I forked gpsprune a while ago and added a minor feature) However I'm glad to have a command-line tool as I have hundreds of tracks I want to convert.

Incidentally the phone can automatically sync the tracks to google drive, which is kinda handy and then you can download them from the site etc.

07 January 2014

Returning to commercial ASP.NET from Ruby on Rails

Why ASP.NET again after all the noise I made about Ruby on Rails? After a brief stint with commercial Ruby on Rails development I should explain why I've decided my next gig will be an ASP.NET project. In short: currently almost all the Rails work available is in London for digital agencies and start-ups, demanding on-site full time presence, and I burned out doing 3 hours a day commuting in less than half-a-year. This is not a sustainable business plan.

The emphasis on start-ups and agencies bodes well for the commercial future of Rails as many of these projects will bloom into large systems needing continuing development. I will continue to use Rails for my own projects (such as the in-progress https://github.com/timabell/symbol-library ). But for me the market in the Reading area seems too quiet to make a business success from just Rails. The final straw was being formally offered a rare local permanent Rails job working with all my favourite open source technologies (Rails, Postgres, Linux etc) only to be handed an employment contract with less job security, rights and benefits than a contractor would have. This confirmed my growing understanding of the local market not being suitable at this time.

So my updated plan of action is to return to providing programming services to the vibrant .NET market in the local area, whilst also working on a database migration product for the same market (still in the research phase), but to keep my hand in with Ruby on Rails with personal projects.


This article is for my Linked In audience, if you want to become part of my network or learn more about my professional services send me a message or invite here: http://www.linkedin.com/in/timabell