Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Отчет с фильтрами, фильтрующими друг друга  [new]
zanderman
Member

Откуда:
Сообщений: 166
Добрый день. Разрабатываю репорт. Репорт - плоская таблица, значения полей которой должны фильтроваться в зависимости от параметров (которые задает пользователь). Но при этом при выборе одного из значений параметров, все остальные перестраиваются "под него". Такая возможность реализована на .net
Возникла проблема с процедурой из которой получается отчет.

Сейчас она в таком виде:
CREATE PROCEDURE stock.StockReportKg 
         @FreshnessCat VARCHAR(max)
	,@Brand VARCHAR(max)
	,@R_Group VARCHAR(max)
	,@Sku_Code INT
	,@Sku_Name VARCHAR(max)
	,@TheYear INT
	,@NameMonth VARCHAR(max)
	,@TheWeek INT
	,@TheDate DATETIME
	,@ChannelName VARCHAR(max)
	,@DwarehouseName VARCHAR(max)
	,@StatusName VARCHAR(max)
	,@ExpiredDate DATETIME
AS
BEGIN
	SELECT p.FreshnessCat
		,p.Brand
		,p.R_Group
		,p.Sku_Code
		,p.Sku_Name
		,t.TheYear
		,t.NameMonth
		,t.TheWeek
		,t.TheDate
		,ch.ChannelEname
		,dw.Dwarehouse
		,st.StatusEname
		,s.StockKg
		,s.ExpiredDate
	FROM stock.Stock s
	LEFT JOIN planning.dbo.products p ON s.ProductID = p.SKU_Code
	LEFT JOIN mart.dbo.[time] t ON s.Thedate = t.TheDate
	LEFT JOIN stock.Channel ch ON s.ChannelID = ch.ChannelID
	LEFT JOIN stock.[Status] st ON s.StatusID = st.StatusID
	LEFT JOIN stock.DWarehouse dw ON s.DWarehouseID = dw.DWarehouseID
	WHERE p.FreshnessCat = @FreshnessCat
		AND p.Brand = @Brand
		AND p.R_Group = @R_Group
		AND p.Sku_Code = @Sku_Code
		AND p.Sku_Name = @Sku_Name
		AND t.TheYear = @TheYear
		AND t.NameMonth = @NameMonth
		AND t.TheWeek = @TheWeek
		AND t.TheDate = @TheDate
		AND ch.ChannelEname = @ChannelName
		AND dw.Dwarehouse = @DwarehouseName
		AND st.StatusEname = @StatusName
END 


Здесь каждый параметр может принимать одно значение. Если же пользователь начнет выбирать несколько значений, то ничего не выйдет. А должно быть так: пользователь может выбирать любые значения параметров и согласно выбранным значениям параметров все другие параметры могут принимать только определенные значения. Подскажите у кого-то был опыт реализации подобного репорта.
19 сен 12, 14:02    [13188854]     Ответить | Цитировать Сообщить модератору
 Re: Отчет с фильтрами, фильтрующими друг друга  [new]
gang
Member

Откуда:
Сообщений: 1394
zanderman,

Ну так опишите вашу логику как
zanderman
согласно выбранным значениям параметров все другие параметры могут принимать только определенные значения
в блоке присвоения значений переменным параметров до того как использовать их в select-e.
19 сен 12, 14:19    [13189104]     Ответить | Цитировать Сообщить модератору
 Re: Отчет с фильтрами, фильтрующими друг друга  [new]
zanderman
Member

Откуда:
Сообщений: 166
gang,

Как вижу это я и как вообщем устроен процесс использования готового репорта:

Пользователь видит перед собой плоскую таблицу. С каким-то количеством комбобоксов-фильтров.
Например: выбирает в комбобоксе Month - 3 месяца - Январь, Февраль, Декабрь.
Эти значения присваиваются параметру Month и передаются в мою процедуру.
Затем я каким-то образом должен распарсить этот параметр, записать в темповую таблицу значения Январь, Февраль, Декабрь и уже из нее в Предложении WHERE присвоить параметру значения.

И так для каждого фильтра. Вопрос главный - правильна ли логика и если да, то что будет с производительностью.
19 сен 12, 15:28    [13189882]     Ответить | Цитировать Сообщить модератору
 Re: Отчет с фильтрами, фильтрующими друг друга  [new]
trew
Member

Откуда: Москва
Сообщений: 2646
zanderman,

WHERE (p.FreshnessCat = ISNULL(@FreshnessCat, FreshnessCat) AND...

или
WHERE (p.FreshnessCat = @FreshnessCat OR @FreshnessCat =@FreshnessCat) AND...

и т.д. для всех параметров.
19 сен 12, 15:40    [13190040]     Ответить | Цитировать Сообщить модератору
 Re: Отчет с фильтрами, фильтрующими друг друга  [new]
zanderman
Member

Откуда:
Сообщений: 166
То, что я описал выше...только для одного параметра
CREATE PROCEDURE stock.StockReportKg @FreshnessCat VARCHAR(100)
AS
DECLARE @input_str VARCHAR(100) = @FreshnessCat
DECLARE @table TABLE (FreshnessCat VARCHAR(100))
DECLARE @delimeter VARCHAR(1) = '$'
DECLARE @pos INT = charindex (
	@delimeter
	,@input_str
	)
DECLARE @id VARCHAR(10)

BEGIN
	WHILE (@pos != 0)
	BEGIN
		SET @id = SUBSTRING(@input_str, 1, @pos - 1)

		INSERT INTO @table (FreshnessCat)
		VALUES (cast(@id AS INT))

		SET @input_str = SUBSTRING(@input_str, @pos + 1, LEN(@input_str))
		SET @pos = CHARINDEX(@delimeter, @input_str)
	END

	SELECT p.FreshnessCat
		,p.Brand
		,p.R_Group
		,p.Sku_Code
		,p.Sku_Name
		,t.TheYear
		,t.NameMonth
		,t.TheWeek
		,t.TheDate
		,ch.ChannelEname
		,dw.Dwarehouse
		,st.StatusEname
		,s.StockKg
		,s.ExpiredDate
	FROM stock.Stock s
	LEFT JOIN planning.dbo.products p ON s.ProductID = p.SKU_Code
	LEFT JOIN mart.dbo.[time] t ON s.Thedate = t.TheDate
	LEFT JOIN stock.Channel ch ON s.ChannelID = ch.ChannelID
	LEFT JOIN stock.[Status] st ON s.StatusID = st.StatusID
	LEFT JOIN stock.DWarehouse dw ON s.DWarehouseID = dw.DWarehouseID
	WHERE p.FreshnessCat IN (
			SELECT FreshnessCat
			FROM @table
			)
END
19 сен 12, 15:43    [13190078]     Ответить | Цитировать Сообщить модератору
 Re: Отчет с фильтрами, фильтрующими друг друга  [new]
Гениально!
Guest
[quot trew]
OR @FreshnessCat =@FreshnessCat
19 сен 12, 15:49    [13190136]     Ответить | Цитировать Сообщить модератору
 Re: Отчет с фильтрами, фильтрующими друг друга  [new]
zanderman
Member

Откуда:
Сообщений: 166
Гениально!
OR @FreshnessCat =@FreshnessCat


Я тоже не понял...

А если ближе к теме, что скажете?
19 сен 12, 17:32    [13191120]     Ответить | Цитировать Сообщить модератору
 Re: Отчет с фильтрами, фильтрующими друг друга  [new]
БлижеКТеме
Guest
zanderman,

А кто Вам мешает заполнить одну (тип,значение) либо несколько(значение) таблиц, в которую(ые) поместить списки значений для каждого параметра.
и джойнить к ним Ваши основные таблицы.
19 сен 12, 17:50    [13191228]     Ответить | Цитировать Сообщить модератору
 Re: Отчет с фильтрами, фильтрующими друг друга  [new]
zanderman
Member

Откуда:
Сообщений: 166
БлижеКТеме
zanderman,

А кто Вам мешает заполнить одну (тип,значение) либо несколько(значение) таблиц, в которую(ые) поместить списки значений для каждого параметра.
и джойнить к ним Ваши основные таблицы.


И что это даст?
По сути такие таблицы (вренее процедуры с представлениями внутри есть) для каждого комбобокса для того,чтобы пользователь сделал выбор.
Но мне то обратно возвращается параметр из приложения. И на этом этапе такие таблицы мне никак не пригодятся.
19 сен 12, 17:59    [13191272]     Ответить | Цитировать Сообщить модератору
 Re: Отчет с фильтрами, фильтрующими друг друга  [new]
беда-беда прям
Guest
zanderman
Но мне то обратно возвращается параметр из приложения. И на этом этапе такие таблицы мне никак не пригодятся.

Так пусть Ваше приложение вместо этого наполняет таблицу(ы) с параметрами для отчета
19 сен 12, 18:07    [13191312]     Ответить | Цитировать Сообщить модератору
 Re: Отчет с фильтрами, фильтрующими друг друга  [new]
zanderman
Member

Откуда:
Сообщений: 166
беда-беда прям,

я конечно дико извиняюсь, а чем же занимается мой скрипт?)
19 сен 12, 18:18    [13191346]     Ответить | Цитировать Сообщить модератору
 Re: Отчет с фильтрами, фильтрующими друг друга  [new]
и действительно ;)
Guest
zanderman,
как по мне - так он занимается тем, что нагружает сервер.

не проще ли на уровне клиента не спихивать список в одну строку, которую потом необходимо повторно парсить, а сразу помещать значения в таблицу параметров, а затем запускать процедуру, которая по этим параметрам выберет требуемые данные без лишних циклов.
19 сен 12, 18:24    [13191370]     Ответить | Цитировать Сообщить модератору
 Re: Отчет с фильтрами, фильтрующими друг друга  [new]
zanderman
Member

Откуда:
Сообщений: 166
и действительно ;)
zanderman,
как по мне - так он занимается тем, что нагружает сервер.

не проще ли на уровне клиента не спихивать список в одну строку, которую потом необходимо повторно парсить, а сразу помещать значения в таблицу параметров, а затем запускать процедуру, которая по этим параметрам выберет требуемые данные без лишних циклов.


Но параметр все равно же останется строкой, которую необходимо будет распарсить... иил вы предлагаете выбранные характеристики не запихивать в параметр? А инсертить их в какуюнть таблицу?
19 сен 12, 18:30    [13191400]     Ответить | Цитировать Сообщить модератору
 Re: Отчет с фильтрами, фильтрующими друг друга  [new]
Бинго!
Guest
zanderman
вы предлагаете выбранные характеристики не запихивать в параметр? А инсертить их в какуюнть таблицу?

именно.
при чем, можно использовать 1 постоянную таблицу типа ReportParams(SPID,ParamName,ParamValue), либо вообще передавать в процедуру табличную переменную(тогда SPID не нужен будет)
19 сен 12, 18:35    [13191424]     Ответить | Цитировать Сообщить модератору
 Re: Отчет с фильтрами, фильтрующими друг друга  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
zanderman
и действительно ;)
не проще ли на уровне клиента не спихивать список в одну строку, которую потом необходимо повторно парсить,

а сразу помещать значения в таблицу параметров, а затем запускать процедуру, которая по этим параметрам выберет требуемые данные без лишних циклов.

Есть табличный тип данных. Соответственно параметр в процедуре может быть такого типа. BOL.
19 сен 12, 18:39    [13191439]     Ответить | Цитировать Сообщить модератору
 Re: Отчет с фильтрами, фильтрующими друг друга  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Бинго!
можно использовать 1 постоянную таблицу типа ReportParams(SPID,ParamName,ParamValue)
Глобальные переменные, и 21 веке то? Опаздываем.

Нарушает принцип модульности.
19 сен 12, 18:41    [13191447]     Ответить | Цитировать Сообщить модератору
 Re: Отчет с фильтрами, фильтрующими друг друга  [new]
zanderman
Member

Откуда:
Сообщений: 166
Mnior,

а что вы скажите по поводу моей реализации?
комбобоксы с мультиселектом далеко неадекватное задание конечно, как по мне.
19 сен 12, 18:46    [13191468]     Ответить | Цитировать Сообщить модератору
 Re: Отчет с фильтрами, фильтрующими друг друга  [new]
zanderman
Member

Откуда:
Сообщений: 166
zanderman,

Я так понимаю лучше устроить парсинг на уровне SQL Serverа, чем на ASPe. И оттуда передавать табличные переменные.
19 сен 12, 18:48    [13191480]     Ответить | Цитировать Сообщить модератору
 Re: Отчет с фильтрами, фильтрующими друг друга  [new]
zanderman
Member

Откуда:
Сообщений: 166
Ну и так, чтобы понять задачу мою до конца думаю стоит посмотреть вот это:

http://demos.devexpress.com/ASPxEditorsDemos/ASPxComboBox/ClientAPI.aspx

У меня будет 1 в 1 практически.
19 сен 12, 18:49    [13191486]     Ответить | Цитировать Сообщить модератору
 Re: Отчет с фильтрами, фильтрующими друг друга  [new]
zanderman
Member

Откуда:
Сообщений: 166
Вот мой окончательный вариант для одного параметра пока:
CREATE PROCEDURE stock.StockReportKg @FreshnessCat VARCHAR(100)
AS
DECLARE @input_str VARCHAR(100) = @FreshnessCat
DECLARE @table TABLE (FreshnessCat VARCHAR(100))
DECLARE @delimeter VARCHAR(1) = '$'
DECLARE @pos INT = charindex (
	@delimeter
	,@input_str
	)
DECLARE @id VARCHAR(10)

BEGIN
	WHILE (@pos != 0)
	BEGIN
		SET @id = SUBSTRING(@input_str, 1, @pos - 1)

		INSERT INTO @table (FreshnessCat)
		VALUES (cast(@id AS VARCHAR(10)))

		SET @input_str = SUBSTRING(@input_str, @pos + 1, LEN(@input_str))
		SET @pos = CHARINDEX(@delimeter, @input_str)
	END

	SELECT p.FreshnessCat
		,p.Brand
		,p.R_Group
		,p.Sku_Code
		,p.Sku_Name
		,t.TheYear
		,t.NameMonth
		,t.TheWeek
		,t.TheDate
		,ch.ChannelEname
		,dw.Dwarehouse
		,st.StatusEname
		,s.StockKg
		,s.ExpiredDate
	FROM stock.Stock s
	LEFT JOIN planning.dbo.products p ON s.ProductID = p.SKU_Code
	LEFT JOIN mart.dbo.[time] t ON s.Thedate = t.TheDate
	LEFT JOIN stock.Channel ch ON s.ChannelID = ch.ChannelID
	LEFT JOIN stock.[Status] st ON s.StatusID = st.StatusID
	LEFT JOIN stock.DWarehouse dw ON s.DWarehouseID = dw.DWarehouseID
	WHERE (
			(
				p.FreshnessCat IN (
					SELECT DISTINCT FreshnessCat
					FROM @table
					)
				)
			OR (
				- 999 = (
					SELECT TOP 1 FreshnessCat
					FROM @table
					)
				)
			)
END


Значение -999, когда выбраны все значения в комбобоксе.
19 сен 12, 19:01    [13191554]     Ответить | Цитировать Сообщить модератору
 Re: Отчет с фильтрами, фильтрующими друг друга  [new]
Увы мне
Guest
Mnior
Глобальные переменные, и 21 веке то? Опаздываем.

Увы, не всем был дан билет в 21 век. В силу работы с sybase ASE, о табличных переменных и параметрах приходится лишь мечтать.
Зато в таком варианте можно из любого интерфейса клиента начитывать параметры :-)
19 сен 12, 19:05    [13191572]     Ответить | Цитировать Сообщить модератору
 Re: Отчет с фильтрами, фильтрующими друг друга  [new]
Ёпрст
Guest
zanderman,

А Вы точно беспокоитесь за производительность?
19 сен 12, 19:08    [13191584]     Ответить | Цитировать Сообщить модератору
 Re: Отчет с фильтрами, фильтрующими друг друга  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
zanderman
Я так понимаю лучше устроить парсинг на уровне SQL Serverа, чем на ASPe. И оттуда передавать табличные переменные.
Нет, как раз наоборот. .Net всё нативно поддерживает (так и передавайте DataTable в качестве параметра). Разве что я не сильно силён в ASP но уверен что там делается всё остальное в пару кликов.

Более того, даже если комуто не повезло, то можно просто наполнять табличную переменную командно, неважно PHP там или иное извращение.
Тупо раз написать функцию которая дополняет перед вызовов объявление переменной и её наполнение (подготавливает текст и параметры для каждой строки).
+ AddTableParam псевдокод
function AddTableParam($ConnectOrStatement, $ParamName, $ParamType, $ArrayOrJSON) {
 ...
  $Query += 'DECLARE @' + $ParamName ' ' + $ParamType + '; INSERT @' + $ParamName ' VALUES (';
  while ($ArrayOrJSON -> $Row) {
    while($Row -> $Column) {
      $QueryParams += $Param = { Name = '@' + $Column.Name + $Counter, Value = $Column.Value };
      $Query += $Param.Name + ',';
    }
    // Stuff
    $Query += '),(';
  }
  // Stuff
 ...
}
Смысл чуть падает, но точно лучше. (этот пример не для вас, zanderman, он для потерпевших)
zanderman
Ну и так, чтобы понять задачу мою до конца думаю стоит посмотреть вот это:
http://demos.devexpress.com/ASPxEditorsDemos/ASPxComboBox/ClientAPI.aspx
У меня будет 1 в 1 практически.
Я наверно тупой. Не понял нифига. Где там множественный выбор? Два значения.

PS: И ещё, ради получения плоских данных процедуру не делают, их обычно для изменений применяют.
Тут пойдёт параметризованное представление (inline function).
19 сен 12, 22:05    [13192426]     Ответить | Цитировать Сообщить модератору
 Re: Отчет с фильтрами, фильтрующими друг друга  [new]
zanderman
Member

Откуда:
Сообщений: 166
Ёпрст
zanderman,

А Вы точно беспокоитесь за производительность?


Безусловно, потому и обратился на форум.
20 сен 12, 10:32    [13194079]     Ответить | Цитировать Сообщить модератору
 Re: Отчет с фильтрами, фильтрующими друг друга  [new]
zanderman
Member

Откуда:
Сообщений: 166
Mnior
Я наверно тупой. Не понял нифига. Где там множественный выбор? Два значения.


Там же пример на вариант с двумя фильтрами.
В моем случае их на порядок больше.
Сегодня отпишусь по поводу того, что получилось с моим вариантом.
20 сен 12, 10:36    [13194108]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить