(SSRS) Report 開發的用法筆記－隨手筆記

流水號

RowNumber(Nothing)

------------------------------------------------------------------------------------

總計

=RunningValue(Fields!SALES_QTY.Value,Sum,Nothing)

------------------------------------------------------------------------------------

僅在最後一頁顯示(僅可用在頁首和頁尾)

=iif(Globals!PageNumber=Globals!TotalPages,false,true)

------------------------------------------------------------------------------------

Expression 參照用法

=Lookup(Fields!ORGANIZATION_CODE.Value,Fields!ORGANIZATION_CODE.Value , Fields!TOTALQTY.Value, "DS_BeforDataGroupTotal")

------------------------------------------------------------------------------------

vbCrLf 換行

------------------------------------------------------------------------------------

空值補零

= IIF(Fields!SALES_AMT.Value is Nothig , 0 , Fields!SALES_AMT.Value)

------------------------------------------------------------------------------------

判斷是否顯示

=IIf(Parameters!Type.Value = "Org", False, True)

------------------------------------------------------------------------------------

Reporting Services DB 紀錄報表與datasource Table
[ReportServer].[dbo].[Catalog]

Type : 1 --> 資料夾
Type : 2 --> Reort
Type : 5 --> DataSource

1 = Folder
2 = Report
3 = Resources
4 = Linked Report
5 = Data Source
6 = Report Model
7 = Report Part (SQL 2008 R2, unverified)
8 = Shared Dataset (SQL 2008 R2)

------------------------------------------------------------------------------------

格式轉換
format(Fields!PERIOD_NAME.Value,"MM/yyyy")

To convert string : =Cstr(Today())
To Convert Datetime: = Datevalue(Cstr(Today()) )

------------------------------------------------------------------------------------

查詢失效帳號報表訂閱紀錄

select c.Name,
c.Path,
c.Description,
sb.Description,
empN.un
  from dbo.Subscriptions sb,
       (select u.userid , replace(u.UserName, 'ACC\', '') un
          from dbo.Users u, AUOREF.dbo.emp_Data_all emp
         where replace(u.UserName, 'ACC\', '') = emp.LOGONID collate
         Chinese_Taiwan_Stroke_CI_AS
           and emp.active = 'N'
           and replace(u.UserName, 'ACC\', '') <> '') empN,
        dbo.Catalog c
where sb.OwnerID = empN.UserID
   and sb.Report_OID = c.ItemID;

------------------------------------------------------------------------------------

報表匯出格式Format

http://xxxxx/ReportServer/Pages/ReportViewer.aspx?report&rs:Command=Render&no=xx&rs:Format=pdf
http://xxxxx/ReportServer/Pages/ReportViewer.aspx?report&rs:Command=Render&no=xx&rs:Format=word
http://xxxxx/ReportServer/Pages/ReportViewer.aspx?report&rs:Command=Render&no=xx&rs:Format=excel