SSRS

Rich text in SSRS 2008

Rich text in SSRS 2008

April 12, 2010

One of the most irritating and common requirements that I faced as a developer in SSRS 2005 was when I had to colour words in the same line differently. Even though SSRS 2005 didn’t provide an inbuilt feature to do this, this could always be done by placing the words in different textboxes. Suppose I had a title as Category = Accessories and Order Count = 19523 where Accessories and 19523 are retrieved from the dataset, and if I just wanted them in a single colour, I could write the below expression in a textbox. =”Category = “+Fields!Category.Value+” and Order Count = “+Fields!Order_Count.Value The hassle was when the customer gave the requirement that the values should be in a different colour and in bold. Now for this, we need to break the expression into different parts such that only consecutive words of the same colour appear in one textbox. Hence, the above expression had to be broken down into 4 textboxes:- 1) Category = 2) Fields!Category.Value 3) and Order Count = 4) Fields!Order_Count.Value and then each textbox could be given it’s own property. simulating rich text in SSRS 2005 This would prove to be very annoying for the developers especially if the sentence was a very long one which had alternating colours in between. Preview of rich text in SSRS 2005 Also, unless you painstakingly spend hours in it, the spacing would not be 100% accurate. For eg, if you see in the above image, you can notice that spacing between the words is not uniform But with the advent of SSRS 2008, this issue is a matter of the past. Now you can directly type in your sentence and/or drag and drop the fields from the dataset or the report parameters. To change the properties of the required text, just highlight the part and then press F4 to view the property panel of the selected text. Rich text in SSRS 2008 In the example above, I have highlighted the [Category] text. Notice how the property panel displays Selected Text, and not the name of the textbox. Now you can change the properties as you like be it the colour, indentation, font, etc. Another thing to notice is that instead of displaying <<Expr>>, we can actually see the words and the Field names have a placeholder. Eg, for Fields!category.Value, we actually see [Category]. Also see below how neat the result looks compared to what we had in 2005. Preview of rich text in SSRS 2008 This can be extended to objects like tables and matrixes also. In SSRS 2005, if it was required to have a column partially bold or coloured, we had to make different textboxes and then enclose them within a rectangle. Then the rectangle could be used in the matrix. Table in Design mode - SSRS 2005  Table in preview mode - SSRS 2005 In SSRS 2008, you can just select the required text and edit as mentioned in the previous section. Matrix in Design Mode - SSRS 2008 Table in preview mode - SSRS 2008 It is small features like this which makes me an ardent fan of SSRS 2008.

Posted by SQLJason, 7 comments
Highlighting threshold values in a chart

Highlighting threshold values in a chart

April 10, 2010

One of my favourite activities is playing around with the charts in SSRS and trying to tweak their properties. Knowing my penchant for this, one of my colleagues asked my help in verifying whether it is possible to have charts where the columns will have a different colour based on a certain threshold value. From the moment I heard it, I knew it should be possible but then I didn’t want to end up in a “You-told-me, now-you-solve-it” situation at the end of implementation. And also, I didn’t want to end up in the bad books of a pretty lady (Oh, did I forget to mention that my colleague is a gorgeous woman? 😀 ). So I thought of giving a quick try to confirm. For implementing this functionality, I used the Adventure Works R2 analysis services database to create a column chart with Order Count measure on data and Category from product dimension on the categories part. Now, click on the column and select the dropdown in the Color property as shown below

Setting the color property

Now, click on the Expression and then give the conditions for which the threshold value should be highlighted, lets say the maximum value for Category should be Green while all others should be Maroon.

=iif(Sum(Fields!Order_Count.Value)=max(Fields!Order_Count.Value, “DataSet1″),”Green”,”Maroon”)

And now, when you preview it, the Category with the highest value would be highlighted in Green.

Chart highlighted with Green for max value

You can also modify your expressions to highlight both the max & min value, or just highlight all columns above or below a particular value.

=iif(Sum(Fields!Order_Count.Value)=max(Fields!Order_Count.Value, “DataSet1″),”Green”,iif(Sum(Fields!Order_Count.Value)=min(Fields!Order_Count.Value, “DataSet1″),”Red”,”Maroon”))

Chart with max and min values highlighted

=iif(Sum(Fields!Order_Count.Value)>10000,”Green”,”Maroon”)

Chart highlighted with green above 10000

 

Posted by SQLJason, 0 comments
Implementing measure security in SSAS 2008

Implementing measure security in SSAS 2008

April 7, 2010

Recently, I was asked to implement security on a set of sensitive measures, such as profit and all calculated measures on top of them as part of the cube development. It looked like a very straight requirement and I never thought I would have so many sleepless nights over it for the next 2 months. My very first way of thinking was using the default Microsoft cell security, where I would define a read-contingent policy for the sensitive measures. If you do not define an explicit read-contingent policy, the role will be able to read the derived measures of the sensitive Profit measure despite the fact that it does not have access to the Profit measure.

Img_Blog1

This led to 3 main problems for me:-

1) Reports which are running normal under the PowerUser role go berserk when running under the restricted RegularUser role. Empty rows started appearing in spite of having NON EMPTY in the MDX queries. Currently, I am following this issue with Microsoft and this is the latest reply I have got ” We have figured out the problem is caused by the cell security in the cube. When you set the “Enable read permissions” in the cell data, the non empty function doesn’t work for the special role. All the null records still display even you are using non empty. For the admin role, the non empty can filter out all the null records. For example, when you query the cube by the scripts below, you can get 42 records without null by PowUsers. If you are using the role RegUsers, you can get 132 with a lot of null. “

2) Performance went for a toss, and reports run under the RegularUser role started taking much longer time than when it was run under the PowerUser role. Later, I found that this could be mitigated with a technique given in this book (a definite must read for Expert cube designers, gives the inner details of SSAS like no other book has given) which I will try to put in another blog.

3) Even though the restricted measures were showing as #N/A in the cube under the RegularUsers, the reports started throwing an error when I just expected the #N/A again.

There was a requirement for a report which had the sensitive data along with some 5 other tables and all other data needed to be visible to the RegularUser. So time for a workaround. Luckily, I got guidance from Raymond Lee, MSDN forum moderator and came up with this approach.

1) I already had a dimension called Product. Added a dummy attribute to the key of Product dimension, and called it as RoleSecurity. The value of this attribute would be 1 and this attribute is hidden in the product dimension.

2) I needed to have security access on the measure Profit. I renamed that measure to Profit Original and made it hidden. Then, in the calculated members of the cube, I created the following calculated member

CREATE MEMBER CURRENTCUBE.[Measures].[Profit] AS IIF( ISERROR ([Product].[RoleSecurity].[1]), 0, [Measures].[Profit Original]);

3) I have 2 roles called RegularUsers and PowerUsers. PowerUsers have access to all measures, so no need to do anything there. For the RestrictedUsers role, select the Dimension Data tab and then use the “Deselect all members” for the RoleSecurity attribute. This implies that the users of this role will not have access to any of the values of this attribute.

Img_Blog2

Once this is deployed, if a user of RegularUser role tries to view the Profit measure, he will see 0 as the result, while the PowerUsers will see the actual value. In the reports, the 0 can be used to conditionally display a message like “Not authorized” if needed.

RegularUser                                                 PowerUser

Img_Blog3 Img_Blog4

The advantages of this technique is that

1) Since dimension security is used, it doesn’t affect performance like cell security does.

2) Reports will not throw up error even if the measure is missing.

3)Most importantly, the issue of null rows is solved

The drawbacks are

1) The Profit Original measure is not secured, jut hidden. If the user becomes aware of the measure and also knows how to use MDX well, he will be able to query it out.

These are the advantages and disadvantages I can think of. In any case, it solved my issue because my business users don’t know MDX and will not be using any tool to query. It would be great if I get to know your views on this technique. Here is the link for the forum post regarding this.

Posted by SQLJason, 5 comments
SSRS reports using database images

SSRS reports using database images

April 6, 2010
In one of my first projects, I was asked to store images in database and then use them for my SSRS 2005 reports. The newbie I was, for a moment I was shell-shocked how I could store images in a database, let alone use them in my SSRS reports. Fortunately for me, I mumbled to my client the most used phrase in the IT indutsry that I will check with the team and let him know, rather than showcasing my ignorance. A couple of searches and I had got all the matter that I required. This post will show how to create a sample employee report based on database images.
1) To start, create the employee table and populate the data into them. Sample code has been given below but remember to give the location of the photos.
CREATE TABLE EmployeeProfile( EmpId INT , EmpName VARCHAR (50) NOT NULL, DeptName VARCHAR (50) NOT NULL, EmpPhoto VARBINARY (MAX) NOT NULL);
GO
INSERT EmployeeProfile (EmpId, EmpName, DeptName, EmpPhoto)
SELECT 1001, ‘Prashant Kumar’, ‘DW’, BulkColumn
FROM OPENROWSET (BULK ‘C:Prashanth.jpg’, SINGLE_BLOB) AS EmployeePicture;
GO
INSERT EmployeeProfile (EmpId, EmpName, DeptName, EmpPhoto)
SELECT 1002, ‘Jitain Raheja’, ‘Manufacturing’, BulkColumn
FROM OPENROWSET (BULK ‘C:Jitain.jpg’, SINGLE_BLOB) AS EmployeePicture;
GO
INSERT EmployeeProfile (EmpId, EmpName, DeptName, EmpPhoto)
SELECT 1003, ‘Sandeep Shetty’, ‘Manufacturing’, BulkColumnFROM OPENROWSET (BULK ‘C:Sandy.jpg’, SINGLE_BLOB) AS EmployeePicture;
GO
INSERT EmployeeProfile (EmpId, EmpName, DeptName, EmpPhoto)
SELECT 1004, ‘Rahul Nair’, ‘DW’, BulkColumn
FROM OPENROWSET (BULK ‘C:Rahul.jpg’, SINGLE_BLOB) AS EmployeePicture;
GO
INSERT EmployeeProfile (EmpId, EmpName, DeptName, EmpPhoto)
SELECT 1005, ‘Hari Selvarajan’, ‘Manufacturing’, BulkColumn
FROM OPENROWSET (BULK ‘C:Hari.jpg’, SINGLE_BLOB) AS EmployeePicture;
2) Create a report with 2 datasets and one report parameter
a) DS_RP Query : SELECT DISTINCT DeptName FROM EmployeeProfile
b) Image Query : SELECT DeptName,EmpId, EmpName, EmpPhoto FROM EmployeeProfile WHERE (DeptName = @RP1)
3) Make sure that the properties of report parameter RP1 is set as follows
4) Go to the layout and then drag and drop a table from the toolbox. In the detail row of the first column, drag and drop an image. Then follow the steps of the image wizard.

Once the database option is selected, click on next and select the datasource, image type and image field from it.

Click on finish.
5) On the other 2 columns of the table, use the Emp Name and the Emp ID.
6) Click on preview. With a bit of formatting, you should be able to attain better results.

On selecting DW

On selecting Manufacturing

 
Posted by SQLJason, 4 comments
Dashboards in SSRS 2008

Dashboards in SSRS 2008

April 5, 2010

To be frank, I was extremely happy when I got to work on SSRS 2008. The 2008 version has changed so much for the better when you compare to the 2005 version, be it on terms of aesthetics or developer friendliness, you just name it. Moreover, I am the sort of person who hates to say NO to the customer when it comes to requirements, so I always make sure that I try to utilize all workarounds before I utter the dreaded No word. So when my customer asked whether I would be able to create a dashboard for them, I started scratching up my head and in the end, managed to develop the following (included a trial version of Dundas Map also)
Update (31/05/2013)
Check out this post – A Sample SSRS Dashboard and Some Tips & Tricks for a better example of SSRS Dashboard
Now talking of dashboard, recently there was a post in the forums asking whether it was possible to create a heatmap sort of control which displays data in 2 dimensions – one dimension is the color which will keep on changing based on one measure and the other was the size which again increases or decreases based on a second measure. Even though it looked impossible in the beginning, I suggested a workaround – utilize the N character in Wingdings which will give you a square box.
To develop this sort of control, follow the steps below:-
1) Open the report and define your datasets with atleast 2 measures.
2) Create a tablix and along with your normal rows and columns, add a new column for the heatmap control
3) In that column, type the letter N and change the font to Wingdings. Now the tablix column should display a square.
4) Change the font color by adding the expression you like. As for myself, I used the code from Dave’s blog (refer this, a wonderful and handy blog on conditional formatting)
5) Change the font size by adding the expression you like.
6) Now when you preview the report, you should be able to see something like below
In my example, the color changes based on the Growth% measure and the size changes based on the Avg Sell In Vol (Last 3 months) measure. This method can be further improvised to get some very nice dashboards.
Posted by SQLJason, 1 comment
A better way of showing current year sales vs previous year

A better way of showing current year sales vs previous year


Many a times, I have found that all the hard work that I had done behind to optimize and performance tune the reports and OLAP had gone unnoticed when it actually came to showing them to the customer. As a techie, I was super excited to see that I had tuned the report from over 36 secs to less than 3 secs and had hoped for a bigger plaudit from the customer but it all came down to one fine remark – the aesthetics don’t look that great and the chart looks super compressed. This is how important aesthetics can be to your reports, if you don’t do your groundwork properly there, you might not get the due credits. And SSRS 2008 really does provide a lot of options to improve the aesthetics of your report.
In one of my projects, I was asked to create a weekly chart report that would display the current year sales vs previous year sales, along with a line chart for Target. I created the rough design and found out that the chart would be too big if we go the usual column chart way, because it would need 53*2 columns and I wanted to fit the chart in one page (else it becomes too messy for the users to use the scrollbars, I avoid them in my designs if I can)
This could be tweaked with a help of a simple trick.
1) Create the normal column chart with the 2 measures (Sales and Sales (Y-1))
2) Click on the first column, and press F4 to open the properties panel
3) Expand the Custom Attributes property, and change the PointWidth to 0.8
4) For the other column, change the PointWidth to 0.35
5) Right click on the chart and select the Chart Area properties
6) Check the Enable 3D option and set the following properties as shown in the fig below
7) Voila, you will get the new column chart as shown below
Posted by SQLJason, 1 comment
Converting multiple rows into a single comma separated row

Converting multiple rows into a single comma separated row

April 4, 2010
Recently, I have been bitten by the MSDN forums bug. I enjoy spending time in the SSAS and SSRS forums, and the learning that I take out from there is tremendous. Initially, I just used to read and learn, while now I reply to a lot of posts too (just the simple ones, keep the tough ones for the experts to solve 😉 ).
So the other day, there was this post – how can you convert multiple rows into a single comma separated row? For eg,
This can be done by the FOR XML command.
select DISTINCT State, (
select City + ‘,’ as [text()]
from tableA soi
where soi.State=t.State
order by City
for xml path( ” )
)
from tableA t
A handy command, especially because you don’t have to use stored procedures to achieve the result.
UPDATE – 5/26/2016
I see that this post still gets a lot of hits, so just wanted to share the version that I use currently. The previous version works really slow with big tables, but this one works much faster.
SELECT OutTab.state ,
Cities =
STUFF ( ( SELECT ‘,’+InrTab.City
FROM tableA InrTab
WHERE InrTab.state= OutTab.state
ORDER BY InrTab.City
FOR XML PATH(”),TYPE
).value(‘.’,’VARCHAR(MAX)’)
, 1,1,SPACE(0))
FROM tableA OutTab
GROUP BY OutTab.State
Posted by SQLJason, 6 comments