RSS

Category Archives: SSRS

The Story of Precision, Scale, and Length in SQL Server

Hi guys,

Did you ever faced issues with BIG mathematical figures – related to either overflow or any  other arithmetic errors. This may be applicable to SQL arithmetic operations or arithmetic  operations you do with SQL table in SSIS etc.

Well, check 1 more time, if you have any issues in your database design related to column  data type which stores the BIG numbers.

I ran into this issue couple of times, and reason behind this issue was same all the time. So  decided to do a quick post. And that reason is “Improper or insufficient precision, scale and length defined for storing Decimals and Numeric values in table“.

I wanted to describe this with an example but there is already a very nice explanation by MSDN  on this topic. Check out: http://msdn.microsoft.com/en-us/library/ms190476.aspx

You will notice in this article that, how any arithmetic operation will affect size and space  allocation of the resultant value during mathematical operation. You should use following shown table for defining and designing tables in such a way that can  handle BIG mathematical figures during operations.

From MSDN, for your quick view:

Operation

Result precision

Result scale *

e1 + e2 max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2)
e1 – e2 max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2)
e1 * e2 p1 + p2 + 1 s1 + s2
e1 / e2 p1 – s1 + s2 + max(6, s1 + p2 + 1) max(6, s1 + p2 + 1)
e1 { UNION | EXCEPT | INTERSECT } e2 max(s1, s2) + max(p1-s1, p2-s2) max(s1, s2)
e1 % e2 min(p1-s1, p2 -s2) + max( s1,s2 ) max(s1, s2)

* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

Important Note: Don’t forget to check for your SQL Server Version.

I hope you will find this informative.
Thanks, Khilit

 

Tags: , , , , ,

MSSQL: Find total records of each Table in given Database

hi guys,
i wanted to do a quick post on how you can find total number of records in each table of the database.

here is the script you can use:

USE <Your Database Name>
GO

;WITH CTE_DATABASETABLESIZE (DATABASENAME, SCHEMANAME, TABLENAME, TOTALROWS)
AS
(
SELECT
 DB_NAME(), SCHEMA_NAME(SO.UID), SO.NAME, SI.ROWS
FROM
 SYSOBJECTS SO
 INNER JOIN SYSINDEXES SI
 ON SO.ID = SI.ID
WHERE
 TYPE = ‘U’ AND SI.INDID IN (0,1)
)

SELECT * FROM CTE_DATABASETABLESIZE
/*ORDER BY SCHEMANAME
    ORDER BY TOTALROWS ASC*/
ORDER BY TOTALROWS DESC

Note: Customize this query according to your needs :)

Thanks, Khilit

 

Tags: ,

User ‘Domain\User’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.

Hi guys,
its seems like this issue have been faced by many prople. i have checked the solutions on web and found that not all answers contains the complete solution. so decided to put 1 complete solution here.

in most of the places, you will find answers like… Run IE as Administrator -> Open Report Manager site -> Add your user in “System Role Assignment” (settings) etc.

But there is tini-tiny step you have to do before you run your IE as Administrator.

  • Open your IE -> Tool -> Internet Options.
  • Security tab -> Local intranet -> Sites -> Advanced -> Add your root level site here.
  • Advanced tab -> Make sure you have checked (selected) the item “Enable Integrated Windows Authentication*” under Security category.

thats it.

now are you ready to follow … Run IE as Administrator etc.
Here is the what you need to do now: http://www.soheib.com/technical-knowledge/sql-server-2012-reporting-services-uac-user-access-control/

i hope this will help you somewhere.
thanks, khilit

 

Tags: , , , , ,

E-Book Gallery for Microsoft Technologies

Hi guys,
good morning !!!

here is the list of cool FREE E-Books from Microsoft Press.
Read the rest of this entry »

 

Tags: , , , , , , , , , , , , ,

SSRS Chart – Displaying % sign on Y axis + Displaying % sign on Data Labels

Hi guys,
Today i would like to show you the little trick for your SSRS graphs.

Issue:
I am displaying values (percentage values) in my bar charts as “Data Labels”. But the “Y” axis of my bar char still displays value without the “%” sign.

What I need:
The bar chart should display the “%” value in Data Labels as well as on my Y axis.

Simple try:
When you format the Data Label to apply “%” sign by making it of type “Percentage” in “Series Label Properties” -> “Number” -> “Percentage”. It actually multiply the value by 100 and put a % sign at the end. Woh … but the value i am displaying on my Data Labels are already percentage. I just need to display the % sign at the end.

Solution:
Let’s trick the SSRS to display that % sign on you Data Labels as well as on your Y axis.

Read the rest of this entry »

 

Tags: , , , , ,

SSRS Report is asking for Username and Password while executing

Hi all,
I want to bring to your focus a very minor detail which could eat-up your time when it happens.

This is the case when your SSRS report is deployed on ReportServer and you are accessing thru any application or interface e.g. SharePoint. Your SSRS report is using Integrated Security and hence no need to provide Username and Password if you have access to the Report. And everything works fine up to this point.

After some days, if you notice that your SSRS Report is not working as expected and it has started asking for Username and Password while executing it. What could be the reason?

Read the rest of this entry »

 

Tags: , , , , , , ,

Quick-Fix: Report parameter with space in SSRS

Hi guys,
One of the most common practices and a wise recommendation is to display selected report parameter(s) in report … may be in header or body of SSRS report.

It’s a good reference when you export the SSRS report into available formats (Excel, PDF, Word etc.) Because if you don’t include/show selected parameter(s) in exported report then it becomes less-meaningful for a person who is reading/using the report … probably for some decision making.

Read the rest of this entry »

 

Tags: , , ,

Spot/Highlight Minimum and Maximum value(s) in each row in Matrix report in SSRS :)

Hi all,
How are you doing?

Today I would show you; how you can highlight minimum and maximum values in an each row of matrix report in SSRS.

Again, “there are lots of ways“, but that depends on what is your solution framework. If you are using standard T-SQL queries for reporting, then it’s easier to write this min-max logic in query itself.

But if you are using SSAS cube then you have to write custom MDX (i.e. calculated column/”Member” in Cube’s term) and do comparison in SSRS report in each row.

I can show you even simpler way where you don’t have to write MDX to achieve this functionality and implementation will be easier, faster & fun :)

Ok, let’s begin step by step.

Read the rest of this entry »

 

Tags: , , ,

Line chart + Bar chart in SSRS Report

Hi All,

Language is the key for communication“. Having said that “Language” could be anything with a clear meaning of message/information to be delivered. Over the generations, we have one language which is never changed for communication among humans i.e. audio-visual communication.

Developing charts for an organization for getting to know “how it’s team or organization is doing?” has been always a key requirement for managers or top-heads in organizations. But for developer it’s merely a representation of values in pretty charts. But every developer has to bear in mind that it’s always bonded with business meanings.

And moreover, the representation could give different perspectives to different people. So, setting-up proper colors, combining with different types of charts (bar-chart & line-chart) may be at-times important for people in organizations to make decisions.

Today I am going to show you, how easily you can integrate a line-chart into a bar-chart & make your SSRS report more elegant and insightful :)

Note: For this demo I am going to use SQL Server Reporting Services 2008 R2.

Read the rest of this entry »

 
4 Comments

Posted by on March 12, 2012 in SQL Server, SSRS, Tutorial

 

Tags: , , ,

Dazzle your SSRS Report with SparkLines

Hi All,

SparkLines” is a new feature introduced in SQL Server Reporting Services 2008 R2.

Fast Facts about SparkLines:
•    It is the simplest way to make your data/values intelligent by showing them in visual way.
•    Because it’s a tiny-chart representation, makes report elegant, meaningful and non-messy.
•    Report users can choose to dig in to values if they find SparkLines chart more appealing. (E.g. By seeing SparkLine chart, you can quickly determine  the fluctuation of values for the listed items rather than reading & analyzing values)
•    It’s more suitable to use SparkLine rather than big charts if we need to analyze more items & their values for a time frame. Because SparkLine chart  is smaller and smarter version of normal charts :)

Let me show you how you can quickly integrate SparkLines in your SSRS report.

Read the rest of this entry »

 
2 Comments

Posted by on March 8, 2012 in SparkLines, SQL Server, SSRS

 

Tags: , ,