Hello Network!
I'm looking to get started in creating custom reports although I'm a little shaky on the process. I've got some basic SQL knowledge and I have access to BIDS. I know I need to create a stored procedure and create the template for the report itself but I'm not sure how to do that. Can someone point me in the right direction so I can start practicing and testing? Thanks!!!
I was actually thinking about this question earlier today, since I certainly had some frustrations when I was getting started. I had some experience with SQL generally, but not T-SQL in particular, and though I had done some sysadmin-type stuff before on other platforms, the SQL Server/Windows Server stack was completely new to me. So here's what I wish I knew when I was just getting started:
- SSMS, SQL Server Profiler: If you're not working directly on your SQL server, just download the latest version of SQL Server Express. As of the 2014 version, SQL Server Profiler is included even with the free express package (you will want this for running T-SQL traces if you ever need to figure out what exactly the application is doing to the database for an action). You do not need to match the version to your database server, just get the most recent version: http://www.microsoft.com/en-us/download/details.aspx?id=42299
- SSDT-BI (formerly known as BIDS). There have been no substantive changes since SQL Server 2008 R2. This runs in a Visual Studio shell, which is included with this 2012 package: https://msdn.microsoft.com/en-US/jj650015 ("Newer" year versions are only needed to match your own Visual Studio license if you have one.)
- Tessitura Report Setup
- SSRS Server: Make sure you have access to this so you can tweak parameter settings after you deploy (and so that you have deploy rights in the first place).
- (Optional, but highly recommended) Source Control. I highly recommend using git using a GUI like Sourcetree to version your installation files. It's a good habit to get into, even if the learning curve is a tiny bit steep for people brand new to SCM. I use one repo for my SSRS solution and another for an SSMS solution containing projects for each custom report procedure or utility.
The hardest part of developing custom reports after learning the overall architecture of the Tessitura reporting system is, IMO, writing correct and idiomatic T-SQL. The best place to learn T-SQL is right inside your own database in SSMS. For example, use the application documentation to identify which stored procedure runs your favorite standard report, and then use SSMS to view the code in that procedure, and/or copy it to make a customization of a standard report.
- 31 Days of SSMS: This is a great series of articles for learning the ropes in Management Studio. https://sqljudo.wordpress.com/2014/08/01/31-days-of-sql-server-management-studio/
- Custom Report Setup Documentation: This is an incredibly valuable document for learning the architecture of the reporting system, as well as specifically using Tessitura Report Setup. Worth reading until you have a thorough understanding. http://www.tessituranetwork.com/network/Learning/Documentation/Customization/Custom%20Report%20Setup.aspx
- SSRS webinars: Ryan Creps' T-Cast on Building a Tessitura-Integrated SSRS report will teach you nearly everything you need to know about working in SSDT-BI: http://www.tessituranetwork.com/network/Learning/Webinars%20Archive/IT.aspx
- MSDN: (duh) Google any SQL keyword with "tsql" and you'll immediately get the official documentation page on MSDN.
- Tessitura Documentation: Tessitura Table Structures, Tessitura Screens Table Reference, Tessitura Database Naming Conventions documents are all really useful for getting familiar with the innards of impresario (as well as T-sql traces in SQL Server Profiler).
- Tessitura Developers page: Besides what goes on in this forum, there's actually a Confluence space and Bitbucket team that you can be granted access to, where code-writing Tessi community can share code and compare notes. This was originally geared towards web/API developers, but those of us who write SQL stand to get quite a lot of value out of sharing code on bitbucket instead of the TASK shared reports and utilities space. All of my database code projects that I have decided to share are in Bitbucket. You can use this, as well as the shared reports space, to find code to use as an example for your projects.
(Many of these are covered in the 31 days, but here's my favorites):
- F5 is your keyboard shortcut to execute the current query. If you have selected only part of the query, only your selection will be executed (I know most people know this, but it still blew my mind when I learned about it).
- This is covered in the 31 days, but right clicking nearly any object in the sidebar will allow you to generate scripts for that object. If you want to see how a standard table was created, and you want to copy that for a local table, Script Table as -> CREATE To -> New Query Editor Window.
- When connecting to a server, use connection options to set the status bar color for that server so you have a big visual reference for whether you're connected to LIVE or TEST.
- Right click a query window and choose Connection to switch between servers for a query without closing and reopening it. Remember that the server displayed in object explorer is NOT NECESSARILY the one your query window is connected to! (another good reason to use status bar coloring).
- Alt-F1 while an object is selected in your query editor window will get you information about that object (table column datatypes, etc.) -- equivalent to running sp_help. (This is configured as a query shortcut in options -- I also like configuring one for "SELECT TOP 100 * FROM ".)
- Make sure to follow the best practices described in Ryan Creps' T-Cast -- use build configurations to switch between live and test; always use a shared TessituraDB data source, this will point to TEST in your solution, but should be set not to overwrite the data sources that are deployed to your server (you would manually create data source on LIVE SSRS).
- If you ever lose track of the "Report Data" pane with all of your fields and parameters, you can find it again at the bottom of the view menu, but only when the report is in design and not preview mode!
- In order to preview in SSDT-BI, you will need to define some default values for your parameters, or have the params be visible. In production, you don't want default values OR visible parameters in your report display. The problem is that after you deploy a report, any changes in SSDT-BI made to those parameter settings will NOT be pushed on a subsequent deploy! You instead have to access SSRS directly and manually change the parameter settings. Alternatively, you could remember to change all of those settings in SSDT-BI whenever you're about to do a deploy to LIVE, but since this takes a lot of busy work and is hard to remember to do every time (especially if you then change back for development), I'd recommend just keeping the settings in your solution appropriate for development, and adding a step to your deployment process of correcting the deployed parameters. (In theory, it would be nice to have these settings linked to build configuration, but I haven't found this to be possible yet.)
- Write SQL scripts that are "Idempotent". Broadly, this means that no matter the state of the database, your script should always achieve the same result. This means that a script that creates a custom report procedure will always start by dropping an older version of the procedure if it exists, create the procedure, and then grant ImpUser permissions to it. We achieve a successful run and identical outcomes whether the database has no existing version of the proc, an older version, or the same version. Makes testing changes and deploying to LIVE much easier. I leverage templates (see 31 days of SSMS) to set myself up for this. (I'll upload some to my profile.)
- Use semicolons in your T-SQL. In the current version, these are USUALLY "optional", but MS has stated that in a future version they will be required. "Usually" means that you can get by without them, except when you can't (things like CTEs and THROW statements come to mind). Better to learn how they should be used now; your code will be cleaner, and you won't have as many problems in the long term.
- SSMS doesn't require the use of solutions, but doing so will let you navigate your projects and files from within SSMS instead of having to use an open dialog box. That's the only benefit I can think of.
- Source control: As I mentioned, I use git with Sourcetree to keep my entire SSMS solution in a repository. Other people use TFS, some might use mercurial, but git has wide adoption and is incredibly powerful. Sourcetree as a GUI keeps the learning curve easier than usual, and additionally you can use cloud servers like Bitbucket to back up your code and work on it from multiple machines. (Git or Mercurial are required for repositories in Bitbucket, including the developer code share.)
- SSMS files in Git: One last little tip if you decide to use Git -- some scripts generated by SSMS are saved in a weird unicode format that git thinks is a binary file. If this happens, you won't be able to use git diffs or hunks. So the first time you save a file from SSMS to be added to your git repo, just be sure to choose Codepage 1252 (Western European/Windows) in Advanced Save Options (equivalent to ASCII). This doesn't happen all the time, but if you can't see the code content of your scripts in Sourcetree, this is your problem.
Hi Nick,
Thanks for this. Nicely done.
Nick
A couple minor updates to this since I wrote it last year:
This article ultimately should be moved into the developer Confluence space so that it can continue to be a living document. I've got that on my to-do list and will get to it eventually if someone else doesn't first (and please feel free to do so without permission). Anyone who does not yet have Confluence access should head to http://tessituracoders.bitbucket.org/ for more information.