Display Checkbox on report (SSRS)

Display Checkbox on report (SSRS)

MAR6

Written by: 
Wednesday, March 06, 2013  

To add checkbox on a report is issue to many. This article will let you know about how to add checkbox on report. You need add textbox beside the label if any and set font style of text box as Wingdings font type which is used to set checkbox on the report based on the value in a dataset.

Firstly add textbox on report.



Change a font family to Windings:

In expression of textbox, write down the expression and show checkbox or unchecked box based on the value from dataset. The value from dataset can be anything such true, false or some optionset field value.

 Here I have displayed checkedbox & uncheckesbox based on optionSet field value.



Add the following code in expression of texbox:
* This is based on optionset field value, can  use boolean or any field value.

=IIF((Fields!vdbrsac1_recvngstategovrneiservcs.Value)=676160001, chr(0254),chr(0168))

Run the report to see check boxes based on value of dataset.

Following are list of characters in “Wingdings” font are given below:

Sql Server Scripts

SQL SERVER – 2008 – 2008 R2 – Create Script to Copy Database Schema and All The Objects – Data, Schema, Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects

Quite often I get the request regarding how to copy all the objects – including schema and data from any database and re-create it on another instance.

SQL Server 2008 and SQL Server 2008 R2 has script generator wizard which does it for us. I ask you to pay special attention to image #5.

After the script is generated, the next challenge often users face is how to execute this large script as SQL Server Management Studio does not open the file. One can use SQLCMD for the same. See that in the last image of this post.

Pay attention to the option Types of data to script – select option ‘Schema and data’

As the file with data will be very large, use SQLCMD to execute the large script which will create database with schema & data.

Please let me know if you have any question or suggestion on this blog post.

Watch SQL in Sixty Seconds Episode on same subject.

SSRS Report using Stored Procedure

SSRS Report using Stored Procedure

I had been fretting over how to pass multiple valued parameter to a stored procedure. I was going to create a report which was going to use a Stored Procedure as the data source to fetch data from the SQL Server.
After a few glitches I finally found a way to do this. Lets go through the entire process. What went wrong and how I solved it.

I created a stored procedure named SalesValue with @Year as an input parameter.
-----------------------------------------------------------------------------------------------------------
if  OBJECT_ID('SalesValue') is not null
drop proc SalesValue
go

create proc SalesValue
(@Year nvarchar(100))
as
SELECT    SalesTerritoryCountry, 
          SalesTerritoryGroup,
          [MonthNumberOfYear],
          CASE
            WHEN monthnumberofyear between 1 and 3 then 'Quarter 1'
            WHEN monthnumberofyear between 4 and 6 then 'Quarter 2'
            WHEN monthnumberofyear between 7 and 9 then 'Quarter 3'
            WHEN monthnumberofyear between 10 and 12 then 'Quarter 4'
          end as [Quarter],
          case
            when monthnumberofyear between 1 and 6 then 'Half Year 1'
            when monthnumberofyear between 7 and 12 then 'Half Year 2'
          end as [Half Year],
          CalendarYear,
          SUM(FactResellerSales.SalesAmount) AS SalesAmount, 
          SUM(FactResellerSales.OrderQuantity) AS OrderQuantity, 
          SUM(FactResellerSales.SalesAmount) / SUM(FactResellerSales.OrderQuantity) AS AvgSale
FROM  FactResellerSales INNER JOIN DimSalesTerritory 
ON FactResellerSales.SalesTerritoryKey = DimSalesTerritory.SalesTerritoryKey 
INNER JOIN DimDate 
ON FactResellerSales.OrderDateKey = DimDate.DateKey
WHERE  CalendarYear in (@Year)
GROUP BY [MonthNumberOfYear],DimSalesTerritory.SalesTerritoryGroup, SalesTerritoryCountry, CalendarYear
ORDER BY DimSalesTerritory.SalesTerritoryGroup, CalendarYear,[MonthNumberOfYear]
go
----------------------------------------------------------------------------------------------------- 



The stored procedure was created successfully.

When I executed the above procedure with one single valued parameter it worked like a charm. and gave the below resultset.


 
But as soon as I entered another value, the query produced an error, which stated - 
Procedure or function SalesValue has too many arguments specified.



This error was pretty straight forward, since the parameter that I created was of an integer type, I couldn't pass more than one value of the integer.

So I tried to use my brain and convert the pair of values to a string and pass, which pretty obviously created another error stating -

Conversion failed when converting the nvarchar value '2007,2008' to data type smallint.

 

Now as per my report requirements, I needed to pass multiple values in a parameter which was in turn being passed to the above stored procedure.

I headed over to BIDS and into my report solution. I created two datasets namely SalesDepartment and YearDataSet



SalesDepartment dataset made use of the stored procedure SalesValue



In order to pass the parameter value to the stored procedure, I had to configure the parameter as below - 



Using the Join function would concatenate the values with a comma separating the values and it would be passed to the query.

The YearDataSet selected the distinct year values from the datasource.



I mapped the parameter @Year to the results produced by the YearDataSet as under -


After running the report it gave me the following error 


The error was being caused due to the multiple values being implicitly converted to nvarchar. The parameter cannot be implicitly converted to smallint. To resolve this problem, we need to use a UDF which will parse the multiple valued nvarchar string and convert it into individual integers which in turn will be passed to the stored procedure. 

I found this UDF code, which has been written by Jason Sansom (http://stackoverflow.com/questions/512105/passing-multiple-values-for-a-single-parameter-in-reporting-services)

CREATE FUNCTION [dbo].[fn_SplitInt]
(
    @List       nvarchar(4000),
    @Delimiter  char(1)= ','
)
RETURNS @Values TABLE
(
    Position int IDENTITY PRIMARY KEY,
    Year int
)

AS

  BEGIN

  -- set up working variables
  DECLARE @Index INT
  DECLARE @ItemValue nvarchar(100)
  SELECT @Index = 1 

  -- iterate until we have no more characters to work with
  WHILE @Index > 0

    BEGIN

      -- find first delimiter
      SELECT @Index = CHARINDEX(@Delimiter,@List)

      -- extract the item value
      IF @Index  > 0     -- if found, take the value left of the delimiter
        SELECT @ItemValue = LEFT(@List,@Index - 1)
      ELSE               -- if none, take the remainder as the last value
        SELECT @ItemValue = @List

      -- insert the value into our new table
      INSERT INTO @Values (Year) VALUES (CAST(@ItemValue AS int))

      -- remove the found item from the working list
      SELECT @List = RIGHT(@List,LEN(@List) - @Index)

      -- if list is empty, we are done
      IF LEN(@List) = 0 BREAK

    END

  RETURN

  END

This will create a function in the AdventureWorksDW2008R2 DB by the name of  dbo.fn_SplitInt. This function will take a nvarchar and a delimiter string. the result would be a table containing a column which would give all the year values in integer. There should not be any spaces between the values after the comma delimiter. Now we make a change to the stored procedure definition. 

if  OBJECT_ID('SalesValue') is not null
drop proc SalesValue
go

create proc SalesValue
(@Year nvarchar(100))
as
SELECT    SalesTerritoryCountry, 
          SalesTerritoryGroup,
          [MonthNumberOfYear],
          CASE
            WHEN monthnumberofyear between 1 and 3 then 'Quarter 1'
            WHEN monthnumberofyear between 4 and 6 then 'Quarter 2'
            WHEN monthnumberofyear between 7 and 9 then 'Quarter 3'
            WHEN monthnumberofyear between 10 and 12 then 'Quarter 4'
          end as [Quarter],
          case
            when monthnumberofyear between 1 and 6 then 'Half Year 1'
            when monthnumberofyear between 7 and 12 then 'Half Year 2'
          end as [Half Year],
          CalendarYear,
          SUM(FactResellerSales.SalesAmount) AS SalesAmount, 
          SUM(FactResellerSales.OrderQuantity) AS OrderQuantity, 
          SUM(FactResellerSales.SalesAmount) / SUM(FactResellerSales.OrderQuantity) AS AvgSale
FROM  FactResellerSales INNER JOIN DimSalesTerritory 
ON FactResellerSales.SalesTerritoryKey = DimSalesTerritory.SalesTerritoryKey 
INNER JOIN DimDate 
ON FactResellerSales.OrderDateKey = DimDate.DateKey
WHERE  CalendarYear in (select Year from dbo.fn_SplitInt(@Year,','))
GROUP BY [MonthNumberOfYear],DimSalesTerritory.SalesTerritoryGroup, SalesTerritoryCountry, CalendarYear
ORDER BY DimSalesTerritory.SalesTerritoryGroup, CalendarYear,[MonthNumberOfYear]
go

After the above changes are made to the stored procedure, the report can be generated easily with the data for all the selected years. 

 

This way I can use stored procedures to create SSRS Reports along with the use of User Defined Functions.