Why doesn’t my gauge show correct values?

Here is an issue I encountered recently while using gauge in reporting services.
There are three gauges in the report one for each product; bikes, computers, and hardware. The value being showed is YTD sales and all gauges show a value of 100 for YTD Sales as seen below:

However the actual YTD sales are 100, 150 and 200 for bikes, computers, and hardware respectively. See the sample data below:

Okay, why is this happening? I am glad you asked!

Minimum and Maximum values for chart:

Starting and ending values (0,100) on the gauge scale are controlled by minimum and maximum values that can be configured on the general page of scale properties. Right click on scale and select scale properties.

Since maximum value is set to 100, scale doesn’t show beyond 100. And when the actual value (YTD sales in this case) exceeds the maximum value the pointer will be defaulted to point to the maximum value.

To resolve this problem, change the maximum value to the following expression.

=sum(Fields!YTDSales.Value)

~Sam.

Other Related Post: SSRS Error – An item with the same key

Advertisements

Full Load. Incremental Load. Whaaat?

DinerSpecials4Back when i was in school, me and a bunch of friends used to hangout at a diner. The diner was popular for its specials, which were displayed on a board outside the entrance.

The manager would roll in every day, wipe the board clean and list all the specials for that day. There will be a few new specials introduced daily, but most of them would be the same every day. Still the manager would erase everything completely (including those that weren’t changed) and rewrite. He was fine with having to spend a lot of time every day to do this, but he wanted to clearly display all the specials.

On the other hand, a manager at a different diner would erase only those specials that were discontinued and add the new ones. He didn’t mind that the specials were not clearly displayed, and only wanted to make the daily changes as quickly as possible.

To continue reading, please visit http://samuelvanga.com/2015/09/08/what-is-the-difference-between-full-load-and-incremental-load/. This is my new all improved blog. Everything just got so much better!

CONCAT() string function–SQL Server 2012

As the name indicates, CONCAT() will concatenate two or more strings. This is one of the two string functions introduced in SQL Server 2012, FORMAT() being the other one.

New? Wait. Concatenating isn’t new. It’s always been there. Yeah. you are right. No, concatenating isn’t new.Yes, CONCAT() function is new.

“+” is used to concatenate strings, prior to 2012. As you can see in the query below, two strings are being concatenated.

SELECT 'HELLO'+SPACE(1)+'WORLD'

It gets tricky when you deal with non varchar data, and null values.

SELECT 1+SPACE(1)+'World' ;

SELECT ‘John’+NULL+‘Doe’ ;

These queries don’t return expected results. To fix that, select query should contain logic to replace null and convert int to string. On the left side of the below image is the corrected query.

image

CONCAT(), as can be seen in the right side, simplifies this process. It concatenates 2 or more strings, and automatically (implicitly) converts null to blank space and integer to string.

One more example to better explain the use of CONCAT().

SELECT CONCAT(1,1) ConcatInt
, CONCAT('John',NULL,'Doe') ConcatNull
, CONCAT(1,SPACE(1),'John') ConcatIntString

Lets wrap up…

Concat() is clearly a simpler way to concatenate strings. It improves code readability and is one of my favorite T-SQL enhancements.

~Sam.

Why did I start blogging?

This post is in response to #meme15 started by Jason Strate (b|t). Two questions were asked in the first assignment. Why did you start blogging, and why do you currently blog?

I recently started writing. My blog is still a baby. I started because I…

  • Want to be a part of the community. I read other people’s blogs. I seek help on forums. I watch those informative free webcasts. I’ve been and still am a consumer. So, why not contribute and help someone.
  • Learn while writing.I strongly believe the best way to learn is to teach. When you teach/write, you can’t just write what you think is correct. Why? Once you hit that publish button, you are opening yourself to criticism. And if what you’ve written is nothing but crap, my friend, you are done. So, to stay in the game you will read, research, and in the process will learn a lot of new things.
  • Enjoy writing. I’m not sure how I can explain this, but I just enjoy explaining, writing.

@SamuelVanga

T-SQL Tuesday #025 – Tips and Tricks

And the trick i share for this month’s T-SQL Tuesday is… Distraction free management studio.

If you’re an avid writer, you might be using Writeroom. It’s a full screen distraction free writing application, which gets your computer out-of-the-way so that you can focus on your work. Dark Room is a similar app for Windows.

Distraction free! Focus on your work! Isn’t that amazing?

So how about a similar app for management studio where you spend most of your time, writing queries? Wouldn’t it be nice to focus on queries alone and not worry about new email pop-ups or any other windows on your computer?

I did a quick search, and results, nada. But, better yet, this experience can be achieved by modifying a few options.

  • Add line numbers. From management studio, choose Tools, options. Expand Text Editor and expand Transact-SQL. In the general properties check Line numbers under Display and click ok.
  • Change background color. Again from options expand Environment and choose Fonts and Colors. Click ok.
  • View query window in full screen. Click view and choose full screen when writing queries. This will bring SSMS to the foreground and closes object explorer too. To exit from full screen again click view and deselect full screen.
  • Hide results window. Finally, press Ctrl+R to hide the results window. Press Ctrl+R again to display results window.

image

Give it a try, you’ll know what i am talking about!

@SamuelVanga

Create Drillthrough Reports

Use Actions!

Your user wants a summary report, containing sales for calendar year, for example. When the user clicks on the report, another detail report should be visible, containing sales for each month of the year, suppose. These type of reports are called drillthrough reports. How a drillthrough report is different from a drilldown report is for some other time.

I use reporting services 2008R2 and AdventureWorksDW2008R2 as the data source for this example.

Step1: Create detail report

I’ve created a report called Sales Detail. It consists of Year, Month, and Sales Amount in a table.

Step2: Add parameter to the detail report

The detail report must only show data for the year on which the user clicks on the summary report. For example, if user clicks on 2006, detail reports should show only data for 2005.

So add a parameter for Calendar Year in the detail report.
drillthrough report

Step3: Create summary report

I’ve created a summary report called Sales Summary. Below is a preview of the report. Notice the underlines for Year. They represent links and encourage users to click on them.
drillthrough report

Step4: Add Actions in the summary report

Add action for the Calendar Year textbox. Property configuration will look like the image below.
drillthrough report

Downloads:

Complete solution can be downloaded from my SkyDrive account.

Please use the email link on the right, if you have any comments or questions.

I’ll leave you with a link to books online for more information on drill through reports.

~Sam.