How to add a Document Map to your reports

How to add a Document Map to your reports

One of the most common things I do during taking interviews is to give real life scenarios and assess the person on how he tackles the situation. Most of the times, what I really look for is how he goes forward in solving the problem – the approach and the way of thinking rather than the answer itself. Another thing that I look for is whether he tries to put some extra effort to implement the solution (especially the reporting part) in an aesthetic and user-friendly way. Most of the candidates would know the answer to some simple question like how to add data labels to their charts but they stutter when I ask what are smart labels or how can we prevent the cluttering of data labels. I would easily give brownie points to someone who details his solution with some extra attention to details. It must be my frequent rendezvous with creating big reports that make me extra fond of document maps in SSRS and an essential part of my SSRS interview questions. It is hard to imagine going through a big book that has no table of contents. But many users and developers have no problem at all in designing big reports without any navigational aid (although at some point of their wretched life, the users are going to curse the person who developed those reports), even though Microsoft has provided this feature. Document Map is a navigational feature in SSRS that, when implemented, allows the user to navigate through the document and its hierarchies. With this feature, you can add a panel to the left of the reports where you can have the list of “contents” of the report. What makes it more special is that on clicking the “content” in the list, you will be directly taken to the page where the content is present. Cool feature, huh? Requirement Suppose you already have a matrix or a table with some groupings as shown below Matrix - Subcategory grouping Suppose the number of subcategories are very large and spans into multiple pages and the requirement is such that each user has to specifically search for a set of subcategories at a particular time of the day. It would make his life a lot easier if there was a document map listing the subcategories on clicking which he would be directly taken to the clicked subcategory Solution 1) Go to the design mode of the report and click on the group properties of subcategory. Subcategory group properties 2) Go to the advanced tab of the properties and set the Document map as the field Subcategory (you can select the appropriate field name from the drop-down). Advanced group properties 3) Save the report, deploy it and then preview it . Report DM Preview Already you can see the panel at the left hand side which contains the list of subcategories. To hide/unhide this panel, you can click on the icon that has been highlighted in red in the above image. Notice that the “Document Map” text highlighted in blue is the report name. 4) Now you can click on any of the subcategories, say Handlebars and you would be taken to the page where the clicked subcategory is. On clicking Handlebars 5) (a) You can also make document map labels for each of your report item. For eg, if you have 2 tables, you can click on the table and press F4 to see the properties. Table document map label 5) (b) You can give the required name in the DocumentMapLabel property as shown above and then see the required result when you preview the report. DM Tables 6) Now that you have seen the toggle symbol, you would have already guessed that it is possible to create hierarchies also in the document map label, for e.g., Subcategory—>Product. For that, all you need to do is to enable Document Map property for the Product group also. Subcategory - Product Hierarchy DM Note Quoting a section from Packtub Learning SQL Server 2008 Reporting Services – “ Document Map is mostly for HTML rendering. Other renderers render differently.

  • PDF: Uses Bookmarks.
  • Excel: Uses named worksheet with hierarchical links. Report sections appear in other sheets.
  • Word document also has a document map like the table of contents.
  • Tiff, CSV, and XML ignore this setting.
  • Recursive data is related to the idea of self joins where the relationship between parent and child is represented by fields in the dataset. “

So there ends the part 3 of my interview series. In my next blog, I would be taking a momentary break from my interview question series and presenting a very interesting application of Document Maps, wait for it 🙂

Posted by SQLJason, 5 comments
OR operation in MDX

OR operation in MDX

A seemingly harmless question which makes you ignore it thinking you know the answer, but the moment you give it a second look, you start scratching your head. This was my first reaction when I was asked to implement an OR operation in MDX and I really had to think to come up with an answer, quite contrary to the no-brainer I thought this question was. Ever since, this has been in my list of interview questions and this blog would be the second part of the series where I unveil some of my favourite questions. Problem Lets face a scenario where you have a relation – Month<—Date & Week<—Date. Now the requirement is such that the Date field should be displayed such that it should either belong to the Month selected or the Week selected. Answer Follow the steps below to get the answer 1) The query below will display the Date member captions for all dates WITH 
  MEMBER [measures].[nam] AS 
    [Date].[Date].CurrentMember.Member_Caption 
SELECT 
  [measures].[nam] ON 0
,[Date].[Date].Children ON 1
FROM [Adventure Works]; 2) Suppose we need to display all the Dates such that it either belongs to the month March,2007 or Week 27 CY 2005. P.S. : Just giving a where clause with a set like the query given below will not work as it will perform an AND operation rather than an OR operation. WITH 
  MEMBER [measures].[nam] AS 
    [Date].[Date].CurrentMember.Member_Caption 
SELECT 
  [measures].[nam] ON 0
,[Date].[Date].Children ON 1
FROM [Adventure Works]
WHERE 
  (
    {[Date].[Calendar Weeks].[Calendar Week].&[27]&[2005]}
   ,{[Date].[Calendar].[Month].&[2007]&[3]}
  ); 3) The correct way is to bring both the conditions inside a set but as of now, they are of different dimensionality and can’t be used together as the members of the same set. Hence we create a tuple of the calendar week and month such that the valid condition of one attribute would be used with the default member of the other dimension (which is usually the All member). WITH 
  MEMBER [measures].[nam] AS 
    [Date].[Date].CurrentMember.Member_Caption 
SELECT 
  [measures].[nam] ON 0
,[Date].[Date].Children ON 1
FROM [Adventure Works]
WHERE 
  {
    (
      [Date].[Calendar].DefaultMember
     ,[Date].[Calendar Weeks].[Calendar Week].&[27]&[2005]
    )
   ,(
      [Date].[Calendar].[Month].&[2007]&[3]
     ,[Date].[Calendar Weeks].DefaultMember
    )
  }; This query would give the correct output as shown below OR operation in MDX This query makes use of the fact that ([Date].[Calendar].DefaultMember,[Date].[Calendar Weeks].[Calendar Week].&[27]&[2005]) = (Date].[Calendar Weeks].[Calendar Week].&[27]&[2005]) We use this technique to make the dimensionality of both the week and month conditions the same. Once this concept is understood, implementing OR conditions in MDX would be a piece of cake. 

Posted by SQLJason, 0 comments
MDX : Non Empty v/s NonEmpty

MDX : Non Empty v/s NonEmpty

Now that the recession pains have allayed, most of my colleagues are having a very busy time either taking interviews or giving interviews. As for myself, I thought of sharing some thoughts on my interview questions through my blogs and here goes my first attempt where I have tried to differentiate between Non Empty and NonEmpty. Find the link to my blog here – MDX : Non Empty v/s NonEmpty Non Empty vs NonEmpty Title MDX

Posted by SQLJason, 1 comment
Better way of repeating charts based on parameters

Better way of repeating charts based on parameters

Ok, so this is not your everyday run-of-the-mill post which talks about how to repeat the same chart for each value selected in the report parameters. I know you have been listening to countless posts instructing how you can do the same with Lists in SSRS (in case you haven’t, this would be a nice place to start). But the subtle issue with just using a list is that they grow only downwards. Now this would mean that in the case of small charts, we would not be using the page efficiently as there would be lots of horizontal space left. So the ideal situation would be that the list grows horizontally and at the end of the page, it just breaks to the next line, just as we would expect our typewriter to behave. Actually speaking, this is not that hard as it looks to be. All it needs is a little tweak in your dataset query and some changes to your layout as I am going to show you in this post. To illustrate this, I am using the following scenario from AdventureWorks database:- a) Report parameter for subcategory b) The report should display charts (Products of the selected subcategory v/s Reseller Amount) for each of the subcategory selected Now that the requirement is crystal clear, follow the steps detailed below:- 1) Edit the dataset query to include a rank for each subcategory. For e.g., in my query, I have the Subcategory, Products and Reseller amount. Create a calculated member using the rank() which would return the rank of the subcategory. Query Designer - Add calculated member for rank 2) Once you have the rank, proceed to the Layout. Calculate the max no of charts that can be displayed horizontally. This is done by using the formula Integer part of (Page Width/Chart Width) In this example, I am taking 3 as the max number of charts displayed in one row. 3) Make the chart with Product on Category and measure on Data Value. Make the title of the chart as [First(Subcategory)] Making the Chart 4) Now create a matrix and add Subcategory on the rows and embed the above chart in the data section of the matrix. This will ensure that the chart will appear once for each subcategory appearing in the matrix. Make the column containing subcategory as hidden and borders of the matrix as none, so that only the chart is visible. Copy this 2 more times so that we have the layout as shown below in the figure Final Layout Notice that the column for subcategory has been made very small. The reason is because anyways they are hidden and we would not want to waste precious real estate (read it as the space in the layout) on hidden things. 5) Now right click on the first chart and select tablix properties. Tablix properties 6) Go to filters tab and click Add button. Enter =Fields!Prod_Rank.Value mod 3 for Expression. Operator should be = and Value should be 1. Also make sure that the data type is integer. Adding the filter This would make sure that only subcategories whose Rank mod 3 is 1 would be displayed in the first matrix. E.g. subcategories with rank 1,4,7,10, etc 7) Repeat step 5 and 6 for second matrix with Value=2 8) Repeat step 5 and 6 for third matrix with Value=0 9) You can preview the report and see your chart growing horizontally first and then vertically as required based on your selections. Final Result This technique would work beautifully on both the 2005 as well as the 2008 versions of SSRS.

Posted by SQLJason, 8 comments
Setting default member in role playing dimensions

Setting default member in role playing dimensions

Today I got a call from one of my colleagues asking how to set a default member for a dimension attribute . I was a little surprised as I thought setting a default member is quite straight forward and was even more surprised to learn that the dimension had processed successfully but the cube had failed with errors. P.S. : If you are a SSAS beginner, please read this msdn article on how to set the default member of a cube to grasp my blog contents completely – Specifying the Default Member I straightaway went to my colleague’s desk as I clearly saw it as an opportunity to learn something new. First thing I did was to check up the dimension in the dimension designer and to verify that the default member was set up correctly. Set default member The default member looked to be correct and then I processed the cube to see the error message. error message The error message clearly mentions that the level [Brand Family] (which is the name of the dimension) was not found in the cube when the default member string was parsed. So this could mean two things:- a) either the default member would have been misspelt b) there would be no dimension / level called [Brand Family] But as I could see, there really was a dimension called [Brand Family] and the default member was not misspelt also. That is when it struck me to have a look at the dimension usage tab. Dimension Usage tab As you can see from the above screenshot, the Brand Family dimension was set up as role playing dimensions, and later I understood from my colleague that the requirement was to setup a common default member in all those 5 role playing dimensions. Now it made sense why the error was coming, because the [Brand Family] dimension is not existent in the cube. Instead, it is being referenced by the role playing dimensions [Main Brand], [Dualist TMC Main Brand], [Other Brand Family]. [Awareness Brand] and [Seg ClassF Brand]. So I suggested to set the default member in the format of <level>.value instead of <dimension>.<level>.value. Since the level names are unique across dimensions, I presumed it should get resolved properly. Setting the edited Default member As expected, with this workaround, the cube processed successfully and we were able to get the same default member set correctly in all the role playing dimensions. After this, I also chanced to view a line from Teo Lachev’s book – Applied Microsoft Analysis Services 2005 “You cannot set the DefaultMember property of a role playing dimension in the Dimension designer because it is not clear which specific role version it is intended for. Instead, the only way to set up the default member is to do it programmatically by using the ALTER CUBE statement.” Syntax of Alter Cube is ALTER CUBE CurrentCube | YourCubeName UPDATE DIMENSION <dimension name>, DEFAULT_MEMBER='<default member>’; Eg: ALTER CUBE CURRENTCUBE UPDATE DIMENSION [DATE].[CALENDAR DATE], DEFAULT_MEMBER = [DATE].[CALENDAR DATE].&[729] The default member would be set up at run time if the ALTER CUBE statement is used and hence, the dimension designer would not show the value of the default member. Even though I haven’t tried, I think it should be possible to set different default members to each role playing dimension through the ALTER CUBE script. This would not have been possible with the particular workaround that I suggested in the beginning. Update I found an old link in the forums in which Deepak Puri (MVP) confirms that it is possible to set different default members for each role playing dimensions using the ALTER CUBE statement. http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/thread/74feffe2-4706-4a5e-9e56-97d1fcf3f6ba/

Posted by SQLJason, 0 comments
Blog Review

Blog Review

It’s been almost a month of steady posting and I thought it would be good to get some feedback on my blogs. So, last day I got my blog reviewed by the team at BeyondRelational and here is the review. So taking their advice, there is some good news and some bad news that I would like to convey to you. The bad news is that I am going to split up my posts between the 2 blogs, which means you would not find all my blogs here. The good news is that I will be posting links / updates here whenever I post something unique at my blog in beyondrelational, so that you guys don’t miss anything 🙂 By the way, I just blogged on Improving cell security performance in SSAS, feel free to have a look and comment. I would love to hear your views on the topic. Title

Posted by SQLJason, 0 comments
Creating solution file from a SSAS database

Creating solution file from a SSAS database

In many of my projects, I have heard my developers asking each other whether it is possible to get the solution file of a SSAS database and I have turned a deaf ear to almost all the cases in spite of knowing the answer. Now before you give me that flabbergasted look, let me try to justify my position. As an IT company, we do have certain processes and one of them is to keep your code in VSS (used for version management). Unless you follow this rule, there is every chance that you might miss upon a particular Change Request / functionality and end up implementing a new fix on an outdated version. And it is not common to see people doing their development work on the online version of SSAS (which is the SSAS database) because it is much easier. You just have to save your work to see the changes while if you need to do the same changes in the offline version (which is the solution file), you will need to save it and then deploy to see the changes (talking about changes like modifying the calculated members script. For most other changes, the cube has to be processed for the changes to be reflected). So I would rather not let them know the answer so that they are extra careful in the future and avoid making changes in the online version, which may just be executed with the help of pdf to word converter.
But sometimes, in support scenarios, it is necessary to know this technique. Let’s say, you have been supporting an application which has been live from the past 5 years and now a change request has come in. Now let us face the ground reality, the application has changed hands so many times that you don’t know how many vendors have been involved nor do you know where the latest source code is. At this time, the best option is to retrieve the source file from the online version and this post will show you exactly how to do that.
1) Go to Start–>Programs–>Microsoft Visual Studio 2005 and click on Microsoft Visual Studio 2005 which will launch the IDE (If your SSAS database is 2008 version, use Microsoft Visual Studio 2008 instead)
2) Click on File–>New Project
Select the Import Analysis Services option and after giving the required Name, Location and Solution Name, click OK.
3) A Wizard message will pop up prompting to continue by clicking Next. Click Next and the fill in the server name as well as the DB which you need to import. Click on Next after that
4) The Wizard will start importing the source files from the database and on completion, the Finish button will be activated. Click on Finish.
5) Presto! You have the source files right in front of you.
Disclaimer: Use with caution, Tech Lead advice recommended. I don’t want companies coming after my neck just because my blog meddled with their processes 🙂
Posted by SQLJason, 3 comments
Report selection in parameter toolbar

Report selection in parameter toolbar

The MSDN forum has been the inspiration for most of my blogs, but in all those cases, I was sure that I could attain the end result and my blogs were usually a graphical step by step depiction of what needs to be done, so that I could post it as an answer to the question. But this blog is quite different, I almost wrote a no saying that it is not possible. And that was when I decided to give it a try before replying No 🙂 To give a background of the problem, the user wants to display the list of reports in a dropdown list and on pressing the view report button, the selected report must be displayed. On reading this question, I started typing – no, it is not supported by design in SSRS and suddenly I thought whether I am doing any value-addition to the post by typing this answer, I might as well as not write it and wait for some expert to confirm it. So I just thought of trying it while some expert gives his answer, and by chance, I stumbled upon a workaround. To implement this, follow the steps below:- 1) Create a report parameter (say RP) and in the report parameter properties, specify the default values. The labels should contain the report names and in the values, use integers as shown in the figure below RP Properties 2) Create a matrix in the layout and for each report add a row. In this example there are 3 reports hence add 3 rows. 3) For each of the row, drag and drop a subreport  from the toolbox into the data cell. Then right click and select the subreport properties. Subreport properties In the General tab, select the required report from the dropdown list which contains the list of reports (as shown in the figure above). Press OK. 4) Once you have modified the properties of all the subreports, select the entire row and right click on it. Row visibility Select Row Visibility option. 5) Select the Show or hide based on an expression radio button Setting row visibility expression For the first row, enter the following expression:- =iif(Parameters!RP.Value="1",False,True) This expression will ensure that the first row will only display if the parameter value is 1, else the row will be hidden. 6) Repeat steps 4 and 5 for each row. Substitute the number 1 by the appropriate value in the expression specified in the 5th step. 7) In the matrix column header, use the below expression to display the name of the report =Parameters!RP.label 8) Save and deploy the reports. Now you can preview the report in internet explorer, and when you select the report name from the parameter dropdown and click on view report button, the appropriate report will be displayed. order count by product Image visibility Rich text This technique can be extended to pass other report parameters also by passing the required parameters to the subreports. Don’t you think I deserve a pat on the back for this? 😉

Posted by SQLJason, 4 comments
SSRS reports using embedded images

SSRS reports using embedded images

Ok, this post is dedicated to all those zillion questioners asking how to use embedded images on your report based on a condition. Yet again, I will be making use of my favourite dataset from AdventureWorks – Order Count by Product (people who are following me would understand that this is the same dataset that has been featuring in many of my blogs. I admit it guys, I am too lazy to make a new dataset. On second thoughts, this doesn’t look  that bad, considering I have created a re-usable component in the form of my dataset. Woah, high fives!) For making this report, I am going to make use of three images mainly:-

a) Home                  b) Thumbs Up        c) Thumbs Down

Home                 thumbsup               thumbs_down

The home image would just be used in the top left of the report, which could be used for hyper linking to the home page. The other 2 images would be used in a matrix, and would be displayed based on a condition. (P.S. : This report is made in SSRS 2008, although the steps are quite similar in SSRS 2005 also) To make the report, follow the steps below:-

1) On the Report Data panel, go to Images and right click on it. Click on Add Image and select the images you need to embed by giving the source path.

Add Image

2) Once you have uploaded the 3 images, drag and drop Home image from the report data to the body of the report and press ok on the popup that appears (This is one method to bring embedded images in your layout)

3) Make a matrix underneath the Home image like shown in the figure below

Tablix

4) Now the matrix will be having Products on it’s rows and order counts on it’s columns. Now let us add the Thumbs up image for the row which has the highest value of Order Count and the Thumb down image for the row which has the least value for Order Count. For this, go to the toolbox and drag and drop the Image tool to the vacant column in the right end of the matrix (This is the second method to bring embedded images in your layout). You would be getting the Image Properties popup on doing this. Click on the expression symbol in the image name.

Image Properties

5) Once you get the Expression editor open, enter the following expression:-

=iif(Fields!Order_Count.Value=max(Fields!Order_Count.Value,”DataSet1″),”thumbsup”,

iif(Fields!Order_Count.Value=min(Fields!Order_Count.Value, “DataSet1″),”thumbs_down”,””))

where thumbsup and thumbs_down are the names of the images embedded in the report. Notice that they are enclosed in double quotes. Also notice that if none of the conditions are true, empty string would be displayed.

6) Save the report and deploy it. Now you should be able to preview the report in internet explorer.

Image Visibility report

This is all it takes to embed images in your reports and use them. You can use them for a wide variety of purposes, though I usually use them for showing indicators or for using them as hyperlinks. However, if you want to store images in your database and use them in your reports, you might want to check out this post.

Posted by SQLJason, 24 comments
Adding breadcrumbs to your drillthrough reports

Adding breadcrumbs to your drillthrough reports

In one of my projects, I was asked to implement a drillthrough report, which had a depth of around 6 pages. After the development, I was sitting with the user who was testing the reports and I noticed that the user was frequently shifting between the sixth and the third report to change the values. As most of us, she was also just using the back button of the internet explorer 3 times to reach the third report from the sixth report. That was when I quizzed her whether this was going to be a common requirement and suggested the idea of using breadcrumbs as a navigation aid. Though I can’t show you the original report, I will illustrate the idea here by making some sample reports. Here, I have created three chart reports using the AdventureWorks database to simulate the drillthrough effect – a) Order by Category Design Mode - Order by Category b) Order by SubCategory Design Mode - Order by SubCategory c) Order by Product Design Mode - Order by Product The Order by Category will be used as the starting point and on clicking a particular category in the chart, it will drill down to the subcategories of that category. Similarly, on clicking a particular subcategory, the chart will drill down to the products of that subcategory. Now, follow the steps detailed below to add the breadcrumbs:- 1) Now in the first report, add a textbox with the text – Order by Category as shown below Text in Order by Category report 2) In the second report, copy the same textbox and just to the right, make another textbox with the text – Order by Subcategory and do the formatting as shown below Text in Order by SubCategory report 3) Right click on the first textbox, select the textbox properties and click on the action property. Now click on the Go to URL radio button and enter the following expression =”javascript:history.go(-1)” Setting the action property for breadcrumbs This particular javascript will retrieve one page back in history. 4) Similarly, in the third report, add the textboxes in the format shown below. Text in Order by Product report 5) Edit the action property of the first textbox (as shown in step 3) and enter the following expression =”javascript:history.go(-2)” For the second textbox, enter the following expression =”javascript:history.go(-1)” 6) Save and deploy all the three reports and view them in internet explorer. Order by Category Order by SubCategory Order by Product As you can see from the images below, the breadcrumbs would be displayed. The active links would be displayed in blue while the inactive (the present page) would be displayed in black. You can click on the breadcrumbs directly to navigate between pages. This makes the drillthrough reports more user friendly and professional. The user for whom I designed this was extremely happy with the end result and gave me a BIG hug for making her life easier. As for me, I have included this trick in my list of best practices when designing drillthrough reports, who knows whether I would get another hug some other day by implementing this again 😉 Title

Posted by SQLJason, 1 comment