5 Simple things I forget about SSRS – Multi Select Parameters

For some reason whenever I get to working on a report I am really irritated at how I “forget” that SSRS doesn’t handle multi-select parameters at all. I am irritated at myself for pushing this horrid fact from my brain, my reluctance to remember or maybe that the painful truth is a coping mechanism or I am just in denial–maybe it’s been fixed.. Then I am reminded how Microsoft failed us by not making this an actual feature. I feel the writing has been on the wall for a while now SSRS is a fading technology. I do however know that there will be SSRS servers running for years to come.

When creating a report and setting up your parameters, you are left to believe that everything is taken care of. In the properties of a parameter you have the option to select Allow Multiple Values. (Image below)

pram1

The sad truth is this really just gives users the opportunity to click and select on more than one item. And if you forget to fix this with code you will find yourself not getting the results you are looking for and off in the weeds trouble shooting your query. I am sure there is always more than one way to skin a cat, especially when it comes to a Microsoft product. But my weapon of choice is creating a function and using for everything.

There is 0 reason in this world to spend hours on end recreating the wheel, I found code online and I just reuse it. (Disclaimer, it’s been so long I am not sure where I got the code or I would give proper credit.) This code will create a function and and you can use it for all your reports that have multi-value parameters.

CREATE FUNCTION [dbo].[SplitParameter]
–Define parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))

RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), –10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), –10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), –10E+4 or 10,000 rows max
cteTally(N) AS (–==== This provides the “base” CTE and limits the number of rows right up front
— for both a performance gain and prevention of accidental “overruns”
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (–==== This returns N+1 (starting position of each “element” just once for each delimiter)
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(–==== Return start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
)
–===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l

Now to complete this task you need to update your code, which is hopefully a stored procedure. First step is to set the parameter to NULL if all items are passed in and then call the function to separate the list and dump it into a temp table. To make things simple I usually use a naming convention of naming the parameter, temp tables and anything related to it after the column being searched.

pram2

 

Now there WHERE clause should look like below. NULL means all were selected and we are not filtering or there is a list.

AND ((@ReportParameter1 IS NULL) OR (I.Item in (SELECT ITEM FROM #ReportParameter1)))

This seems like extra work that you shouldn’t have to do, but with this info being easy to find online it makes this task simple to complete. So if you are new to report writing or you are like me and forget, hopefully this help you.

Inside SQLSaturday – Romancing your Sponsors

Romancing your Sponsors

To be honest, this seems like a no brainer, but I have noticed that in some locations the sponsors are just “there” so to speak. So these are my tips and a lot of them I have gathered from the sponsors over the last year or so.

Make sure your sponsor area gets good foot traffic–this is how they meet your attendees. Don’t build rooms as tracks that would keep folks in 1 room or one end of a building the entire day. Have lunch set up early and allow the sponsors and lunch speakers to go through so they will be ready for the lunch break. . . . → Read More: Inside SQLSaturday – Romancing your Sponsors

5 Simple things I always forget about SSRS – Custom Color Palette

There are spells where I am inside SSRS daily and then there are some stretches of time where I wrapped up doing other things and don’t see SSRS for months on end. I always seem “surprised” when I forget some of SSRS’s behaviors. I am sure sometimes I am just pushing the bad out of my head and other times it might be that I just don’t use it often enough to keep that info close to the top of my stack. So I decided that I should start a small blog series as a gentle reminder to myself and maybe along the way it will help someone else.

Custom . . . → Read More: 5 Simple things I always forget about SSRS – Custom Color Palette

Nashville’s BI User Group – 1st Meeting

WOW!

So that is about the only word I can use to sum up how I feel about last night’s meeting.

We had about 45 of #BIHappyHour in the hotel lobby, it was a little cramped but we were not moving into the open spaces very well. In the future will will do a better job of herding folks into more open space. It warmed my heart to see so many people chatting and introducing themselves. Our food was delivered, special thanks to my Dad and my husband for going to pick it up! I thought the food was awesome and I didn’t get to eat any of it until . . . → Read More: Nashville’s BI User Group – 1st Meeting

Inside SQLSaturday – PayPal

PayPal

This year PASS changed the way money is handled for a SQLSaturday. In the past, they had a PayPal account and all the money was collected there. You would then request disbursement from them, with a limit of like 3 or something and 30 days after the event they’d allow you to pull out any remaining amount. Now, you must provide your own PayPal Account. But what does that mean to you? These details are things that happened to me and may not be a concern for you, if you’ve mastered PayPal.

Verify your account This was a newly-created PayPal account and we were unaware that you must verify . . . → Read More: Inside SQLSaturday – PayPal

Nashville’s newest Pass Chapter

FINALLY NashBI!

If you have known me for very long you know that Kerry and I have been talking about starting a Business Intelligence user group here in Nashville for about a year. At first we were just going to do some additional meetings under the SQL user group’s umbrella. We thought that working as a lager group could be better for both groups. However we finally discovered that BI was huge and there was a lot of interest in it. Just looking at MeetUp there are 3-4 data related groups.

We are pleased to announce the launch of a new PASS chapter here in Nashville. The Nashville Business Intelligence . . . → Read More: Nashville’s newest Pass Chapter

WIT Summit 2014

Recapping the Women in Technology Luncheon

 

As a member of the PASS Women in Technology virtual Chapter I had been on several planning meetings throughout the past year about the luncheon. I had known for some time that they were hoping to secure Kimberly Bryant [ TWITTER | Linkedin ] as our guest speaker.

If you are unaware Kimberly started the organization Black Girls Code [ TWITTER ]. She “hopes to provide young and pre-teen girls of color opportunities to learn in-demand skills in technology and computer programming at a time when they are naturally thinking about what they want to be when they grow up.”

To be perfectly . . . → Read More: WIT Summit 2014

#WIT

I am live tweeting the #SUMMIT14 #WIT.

twitter.com/tameraclark

Where to find Tammy

During the next week you may find it hard to track me down. But I can tell you with a fair amount of certainty that I will be in these places at the times listed below. If you miss me, just shoot me an email or tweet so I can let you know where I will be. I will try to put updates as I know more.

Tuesday:

SQLSaturday Meeting 2p-3:3 Chapter Leader Meeting 3:45p-6 SQL Karaoke 9:30p-?

 

Wednesday:

Regional Mentor Meeting 12-1p

Thursday:

WIT Luncheon 11:45-1:15p OUT OF POCKET 5p-?

Friday:

QA with the Board 1p-2:15 Community Zone 2p-3

Last minute thoughts for Summit 2014

I have written before about Summit and the kind of things to take/pack/do. But I feel that some things have changed and that I am smarter now so I want to revisit this list.

 

Packing

Before a trip we use the dining room table as a place to hold things we want to take and don’t currently need to use. We fondly refer to this as the staging table. I started staging things about a week or so ago. Also make a list of things you don’t want to be without during your trip so you can check it off as it goes in the bag.

If you plan . . . → Read More: Last minute thoughts for Summit 2014