Uncategorized

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

And here I come!

I have been trying to force myself to the notion of writing blogs for a long time, but then it never happened. I always found myself to be too lazy to pick up a subject, sit down and then write my experiences about it. Also, never seemed to find enough time for that. But things have started changing for the better now and I think I would be able to do justice to my blogs now.
Mainly, my blogs would be concentrating on SQL Server Analysis Services and SQL Server Reporting Services. But then, you never know what all you would end up writing in the future ;). So here goes the first one…
Posted by SQLJason, 0 comments