Multi Select Type-in Parameter

Nov 22, 2006

Hi All

Can anyone tell me whether or not it is
possible to multi select when you have a parameter
that is set as non-querried in order for it to be
typed instead of selected.

My users prefer typing the values and selecting
more than one. But at the moment I cant give them both..

I'm using SSRS with SSAS cube all in BI all 2005

Please help. I suspect that if it's possible it may just be a
syntax thing but I am yet to find it.

Thanks in advance

Gerhard Davids

View 5 Replies


ADVERTISEMENT

String Type Multi-Valued Parameter Issue

Jul 3, 2007

Hi There.



I have a Multi-valued parameter that is a string type and it freaks out when a do a select all from my drop down list. I suspect it has something to do with size as it works great if a pick a reasonable amount of items. My parameter list is populated by 1463 items of 12 characters each. Is there a threshold I should have to worry about?



Thanks, Mike

View 9 Replies View Related

Remove Select All Options From Multi Select Parameter Dropdown

Jun 8, 2007

Hi All



I am using SQL Server 2005 with SP2. I have multi select parameter in the report. In SP2 reporting services gives Select All option in the drop down.



Is there any way I can remove that option from the list?



Thanks

View 4 Replies View Related

Multi Select Parameter From Function - Select All?

Sep 6, 2007



I am using RS 2000. I have a multi select parameter where I can select multiple states by separating with a comma. I am trying to figure out how to incorporate an "All" parameter.

Query:

Select [name], city, state, zipcode
From Golf inner join charlist_to_table(@State,Default)f on State = f.str

Function:

CREATE FUNCTION charlist_to_table
(@list ntext,
@delimiter nchar(1) = N',')
RETURNS @tbl Table (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@tmpstr nvarchar(4000),
@leftover nvarchar(4000),
@tmpval nvarchar(4000)
SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
SET @textpos = @textpos + @chunklen
SET @pos = charindex(@delimiter, @tmpstr)
WHILE @pos > 0
BEGIN
SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))
INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
SET @pos = charindex(@delimiter, @tmpstr)
END
SET @leftover = @tmpstr
END
INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)),
ltrim(rtrim(@leftover)))
RETURN
END
GO


Anyone have any ideas?

Thanks,
Deb

View 5 Replies View Related

Select Values From Multi-value Parameter

Oct 16, 2006

Is it possible to pass values from UI to a multi-value parameter in a report and from this report, select values from this multi-value parameter to finally display data?

Thanks!

View 5 Replies View Related

Multi Select Parameter Problem

May 17, 2007

I have a multi-select parameter for a report, but If I select more than one value the report does not return any data. However, if only one value is selected data for that value is returned. I am using the IN option for the SP parameter.



SELECT Code,Description FROM Product

WHERE Code > 0 AND Description IN (@Description)
I am using SP2 on SQL2005, also I do not have the option of Select-All which I expected to be present.
Any help would be most appreiated.

Thanks

JohnJames

View 3 Replies View Related

Select All As Default For A Multi-value Parameter

Feb 22, 2007

Hi!

I have the following problem:

In my report I have -among others- a multi-value parameter, populated by a query (so I cannot a priori know the content of the list).

I would like my report to start without any user choice, through default parameters, so what I need is the "select all" choice selected by default. How can I achieve this?

[The only default value I am able to pass to the multi-value parameter is one of the elements populating the list, statically writing it in the "Non-queried" section of "Default values": "From query" option seems not to work for multi-valued]

Any help will be greatly appreciated!

Thanks

Stefano

View 7 Replies View Related

Multi Parameter Select Query

Apr 4, 2007

Hi All,



I have a procdeure as written below.

I have created datasets in te report and in Report parameters clicked the Multi-value Parameter option.



When I run the report, I get all the customer names, when I select one customer report returns correct data. When I select two customers in the list box, the result set is empty. Can anyone guide me on what the error could be?



Thanks

Josh



Procedure:



create procedure MyMultiReport @customername nvarchar(30), @businessplantype nvarchar(30), @businessplanyear nvarchar(10) as
Select PlanDatameta.sort,sysperiod.id,Planmeta.id,Planmonthlydata.Productmainpkey,Country, BusinessDivisiondescription, PlanSegmentPkey, Plantext.referencepkey, Plantext.usage, sheet, name, Plantext.text, Brand, Size, text1, PlanDatameta.sort+' '+Plantext1.text as LineDescription,line, Month1, Month2, Month3, Month4, Month5, Month6, Month7, Month8, Month9, Month10, Month11, Month12, Total from Planmonthlydata join Plantext on Plantext.referencepkey=Planmonthlydata.Plansegmentpkey join PlanDatameta on PlanDatameta.pkey=Planmonthlydata.PlanDatametapkey join Productdescription on Productdescription.Productmainpkey=Planmonthlydata.Productmainpkey join Productmain on Productdescription.Productmainpkey=Productmain.pkey join Plansegment on Plansegment.pkey=Planmonthlydata.Plansegmentpkey join bpamain on bpamain.pkey=Plansegment.bpamainpkey join sysperiod on sysperiod.pkey=Plansegment.sysperiodpkey join Planmeta on Planmeta.pkey=Plansegment.Planmetapkey join Plantext Plantext1 on PlanDatameta.pkey=Plantext1.referencepkey where Planmonthlydata.status<>'d' and (PlanDatameta.sheet='PlanProductSummary') and Plantext.text<>'' and (PlanDatameta.line='MyPlanBaselineVolumeBasic' or PlanDatameta.line='BaselineVolumes' or PlanDatameta.line='IncrementalVolumes'or PlanDatameta.line='TotalVolumes') and name in (@customername) order by PlanDatameta.sort,Plantext.text,text1return

View 2 Replies View Related

Store Multi-Select Values In An Image Data Type?

Dec 20, 2005

I was working on figuring out where a certain application wasstoring the multiple selection choices I was doing through the app.I finally figured out that they were being store in an IMAGEdata type colum with the variable length of 26 bytes.This is the first time I ran into such way of storing multipleselections in a single Image data type.Is this a better alternative than to store into a One-to-Manytables? If so then I'll have to consider using the Image datatype approach next time I have to do something like storing1 to thousands of selections.Thank you

View 4 Replies View Related

Multi-Select Parameter Issues - DataType Also?

Jan 9, 2007

I've tried several things to get my multi-select parameter to take more than one value but it seems to only take the 1st one I input. I've been working on this for days and been trying alternate methods but nothing seems to work. I think my first question is if I'm planning on using a parameter as a multi-select parameter what is the data type I should call that parameter. In my case here, the evt_key is unique and will always be 36 characters. I cant imagine in my lifetime that I'm going to have any more than 20 items in the multiselect. Am I looking over something or any other suggestions? I also attached my sp at the bottom.

Thanks

____________________________________________________________________

CREATE procedure dbo.rpt_client_ngcoa_event_registration_attendees_by_state
@evt_key varchar(4000)
as

set nocount on
set transaction isolation level read uncommitted

create table #tmp_dates
(
tmp_evt_key varchar(4000),
tmp_title nvarchar(160),
tmp_start_date datetime
)

create table #temp
(
tmp_key varchar(4000), -- event
evt_title nvarchar(160),
reg_key varchar(38),
reg_session_code nvarchar(400),
type nvarchar(20),
reg_name nvarchar(300),
reg_sort nvarchar(300),
reg_org nvarchar(300),
reg_date datetime,
amount_paid money,
adr_city_state_code NVARCHAR(110) NULL,
adr_state NVARCHAR(40) NULL,
adr_country NVARCHAR(60) NULL,
adr_city NVARCHAR(40) NULL,
adr_post_code NVARCHAR(20) NULL,
src_code NVARCHAR(50) NULL,
days_out int
)

/*
-- FOR MUTLI SELECT FUNCTIONALITY --
create table #tempet
(
tmpet_key varchar(4000)
)

if @evt_key = '' or @evt_key is null

begin
insert #tempet
select distinct evt_key
from ev_event
where evt_key in ('00394886-dfc7-4466-b674-1f2b3ede79ea', '10BE664D-DA4F-44F4-802C-ABD4FC015FB4','56504014-9787-4207-8FAD-EC6C6A384C1D')
and evt_delete_flag = 0
end

else
begin
insert #tempet
exec _selectstringfromstring @idstring = @evt_key,@intorstring='S'
end

if @evt_key = '' goto finalselect
set @evt_key = dbo.av_preprptguidparam(@evt_key,'returnnull')


*/



IF @evt_key='' SELECT @evt_key=NULL





insert into #tmp_dates
select distinct
reg_evt_key,
evt_title,
evt_start_date
from ev_registrant
join ev_event on reg_evt_key = evt_key
--join #tempet on evt_key=tmpet_key
where reg_delete_flag = 0
and evt_delete_flag = 0 --and reg_evt_key = '10BE664D-DA4F-44F4-802C-ABD4FC015FB4'
and (@evt_key is null or (@evt_key is not null and reg_evt_key = @evt_key))
---AND (@evt_key IS NULL OR (@evt_key IS NOT NULL AND reg_evt_key IN (SELECT item FROM dbo.av_SelectStringFromString(@evt_key,','))))


insert into #temp
select tmp_evt_key,
tmp_title,
reg_key,
-- net_prc_code,
case net_prc_code when null then tmp_title
when '' then tmp_title
when 'NULL' then tmp_title
when ' ' then tmp_title
else net_prc_code end,
'Reg Code',
cst_ind_full_name_dn,
cst_sort_name_dn,
cst_org_name_dn,
dbo.av_end_of_day(reg_add_date),
net_payamount,
adr_city_state_code,
adr_state,
adr_country,
adr_city,
adr_post_code,
src_code,
datediff(dd, reg_add_date, tmp_start_date)
from #tmp_dates
join ev_registrant on reg_evt_key = tmp_evt_key
join vw_ac_invoice_detail on reg_ivd_key = net_ivd_key
join co_customer on cst_key = reg_cst_key
LEFT JOIN co_customer_x_address x ON x.cxa_key = reg_cxa_key AND cxa_delete_flag = 0
LEFT JOIN co_address a ON a.adr_key = x.cxa_adr_key AND adr_delete_flag = 0
LEFT JOIN co_source_code o ON src_key = reg_src_key AND src_delete_flag = 0
--join #tempet on reg_evt_key=tmpet_key
where reg_delete_flag = 0
and reg_cancel_date is null
and cst_delete_flag = 0
AND (@evt_key IS NULL OR (@evt_key IS NOT NULL AND tmp_evt_key IN (SELECT item FROM dbo.av_SelectStringFromString(@evt_key,','))))



finalselect:
select *
from #temp
--where (@evt_key IS NULL OR (@evt_key IS NOT NULL AND tmp_key IN (SELECT item FROM dbo.av_SelectStringFromString(@evt_key,','))))
order by days_out desc, type, reg_name
GO

View 5 Replies View Related

Expression Problem Using A Multi-Select Parameter

Apr 24, 2007

I have a rectangle region in a report that contains a graph and a table. I want to display that list region only when the user selects a "Select All" from a multi-select report parameter. This rectangle region is used only to display summary data for All Agencies.



My report also contains a list region with graphs and tables, where I display data for each agency (my detail group), and page-break on each agency.



The problem I am experiencing occurs when using the Expression Builder for the Visibility property for my rectangle and list regions. Since a multi-select parameter is an array, I am forced to select an element in my paramater such as =Parameters!Agency.Value(0). When the user chooses "(Select All)", the first element is the first agency in the list. I don't want that.



How can I get Reporting Services to display a rectangle or list region when "Select All" is chosen, and to hide that rectangle or list region when one or more agencies are chosen from a multi-select parameter?



I have tried using Agency.Label and I've tried other expressions such as Parameters!Agency.Count = Count(Agency.Value), etc, without success.

View 4 Replies View Related

Is There A Way To Select What Fields To Display On A Report From A Multi-value Parameter?

Feb 19, 2008

I am trying to figure out a way to toggle the visibility of attribute data based on a parameter. Specifically, I have a report that has many columns that an end-user may not want to see, depending on what they are using the report for. I know you can toggle visibilities on individual columns easily enough, however I want the user to be able to select which fields (at the attribute level) they want visible on the report up in the parameters, via a multi-value prompt.

Is this possible with reporting services 2005?

Thanks.

View 9 Replies View Related

Check If (Select All) Option Was Selected In Multi-value Parameter.

Jan 22, 2008


I am working in .net 2.0.
How can I check if "(Select All)" option was selected in multi-value parameter?
Thanks in advance!

View 4 Replies View Related

Select All As Default For A Multi-value Parameter Which Source Is Non-query

Apr 5, 2007

Hi, I have report parameter and its values are static lik (ABC, DEF, GHI ) etc. I want to select all as a default for this parameter. How I can do this?

View 12 Replies View Related

Displaying All On A Report When Select All Is Chosen From A Multi-value Parameter

Sep 28, 2007

Hi All

New user here. I've got a report with a couple of multi-value parameters and I'm displaying the selected values on the report using = Join(Parameters!ReportingGroup.Value, ", "). This works properly.

The users now want the report to display "All" if "Select All" is chosen, rather than showing the whole list.

Can anybody help me with this? I'm thinking of using an IIF statement in my expression, but have no idea how to define the condition?

Thanks in advance

Grant






View 3 Replies View Related

Control Number Of Options Selected In A Multi Select Parameter

Feb 13, 2007

Hi All

I have a report which has a multi-value parameter. Problem is, it can contain up to 100 options.Is there a way to limit the number of options that is passed to the SQL statement?. EG list has 100 options, user selects 10 but only the first 4 selected options are passed to the SQL statement.
Many Thanks
Delli

View 4 Replies View Related

Keep A Multi-valued Parameter Cleared From Selections Until The User Select His Choice

Apr 28, 2008

I have a report that includes two multi-valued parameters.
In the Default Values section, I choose 'from query' and select dataset and value field.
In the Available Values section, I choose 'from query' select the same dataset and value field, and in the label field I select the relevant label field.
When I run the report my multi-valued parameters look like I selected the option 'select all' (all options are selected).
How can I keep the multi-valued parameters cleared from selections until the user select his choice? Thanks in advance.

View 5 Replies View Related

Reporting Services :: Null Value Not Appearing In SSRS Multi-Select Parameter List

Apr 16, 2009

I am trying to get a null value to pass to a multi-select parameter in SSRS 2005.  The multi-select box is getting its possible values from the following query:

SELECT NULL CMP_ID, '<all companies>' COMPANY UNION SELECT DISTINCT T1.CMP_ID, T1.COMPANY FROM VIEWCOMPANYLIST T1 WHERE T1.DIV_ID = 2  ORDER BY 1

This query is pre-pending a row with NULL as the first ID.  The resulting data set is exactly what you would expect - Row 1 has a null in column CMP_ID, and <all companies> in column COMPANY.  All the rest of the rows have proper data. My multi-select parameter box is of type Multi-value Int, with possible values populated by this query and a default of NULL. However, when I preview the report, the <all companies> row disappears. 

It's not even in the list, and it's not getting selected by the default selector.  So, of course, when I try to View Report without selecting anything, I get prompted to select a company. Why the prepended row in my dataset is getting dropped from the multi-select box at display time?  And yes, I have to be able to pass a null value because the stored procedure on which the report is based expects this parameter to be optional.

View 4 Replies View Related

Multi-Select String Parameter Values Are Converted To N'Value1', N'Value2' In Clause When Report Is Run

Apr 14, 2008



I understand that Multi-Select Parameters are converted behind the scenes to an In Clause when a report is executed. The problem that I have is that my multi-select string parameter is turned into an in claused filled with nvarchar/unicode expressions like:


Where columnName in (N'Value1', N'Value2', N'Value3'...)

This nvarchar / unicode expression takes what is already a fairly slow-performing construct and just drives it into the ground. When I capture my query with Profiler (so I can see the In Clause that is being built), I can run it in Management Studio and see the execution plan. Using N'Values' instead of just 'Value1', 'Value2','Value3' causes the query performance to drop from 40 seconds to two minutes and 40 seconds. It's horrible. How can I make it stop!!!?

Is there any way to force the query-rewriting process in Reporting Services to just use plain-old, varchar text values instead of forcing each value in the list to be converted on the fly to an Nvarchar value like this? The column from which I am pulling values for the parameter and the column that I am filtering are both just plain varchar.

Thanks,

TC

View 3 Replies View Related

Reporting Services :: Multi-value Parameter Not Passing In Subreport Parameter List

Jul 29, 2015

I have two report , first is main report which is matrix and have one parameter User_ids which is multi value selection and my second report is basic chart of user_wise performance.

Now, my main report (matrix ) works fine for Multiple selection of users and i have putted one textbox on main report chart which has action properties set for chart report, when user click on chart button it must goes to chart with user selected in main report. Now , i have used expression for parameter to send it like ..

=join(parameter!user_id!value,",") which pass selected value to chart 

And when I am selecting single user it passing that value to chart parameter list but , when it is more than one user it errors with conversion failed when converting the nvarchar value '121,128' to data type int. But my chart also works when passing 121,128 in user parameter in preview of report .

View 2 Replies View Related

SSRS Toggle Parameter Between Multi-Value And Single Value Based On Another Parameter

Feb 4, 2008



I would like to be able to adjust the multi-value property of a parameter based on the value of another parameter in my report. The controlling paramter would be binary with two options for Single or Multiple selection. I would like my parameter to default to multi-value, which I can do on the screen selection. I have tried to add an IIF statement to the XML code, with no success. Any ideas would be helpful.

Thanks

View 4 Replies View Related

Help With Multi Join Or Multi Tier Select.

Jul 20, 2005

Hello,I am trying to construct a query across 5 tables but primarily 3tables. Plan, Provider, ProviderLocation are the three primary tablesthe other tables are lookup tables for values the other tables.PlanID is the primary in Plan andPlanProviderProviderLocationLookups---------------------------------------------PlanIDProviderIDProviderIDLookupTypePlanNamePlanIDProviderStatusLookupKeyRegionIDLastName...LookupValue....FirstName...Given a PlanID I want all the Providers with a ProviderStatus = 0I can get the query to work just fine if there are records but what Iwant is if there are no records then I at least want one record withthe Plan information. Here is a sample of the Query:SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,pl.InvalidDataFROM Plans plnINNER JOIN Lookups l3 ON l3.LookupType = 'REGN'AND pln.RegionID = l3.Lookupkeyleft outer JOIN Provider p ON pln.PlanID = p.PlanIDleft outer JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderIDleft outer JOIN Lookups l1 ON l1.LookupType = 'PLRM'AND pl.ReasonMain = l1.LookupKeyleft outer JOIN Lookups l2 ON l2.LookupType = 'PLX1'AND pl.ReasonSub = l2.LookupkeyWHERE pln.PlanID = '123456789' AND pl.ProviderStatus = 0ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNumI know the problew the ProviderStatus on the Where clause is keepingany records from being returned but I'm not good enough at this toanother select.Can anybody give me some suggestions?ThanksDavid

View 5 Replies View Related

How To Declare A Procedure Parameter Type To Match A Referenced Table Colum Type

Dec 14, 2007

I like to define my procedure parameter type to match a referenced table colum type,
similar to PL/SQL "table.column%type" notation.
That way, when the table column is changes, I would not have to change my stored proc.
Any suggestion?

View 1 Replies View Related

(Select All) In Multi-select Enabled Drop Down Parameters Doesn't Work

Apr 29, 2008

Hello all,
I have two mult-value parameters in my report. Both of them working with selecting one or more values. But, when I test using "(Select All)" values for both parameters , only one parameter works. The "available values" for these two parameters are both from the data set.

select distinct ProductType
from Product
order by ProductType

Any suggestion? thx


View 12 Replies View Related

The Multi Type Identifier Xxx Cannot Be Bound

Jul 2, 2007

HI Gurus,
Trying to alter an existing Trigger on an insert event on one of our tables. The following sql generates: "Msg 4104, Level 16, State 1, Procedure insxECSIBHEADER, Line 4 The multi-part identifier "INSERTED.PROJECT" could not be bound."
Here's the SQL itself:
set QUOTED_IDENTIFIER ONgoALTER TRIGGER [insxECSIBHEADER] ON [dbo].[xECSIBHeader] AFTER INSERT AS BEGIN  IF SUBSTRING(INSERTED.[PROJECT],4,2) = 'DR'    BEGIN             UPDATE xECSIBHEADER SET xECSIBHEADER.[IBNUMBER] = LEFT(xECSIBHEADER.[PROJECT],2) + 'D' + Cast(xECSIBHEADER.[IBHeaderKey] AS VarChar(15)) FROM INSERTED INS INNER JOIN xECSIBHEADER ON xECSIBHEADER.[IBHeaderKey] = INS.[IBHeaderKey] WHERE INS.[IBNUMBER] IS NULL   END ELSE  BEGIN            UPDATE xECSIBHEADER SET xECSIBHEADER.[IBNUMBER] = LEFT(xECSIBHEADER.[PROJECT],2) + Cast(xECSIBHEADER.[IBHeaderKey] AS VarChar(15)) FROM INSERTED INS INNER JOIN xECSIBHEADER ON xECSIBHEADER.[IBHeaderKey] = INS.[IBHeaderKey] WHERE INS.[IBNUMBER] IS NULL  END
END

View 6 Replies View Related

(Select All) In Multi-select Enabled Drop Down Parameters

Feb 27, 2008

There are several parameters on a report. One of the parameter is a multi-select enabled parameter and I suppressed the value "All" showing as one of the item in the drop down list, simply by filter out the [bha].[bha].CURRENTMEMBER.LEVEL.ORDINAL to 1, as "(Select All)" is pre-assigned to the drop list when multi-select is enabled and it is confusing to show "(Select All)" and "All" in the drop list. However I have another report which is linked to this report and the value which is required to pass to this report for this parameter is "All". Can I pass the "Select All" as a parameter from the other report? If so, how? Thanks.

View 1 Replies View Related

Multi Value Parameter

Jan 16, 2007

Hi,

In my report i have a multi valued parameter, when i view my report in the Web application with Report Viewer, The multi valued parameter is displaying in light color.Can we able to change this.

Thanks in advance

View 2 Replies View Related

Multi-Value Parameter

Sep 5, 2007

I have a stored procedure im passing into Reporting Services. Only problem is , What do i need to change to allow the user to select more then one value. I already know what to do on the reporting services side, but it keeps erroring with the data source IE my stored procedure. Here's the code:




Code Snippet
SE [RC_STAT]
GO
/****** Object: StoredProcedure [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY] Script Date: 09/05/2007 13:49:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY]
(@Region int = Null)
AS
BEGIN
SELECT Budget_Reporting_Detail.Budget_Report_Detail_Datetime, Budget_Reporting_Detail.Budget_Report_Detail_Document_Type,
Budget_Reporting_Detail.Budget_Report_Detail_Code, Budget_Reporting_Detail.Budget_Report_Detail_Description,
ISNULL(Budget_Reporting_Detail.Budget_Report_Detail_Amount, 0) AS Actual, Budget_Reporting_Detail.Budget_Report_Detail_Qty,
Budget_Reporting_Detail.Budget_Report_Detail_Responsible, Territory.Name+'('+Code+')' as [Name], Region.Region, Round((Forecast.Budget_Amount/13),2) AS Budget,
Forecast.Budget_Type_Code, Forecast.Budget_Year, Budget_Forecast_Period,
Forecast.SalesPerson_Purchaser_Code
FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Detail AS Budget_Reporting_Detail RIGHT OUTER JOIN
RC_DWDB_INSTANCE_1.dbo.Region AS Region RIGHT OUTER JOIN
(SELECT Budget_Type_Code, Budget_Year, SalesPerson_Purchaser_Code, Budget_Amount
FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget
) AS Forecast INNER JOIN
RC_DWDB_INSTANCE_1.dbo.Territory AS Territory INNER JOIN
RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Sales_Responsible AS Territory_In_Sales_Responsible ON
Territory.Code = Territory_In_Sales_Responsible.Territory_Code INNER JOIN
RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region AS Territory_In_Region ON Territory_In_Region.Territory_Code = Territory.Code ON
Forecast.SalesPerson_Purchaser_Code = Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code ON
Region.Region_Key = Territory_In_Region.Region_Key ON Budget_Reporting_Detail.Budget_Type_Code = Forecast.Budget_Type_Code AND
Budget_Reporting_Detail.Budget_Year = Forecast.Budget_Year AND
Budget_Reporting_Detail.SalesPerson_Purchaser_Code = Forecast.SalesPerson_Purchaser_Code
WHERE (Region.Region_Key IN( @Region)) AND (Forecast.Budget_Year = 2007)



END




what am i doing wrong?

View 1 Replies View Related

Multi-value Parameter

Jan 15, 2008



Hi:
I am building a report and have a few parameters. One of this parameters is set up as Multi-Value. When I only select one value everything is running fine. But when I select multi values I get an error saying I must declare my variable.

Any idea why this is happening?

Ben

View 3 Replies View Related

Multi Value Parameter

Sep 3, 2007

hi,
i read from this forum that to pass array to report is using multi value parameter . my problem now is
1. can i pass multi dimension array.
2. how do i show the multi value parameter in a table . for example i have a multi value parameter that may contain 10 or 20 array . how do i dynamic it show in table.

rgds,
charles

View 1 Replies View Related

Trouble With A Multi Record Type Flat File

Jun 19, 2007

I am currently working on project where I need to insert, delete and update data from a text file I receive into multiple tables. So the file has multiple recordset types(50 to be exact) and each record has a a code to perform an -Update, Insert, or Delete to the destination table. Also when I receive the records they are not sorted. I need to sort the sets for each destination table and then read the the sorted set sequentially and perform the correct action.



Currently I am importing the record via flatfilesource into one column. I am using a script component here that would consist I guess 50 outputs including the fields needed for each table. The outputs are sorted by sortkey field and when I add the record to the output I perform data type transforms needed for each field(most are strings but I need to convert some dates, numbers ect..).



***is there a better way of accomplishing this?***

(ps. I could use the conditional split and the derive column into the 50 different table but it was giving me errors that were almost forcing me to use a nvarchar type instead of a varchar type during some of the field transformations.)



At this point I would need to read through each of the outputs sequentially and perform the update, insert or delete into the needed table. Would I have to create 50 script components with an ADO.net recordset adapter to update the tables for each of the outputs? I am hoping you can help come up with a better way to accomplish all of this.



Also if I do need to update the tables with the script component could someone point me to an example of how to programmatically accomplish that. Thanks for any help of suggestions that you may provide as I am feeling kind of stuck.

View 4 Replies View Related

Unable To Cast Object Of Type 'System.String' To Type 'System.Web.UI.WebControls.Parameter'.

Jun 19, 2008

I'm getting this error on a vb.net page the needs to execute two separate stored procedures.  The first one, is the main insert, and returns the identity value for the ClientID.  The second stored procedure inserts data, but needs to insert the ClientID returned in the first stored procedure.  What am I doing wrong with including the identity value "ClientID" in the second stored procedure? 
Unable to cast object of type 'System.String' to type 'System.Web.UI.WebControls.Parameter'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Web.UI.WebControls.Parameter'.Source Error:




Line 14: If li.Selected Then
Line 15: InsertClientCompanyType.InsertParameters("CompanyTypeID").DefaultValue = li.Value
Line 16: InsertClientCompanyType.InsertParameters("ClientID") = ViewState("ClientID")
Line 17:
Line 18:
Source File: C:InetpubwwwrootIntranetExternalAppsNewEmploymentClientNewClient.aspx.vb    Line: 16
Here is my code behind... What am I doing wrong with grabbing the ClientID from the first stored procedure insert? 
 
Protected Sub InsertNewClient_Inserted(ByVal sender As Object, ByVal e As SqlDataSourceStatusEventArgs)ClientID.Text = e.Command.Parameters("@ClientID").Value.ToString()ViewState("ClientID") = e.Command.Parameters("@ClientID").Value.ToString()End SubProtected Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit.ClickInsertNewClient.Insert()For Each li As ListItem In CompanyTypeID.Items
If li.Selected ThenInsertClientCompanyType.InsertParameters("CompanyTypeID").DefaultValue = li.ValueInsertClientCompanyType.InsertParameters("ClientID") = ViewState("ClientID")InsertClientCompanyType.Insert()End IfNextEnd Sub

View 2 Replies View Related

Help With Sproc And Multi Parameter

Dec 11, 2006

I'm trying to build a sproc that will return rows even if some of the parameters are blank. For example; if a user does not enter a priority, a status, or a caller the sproce should still return rows based on the other parameters.
 Can anyone help me find a way to modify my sproc bellow to allow this? I think the way I have it  bellow will only return those rows where the user has entered a parameter or the record has a null in the field.ALTER PROCEDURE dbo.ContactManagementAction

(
@ClientID int,
@Priority int,
@TStart datetime,
@TEnd datetime,
@Status nvarchar,
@ConTypeID int,
@Caller nvarchar,
@Keyword nvarchar
)

AS
SELECT Task_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument, ReminderDate, Reminder, ReminderTime, Sol_ID,
DateEntered, EnteredBy, Caller, ContactTypeID, DueDate
FROM tblTasks
WHERE (ClientID = @ClientID) AND (Priority = @Priority) OR (Priority IS NULL) AND (ActionDate BETWEEN @TStart AND @TEnd) AND (Status = @Status) OR (Status IS NULL) AND
(ContactTypeID = @ConTypeID) OR (ContactTypeID IS NULL) AND (Caller = @Caller) OR (Caller IS NULL) AND (Subject LIKE @Keyword) OR (Subject IS NULL)

RETURN 

View 15 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved