Adatis BI Blogs

SSRS and Dax

Tools like Power BI have changed reporting allowing power users to leverage tabular cubes to present information quicker and without the (perceived) need for developers. However, experience tells us many users still want data in tables with a myriad of formatting and display rules. Power BI is not quite there yet in terms of providing all this functionality in the same way that SSRS is. For me, SSRS's great value and, at the same time its curse, is the sheer amount of customisation a developer can do. I have found that almost anything a business user demands in terms of formatting and display is possible. But you have invested your time and money in a tabular SSAS model which plays nicely with Power BI but your users want SSRS reports so how to get to your data - using DAX, of course. Using EVALUATE, SUMMARIZECOLUMNS and SELECTCOLUMNS you can return data from a tabular model in a tabular format ready to be read as a dataset in SSRS. If you had the following data in your tabular model:  ProductName  ProductCategory  Country  Sales  Surly Steamroller  Complete bike  France  £46,575  Genesis Day One   Frame  France  £47,987  Genesis Day One   Frame  France  £47,987  Genesis Vapour 20  Complete bike  France  £24,867  Genesis Vapour 20  Complete bike  United Kingdom  £21,856  Genesis Day One   Complete bike  United Kingdom  £47,875  Surly Steamroller  Complete bike  United Kingdom  £27,969  Surly Steamroller  Complete bike  United States  £46,575  Genesis Day One   Frame  United States  £47,987  Genesis Day One   Complete bike  United States  £47,987A pattern for returning data for SSRS could be:DEFINE VAR FilterCountry = @country EVALUATE SELECTCOLUMNS ( SUMMARIZECOLUMNS ( 'Product'[ProductName], 'ProductCategory'[ProductCategory], 'Geography'[Country], FILTER ( VALUES ( 'Geography'[Country] ), ( OR ( ( FilterCountry = "All" ), PATHCONTAINS ( FilterCountry, 'Geography'[Country] ) ) ) ), "Sales", [Sales] ), "Product Name", [ProductName], "Product Category", [ProductCategory], "Sales", [Sales], "Estimated VAT", [Sales] * 0.2 ) To step through this…The DEFINE part declare a variable which will take a value from a SSRS parameterSUMMARIZECOLUMNS will return a CROSS JOIN of all the dimension columns entered, by passing in a measure or fact column as the last part of the block (here [Sales]) it will return only the cmbinations of dimension where there is a value for measure or fact item - particularly useful if you are dealing with date table stretching into the future.FILTER will filter the SUMMARIZECOLUMNS based on the SSRS parameter value. By adding the OR and PATHCONTAINS sections you can handle multi-value parameters in SSRS. This is a good blog post explaining this approach.SELECTCOLUMNS allows you to both provide friendly names for any dimensions (this is better handled in the tabular model but the amount of times it isn't) and also perform calculations within your DAX query. If your SSRS parameter was set to France and United Kingdom, the results would be:   Product Name  Product Category  Country  Sales  Estimated VAT  Surly Steamroller  Complete bike  France   £46,575  £931.50  Genesis Day One   Frame  France  £47,987  £959.74  Genesis Day One   Frame  France  £47,987  £959.74  Genesis Vapour 20  Complete bike  France  £24,867  £497.34  Genesis Vapour 20  Complete bike   United Kingdom  £21,856  £437.12  Genesis Day One   Complete bike  United Kingdom  £47,875  £957.50  Surly Steamroller  Complete bike  United Kingdom  £27,969  £559.38If you set up a connection to your tabular cube in SSRS, you can paste in your DAX code, configure your parameters and you are good to go!

On-Premise Power BI: Part 1 - Technical Preview

Back in October 2016 at SQL Pass, Seattle, Microsoft announced that Power BI was going on premise.  Along with the big fanfare, there was also a Technical Preview released – in which a user could download a pre-built VM from Azure Marketplace and get a first look of how SSRS and Power BI could work together.  Roll on a few months (January 17th 2017 – to be precise) and the new Technical Preview is available.  This time, you can actually download a standalone copy of Power BI Desktop (for SSRS) and a slimmed down SQL Server Reporting Services 2016 configuration tool.  If you follow the official Microsoft blog, getting set up is really easy.  Just a heads up – there are still lots of limitations with this preview, which can be found at the bottom of the January blog. The blog will be broken into two parts – with Part 1 focusing on my experiences with the January Technical Preview of SSRS and Power BI. Technical Preview Feedback Setting up a local SSRS Server (on my own machine) and deploying a Power BI Report was really easy, which is good feedback in its own right.  Annoyingly, only Analysis Services connections are currently supported within the preview Power BI Desktop application and all of my workbooks use online services or SQL Server databases!  This meant I had to download a tabular model for Adventure Works and create a quick test report using the dataset.  Not a major problem, but an inconvenience nonetheless. All of my other observations are based around differences between the regular SSRS service and this new offering.  In essence, not much has changed – which can only help a user’s experience.  Here are the things to be aware of: SSRS Configuration Tool There are two new options – ‘Scale-out Deployment’ and ‘Power BI Integration’.  The latter is the one we care about.  In effect, you need to tie SSRS version of Power BI Desktop (which is available through the Technical Preview install) to the SSRS Configuration. Power BI Desktop The application looks pretty much the same, with the only differences highlighted below.  The most important bit is saving the report to the SSRS Server.  I thought it would have been under the ‘Publish’ option, but Microsoft opted to put it in ‘Save As’.  The application itself is called ‘Power Bi Desktop (SQL Server Reporting Services)’ and I imagine it will stay this way, in order to differentiate between On-Premise and Cloud versions. The final step is to define the SSRS Server.  A grid appears and you can either choose an old URL or define a new one.  As long as you are connected to an Analysis Services model and type in the correct server name, then the report will successfully deploy. SSRS Report Server I deployed a test Power BI Report to the local SSRS Server and there are a few specific options now.  By clicking the ellipses, you can open the report as normal, Edit directly in Power BI Desktop or even download the pbix file.  Notice the ‘Power BI’ icon on the tile of the report, which also helps with distinguish the type of reports in the SSRS portal. Conclusion My first impressions of Power BI On-Premise are good.  Whilst there are still some clear gaps and limitations, the fact that Microsoft are looking at bring SSRS and Power BI together must be a good thing.  They recognize the need to bring Mobile, standard On-Premise and ‘director pleasing’ reports together in a one stop shop. I am certainly excited about the next Technical Preview for Power BI On-Premise.  Not only should it contain more data connectors and features, Microsoft should reveal more about the long term visions.  Look out for my next blog where I will discuss licensing implications, architecture and some of the common Power BI questions I get from prospective clients.   Further Reading o   Power BI Reports in SSRS Release Notes - o   October 2016 Technical Preview Blog - o   December 2016 Feedback Review Blog - o   January 2017 Technical Preview Blog -

Microsoft BI – Coming Soon to SQL Server 2016

After attending the Pass Summit 2016 a couple of weeks ago, I attended a number of sessions that provided an insight into the direction Microsoft are heading with BI.  I thought I’d share this with the community. Looking back to October 2015, the official Reporting Roadmap blog from Microsoft stated their intent in dramatically improving the visualisations, integration and harmonisation of both on-prem (SSRS) and cloud based services (Power BI).  Whilst reporting appeared to be a renewed focus, they are constantly driving other areas of BI - such as analysis services and database/data warehousing development in the cloud. Now, for the interesting bit.  Here is what we can expect in the SQL Server 20916 BI Stack in the near future: -          SQL Server 2018 CTP (to be released within the next month). o   This seems very early, considering 2016 has only just been released!  Let’s wait for an official Microsoft announcement. -          Azure DW o   Auto Create Statistics §  Currently statistics have to be generated and updated on the fly. o   Replicated Distributed Table §  One that will excite any cloud based SQL Server developer. §  This will reduce data transfer between distributions/nodes and consequently improve performance. -          Azure Data Lake o   General Availability (GA) is imminent. o   Future Features: §  Polybase, so that you can connect the two big MMP platforms (ADL and DW). §  SSIS connectors (released with GA) for Store. §  Python and R in U-SQL. -          SSIS o   Lots of new connectors, including: §  ADLS. §  CRM. -          SSAS o   Migrating Power BI Models into Tabular. §  This is coming very soon apparently, but will be developed in Azure SSAS first. o   Object Level Security in Tabular §  We currently have row level, but there are talk to secure a physical object, not just a row. §  Even better news - Microsoft want to integrate the two together, which will make security awesome in Tabular. -          SSRS o   Supporting (not pinning) Excel reports in RS report. §  This will come, but Power BI is the focus right now and we may have to wait a while. -          Power BI o   Additional and better Pivot Table functionality. o   Integrating Active Directory dynamically. o   Potential to use Direct Query and Imported modes together – as a hybrid. §  Functionality is possible, but performance needs to be weighed up by Microsoft before anything will emerge. o   Quick Calcs. §  Only ‘Percent of Current Total’ currently available. §  Potential is to offer lots more – such as YTD, MAT, Current Year vs. Previous Year, etc. §  This is for the users who aren’t familiar with DAX. o   Template organisational Content Packs. §  The ability to give the user the ability to personalise colours, fonts, etc. within a structured (organisational) content pack. -          Power BI Embed o   Application developer will be able to limit user sessions and therefore, reduce the charge per 1 hour costs that come with it.   There are some features/issues Microsoft do not plan to change.  Although, the good thing about Microsoft is that they are community driven, so if you feel strongly about anything (and get support from your peers), they may change their minds. -          SSRS o   Q&A, Query Editor (Power Query), R integration, etc. not being developed. §  Pretty obvious really.  Whilst they are going to introduce almost everything from Power BI, some elements of functionality are just not needed for on pre purposes. §  R Scripts may come one day, but not a focus right now. -          Power BI o   Source Control §  No immediate plans to integrate with TFS or modularise the pbix files (for a more developer based solution) §  Not surprising as this is a self-service tool, not a development team. §  Work around is to upload pbix files into OneDrive and use the versioning as an element of Source Control or add a file into Visual Studio. §  Keep Voting on if you want this! (Currently 278 votes). · -          Power BI Embed o   Licence model §  ‘Speak to Marketing’ is what we were told.   Is everyone excited?  I certainly am.  Microsoft are openly admitting that any new BI related feature will be developed in the cloud first, but this was pretty obvious.  For all you on-prem fanatics (me included), at least the bugs/undocumented features will be ironed out before we get to use them!  My personal favourite has to be the direction SSRS is going.  It was recently labelled a ‘mature’ product, but now RS has suddenly become a cool and current tool to use.  The fact Power BI and SSRS will become almost the same product, tells us all that on-prem is still an important part of the Microsoft licensing strategy.   I am sure there are lots of other features coming into the BI stack over the next 6 months to a year and these were just the ones I had heard about.  Feel free to comment if you have additional ones to share.

October 28th Technical Preview for SSRS – Including Power BI in SSRS

Today at the Pass Summit, Microsoft announced some cool new features that are coming into SSRS 2016.   I attended the ‘SQL Server Reporting Services: What’s New and What’s Next’ session (presented by Chris Finlan and Riccardo Muti and there was certainly some very exciting news for anyone in the BI sector. There will be a technical preview available from 28th October, in which you can download a pre-configured Virtual Machine in the Azure Marketplace.  This includes sample reports and data, enabling you to try the features whilst Microsoft work on a complete preview for on premise R&D. This blog lists the new feature from a high level.  I will announce more information as and when it is available. Features   1.       Power BI reports in SQL Server Reporting Services. a.       I won’t steal Microsoft’s thunder and instead, redirect you to their blog written on October 25th: b.      In essence, you can create a Power BI desktop report and hook it straight into your on premise SSRS reporting solution. Amazing, right?! 2.       Report Manager. a.       List View is back!  You can again view reports the old fashioned way. b.      Show Hidden Items is now unchecked by default.  Sounds insignificant, but it is the little things that sometimes matter. 3.       Report Comments Section a.       This is really cool.  A user can write a comment against a given report and even ‘snapshot’ the report as an image and upload it.  This is a good way to start an internal company discussion, as well as trace how a visualization has changed over time. b.      All comments and images are stored in the Report Server db, which helps for auditing or plugging the data into an external tool/text file. 4.       Other – More information to be announced soon a.       Direct URL link from a KPI.  If you click a KPI, it will take you to a specified link, without the need to select it form an option. b.      Mobile Reporting auditing. c.       Better support for generated MDX.  The current date workaround will no longer be needed and make everyone’s life easier.  d.      General performance. Current Preview Limitations   As this is an interim SSRS Preview release, there are some current limitations.  These are more specific to the Power BI reports in SSRS: -          Power BI reports that connect “live” to Analysis Services models – both Tabular and         Multidimensional  (cubes). No other data sources are currently available. -         Custom Visuals not supported. Coming Soon   As soon as I get back from the Pass Summit (and over my jet lag), I will be downloading the preview and trying out the cool features.  Please come back to my blog page to check out my findings and more importantly, feel free to comment on any quirks/issues/limitations that you have come across yourself. It is certainly exciting times for the on premise enthusiast out there.  I had lost all hope for SSRS, but with 2016, Microsoft have rekindled my love for enterprise data visualisation.

New Features in SQL Server 2016 – Part 3: Reporting Services

I am pleased to announce Microsoft have made some improvement to Reporting Services (SSRS), in the upcoming version of SQL Server 2016 CTP2.  With the new acquisition of Datazen and advances in Power BI, SSRS seemed to be falling behind – both in terms of functionality and look and feel. In the Preview version of SQL Server 2016, the following features have been announced or improved: ·        Built-in R Analytics. ·        Improved DPI in Report Builder. ·        Reporting Services Subscriptions. Within the SQL Community, there has been a lot of talk around some of the existing SSRS functionality being improved and made more modern.  As SQL Server 2016 CTP2 has only been public for a couple of weeks, not all SSRS features are yet available.  The below are expected to be in the full release, although they have yet to be officially announced. ·        New Parameter Panel, chart types and design. ·        Power View included in SSRS. ·        Mobile BI. Improved DPI in Report Builder DPI (Dots per Inches) on new devices make rendering reports very difficult in the current version of SSRS.  Although these devices have high DPI, this means that more pixels need to be drawn and the display can look very small.  SSRS 2016 will be able to handle high DPI, without distorting reports. 1.      Windows 8.1 DPI Scaling Enhancements. a.      Optimizing the usability and readability of high-DPI displays. b.     Empowering developers to optimize app-specific scaling based on display DPI. 2.      High DPI and Windows 8.1. Reporting Services Subscriptions The following changes have been made to the Reporting Services subscriptions, depending on native SSRS mode and SharePoint. Native and SharePoint: 1.      Subscription description. a.      Include a description of the report as part of the subscription properties. The description is included on the subscription summary page. 2.      Change subscription owner. a.      Starting with the SQL Server 2016 Community Technology CTP2 release, you can change subscription owners using the user interface or script. This helps when carrying out routine tasks e.g. when users leave or change roles in your organisation.   Native ONLY: 1.      Enable and disable subscriptions. a.      User interface options to quickly disable and enable subscriptions. Disabled subscriptions can be easily re-enabled. 2.      Shared credential for file share subscriptions. Two workflows now exist with Reporting Services file share subscriptions: a.      Reporting Services administrator can configure a single file share account, which is used for one to many subscriptions. b.     Configure individual subscriptions with specific credentials for the destination file share. Built-in R Analytics The enables Data Scientists to use R scripts to plug into SSRS reports.  R is a very powerful analytical tool that can cluster data and create powerful trending capabilities. 1.      Previously integrating R with SSRS involved an unofficial plug in (to Visual Studio) from Codeplex.  To download the add-in, click here.  I have also provided a link to Jen Underwood’s blog, found under ‘References’. 2.      New built in R component will enable Data Scientists to interact and tests R scripts in a transactional environment. 3.      Create SSRS reports referencing R scripts. New Parameter Panel, Chart Types and Design According to Gilbert Quevauvilliers, SSRS will undergo quite an overhaul in SQL Server 2016.  Some of the improved features include: 1.      Parameters panel will be more interactive and respond in a similar way to the Slicers in Excel. 2.      New chart types being provided, similar to the ones in Power BI. 3.      HTML to replace Silverlight, which will ensure reports can render in the most used browsers.  Rendering speeds will also increase. Power View included in SSRS The talk on Social Media sites (like twitter) is that Power View will be added to SSRS 2016 CTP3.  This could give the following benefits: 1.      Almost any data source will plug in to SSRS. 2.      Better, more modern chart types – improving user analysis. 3.      Potentially the ability to write native M into an SSRS report. Mobile BI SQL Server 2016 will support Mobile BI and data visualisation.  The devices currently supported are Windows, iOS and Android devices.  1.      Users can now visualise and interact with data more easily, without additional charge. 2.      Ability to connect to enterprise data sources and use Active Directory for user authentication. 3.      Deliver live data updates on mobile devices and personalise data queries for different users. References For more information on all of the new SSRS SQL Server 2016 features, the below resources/blogs are highly recommended. ·        Official Microsoft Page - ·        Gilbert Quevauvilliers BI blog – ·        Matt Landis Blog - ·        Jen Underwood’s R in SSRS Blog -

SQL PASS Summit - Day 3

Today was the final day of the pass summit. The schedule was a little different in the morning with no keynote speech. Instead I opted to see Jerimiah Peschka’s session ‘Dynamic SQL: Build Fast, Flexible Queries’. This was an interesting talk, which covered the security aspects of dynamic SQL and how to avoid things such as SQL injection and also how to ensure that queries generated from dynamic SQL ran quickly and avoided issues such as ensuring plan reuse on each execution. Whilst I very rarely find a myself situation in which Dynamic SQL is essential I found this an interesting talk and I am sure that when the need does arise it will have been very handy. Following this I attended a Microsoft lab on Azure Machine Learning (AML) ‘Authoring a hand-written digit recognizer in Microsoft Azure Machine Learning Studio’. This was a hands on session where computers and a set of exercises and sample data are supplied. Essentially the lesson consisted of providing a model with vector images of numbers, training the model to recognise the numbers and then passing a new set of number vector images to the model to see it identify them (> 85%) correctly. The session was interesting and gave me an overview of using AML Studio which was great. My main issue was that the data was already provided in vector format (csv files which were imported), whilst I can see time constraints mean that the conversion from image to vector during the class would have been difficult it would have been very interesting to have seen a few of the examples of the images so that we could see exactly what it was that was being identified and how different the numbers were as this would give an illustration of how clever the algorithms are. I finished the conference with Brain Larson’s talk entitled ‘Effective Reporting through SSRS Advanced Authoring Features’. As you may know SSRS has not been updated for some time now and with the current focus on Power View I wasn’t sure how much I would benefit from this and what the plans are for this tool in the future. I was quite surprised therefore when the room filled up and overflowed so that there were quite a crowd of people in the room who had to stand in order to watch it. The talk showed some interesting uses of SSRS - images embedded in the background of reports, adding strip lines and markers and an example of creating an interactive report which allowed users to set their preferences for running other reports. The examples given were very good as was the delivery, my only issue here is that without new features added to SSRS (for example ability to do an asynchronous refresh of some report elements) I am not sure what the uptake of SSRS is going to be. All in all I have had a fantastic and informative time here. I leave you with some pictures, taken earlier in the week :)  

Maintaining Hierarchy State On Report Refresh

I recently had a request from a client who wanted to display use a parent child hierarchy to navigate their report. The issue with this however is when you click the hierarchy the report refreshes, the data is updated in the charts, but the hierarchy collapses to its original state as below. This wasn’t suitable for the clients needs as it was a large hierarchy with several levels of depth.   What was required was to maintain the state of the hierarchy so it would stay expanded during report refresh. After a little thought I came up with the following solution: 1. Setup a standard report with an indented hierarchy with expandable sections. This has been covered in many places so I’ll only give a brief overview, for this example report I have used adventure works multidimensional model with the following MDX query as the source for the hierarchy data:- Add the fields in a matrix like this:   Right click the Organizations text box, select ‘Textbox Properties’ and in the Alignment tab set the Left padding property to the following value (change the number to adjust indentation): Following this you need to setup groupings in order for the expanding hierarchy. Add a grouping on the UniqueName property On the visibility section set the toggle property to HIDE (ignore the expression for now -) and set the "’Display can be toggled by this report item” property as per the below. In the advanced section set the recursive parent property to [Organisations.ParentUniqueName] and this should give a working report with an expandable hierarchy.   2. Add a hidden parameter to the report called OrganisationLevel. Set the default value to the top of the hierarchy. Add another column to the matrix, add text to it for the link. Select the text and right-click the selected text navigate to properties and place an action to go to original report and pass the OrganisationLevel parameter with a value of [Organisations.UniqueName]   3. Add a dataset dsHierarchyVisibleMembers with the below MDX query – this query uses the OrganisationLevel parameter to work out which level in the hierarchy the user has clicked on and the entities at the same level which we can then use to control the state of the expanding hierarchy.    Now set the visibility property on the row grouping to the following value: The last step is to set the hierarchy collapsed/expanded state, which we using the following formula for the ‘InitialToggleState’ property of the text box to the following. Once these steps are completed clicking one of the elements in the report will pass the parameter, but the hierarchy state is maintained between clicks. Clicking View on ‘Northeast Division’ the left report displays the following in the right – note the chart has been updated but that the hierarchy expansion state remains as it was before the user clicked.   That’s all for now, hope this is of use.

Word Cloud Reports in SSRS

Whilst SSRS does not have an inbuilt utility for building Word Cloud reports (sometimes also called tag clouds) this can be achieved with a little HTML as per the sample below. Essentially the concept is that you generate a HTML string which allows you to control the sizes and colours the of the text which you can then render in a Tablix or Text Box. There are many ways of achieving this, the method that I will describe dynamically sizes the text depending on the results generated from the query, you will need to decide if this meets your needs and adapt to your requirements as appropriate but the central concepts will be the same. For this example I have used the AdventureWorksDW database, available for download from: 1. The first step is to construct a query in the dataset that will return the html string in the required format. To produce this report I used the query below. As noted in the comments in the query, you can amend the parameters to control the text sizing and base size. DECLARE @max INT DECLARE @min INT DECLARE @base INT = 6 --This sets the base text size. DECLARE @scalefactor INT = 60 --This controls amount of size increase. DECLARE @multiplier FLOAT DECLARE @divisor FLOAT CREATE TABLE #tmpProductOrders      (Product VARCHAR(250)      ,Orders INT       ) INSERT INTO #tmpProductOrders SELECT        PSC.EnglishProductSubcategoryName AS Product       ,SUM(FI.OrderQuantity) Orders FROM [dbo].[FactInternetSales] FI INNER JOIN [dbo].[DimProduct] P         ON P.ProductKey = FI.ProductKey INNER JOIN [dbo].[DimProductSubcategory] AS PSC         ON PSC.ProductSubcategoryKey = P.ProductSubcategoryKey GROUP BY PSC.EnglishProductSubcategoryName SELECT @max=MAX(Orders), @min=MIN(Orders) FROM #tmpProductOrders SELECT @divisor = CAST((@max-@min) AS FLOAT); SELECT @divisor=IIF(@divisor=0.00, 1.00, @divisor) SELECT @multiplier = (@scalefactor-@base)/@divisor SELECT           REPLACE(REPLACE(                   (SELECT '<span style=font-size:' + CAST(u.Fontsize AS VARCHAR(10)) + 'pt;>' + u.Product + '</span><span> </span>'                    FROM                       (                        SELECT                               Product                              ,Orders                              ,@base + ((@max-(@max-(Orders-@min)))*@multiplier) AS Fontsize                        FROM                                (                                 SELECT                                           Product                                          ,Orders                                 FROM #tmpProductOrders                                ) sh                        ) u                     ORDER BY NEWID() --This provides a random ordering                    FOR XML PATH('') )              , '&lt;','<'),'&gt;','>'            ) AS KeywordCloud DROP TABLE #tmpProductOrders 2. Once you have the query which produces the HTML in the required format the next step is to place this into a Text Box or Tablix within the report. For this example I used a Text Box and expression to populate the data. It is important to note here that unlike most browsers the HTML rendering engine in SSRS is very sensitive and you need to be careful as slight errors in syntax or use of unrecognised tags will stop the html from rendering. 3. The final step is to change the properties so that the render format is set to HTML One can also use the HTML to change the colour or other properties of the individual words as required. I hope you find this useful!

Adding Strip Lines to SSRS Reports

Once in awhile you happen upon a really useful feature in SSRS that you were unaware of. For me strip lines are definitely one of these features that you don’t see that much mention of but which can add valuable insight to a report. Below is a simple example of a scatter chart with a horizontal and vertical strip line each marking the average values of their axis.  In order to add strip lines to the report you need to do the following: 1. Select the chart axis to which you wish to add the strip line and go to the properties window. In this there is an option marked ‘StripLines’. When you click in the cell you can open up the Strip Line Collection editor.   2. Click the ‘Add’ button in the strip line editor (note: you can add multiple strip lines to each axis if required). 3. In the option marked ‘IntervalOffset’ specify the required value for the Strip Line, you can either specify a fixed value or use an expression from your Dataset. If you require the line to repeat over fixed values you can fill in the properties for ‘Interval’. 4. You then need to set the display properties for the strip lines under appearance in order for them to be visible. To produce the example strip lines in this post I have set the ‘BorderColour’ to black and the ‘BorderStyle’ to dashed.     5. If you wish to add text after the strip line on the graph – for example to illustrate that values after the strip line are above average as shown below then the settings are under the title section. That’s it for now, hope that you’ve found this helpful:)

Maintaining Table Width with Hidden Columns in SSRS

Sometimes when using SSRS to create documents such as invoices, it is necessary to include columns in a table which may or may not be shown.  Often with these documents layout is very important, and having a table changing width as columns are shown or hidden is unacceptable.  It is not possible in SSRS to give a table a fixed width, but we can use another method to simulate this without having to dig into the RDL file. We’ll make a report that contains a table made up of 5 columns: Product (Static) Colour (Variable) Weight (Variable) Delivery Date (Variable) Price (Static) Columns labelled as ‘static’ are always shown on the report, and those labelled ‘variable’ may be optionally shown or hidden by the user at run-time.  In order to make it appear that the table maintains a constant width when some columns are hidden Test Data First we need some test data for out report.  Use the following script to set up a small table and sample data for use in the example: 1: CREATE TABLE [dbo].[InvoiceProducts] 2: ( Product VARCHAR(20) NOT NULL, 3: Colour VARCHAR(10) NOT NULL, 4: Weight NUMERIC(10,2) NOT NULL, 5: DeliveryDate DATETIME NULL, 6: Price NUMERIC(10,2) NOT NULL); 7:  8: INSERT INTO [dbo].[InvoiceProducts] VALUES ('Milk','White',1500,'2013-05-13',1.45); 9: INSERT INTO [dbo].[InvoiceProducts] VALUES ('Beans','Orange',450,NULL,0.75); 10: INSERT INTO [dbo].[InvoiceProducts] VALUES ('Eggs','Yellow',500,'2013-06-01',1.75); Creating the Report Open Visual Studio and create a new blank SSRS report.  Add a data source connection to the database where you created the InvoiceProducts table, then create a new data set using: 1: SELECT 2: Product 3: ,Colour 4: ,Weight 5: ,DeliveryDate 6: ,Price 7: FROM 8: dbo.InvoiceProducts Add a table to the report body, then map the data set fields to the table in the order of the SELECT statement.  Your table should look like this: Next we need to add parameters to control the visibility on the Colour, Weight and Delivery Date columns.  These should be set to a type of ‘boolean’ with the default value set to true.  Finally, set the visibility on each of the variable columns to be controlled by the parameters you’ve just added.  To do this, left-click into one of the column cells, then right-click on the solid grey bar above the column header.  Select ‘column visibility’, then select the radio button labelled ‘Show or hide based on an expression’.  Click the ‘Fx’ button to bring up the expression editor, then enter an expression like: 1: =Not(Parameters!ShowColour.Value) Do this for each of the columns for which we need to control the visibility, using the appropriate parameter for each column. If we test the report now, firstly with all of our parameters set to ‘true’ we should see all the columns: Now set the ‘Show Colour’ parameter to false and run again.  The column is hidden and the table width is reduced by the column width: To prevent this behaviour and maintain a constant table width, we need to insert and extra blank column for each column that may be conditionally hidden.  In the designer, insert 3 columns to the left of the ‘Colour’ column: By setting the ‘column visibility’ properties of these columns to give the opposite effect to the columns containing our data, we can add a blank column for each data column we hide and thus maintain total table width (given that the widths of a blank column and its corresponding data column are the same).  Starting with the left-most blank column, set the column visibility property to: 1: =Parameters!ShowColour.Value Set the other blank columns visibility using the parameters for weight and delivery date.  The effect is the when one of the parameters is set to ‘false’ (i.e. do not show the column), the data column will be hidden, but a blank column of the same width will be shown.  With suitable formatting, this will be invisible to the end user and will give the impression that the table width stays the same.  Below shows the report with all columns showing, and then with the colour column hidden: You could merge the ‘Product’ data cells with the blank cells between it and the ‘Colour’ data cells and this would allow the product data to expand to fill the blank column.  As far as the end user is concerned, the product column has simply gotten wider. Conclusion For occasions when precision layout is important, maintaining the width of a table that contains optionally hidden columns may be necessary.  Using the method outlined in this post, I have shown how it is relatively easy to achieve this using standard SSRS components and simple expressions.  This is a much more sustainable solution than editing the RDL of a report at run-time!

Differences between Regular SSRS and SSRS for Dynamics AX 2012

I’ve spent quite a long time recently developing SSRS reports for Dynamics AX 2012.  In a change from AX 2009, the newer version now uses SSRS as it’s main reporting engine.  No doubt this is so that business with existing experience in the MS SQL stack can get on creating reports for their new AX 2012 installation without the need for re-training or bringing in extra people trained in X++. So not a bad idea, however my experience has turned up a number of important differences between standard SSRS reports and those in Dynamics AX: Placement of Report Code In Dynamics AX 2012 SSRS reports you cannot use the code window found by going to the report properties.  The window is still there, and you can still type into it, but as soon as you build the report in AX the code will be stripped out of the report.  This is because when you build a report design within a Dynamics AX report model in Visual Studio, the process adds a custom code block to the report XML (a method to overwrite the report OnInit() method).  This wipes out any code you may have added. Instead, you must add code as a new Data Method in the report model.  This can then be referenced in your report design like this: =MyMethod(arg1) No ‘ResetPageNumbers’ Property on PageBreak A great addition to SSRS 2008 was the ability to reset the page number global based on groups within your report.  This was handled by a property found by expanding out the ‘PageBreak’ property for the group.  Unfortunately this option does not exist in SSRS for Dynamics AX 2012.  There may be a good reason for this, and if anyone knows it please let me know! To get around this I added a data method which keeps track of which group my report is on as it renders and resets the page number appropriately. Conditionally Hidden Elements cause ConsumeContainerWhitespace Property to Fail Suppose I have a one-page report that should fit onto an A4 page.  The report contains a data table and another section, which should appear at the bottom of the page regardless of the number of rows in the table. To do this, I enclose the data table in a rectangle element which is sized such that the ‘bottom section’ is at the bottom of the page.  By setting the report property ‘consumecontainerwhitespace’ to true, I ensure that as the table grows it fills up it’s containing rectangle, but the ‘bottom section’ stays where it is. This example would also work in AX, unless you place an element in the rectangle which can be conditionally hidden.  For example I may have a row in the table which should only be shown in certain conditions.  In standard SSRS, the report would continue to function as before, with the table consuming the rectangle and the rectangle staying the same size.  In AX however, the addition of the hidden row causes this behaviour to break and that rectangle to grow as rows are added to the table. A workaround for this is to make the optional row ‘invisible’ by shrinking it and/or making the text and borders white so they don’t show up.

SSRS Language parameter using SSAS Translations

Problem SSAS translations are known to deal with warehousing language requirements well.  We recently had a requirement for a multi-language report suite on top of a user browsed cube. First thought was SSAS translations, these will pick up the users local language settings and allow an elegant solution to the problem.  However a requirement was put forward for a language picker in the SSRS report suite to over-ride the users locale if they so desire.  This was not so obvious. Data source We started with a list of languages to build a picker from, this was built by importing the table available here to a SQL Server and building it as a dataset in our report: It is important to bring through the decimal value in column 4 and the short string in column 2 as we will see later. Parameter Build Use the dataset on MSDN to build a language select parameter.  Use the language name in the label field and the decimal locale identifier in the value field.  The setup should look something like this: Default value setup If you just need English as the default use the UK code 2057 as your default value, however a more elegant final solution is a little more complex.  Reporting services provides the built in variable User!Language which holds the users language in the short string ‘en-gb’ style format.  So although we can access the users language it needs translating into a localeId before we can pass it to SSAS.  Fortunately for us the same table we used above can be used to translate between short string and locale. I achieved this using a further hidden parameter defaulted to the built in User!Language variable, the parameter was set up as detailed below: Using the dataset where LanguageCode = short string in our source table If this parameter is run first (it must be earlier in the report parameters list) it will contain the users language in the form ‘en-gb’ in the value and ‘2057’ in the label field.  It is then simple to set the default of the first dropdown parameter to the value of the hidden parameter’s label using the following syntax. =Parameters!UserLanguage.Label I am open to suggestions of a way to get the default without a hidden parameter or a large switch statement so if you find something more elegant please let me know!  It is also worth noting that if a user has a language that your source table does not have an entry for you are obviously going to lose the defaulting behaviour.  Parameter Ordering It is essential to make sure that the parameters are ordered in your parameter list in the sequence they need to be evaluated in.  In this case the hidden user language parameter needs to be first, followed by the visible language drop down parameter.  Only then should other report parameters be added. Passing Parameter value to SSAS In order for SSAS to provide us the data in the language requested we need to pass the locale to analysis services.  We can do this by over-riding the locale in the connection string.  Go to your report data source and double click for properties.  Then we need to edit the connection string expression as circled. Here we need to make sure our connection to SSAS is provided with the locale from the parameter.  We use the following expression to build our connections string. ="Data Source=localhost;Initial Catalog=" & CHR(34) & "Adventure Works DW 2008R2" & CHR(34) & ";Locale Identifier=" & Parameters!Language.Value (CHR(34) is the “ character that we wanted to escape properly.) This builds the string detailed below for the parameter selection Spanish. =Data Source=localhost;Initial Catalog="Adventure Works DW 2008R2";Locale Identifier=3082 This should now preview and allow you to select the language and your data sets will return any translations you have present in the cube. Notes If you are using a shared data source reference this is not going to work for you as it is only possible to expression a data source held in the report.  In this case perhaps you can use a solution along the lines of the one detailed by Mosha here: Another warning is to finish your report design first because you cant refresh datasets and metadata once the expression is set Tested on 2008R2 only.   Hope this helps everyone and allows you to select your existing translations in a flexible way through SSRS.  Your comments are welcome.