Hans_Huginn: SSRS

레이블이 SSRS인 게시물을 표시합니다. 모든 게시물 표시

2015년 5월 27일 수요일

[SSRS] 공간쿼리를 사용한 지도 레포트 개발 ( how to create map report in ssrs using Spatial Query )

* 문제: SSRS에서 지도 레포트를 만들어 보자

* 해결: SQL Server의 공간쿼리를 이용해 지도 레포트를 만들 수 있다.

* Step01. 조회 쿼리 생성

1. AdventureWorks2008R2의 [Person].[Address] 테이블 정보 확인

sp_help 'Person.Address'

2. 쿼리

select a.AddressID, a.totaldue, a.seller_cnt, b.SpatialLocation
from (
 select 
  ShipToAddressID as AddressID
  , sum(totaldue) as totaldue
  , sum(salespersonid) as seller_cnt
 from Sales.SalesOrderHeader
 group by ShipToAddressID
) as a 
join Person.Address as b 
 on a.AddressID = b.AddressID

- SSMS에서도 공간 결과 확인이 가능하다.

* Step02. Report 개발

1. 데이터 집합 추가
- 쿼리

select a.AddressID, a.totaldue, a.seller_cnt, b.SpatialLocation
from (
 select 
  ShipToAddressID as AddressID
  , sum(totaldue) as totaldue
  , sum(salespersonid) as seller_cnt
 from Sales.SalesOrderHeader
 group by ShipToAddressID
) as a 
join Person.Address as b 
 on a.AddressID = b.AddressID

2. 지도 추가

- 공간 데이터 원본 선택 > Sql Server 공간 쿼리 선택

- 앞서 생성한 데이터 집합 선택

- 공간 데이터 및 지도 보기 옵션 선택 > 계층유형: 점, Bing Maps 계층 추가 체크

- 지도 시각화 선택 > 거품형 지도 선택( 3가지 중 택 1 )

- 분석 데이터 집합 선택 > 앞서 생성한 데이터 집합 선택

- 색 테마 및 데이터 시각화 선택 > 시각화할 메뉴를 원하는 값으로 설정

3. 결과

[SSRS] Shapefile을 사용한 지도 레포트 개발 ( how to create map report in ssrs using shapefiles )

* 문제: SSRS에서 지도 레포트를 만들어 보자

* 해결: Shapefile을 사용하여 지도 레포트를 만들 수 있다.

* Step01. Shapefile Download

1. GADM Database
- http://www.gadm.org/country
- 원하는 나라 선택 후 Shapefile 다운로드

2. 통계청
- http://sgis.kostat.go.kr/contents/shortcut/shortcut_05.jsp#
- 해당 링크에서 원하는 Shapefile 다운로드 가능하다 ( 로그인 필요함 )

* Step02. 매칭 데이터 생성 ( GADM의 shapfile 사용 )

1. 필요한 데이터만 추출해 DB에 저장해보자
- KOR_adm.zip 압축 풀기 > KOR_adm1.csv 에서 PID, NAME_0, NAME_1 만 활용

- DB서버에 테이블 생성

create table dbo.tb_kor_adm
(
PID    int,
NAME_0   varchar(30),
NAME_1   varchar(30),
value   int
)

- 데이터 입력

insert into dbo.tb_kor_adm values(2735,'South Korea','Busan',1)
insert into dbo.tb_kor_adm values(2736,'South Korea','Chungcheongbuk-do',2)
insert into dbo.tb_kor_adm values(2737,'South Korea','Chungcheongnam-do',3)
insert into dbo.tb_kor_adm values(2738,'South Korea','Daegu',4)
insert into dbo.tb_kor_adm values(2739,'South Korea','Daejeon',5)
insert into dbo.tb_kor_adm values(2740,'South Korea','Gangwon-do',6)
insert into dbo.tb_kor_adm values(2741,'South Korea','Gwangju',7)
insert into dbo.tb_kor_adm values(2742,'South Korea','Gyeonggi-do',8)
insert into dbo.tb_kor_adm values(2743,'South Korea','Gyeongsangbuk-do',9)
insert into dbo.tb_kor_adm values(2744,'South Korea','Gyeongsangnam-do',10)
insert into dbo.tb_kor_adm values(2745,'South Korea','Incheon',11)
insert into dbo.tb_kor_adm values(2746,'South Korea','Jeju',12)
insert into dbo.tb_kor_adm values(2747,'South Korea','Jeollabuk-do',13)
insert into dbo.tb_kor_adm values(2748,'South Korea','Jeollanam-do',14)
insert into dbo.tb_kor_adm values(2749,'South Korea','Seoul',15)
insert into dbo.tb_kor_adm values(2750,'South Korea','Ulsan',16)

* Step03. Report 개발

1. 데이터 집합 추가
- 쿼리

select * from  dbo.tb_kor_adm

2. 지도 추가

- 공간 데이터 원본 선택 > ESRI 셰이프 파일 > 다운로드한 파일 선택

- 공간 데이터 및 지도 보기 옵션 선택

- 지도 시각화 선택 > 컬러 분석 지도 선택( 3가지 중 택 1 )

- 분석 데이터 집합 선택 > 앞서 생성한 데이터 집합 선택

- 공간 및 분석 데이터에 대한 일치 필드 지정

- 색 테마 및 데이터 시각화 선택 > 시각화할 메뉴를 원하는 값으로 설정

3. 결과

2015년 5월 18일 월요일

[SSRS]차트 너비 동적으로 설정하기(Dynamic set of chart width)

*문제: 차트 너비를 동적으로 설정하고 싶은 경우

*해결: 차트 속성의 DynamicHeight, DynamicWidth 속성을 활용

*Step0. SP 생성

CREATE PROC DBO.UP_GET_SALES
     @TOP_CNT INT
AS
BEGIN

SELECT TOP(@TOP_CNT) 
     DATEPART(YEAR,[ORDERDATE])     AS YEAR
     , DATEPART(MONTH,[ORDERDATE])  AS MONTH
     , SUM(B.UNITPRICE)             AS UNITPRICE
     , COUNT(*)                     AS CNT
FROM [SALES].[SALESORDERHEADER]     AS A WITH(NOLOCK)
JOIN [SALES].[SALESORDERDETAIL]     AS B WITH(NOLOCK)
     ON A.SALESORDERID = B.SALESORDERID
JOIN [PRODUCTION].[PRODUCT]         AS PD WITH(NOLOCK)
     ON B.PRODUCTID = PD.PRODUCTID
WHERE DATEPART(YEAR,[ORDERDATE]) = 2013
GROUP BY DATEPART(YEAR,[ORDERDATE])
     , DATEPART(MONTH,[ORDERDATE]) 
ORDER BY 2
END

*Step1. Report 매개변수 생성

*Step2. 데이터 집합 생성

a. 생성한 SP로 데이터 집합을 생성하고, report 매개변수를 SP에 매개변수 값으로 전달

*Step3. DynamicWidth 설정

a. 매개변수 값을 전달 받아 차트의 너비(inch)로 설정해 보자

차트 클릭 > 속성 창 > DynamicWidth

=(1 + Parameters!rp_top_cnt.Value / 2) & " in"

or

=(1 + CountDistinct(Fields!MONTH.Value, "DataSet") / 2) & " in"

ex> 12를 입력하면 --> 7 in, 10을 입력하면 --> 6 in

*Step4. 결과

a. 매개변수의 값에 따라 차트의 너비가 변경 된다.

[SSRS] 이중축 혼합형 그래프 그리기 ( create double axis chart in SSRS )

*문제: 이중축 혼합형 그래프 그리기

*Step0. 쿼리 생성

SELECT
    DATEPART(YEAR,[ORDERDATE]) AS Year
    , DATEPART(MONTH,[ORDERDATE]) AS MONTH
    , pc.Name as Category_Name
    , psc.Name as SubCategory_Name
    , SUM(B.UNITPRICE) AS UNITPRICE
    , count(*) as cnt
FROM [SALES].[SALESORDERHEADER] AS A with(nolock)
JOIN [SALES].[SALESORDERDETAIL] AS B with(nolock)
    ON A.SALESORDERID = B.SALESORDERID
JOIN [Production].[Product] as pd with(nolock)
    ON B.ProductID = PD.ProductID
join [Production].[ProductSubcategory] as psc with(nolock)
    on pd.ProductSubcategoryID = psc.ProductSubcategoryID
join [Production].[ProductCategory] as pc with(nolock)
    on psc.ProductCategoryID = pc.ProductCategoryID
where DATEPART(YEAR,[ORDERDATE]) = 2013
GROUP BY DATEPART(YEAR,[ORDERDATE])
    , DATEPART(MONTH,[ORDERDATE]) 
    ,psc.Name
    ,pc.Name

*Step1. 신규 그래프 생성

a. 마우스 우클릭 > 삽입 > 차트 > 세로 막대형 차트 선택 > 확인

*Step2. 차트 데이터 입력

a. 범주 그룹
- 그래프의 x축에 필요한 그룹을 설정

b. 합계 값
- 합계 데이터를 입력

*Step3. 그래프 종류 변경

a. 세로 막대 그래프를 선형 그래프로 변경
b. 변경할 값 선택 > 마우스 우클릭 > 차트 종류 변경 선택 > 선형 그래프 선택

*Step4. 이중축 설정

a. 선형 그래프 값 선택 > 마우스 우클릭 > 차트 종류 변경 선택 > 계열 속성 선택

b. 축 및 차트영역 tab > 세로 축 : 보조 선택

c. 표식 tab > 표식 유형: 원, 표식 색: 흰색, 표식 테두리 색: 빨강 선택

*Step5. 기타 옵션 설정

a. Y 축 주 눈금선 표시 체크 해제

b. Y축 1번째 제목 수정, Y축 2번째 제목 수정

c. 차트 제목 삭제, X축 제목 삭제

d. 범례 Position: TopLeft 선택

e. 선형 그래프 데이터 레이블 표시 체크

* 결과

2015년 5월 15일 금요일

[SSRS] 행렬의 대화형 정렬 Part2

*문제: 열 그룹으로 행그룹 정렬

*해결: 입력란 속성의 동작 옵션을 활용

*Step0. 열 그룹으로 행그룹 정렬

아래 행렬에서 2011년(열그룹)을 클릭했을때 2011년 내부에서 카테고리별 sum 값을 정렬을 해보자.

*Step1. Year 열그룹 마우스 우클릭 > 입력란 속성

*Step2. 대화형 정렬 Tab

a. 이 입력란에서 대화형 정렬 사용 체크

b. 정렬할 항목으로 Category (행 그룹) 선택

c. 정렬 기준으로 sum값 선택

*Step3. 레포트 실행 ( Error 발생 )

a. 레포트를 실행하면 아래와 같은 에러가 발생한다.

b. SSRS에서는 열그룹으로 행그룹을 정렬할 항목으로 사용할 수 없다.

c. 이를 해결하기 위해서 트릭을 사용해야 한다.

*Step0. 트릭

a. SSRS의 경우 최초에 쿼리에서 조회된 정렬 기준으로 데이터를 화면에 출력한다

b. 이를 이용해 행렬을 원하는 형태로 출력해 보자.

*Step1. SP 생성하기

a. 로직

- 열 그룹 값을 받을 매개변수 생성

- 기본 데이터 생성

- 매개변수로 받은 연도 데이터 만을 이용해 카테고리별 sum값 sorting

- 정렬 컬럼 기준으로 데이터 조회

b. 쿼리