March 11, 2013 at 12:57 pm
I have a Crystal Report that I am converting to Reporting Services. The report lists accounts by SIC Code, which is in the following format on the database: '#### - text description' (for example, "5812 - EATING PLACES"). The users are allowed to select multiple SIC codes to report on, and they only have to enter the first 4 characters of the code (in this example, '5812'). In Crystal, the code uses "startwith" to produce the result set (where siccode startswith {?SIC Code}). The "in" clause in Reporting Services is used for multi value parms, but it must be an exact match. How do I do the equivalent in Reporting Services?? Thanks for any help you could offer.
March 13, 2013 at 10:23 am
Barbara:
A couple of thoughts come to mind. Top of my head, I don't think SSRS has an equivalent built-in feature you described in Crystal. So, for the moment, I'll offer a possible workaround.
1) It sounds like you want to give users the ability to provide more than one choice at a time -- i.e., multivalue parameter?
2) If you are able to provide a list of choices, and the list isn't too long, you could create a multivalued parameter that is populated with all of the choices a user might select (e.g. "5812 - EATING PLACES", "0000 - BORING PLACES", "0013 - UNLUCKY PLACES","0666 - EVIL PLACES",...). Set the value of the parameter to the 4 digit code, and the label of the parameter to the full description of each choice.
3) Offering a multivalued parameter means the user merely needs to click-to-choose rather than type in their selections.
4) Regarding the sql to process the parameter, two basic approaches to consider. One is to write the sql code in SSRS with a "WHERE SICCode in (@paramSICCodes) " . The other approach is to pass the parameter to a stored procedure, but you'll need to parse out the individual values contained within the parameter as one of the steps within the stored proc. Jeff Moden's DelimitedSplit8k function is great at this task (see
http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url])
Again, this work around assumes you have some sort of reference table of SIC codes available, rather than just the fulltext descriptions.
--Pete
March 13, 2013 at 2:19 pm
peterzeke (3/13/2013)
A couple of thoughts come to mind. Top of my head, I don't think SSRS has an equivalent built-in feature you described in Crystal. So, for the moment, I'll offer a possible workaround.
1) It sounds like you want to give users the ability to provide more than one choice at a time -- i.e., multivalue parameter?
2) If you are able to provide a list of choices, and the list isn't too long, you could create a multivalued parameter that is populated with all of the choices a user might select (e.g. "5812 - EATING PLACES", "0000 - BORING PLACES", "0013 - UNLUCKY PLACES","0666 - EVIL PLACES",...). Set the value of the parameter to the 4 digit code, and the label of the parameter to the full description of each choice.
3) Offering a multivalued parameter means the user merely needs to click-to-choose rather than type in their selections.
4) Regarding the sql to process the parameter, two basic approaches to consider. One is to write the sql code in SSRS with a "WHERE SICCode in (@paramSICCodes) " . The other approach is to pass the parameter to a stored procedure, but you'll need to parse out the individual values contained within the parameter as one of the steps within the stored proc. Jeff Moden's DelimitedSplit8k function is great at this task (see
http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url])
Again, this work around assumes you have some sort of reference table of SIC codes available, rather than just the fulltext descriptions.
--Pete[/quote
Hi Pete,
To answer your questions:
1) Yes, the users need a multi-value parameter. They should be able to report on as many SIC codes as they like.
2) I have set up this parameter to use a query to provide a list of choices, where the value is the 4 digit SIC code ("5812") and the label is the 4 digit code + description, i.e. "5812 - EATING PLACES"
3) Yes, I am aware of that. I have used multi-value parameters many times.
4) I guess I still don't understand what I need to do. I know I can parse the individual values, but how do I get them into a WHERE clause that works like the "startswith" in Crystal?? There are 1100 possible
SIC codes. The table that I am using to produce the parameter list of choices contains a standardized listing of SIC codes, but the users are able to type whatever they want as a description, so some accounts may
appear on the database as "5812 - EATING PLACES" or "5812-RESTAURANTS" or "5812 - PLACES to EAT" , but they should be able to select the standard "5812 - EATING PLACES" and the report should return all
accounts with an SIC that starts with "5812" which would give them all 3 of these example.
I appreciate any help I could get.
Barbara
March 14, 2013 at 8:17 am
Barbara -- Just out of curiosity, do you have permissions to create a stored proc or function on SQL server from where the data is being sourced? If not, then it changes how I might approach the problem by writing the sql completely in the SSRS rdl.
--pete
March 14, 2013 at 8:38 am
Hi Pete. Yes, I could create a stored procedure or function on the server if I needed to.
March 14, 2013 at 9:24 am
OK -- you've can create stored procs and such... good to know.
Next question -- do you have a "numbers" table or "tally" table on the server?
If not, here's some SQL to build one. The solution I'm pondering may not need a permanent tally table, but it won't hurt to have one. (I think I obtained the code from Itzik Ben-Gan). The code below will insert 10,000 rows with one field populated with the numbers 1 to 10,000. This table can be leveraged to rapidly parse out the values from a multivalued delimited string.
If you want to make sure about what this table will look like, comment out the "Insert" clause and just run the code in SSMS.
My overall intention is to demonstrate, just using sql, how a multivalued delimited string can be parsed and then referenced in a way that mimicks the "StartsWith" functionality found in Crystal.
SET NOCOUNT on
CREATE TABLE dbo.Tally
(
N INT
,CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED ( N )
)
DECLARE @n AS BIGINT;
SET @n = 10000; /* SET ROW LIMIT HERE */
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS N FROM L5)
INSERT DBO.[Tally] ([N])
SELECT N FROM Nums WHERE n <= @n;
SET NOCOUNT off
March 14, 2013 at 9:54 am
Now, assuming you have a tally table already or built one from the script I provided in the previous post, below is the sql to build a sample test table from which to query, along with the sql to parse a multivalue parameter in order to return the desired records from the test table.
Code to populate a test table:
/****************************************************/
/* BUILD A SAMPLE DATASET OF SICCODES*/
/****************************************************/
IF OBJECT_ID('TEMPDB..#SICCODES') IS NOT NULL
DROP TABLE #SICCODES
CREATE TABLE #SICCODES
(
SICCODE VARCHAR(4)
,SICCODE_TEXT VARCHAR(50)
)
INSERT #SICCODES
(
SICCODE
,SICCODE_TEXT
)
SELECT '5810', '5810-RETAIL-EATING & DRINKING PLACES'
UNION ALL
SELECT '5812', '5812-RETAIL-EATING PLACES'
UNION ALL
SELECT '5812','5812-RESTAURANTS'
UNION ALL
SELECT '5812','5812-PLACES TO EAT'
UNION ALL
SELECT '5812','5812-DINERS, DRIVE-INS, AND DIVES'
UNION ALL
SELECT '5900', '5900-RETAIL-MISCELLANEOUS RETAIL'
UNION ALL
SELECT '5912', '5912-RETAIL-DRUG STORES AND PROPRIETARY STORES'
UNION ALL
SELECT '5940', '5940-RETAIL-MISCELLANEOUS SHOPPING GOODS STORES'
UNION ALL
SELECT '5944', '5944-RETAIL-JEWELRY STORES'
UNION ALL
SELECT '5945', '5945-RETAIL-HOBBY, TOY & GAME SHOPS'
/* SELECT * FROM #SICCODES */
Code to parse out a multivalue parameter to simulate "StartsWith...". Be sure to test out both joins in the SQL -- there's an "=" join and a "Like" join (only one should be active at a time):
/************************************************************************************************/
/* RETURN RESULTS BASED ON SIMULATING THE PASSING OF A MULTIVALUE DELIMITED STRING PARAMETER*/
/************************************************************************************************/
DECLARE @Parameter VARCHAR(2000)
,@Delimiter CHAR(1)
SET @Delimiter = ','
SET @Parameter = '5812,594' /* SIMULATE THE VARIOUS VALUES THAT MIGHT BE SUBMITTED IN A MULTIVALUE PARAMETER PASSED FROM SSRS */
SET @Parameter = @Delimiter + @Parameter + @Delimiter /* BOOKEND THE PARAMETER ARRAY WITH A COMMA ON EACH END... NECESSARY IF ONLY ONE VALUE EXISTS IN THE @PARAMETER */
SELECTC.SICCODE
,C.SICCODE_TEXT
FROM#SICCODES C
JOIN /* PARSE OUT THE MULTIVALUED DELIMITED PARAMETER INTO INDIVIDUAL ROWS OF DATA */
(
SELECT DISTINCT ParamValue = LTRIM(SUBSTRING(@Parameter,N+1,CHARINDEX(@Delimiter,@Parameter,N+1)-N-1))
FROM dbo.Tally
WHERE N < LEN(@Parameter)
AND SUBSTRING(@Parameter,N,1) = @Delimiter --Notice how we find the comma
) Sub
ON C.SICCODE = Sub.ParamValue -- THIS IDENTIFIES EXACT SICCODE MATCHES
/* USE THE "LIKE" JOIN CLAUSE BELOW, INSTEAD, IF YOU NEED TO IDENTIFY DATA BY THE TEXT DESCRIPTIONS RATHER THAN BY EXPLICIT SICCODE */
-- ON C.SICCODE_TEXT LIKE Sub.ParamValue + '%' /* THIS "LIKE" JOIN TRULY MIMICKS "STARTSWITH".... */
March 14, 2013 at 2:06 pm
Thanks Pete. Let me take a look at the code and I'll let you know if I can make it work for me.
March 15, 2013 at 7:40 am
One last thing, the code I posted is essentially a proof of concept. Ultimately, for SSRS reports that I build that involve a multivalued parameter, I pass the parameter to a stored procedure wherein I leverage a table-based splitter function (like Jeff Moden's DelimitedSplit8k) to capture the data relevant to all of the parsed values from the parameter.
The code from the previous post basically mimicks this approach, but it also demonstrates how you could include a multivalue pararm directly in an embedded SSRS sql query, too.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply