1 / 76

60 Reporting Tips in 60 Minutes

60 Reporting Tips in 60 Minutes. Ike Ellis Partner Crafting Bytes. SSMS: Query Shortcuts. SELECT COUNT(*) FROM SELECT TOP 100 * FROM. SSMS: Don’t forget the splitter bar. SSMS: Comment Shortcut Key. --select * from sales.customers c --join sales.orders o

thompsonr
Download Presentation

60 Reporting Tips in 60 Minutes

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 60 Reporting Tips in 60 Minutes Ike Ellis Partner Crafting Bytes

  2. SSMS: Query Shortcuts SELECT COUNT(*) FROM SELECT TOP 100 * FROM

  3. SSMS: Don’t forget the splitter bar

  4. SSMS: Comment Shortcut Key --select * from sales.customers c --join sales.orders o --on c.custid = o.custid CTRL-K, C CTRL-K, U

  5. Three Ways to Search Schema: select object_name(object_id), definition as name from sys.all_sql_modules where definition like '%cust%' Object Explorer Details F7

  6. SSDT and SSMS: Search options Spotlight

  7. SSMS: USE DATABASE • CTRL-U • Type database • Hit Enter

  8. SSMS: Status bar on top

  9. SSMS: Use the middle mouse button to close tabs zz Close windows fast Works in Chrome, SSDT, SSMS, Excel, Everywhere

  10. SSMS: Color the status bar

  11. SSMS: Drag Columns Over

  12. SSMS: Performance Dashboard • Added to Standard Reports

  13. SSMS: Quickly alias columns

  14. Excel: Four Directional Arrow for custom sort

  15. Excel: Sorting is by level

  16. Excel: Filter – Keep only selected items And hide selected item. Notice the total changes.

  17. Excel: Member Search

  18. EXCEL: FORMULA TEXT

  19. EXCEL: F-9 key

  20. EXCEL: Fun with AutoNumbering

  21. EXCEL: Named Dataset

  22. Excel: Defer Layout Update SSAS – Prevent load and delay when using pivot tables.

  23. EXCEL: Fun with the aggregate toolbar

  24. T-SQL: No reason to use ISNULL  CONCAT! • Messy vs clean code • No + symbol needed • No ISNULL needed

  25. T-SQL: Use windowing functions Neat evolution

  26. T-SQL: Testing and Refactoring with EXCEPT Keyword ;with FreightTotal as ( select custid, sum(freight) as totalFreight from sales.orders group by custid ) select o.custid , o.freight , ft.totalFreight as totalFreight from sales.orders o join FreightTotal ft on o.custid = ft.custid EXCEPT select custid , freight , sum(freight) over (partition by custid) as totalFreight from sales.orders

  27. T-SQL: Life is so easy with a dates table Find the sales numbers for the first Monday of every month of the year T-SQL with no dates table T-SQL with dates table

  28. T-SQL: Prettify! http://extras.sqlservercentral.com/prettifier/prettifier.aspx RedGatePlugIn for SQL Server Management Studio

  29. TSQL: Or use SQLPrompt - RedGate

  30. T-SQL: Execute scripts over multiple servers

  31. T-SQL: Try_Cast Avoiding terrible casting errors

  32. T-SQL: Never reinvent the wheel – SQL# Take SQL# for example Good Documentation Easy Syntax Cheap (and much of it is free)

  33. T-SQL: Save scripts in a project for easy navigation

  34. T-SQL: Fight Dependencies - Alias, Obscure, and De-couple Views, Aliases

  35. T-SQL: Check for heaps/clustered indexes SELECT t.[Name] FROM sys.Indexesi JOIN sys.Tables t ON t.Object_ID = i.Object_id WHERE i.type_desc = 'HEAP' ORDER BY t.[Name]

  36. T-SQL: Index all the keys select object_name(c.object_id), c.name from sys.columns c where c.name like '%id' and c.object_id not in ( select object_id from sys.index_columns )

  37. SSRS: Skip GIS, use MorgueFile

  38. SSRS: Log, Log, Log (and beware of subscriptions) • selectc.Name • ,e.InstanceName • ,e.UserName • ,e.Parameters • ,e.TimeStart • ,e.TimeEnd • ,e.TimeDataRetrieval • ,e.TimeProcessing • ,e.TimeRendering • fromexecutionloge • joincatalogc • one.reportid=c.ItemID • Send a Link, or a file on a shared folder that you can audit. Find someway to audit who opened the link or the file in the folder. Try to avoid sending the PDF without a way to audit it.

  39. SSRS: Store colors in the database Colors = Business Logic Put it in the database Use Expressions to read the colors Include action colors and levels

  40. SSRS: Store Formats in the Database

  41. SSRS: Get Buy-in to Export to Single Format • Build to export to a single format • Excel • Word • Web • PDF • Then get buy-in and make it a standard in the organization

  42. SSRS: Used Linked Reports to Manage Security • Allows you to use Role assignments and • Not have duplicate reports in folders • Not have users in folders they shouldn’t be in • Not manage security on individual reports

  43. SSRS: Use a report footer • Put executive sponsor there & email address • Date Executed • Parameters Used • Date Created • Date Modified • Put row count there

  44. SSRS: Use a wiki for taxonomy Record owner Record changes Record technical calculation

  45. SSIS: The proper way to execute an SSIS package

  46. SSIS: Color Blind

  47. Power BI - Visualization: Bad Dashboard

  48. Power BI - Visualization: Use color sparingly. In nature, colors do two things: Entice Warn So let’s use them sparingly.. WARNING: POISON FROG! 49

  49. Power BI -Visualization: Stephen Few

  50. Power BI - Visualization: Cynthia Brewer

More Related