Category Archives: SQL RS

RS 的過濾條件用 OR

但就介面只能設定 AND,可以透過 VB.NET 設定 OR

image

運算式以 VB.NET 定義 Or 條件

image

值要求 true

image

透過 RS 呈現 R 的圖形

參考:https://www.mssqltips.com/sqlservertip/4127/sql-server-2016-r-services-display-r-plots-in-reporting-services/

我寫的範例報表 ShowRImage.rdl

透過 T-SQL 呼叫 R Script 可以直接傳回圖檔的 binary 結果,就可以透過影像物件呈現

以如下的 R-Script 為例

EXEC   sp_execute_external_script
      @language = N’R’
     ,@script = N’    df <- inputDataSet; #read input data
                image_file = tempfile(); #create a temporary file
                jpeg(filename = image_file, width=500, height=500); #create a JPEG graphic device
                hist(df$Ages); #plot the histogram
                dev.off(); #dev.off returns the number and name of the new active device (after the specified device has been shut down). (device = graphical device)
                #file() opens a file, in this case the image. rb = read binary
                #readBin() reads binary data. what = described the mode of the data. In this case, it"s raw data. n = maximum number of records to read.
                #data.frame converts the data to a data frame, which is required as output by SQL Server. The result is written to the OutputDataset variable.
                OutputDataset <- data.frame(data=readBin(file(image_file,"rb"),what=raw(),n=1e6));
                    ‘
    ,@input_data_1 = N’SELECT Ages = DATEDIFF(YEAR,[BirthDate],GETDATE())
                        FROM [AdventureWorksDW].[dbo].[DimCustomer];’
    ,@input_data_1_name = N’inputDataSet’
    ,@output_data_1_name = N’OutputDataset’
WITH RESULT SETS ((plot varbinary(max)));

從 SSMS 的執行結果可以取到 varbinary(max) 的結果:

image

在 Report Builder 就以這段 T-SQL 當作資料集

image

在報表中加入影像物件,設定來源就是資料庫傳回:

image

透過 Report Builder 和 Report Server 都可以正常執行:

image

image

RS 訂閱報表採用 Windows 檔案共用時,輸出的檔案名稱包含日期

感謝聽課的朋友提醒,在檔案名稱欄位加上 @Timestamp

image

輸出如下的檔案

image

在 SharePoint 上透過 Power View 存取 AS Tabular Model 時要換身分

感謝微軟 Ray 告知的解法,在 SharePoinrt 上要用 Report Data Source(Data Source Type 選擇 Microsoft BI Semantic Model for Power View) 而非 BI Semantic Model Connection

image

詳細作法參考以下網址:

http://whitepages.unlimitedviz.com/2012/04/fixing-access-errors-when-creating-a-bism-connection-for-powerview/

讓 RS 可以存取 AS 的 Tabular Model

參考以下的網址

https://www.simple-talk.com/sql/database-administration/using-dax-to-create-ssrs-reports-the-basics/

補充一點,若要設定 DAX,也可以直接點 Expression 按鈕,而不必如文中叫起 Query Designer

image

image

我的 Sample Code

RS 呼叫自訂的組件,產生背景圖

範例程式:CustomAssemblyGenerateBackgroundImage

聽課的朋友需要 Report 的報表自動帶某些文字的浮水印,所以我建立一個 .NET 自訂組件如下

簡單建立輸入文字後產生 Bitmap 圖的 byte array 函數

using System;
using System.Text;
using System.Drawing;
using System.Drawing.Text;
using System.Drawing.Imaging;
using System.Drawing.Drawing2D;
using System.IO;
namespace GenerateImages
{
    public class Class1
    {
        public static byte[] CreateBitmapImage(string sImageText)
        {
            Bitmap objBmpImage = new Bitmap(1, 1);

            int intWidth = 0;
            int intHeight = 0;

            Font objFont = new Font("Arial", 40, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Pixel);
            Graphics objGraphics = Graphics.FromImage(objBmpImage);
            intWidth = (int)objGraphics.MeasureString(sImageText, objFont).Width;
            intHeight = (int)objGraphics.MeasureString(sImageText, objFont).Height;
            objBmpImage = new Bitmap(objBmpImage, new Size(intWidth, intHeight));
            objGraphics = Graphics.FromImage(objBmpImage);
            objGraphics.Clear(Color.White);
            objGraphics.SmoothingMode = SmoothingMode.AntiAlias;
            objGraphics.TextRenderingHint = TextRenderingHint.AntiAlias;
            objGraphics.DrawString(sImageText, objFont, new SolidBrush(Color.FromArgb(102, 102, 102)), 0, 0);
            objGraphics.Flush();

            //傳回 Bitmap 物件 RS 不能用,需要 byte array
            //objBmpImage.Save(@"C:\inetpub\images\test.bmp");
            //return (objBmpImage);
            MemoryStream ms = new MemoryStream();
            objBmpImage.Save(ms,ImageFormat.Bmp);
            Byte[] bt=ms.ToArray();
            return (bt);
        }

    }
}

透過 gacutil 註冊

image

並修改 在 C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer\rssrvpolicy.config 設定可信賴的 Assembly

<CodeGroup
               class="UnionCodeGroup"
               version="1"
               PermissionSetName="FullTrust"
               Name="GenerateImages.Class1"
               Description="產生 image">
                          <IMembershipCondition
                               class="UrlMembershipCondition"
                               version="1"
                               Url="C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer\bin\GenerateImages.dll" />

</CodeGroup>

RS 2014 似乎只能參照 NET Framework 2.0

image

報表背景呼叫組件

image

透過參數設定背景要產生的文字

image

透過 T-SQL 發送訂閱報表

use reportserver
go
declare @eventData uniqueidentifier
select @EventData=[EventData] from [dbo].[Subscriptions] s
join [dbo].[ReportSchedule] r on s.SubscriptionID=r.SubscriptionID
join [dbo].[Schedule] c on r.ScheduleID=c.ScheduleID
where s.Description=N'<建立訂閱時的描述>’
exec [ReportServer].dbo.AddEvent @EventType=’TimedSubscription’, @EventData=@eventData

image

Where 要填的內容就是上述的描述欄位

透過設定報表變數改變單雙列的背景顏色

建立報表變數 LineTotalNo 後

image

在報表增加函數

Public Function SetNo(val as Microsoft.ReportingServices.ReportProcessing.OnDemandReportObjectModel.Variable)  As Integer
    val.Value = val.Value + 1
    Return val.Value
End Function

Public Function GetNo(val as Microsoft.ReportingServices.ReportProcessing.OnDemandReportObjectModel.Variable)  As Integer
    Return val.Value
End Function

一個格子設定變數值,其他的格子取值就可以設定背景顏色單雙列不同

image
簡單報表範例:

https://onedrive.live.com/?cid=BF14192BD27975CB&id=bf14192bd27975cb%212277

紀錄 Reporting Services 的訂閱執行歷程

因為 RS 的訂閱 log 是寫在 file 內,預設 system table Subscriptions 只有 laststatus,因應朋友 Jay 的需求,他提醒可以用 trigger 來記錄:

use ReportServer
go
SELECT [SubscriptionID]
      ,[LastStatus]
      ,[LastRunTime] into tbRecord
  FROM [ReportServer].[dbo].[Subscriptions]
  where 1=0
  go

 

create trigger trgSub on Subscriptions
  for update,insert
  as
  insert tbRecord select [SubscriptionID]
      ,[LastStatus]
      ,[LastRunTime] from inserted
go

select * from Subscriptions
select * from tbRecord

 

 

此外,要小心規劃清掉 log 的排程

透過 PowerShell 部署 AS DB 和 rdl 到 SharePoint

SQL 制式教材內,透過 PowerShell 部署 AS DB 和 rdl 到 SharePoint 的 Script 可以參考:

#執行 xmla 部署 AS DB

Import-module sqlps
Import-module sqlascmdlets
Invoke-ASCmd -Inputfile:"$env:SUBDIR\SetupFiles\SetupSSAS.xmla" -Server:"localhost"

 

#部署  rdl 到 SharePoint

Add-PSSnapin Microsoft.SharePoint.PowerShell
$siteName = "http://localhost/sites/adventureworks"
$docLibName = "Reports"

$web = Get-SPWeb $siteName
$docLib = $web.Lists[$docLibName]
$rootFolder = $docLib.RootFolder
$folderURL = $rootFolder.Url
$folder = $web.GetFolder($folderURL)
foreach ($file in $folder.Files) {
  $docLib.Items.DeleteItemById($file.Item.Id)
}

$subs = @()

foreach($subfolder in $folder.SubFolders) {
  if ($subfolder.Name -ne "Forms") {
      $subs = $subs + $subfolder.Url
  }
}

foreach ($url in $subs) {
  $folder.SubFolders.Delete($url)
}

$folderName = "$env:SUBDIR\SetupFiles\Reports"
$files =  ([System.IO.DirectoryInfo] (Get-Item $folderName)).GetFiles() | ForEach-Object {
    $fileStream = ([System.IO.FileInfo] (Get-Item $_.FullName)).OpenRead()
    $content = New-Object byte[] $fileStream.Length
    $fileStream.Read($content, 0, [int]$fileStream.Length);
    $fileStream.Close()
    $spFile = $rootFolder.Files.Add($rootFolder.Url + "/" + $_.Name, $content, $true)
}