Multi-value parameter default values not working

I have a parameter that gets it's available values from a dataset.

I use this exact same data to populate the default values.

When I run the report, the available values get populated; however the default values are not being selected.

This works on other parameters on the same report. However, on this parameter, it is not working.

I have tried ltrim/rtrim (Been burned with that before when the field type is a char)

I change the data field in the query, without changing the parameter setup, and it works...

Here is my query:

Code Snippet

Select distinct
CityName

from dimHotel dH (NOLOCK)
Inner join factHotel fH (NOLOCK)
on dh.HotelKey = fH.HotelKey

Where dH.CityName <> ''
and dH.CityName is not null
and fH.ClientKey in (@.ClientID)

Order by CityName

The parameter is setup correctly, and matches the setup of another parameter on the same report that is working fine.

I have tried deleting the parameter and re-adding it. This did not work.

I also deleted and re-added the query. No luck.

Any ideas?

Thanks!!

Thanks

Ok, this is weird...

If I change the query to:

Code Snippet

Select distinct
Replace(CityName,'','') as CityName

It works fine.

If I do a lower or upper, it works fine.

Convert or l/rtrim do not work.

Anyone have any ideas?

At this point I am thinking that there is a character in one of the fields that shouldnt be in there... I am looking at that now.

BobP

|||

Ok, it's an intermittent problem.

Sometimes the replace works, sometimes it doesn't.

Still trying to figure out why...

Does anyone know if there are any characters that could be in the data field, and prevent the default parameters from populating?

Thanks

|||

This same query populates a multi value drop down on a totally different report and it does not work there, either.

I am thinking it is data related.

Can anyone think of a reason why the multi select drop down would be populated, but the defaults not be set?

There are only 360 rows, so it's not a row number issue.

Any ideas would be very welcomed.

Thanks

BobP

Multi-value parameter default values not working

I have a parameter that gets it's available values from a dataset.

I use this exact same data to populate the default values.

When I run the report, the available values get populated; however the default values are not being selected.

This works on other parameters on the same report. However, on this parameter, it is not working.

I have tried ltrim/rtrim (Been burned with that before when the field type is a char)

I change the data field in the query, without changing the parameter setup, and it works...

Here is my query:

Code Snippet

Select distinct
CityName

from dimHotel dH (NOLOCK)
Inner join factHotel fH (NOLOCK)
on dh.HotelKey = fH.HotelKey

Where dH.CityName <> ''
and dH.CityName is not null
and fH.ClientKey in (@.ClientID)

Order by CityName

The parameter is setup correctly, and matches the setup of another parameter on the same report that is working fine.

I have tried deleting the parameter and re-adding it. This did not work.

I also deleted and re-added the query. No luck.

Any ideas?

Thanks!!

Thanks

Ok, this is weird...

If I change the query to:

Code Snippet

Select distinct
Replace(CityName,'','') as CityName

It works fine.

If I do a lower or upper, it works fine.

Convert or l/rtrim do not work.

Anyone have any ideas?

At this point I am thinking that there is a character in one of the fields that shouldnt be in there... I am looking at that now.

BobP

|||

Ok, it's an intermittent problem.

Sometimes the replace works, sometimes it doesn't.

Still trying to figure out why...

Does anyone know if there are any characters that could be in the data field, and prevent the default parameters from populating?

Thanks

|||

This same query populates a multi value drop down on a totally different report and it does not work there, either.

I am thinking it is data related.

Can anyone think of a reason why the multi select drop down would be populated, but the defaults not be set?

There are only 360 rows, so it's not a row number issue.

Any ideas would be very welcomed.

Thanks

BobP

sql

Multiserver Job

hello
when we are trying to execute a multiserver job the following error is coming.can u please suggest the exact solution for this

Blocking error preventing further downloads: The specified @.database_name ('ibbflorida') does not exist. [SQLSTATE 42000] (Error 14262) Unable to create the local version of MSX job '0x6160950EA825544CA777E978E09ECC59'. [SQLSTATE 42000] (Error 50000)

thank you.my guess is that your job is referencing a database called 'IBBFLORIDA' and on one of your servers this database does not exist.|||Thanks for responding MR.Paul.

The database Ibbflorida is existing in one of my server but still it is giving error i could not understand what is the problem|||you didn't explain what your job does or attaach any code around this db reference so I am guessing here...

If ther should only be one copy of IBBFlorida tha I suspect that when your job runs on the linked server, the linked server incorrectly assums the IBBFlorida db is local. What happens if you add a server reference. If IBBFlorida is on ServerA and the job is running on ServerB then change all references to IBBFlorida to ServerA.IBBFlorida.|||hi paul
iam sending u the code for your convenience plz check it here i used db1, db2 in place of original databases names is there any problem with procedure.

thank you once again

CREATE procedure dbo.SP_IU_BALANCES
as
DECLARE
@.LV1 VARCHAR(255),
@.LV2 VARCHAR(255),
@.LV3 varchar(9)

declare TABLECURSOR CURSOR
FOR
select F3,F4,F1 from SERVER1.DB1.dbo.TABLE1
OPEN TABLECURSOR
FETCH NEXT FROM TABLECURSOR INTO @.LV1,@.LV2,@.LV3
WHILE @.@.FETCH_STATUS=0
BEGIN
update SERVER2.DB2.dbo.TABLE2
set TABLE2F1 = @.LV1,
TABLE2F2 = @.LV2
where TABLE2F3=@.LV3
FETCH NEXT FROM TABLECURSOR INTO @.LV1,@.LV2,@.LV3
END;

CLOSE TABLECURSOR;
DEALLOCATE TABLECURSOR;

insert into SERVER1.DB1.dbo.TABLE1(F1, F2, F3, F4, F5)
(select F1, getdate() +1 , F3, 0, F3
from SERVER1.DB1.dbo.TABLE1 where F2 = getdate())|||when you execute this are you still seeing the error from your original post?|||procedure is working fine when we executed manually. but when the same procedure is called in the job then it is giving the error which i mentioned before|||I am drwaing a blank on this one, I will continue to research the problem.

Anyone else have an idea?

multiplue UI Report Tables using exact same dataset ?

Is there an easier way to manage the table UI element on the report. For
example in my report I have a table for the header info and the data, the
data uses maybe 10 columns and we could be perhapps adding more columns
(fields) to it in the future. The header uses 4 columns. It's a pain
because everytime I add a column to the main report details I have to go up
and fix the header and merge the appropriate cells to get it back to 4 cells
in the header. Is there not a way to use multiple table elements but they
both use the same dataset,. When I say same dataset, I dont mean a copy of
the same dataset, I want it to use the same dataset without the query
running twice for each table.
You guys know what I mean?
Possible ?Sorry, I'm not sure what you mean!
Is the table header a seperate table from the 'data' table?
Are they using the same dataset or not?
If the header and data are in fact seperate tables, then why are you
doing it like that, you seem to be making extra work for yourself!
If two tables use the same dataset the query would only be run once.
Chris
D Witherspoon wrote:
> Is there an easier way to manage the table UI element on the report.
> For example in my report I have a table for the header info and the
> data, the data uses maybe 10 columns and we could be perhapps adding
> more columns (fields) to it in the future. The header uses 4
> columns. It's a pain because everytime I add a column to the main
> report details I have to go up and fix the header and merge the
> appropriate cells to get it back to 4 cells in the header. Is there
> not a way to use multiple table elements but they both use the same
> dataset,. When I say same dataset, I dont mean a copy of the same
> dataset, I want it to use the same dataset without the query running
> twice for each table.
> You guys know what I mean?
> Possible ?