Paging Issue in SSRS Report adds a question mark instead of display the total number of pages

Paging Issue in SSRS Report adds a question mark instead of  displaying the total number of pages

While working with SSRS reports,We had one report where we have limited the no. of row to 5 .

but the report was showing question mark  like in below screenshot.

if you will see the screenshot  there is a question mark after 2 .we had a total of 2 pages only in the report 

but instead of displaying the last page it was showing a question mark and we cannot show question mark as it was client request.

So i researched on this then i came to know that in 

In SSRS 2005, if a report had 100 pages of records all  pages were rendered as a whole.

In 2008,if a report had 100 pages  only a page of data is rendered.

And the Reason was like rendering 100 pages as a whole will have more  load on client machine so in order to avoid this a single page is rendered .

but as it was client request so we have provided a solution for this.

So here is the solution just add a textbox in your header and footer of the Report.

Write expression- =Globals!TotalPages

Hide the text box.by changing hidden property of Text box to true.

Preview the Report it will have no question mark as in above screenshot..

Saving Change is not Permitted Error while Saving Changes in a table in SQL

Saving Change is not Permitted Error while Saving Changes in a table in SQL

One of the developer in our company was trying to save the changes in a table he was changing the name of a column through Graphic Interface 

Right click table -Design -Changing column name 

But when he tried to save the changes he got the error Saving Change is not Permitted.

He again tried  the same but failed again with same error as in below screenshot..

So,Below is the solution

In order to Save the changes you just have to do the below steps

Go to Menu bar of SSMS-Tools-Options-Designers

-Prevent saving Changes that require table re-creation -Uncheck this box

-ok

Now try to save changes in table it will save Succesfully.

Reseting Identity value back to previous value or 1 after Delete command in SQL

Reseting Identity value to previous value or 1 after Delete command in SQL

In order to explain this first you should know like if  a table having Identity value column assuming that the number of records are 5 and you deleted 5th record and inserted a new record then the next value in Identity key column will be 6 not 5.It will be 1,2,3,4,6

secondly if you will delete all data from the table then when you will insert a new record in the table, it will starts from 7 whereas if you use truncate command to delete all data then insert a new record then it will starts from 1 this is one of the big difference between truncate and Delete commands
.

Now i will explain how to reset identity value back to previous value or 1.

--Creating table with identity column

CREATE TABLE [dbo].[Tableidentity](
[ID] [int] IDENTITY(1,1) NOT NULL,
[value] [nchar](10) NULL
) ON [PRIMARY]
GO

--Inserting some values

INSERT INTO [Tableidentity]

VALUES ('value1'),('value2'),('value3')

GO

select * from tableidentity

ID value

1 value1    

2 value2    

3      value3

Now we will delete a record from table

delete from tableidentity where id in (2,3)

 insert value 2 once again

INSERT INTO [Tableidentity]

VALUES ('value2')

GO

select * from tableidentity

ID value

1 value1    

4 value2   

you will notice that now Id value of new record is 4 as, 2 and 3 id value has been deleted but you want that it should be 2 i.e.  same as previous value so remember as soon as you will delete a record reseed the value to its previous value.

delete from tableidentity where id =4

after deleting reseeding it back to previos values

-- identity column reset to 1

DBCC CHECKIDENT ('[Tableidentity]', RESEED, 1)

Now insert a new record 

INSERT INTO [Tableidentity]

VALUES ('value2')

GO

ID value

1 value1    

2 value2  

It will insert as required order.

Note: when you are deleteting all records of table using delete command then new value inserted will be maximum Identity value of deleted value+1

for eg we will delete all record and then will insert a new record.

--deleting all records of table

delete  from [Tableidentity]

Now insert a new record 

INSERT INTO [Tableidentity]

VALUES ('value1'),('value2')

ID value

3 value1   

4      value2

The values are inserting from 3 as i mentioned above the maximum identity value when deleted was 2 so new records started from 3

So it's a best practise to use truncate when deleting all records so that you may not have to reseed it to 1.

--truncate command

truncate table tableidentity.

--Reseed to 1 without truncate i.e. after running delete all record command setting identity value back to 1

DBCC CHECKIDENT ('[Tableidentity]', RESEED, 1)

Granting a user Select only Permission or other permissions in SQL

Granting a user Select only Permission or other permissions in SQL

In many cases for the security reasons of Database you want that developer or user should only have select permission to database they cannot perform insert,Update,Delete in the Database in order to give only select permission to user below are the steps and script.

--Create a New Login 

--In our case we created a login Name as TestLogin 

CREATE LOGIN TestLogin

    WITH PASSWORD = 'test#login123';

If you will have password that doesnt match with windows policy or very small, then it will throw an error like

Msg 15116, Level 16, State 1, Line 1

Password validation failed. The password does not meet Windows policy requirements because it is too short.

--Now run below script to create a user for that login

USE Testingnew  ;--only this DB will have select permission

CREATE USER usertest FOR login TestLogin ;

grant select  to usertest

Now connect to user ssms login with TestLogin password-test#login123'

only TestingDB will have select permission try to open other db it will give an error

The database 'XYZ' is not accessible. (ObjectExplorer)

Now run a insert query for the DB (testing new) for which you have created your user.

insert into table1 (col1) values('2')

Msg 229, Level 14, State 5, Line 1

The INSERT permission was denied on the object 'table1', database 'Testingnew', schema 'dbo'.

Try to run a select query 

select * from table1 

It will work without any issue.

If you want to provide other permission like insert update delete to your user then use below script

USE Testingnew

grant  insert, update, delete to usertest

Note:If you will run above code with same login i.e TestLogin it will not works Displaying a message like

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

To solve this connect with your main login i.e sa or the login you used to create your login first time then run 

USE Testingnew

grant  insert, update, delete to usertest










then run a insert query


insert into table1 (col1) values('2')


It will insert record into your table.

The Value expression for the text box ‘XYZ’ refers to the field ‘XYZ’. Report item expressions can only refer to fields within the current dataset scope Issue fixed in SSRS Report

The Value expression for the text box ‘XYZ’ refers to the field ‘XYZ’. Report item expressions can only refer to fields within the current dataset scope Issue fixed in SSRS Report

The Reason of this error is you have changed any column name in your query and report is referring to old column Name of the sql,to fix this one change your Report old column Name to New column Name.

In order to reproduce create any report preview the report, Now, change ur column name in your

query inside Dataset Try to preview the Report .

This will throw the above error.

Eg

What i did was i Changed EmpName column to EmployeeName.

For Eg

Suppose my dataset query is

Select empid,Empname from Employee

where Empname in (@EmpName)

I preview the Report it works fine with above query.

then

I opened the Dataset change the Query to  

Select empid,Empname as EmployeeName from Employee

where Employeename in (@EmpName)

I preview the report the Report was throwing above error.

In order to fix this issue .

In Design view of Report

Go to your table  click on 2 row below columnName (EmpName)  like in below Screenshot.

In my Screenshot it task change Depends according to your column Name of reports  Select new value of your columnName from Dropdown 

Preview the Report-The Issue will  be fixed..

Select All as default value for Multivalue parameter in SSRS

Select All as default value for Multivalue parameter in SSRS

In order to Set Select All as Default value for Multivalue Parameter in SSRS Report. The best way is 

like set default values of Parameter same as Available values and in Dataset of Parameter add a where condition where ColumnName is not Null .

This is the same column which u are using as Parameter in your value field.

In order to explain in a more better way .

We will create a Dataset and a Paratemeter.

--Parameter Dataset query

--DSTEmployee

select empid,empname from employee

Add Parameter

--Employee

Parameter Properties

--Allow Multiple Selection

--Available Values Tab

-From DSTEmployee

Label field-Empname

Value Field-EmpName

--Default Value Tab 

-From DSTEmployee

Value Field-EmpName

Ok

Now in order to add Select All as Default value add a where condition in your query where Empname is not Null like below

--DSTEmployee

select empid,empname from employee where EmpName is not null

Preview the Report-Report will have Parameter Default value as Select ALL EmployeeName.

Order By Months Name in SQL and SSRS Report

Order By Months Name in SQL and SSRS Report

I was working on Report where i have to display all month Names in order by of Months if  User selectes more than 12 months then it will display like screen below.

Next january month it is showing after december which means Next year.

So, I Handled this in my sql only in my query i ordered by month and year.

below is the demonstration.

--First we will create a table

Create TABLE [dbo].[EmployeeMonth]

(

[EmpID] [nvarchar](10) NULL,

[Name] [nvarchar](10) NULL,

[MonthName] [nvarchar](10) NULL,

[Year] nvarchar(10)

) 

GO

INSERT INTO [dbo].[EmployeeMonth]VALUES(1,'A','March','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(2,'B','May','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(3,'C','December','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(4,'D','November','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(5,'E','January','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(6,'F','June','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(7,'G','February','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(8,'H','April','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(9,'I','October','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(10,'J','August','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(11,'K','July','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(12,'L','September','2013')

INSERT INTO [dbo].[EmployeeMonth]VALUES(5,'E','January','2014')

INSERT INTO [dbo].[EmployeeMonth]VALUES(7,'G','February','2014')

GO

select * from [EmployeeMonth]

Output

EmpID Name MonthName Year

1 A March        2013

2 B May         2013

3 C December 2013

4 D November 2013

5 E January        2013

6 F June       2013

7 G February 2013

8 H April         2013

9 I October       2013

10 J August        2013

11 K July       2013

12 L September 2013

5 E January          2014

7 G February 2014

Now in order to get order by based on month and year to display in a report 

below is the query

select * from [EmployeeMonth]

order by YEAR,DATEPART(mm,CAST([MONTHName]+ ' 1900' AS DATETIME)) asc

Output

EmpID Name MonthName Year

5 E January         2013

7 G February 2013

1 A March         2013

8 H April         2013

2 B May         2013

6 F June         2013

11 K July         2013

10 J August         2013

12 L September 2013

9 I October         2013

4 D November 2013

3 C December 2013

5 E January         2014

7 G February 2014

Write the query in you report and you will achieve the Output as shown in the above screenshot.