Archive for the ‘SSRS’ Category

In this example we will be using a Tablix as the objects but really it could be a Matrix, Graphs, Textbox etc.

Assume a scenario. In Tablix A (on the left hand side) we have a list of fruits :
Melons and Apples

If we click on Melons then on a second Tablix B (on the right hand side) we will display the colours Yellow and Green. If apples is selected then we will show Red and Green.

Create a Parameter called DrillThrough of type Text and make the visibility Hidden.

Goto the textbox property of the cell holding Tablix A’s values.
This is where the magic happens:
Select Action.
From there select “Go to report”
Under the Specify a Report select the name of the report you are currently on (this is so that it refer’s back to itself).
Then click the “Add” parameter button.
Select the parameter DrillThrough and under value select the field from Tablix A.

You can now test if you wanted using a text box by creating a text box and in the expression put in =Parameters!DrillThrough.Value
Delete the text box if yout testing is complete.

Finally create a Dataset where the parameter DrillThrough is used so that depending on what is clicked the list of Fruits in Tablix A that will be sent through to display the list of relevant colours in Tablix B.

Read Full Post »

I have found the below  somewhere in the internet but I don’t remember where:
–>> Line Feed
— Newlines in text boxes
If you’re using an expression to build up a paragraph or two of text in a text box and need a new line, use this:
VBCRLF (Visual Basic Carriage Return Line Feed for those who’re interested!)

E.g. =”This is line 1″ + VBCRLF + “This isn’t” will produce:
This is line 1
This isn’t

Gotcha: If VBCRLF is the first or last thing in your text box, Reporting Services will ignore it (I have no idea why!). Make sure you put in one extra VBCRLF at the beginning or end if you’re using them there.
—- >>>

– – Checkboxes
I would have thought it would be fairly common to want to display a check box on a report which is checked based on a value in the dataset but it would appear that it’s not so common as there is no control provided to handle this. If your client, like mine, really wants a checkbox displayed on a report, you’ll need to use wingdings. Yes, you heard right, add a small text box to your report, set the font to wingdings and use something like the following expression:
=iif(Fields!BookingMade.Value = “1”,chr(0254),”o”)

Checked Box > Wingding 2 =chr(82)
Unchecked Box > Wingding 2 =chr(163)

Read Full Post »

-- For use with usp_getMonth (which can be found in the Second Option in the post - http://wp.me/pAchH-4l)

-- The below can be used when in SSRS you only want to show the months which are available for the users to select. This requires the the a parameter which would return the year selected

=iif(Parameters!Year.Value = year(today()), month(today()), 12)

Read Full Post »

SSRS does not allow parameters to dynamically hide and unhide (unless asp scripting/xml/url changing/other scripting outside BIDS is implemented).

A workaround around could be to keep all the parameters visible and instead of trying to hide the ones not needed just make a single value available.

Let me elaborate:

1) @Par [text type] > the parameter you want the the dynamic effect applied to it

2) “usp_dp” > if the parameter is a multi valued then lets say this is the name of the SP it generates the values from. This SP also needs to have a parameter of its own that will take an integer value input. Lets call it @ID.

3) @Switch [text type] > if this is equal “A” then you want to use @Par else not.

You will need:
1) Another paramter (either existing or a new one) that accepts integer. I usually use an existing parameter that accepts a certain ID number. For this example lets say we call it @ID (this parameters is of same tyep and name as in usp_dp).

2) On BIDS 2008 on the “Report Parameter Properties” goto the “Advanced” tab and select “Always refresh” or do the same for any other versions. – This step is optional but recommended.

We will first consider that its a multi-valued parameter:
1) Goto the “Dataset Properties” of “usp_dp” and then to “Parameters” and on for @ID Parameter Name put the below “Parameter Value:


What the above does is:
Depending on the value of @ID it will generate the list available for @Par to display. So if you don’t want to display any values you have to say that @ID = 0.

Finally what you need to do is write your “usp_dp” script in such a way that when ever @ID =0 the script should return “N/A” else it will return the normal values for the parameters.

I am sure if you have worked out the multivalued parameter then working out how to apply a similar effect to a textbox parameter (where you need to type in your value) will not be a problem. My suggestion is to use the “Default Values” option in the “Report Parameter Properties”.

Read Full Post »

-- TSQL -- Fractions (or float) not being returnedTSQL -- Counting Nulls »TSQL, SSRS -- Date Manipulation
--July 19, 2010 by Usama 

SELECT getdate() Date1, getdate() + 1 Date2
     , DATEDIFF(day,GETDATE() + 1, getdate()) NegetiveFigure
	, DATEDIFF(day,GETDATE(), getdate() + 1) PositiveFigure_PlaceTheBiggerOnTheRight

	dateadd(day, datediff(day, 0, getdate()), 0) as StartDateToday,
	dateadd(ms, -3, dateadd(day, datediff(day, 0, getdate())+1, 0)) as EndDateToday

Select dateadd(D, 0, datediff(D, 0, GETDATE())) -- Time Stripped (without any data type conversion for optimized performance)

Select DATEADD(m, DATEDIFF(m,0,GETDATE() )-1, 0) -- Beginning of previous month
select DATEADD(ms,-3,DATEADD(m, DATEDIFF(m,0,GETDATE()) , 0)) -- End of previous month


=DateAdd("m",DateDiff("m", "1900-01-01″, Today()) -1, "1900-01-01″) -- Beginning of previous month

=DateAdd("d", -1, DateAdd("m",DateDiff("m", "1900-01-01″, Today()), "1900-01-01″)) -- End of previous month


Select DATEADD(m, DATEDIFF(m,0,GETDATE() ), 0) -- Beginning of the month

select DATEADD(ms,-3,DATEADD(m, DATEDIFF(m,0,GETDATE()) + 1, 0)) -- End of the month 


=DateAdd("m",DateDiff("m", "1900-01-01″, Today()), "1900-01-01″) -- Beginning of the month 

=DateAdd("d", -1, DateAdd("m",DateDiff("m", "1900-01-01″, Today())+1, "1900-01-01″)) -- End of the month


-- Converting String to Datetime

declare @SelectedMonth int = 1, @year int = 2010 

-- Replace is not needed in the below line but is just illustrated if you want a leading 0 when the month is a single digit
declare @date datetime = convert(datetime, '28/' + replace(str(@SelectedMonth,2),' ‘,'0′) + ‘/' + str(@year ,4), 103)
select @date '


DATENAME(weekday , getdate())
,  DATEADD(ms,-3,DATEADD(D, DATEDIFF(D,0,GETDATE()) , 0)) -- End of play > Yesterday 
, DATEADD(D, DATEDIFF(D,0,GETDATE())-1 , 0) -- Beginning of play > Yesterday 

Read Full Post »