پاس دادن پارامتر از داخل گزارش به کوئری در حالت Direct Query

در این پست به بررسی امکان پاس دادن پارامتر ورودی به کوئری مورد نظر در حالت Direct Query می پردازیم. در این حالت شما این امکان رو در Power BI دارید که کوئری مورد نظرتون رو به صورت داینامیک شرط گذاری کنید. و در ازای ورود و انتخاب مقدار یا مقادیر مورد نظرتون داده های مورد انتظار رو دریافت کنید. برای ساده تر شدن راهکار از یک مثال ساده استفاده می کنیم. برای ایجاد این مثال از کد زیر می تونید استفاده کنید:

USE test
GO

DROP TABLE test_table
GO

CREATE TABLE [dbo].[test_table](
	Title VARCHAR(10) NOT NULL,
	Qty INT NOT NULL
)
GO


INSERT INTO test_table VALUES
('A', 100),
('B', 200),
('C', 300),
('D', 400),
('E', 500)

جهت یادآوری: لازمه دیتابیس مورد نظر خودتون رو انتخاب کنید.

اما مزایای روش Direct Query:

• در این روش بر خلاف روش import داده ای ذخیره نمیشه همین هم Direct Query رو روش خوبی برای کار با حجم زیاد داده کرده

• آخرین و به روز ترین داده ها رو در اختیار دارید

• محدودیت حجم 1 گیکاباتی در این روش وجود ندارد

برای استفاده از این روش کافیه گزینه Direct Query رو از قسمت Data connectivity mode انتخاب کنید.

مثال اول: پارامتر تک مقدار

در این مثال شرط شما، شامل یک مقدار خواهد بود. به این صورت که با انتخاب Title مورد نظر از داده ها، یک Native Query به سمت دیتابیس می رود و خروجی به ازای انتخاب شما برگردانده می شود.

قدم اول: ورود داده در حالت Direct Query

ابتدا کوئری مورد نظر رو به صورت یک شرط ثابت و در حالت Direct Query وارد می کنیم.

SELECT * FROM test_table WHERE Title='A'

خروجی:

در صورتی که پنجره Advanced Editor رو چک کنید، عبارت M زیر را خواهیم داشت:

let
    Source = Sql.Database("localhost", "test", [Query="SELECT * FROM test_table WHERE Title='A'", CreateNavigationProperties=false])
in
    Source

قدم دوم: ایجاد Parameter

برای ساخت پارامتر از تب Home گزینه Manage Parameters  یا New Parameter رو انتخاب می کنیم.

در پنچره باز شده پارامتری با نام Parameter و مقدار پیش فرض A می سازیم.

قدم سوم: استفاده از Parameter در M Language

قبل از استفاده از پارامتر، صرفا جهت خوانا بودن عبارت M، کوئری نوشته شده رو با استفاده از متغیری به نام query به Source پاس می دهیم به این صورت:

let
    query = "SELECT * FROM test_table WHERE Title='A'",
    Source = Sql.Database("localhost", "test", [Query=query, CreateNavigationProperties=false])
in
    Source

نکته: دقت کنید کاما آخر خط فراموش نشود در غیر این صورت با خطا مواجه می شوید.

برای وارد کردن پارامتر ایجاد شده عبارت مقابل query رو به این شکل تغییر می دهیم:

query = "SELECT * FROM test_table WHERE Title=" & "'" & Parameter & "'"

در این عبارت پارامتر ساخته شده یعنی Parameter، جایگزین مقدار A که به صورت ثابت در شرط وجود داشت می شود. دقت کنید! که لازمه Single Quotation ها رو هم در عبارت لحاظ کنید.

در این عبارت پارامتر ساخته شده یعنی Parameter، جایگزین مقدار A که به صورت ثابت در شرط وجود داشت می شود. دقت کنید! که لازمه Single Quotation ها رو هم در عبارت لحاظ کنید.

قدم چهارم و آخر: نحوه Bind فیلد مورد نظر به پارامتر

برای استفاده از پارامتر، لازمه فیلدی که حاوی مقادیر برای شرط هست رو به پارامتر bind کنیم. برای این کار جدولی دیگه شامل صرفا Title ها هم ایجاد کردم و ستون این جدول رو به Parameter وصل می کنم. برای اتصال، از تب Model بر روی ستون مورد نظر کلیک کنید و از قسمت Properties و بخش Advanced و Bind to parameter، از لیست باز شده نام پارامتر ساخته شده رو انتخاب کنید.

اگر slicer ای از مقادیر ستون bind شده ایجاد کنیم، به ازای هر انتخاب شرط کوئری به مقدار انتخابی تغییر میکنه و داده های مورد نظر از دیتابیس گرفته میشه.

دو نکته در این مثال قابل توجه است:

• در صورت اجرا و انتخاب مقدار، هشداری به صورت زیر دریافت خواهید کرد. که برای رفع اون لازمه تیک Require user approval for new native database queries از پنچره Options و قسمت Security رو بردارید. البته به موارد امنیتی دقت کنید و قبل از استفاده حتما در موردش تحقیق کنید!

• اما نکته دوم این که در مثال اول فقط یک مقدار به عنوان پارامتر قابل پاس دادن است. اگر مجموعه از مقادیر رو انتخاب کنید با خطا رو به رو می شوید. این خطا نشان می دهد که امکان استفاده از عملگر & رو در عبارت M ندارید. یعنی لیست رو نمی تونیم به صورت متن به کوئری پیوست کنیم.

مثال دوم: پارامتر چند مقداره و Select All

روند کار برای این مثال کاملا مشابه مثال اول است با این تفاوت که عبارت M نوشته شده به گونه ای است که در صورت انتخاب چند گزینه و همچنین Select All، با استفاده از شرط هایی که گذاشته شده امکان تغییر کوئری به سه صورت فراهم می شود.

پارامتر رو با نام ParameterMultiple نعریف می کنم و مقدار پیش فرض رو برابر SelectAll قرار می دهم.

عبارت M رو به صورت زیر در حالت های مختلف می نویسیم:

let
    selected_values = if Type.Is(Value.Type(ParameterMultiple), List.Type) then 
                        Text.Combine({"'", Text.Combine(ParameterMultiple, "','") , "'"})
                      else  
                        Text.Combine({"'" , ParameterMultiple , "'"}),
    query = if ParameterMultiple = "__SelectAll__" or List.Contains(ParameterMultiple, "__SelectAll__") then
                "SELECT * FROM test_table" 
            else 
                "SELECT * FROM test_table WHERE Title IN(" & selected_values & ")",
    Source = Sql.Database(".", "test", [Query=query, CreateNavigationProperties=false])
in
    Source

1. در صورتی که پارامتر به از نوع لیست بود، لازمه مقادیر لیست به متن تبدیل شوند و هر کدوم از مقادیر با کاما از هم جدا شوند. به طور مثال در صورت انتخاب A و B از slicer، مقادیر پارامتر که از جنس لیست می باشند رو به متن تبدیل و با کاما جدا می کنیم یعنی خروجی به این صورت می شود ” ‘A’, ‘B’ “.

2. در صورتی که جنس پارامتر از نوع لیست نبود و به صورت متن بود مشابه مثال اول یک مقدار پاس داده می شود که تنها لازم است Single Quotation در دو طرف آن قرار داد. یعنی خروجی به این صورت میشود ” ‘A’ “

3. در صورتی که پارامتر برابر SelectAll بود کوئری بدون شرط برقرار می شود.

4. در صورتی که مقدار SelectAll انتخاب نشده بود عبارت مقابل selected_values که در شماره 1 و 2 بوجود آمدند به کوئری ضمیمه می شوند. دقت کنید که در کوئری این قسمت از IN برای گرفتن چند شرط استفاده شده.

و آخرین نکته، این که گزینه Multi-select و  Select All رو حتما فعال کنید.