Sql Server & SSRS Tips

For displaying page number and total pages on ssrs reports like (Page 1 of 10) you have to use the following command which you write in test box as an expression. command is ( = "Page " & Globals!PageNumber & " of " & Globals!TotalPages )  "Page "                         (for displaying Page)  &                                   (for concatenating text with formula) Globals!PageNumber   (formula for displaying current page number)  " of "                             (for displaying of)  Globals!TotalPages      (formula for displaying total page numbers)

Right click on table select Script table as then select drop and create to. a sql file with queries will open. copy all the contents of this file and the make new query file paste it in to te nw query file. Change name of data base from old (the one which you are coping) to new (the one in which you want to copy table) with find replace option. which is written in use[database_Name]. After changing the name press F5 or execute query. and then refresh database from object explore.Now you will se that table is copied to the new database. 

If you want that your ssrs report show drop down list for the values of parameters insted of entering a value by hand. then first create a procedure for that paramete. eg you have a parameter Project and you want to display all projects names in drop down list on report. then create procedure which retrieves you all projectsID's and projectNames like:   (select Projet.ProjectID, Projet.ProjectName from Projet) and add this procedure as a dataset in the report then return on report select parameter from Report Data tool bar go to its properties in general properties check the box " Allow multiple values ", now click on Avaliable values option from left pane.In this option check the radio button " get values from a query " Select dataset which you have creted for that paramenter(which retrieves you id and name like i have created for Project). In "value field" select id (which you want to match with query parameters eg ProjectID) and in " La...

If you want to duaplay system current date on SSRS report .then first create a parameter like "Date" and set its Data type to "Date/time" from properties. and the fo to the default value option in propoerties then check specify value then click on button named " fx ", a window will be open for writing formula then write " = today() " in the window and click on ok. and now go to the test box where you want to disply date. right click on text box and go to expression . then select parameters from left pane. all parameters will be shown at the right pane. then go to right pane and double click on date parameter.now a formula will be created like " = parameters!Date.value" . then click ok. now run your report your report will show system current date.

If you want to retrieve comma seperated values instead of all values in multiple line. then first set the values to varchar.. eg if you have a parameter which you want to retrieve comma seperated values for this parameter then initilize it as varchar. eg @ProjectID varchar(25), @RegistrationNo varchar(25) then in where clause use function "select item from fn_split" and pass your parameter which you want to split as the first argument of this function and pass an operator as second argument on which you want to spit value eg. where (Project.intProjectID in (select item from fn_split(@ProjectID, ','))) and (Student.intRegNo in (select item from fn_split(@RegistrationNo, ','))) This is a sql server built in function "select item from fn_split"