Change data source dynamically in report server.

Hi,

I need to change the data source for report server objects (rdl files) dynamically. In other words, I am having databases with identical structures but different data for different clients. Then I need to use same set of reports for different customers. They will access reports through web and they should provide user id/pwd and database name in web login interface, and then it should direct to the the particular database and should be able to access the same set of reports.

Any thoughts !

Roshan.

You can accomplish this using Report Parameters. The connection string would look something like the following.

="Data Source=ServerName;Initial Catalog=" & Parameters!Database.Value

Ian|||

Thanks, it is working.

Is there any way to change datasource in report model dynamically ?. i.e. sharing reports created from Report Builder between multiple databases.

I would like to create one standard report model with standard set of reports and letting usrs (different customers) add their own reports.

-Roshan

challenging search task is not working as expected

Hi Guys,

I have two tables called table1 and table2.

table1 has search words and table2 has file names as below and want to get file names from table2 those match with all search words.

table1

-

-searchword- column name

--

Learn more about melons row0

--

%.txt row1

-

table2

-testname- column name

--

FKOV43C6.EXE

-
frusdr.txt

-
FRUSDR.TXT

SPGP_FWPkg_66G.zip

readme.txt

--
README.TXT

-
watermelon.exe

-
Learn more about melons read me.txt

-

Here is the script what I have tried...............I hope some one will help to come out this loop.Thanks in advance.

===============================================================================

select * from @.table2 t2 where t2.[testname] in (

SELECT tb.[testname] FROM @.table1 ta

JOIN @.table2 tb ON '% ' + tb.[testname] + ' %' LIKE '% ' + ta.searchword + ' %'

group by tb.[testname] having count(*) = (SELECT COUNT(*) FROM @.table1)

)

===============================================================================

script to create tables

============================================================================

DECLARE @.table1 TABLE (

searchword VARCHAR(255)

)

INSERT INTO @.table1 (

searchword

) VALUES ( 'Learn more about melons' )

INSERT INTO @.table1 (

searchword

) VALUES ( '%.txt' )

DECLARE @.table2 TABLE (

testname VARCHAR(255)

)

INSERT INTO @.table2 (

testname

) VALUES ( 'FKOV43C6.EXE' )

INSERT INTO @.table2 (

testname

) VALUES ('frusdr.txt' )

INSERT INTO @.table2 (

testname

) VALUES ('FRUSDR.TXT' )

INSERT INTO @.table2 ( testname

) VALUES ( 'SPGP_FWPkg_66G.zip' )

INSERT INTO @.table2 (

testname

) VALUES ( 'readme.txt' )

INSERT INTO @.table2 (testname

) VALUES ('README.TXT' )

INSERT INTO @.table2 (testname) VALUES (

'watermelon.exe' )

INSERT INTO @.table2 (

testname

) VALUES ('Learn more about melons read me.txt' )

SELECT * FROM @.table2

DECLARE @.table3 TABLE (

testname VARCHAR(255)

)

INSERT INTO @.table2 (

testname

) VALUES ('Melon release NOTES 321.xls' )

===================================================================================

Here it is:

DECLARE @.num AS int;
SELECT @.num = COUNT(*) FROM @.table1;

SELECT tb.[testname] FROM @.table1 ta
cross JOIN (select distinct * from @.table2) tb
where tb.[testname] LIKE + '%' + ta.searchword + '%'
group by tb.[testname] having count(ta.searchword) = @.num
|||

maybe you also should take an look on "full text index":

f. ex. contains-function

|||

Hi Zuomin,

Thank you very much.It worked perfectly. Thanks for spending your valuable time.

challenging search task is not working as expected

Hi Guys,

I have two tables called table1 and table2.

table1 has search words and table2 has file names as below and want to get file names from table2 those match with all search words.

table1

-

-searchword- column name

--

Learn more about melons row0

--

%.txt row1

-

table2

-testname- column name

--

FKOV43C6.EXE

-
frusdr.txt

-
FRUSDR.TXT

SPGP_FWPkg_66G.zip

readme.txt

--
README.TXT

-
watermelon.exe

-
Learn more about melons read me.txt

-

Here is the script what I have tried...............I hope some one will help to come out this loop.Thanks in advance.

===============================================================================

select * from @.table2 t2 where t2.[testname] in (

SELECT tb.[testname] FROM @.table1 ta

JOIN @.table2 tb ON '% ' + tb.[testname] + ' %' LIKE '% ' + ta.searchword + ' %'

group by tb.[testname] having count(*) = (SELECT COUNT(*) FROM @.table1)

)

===============================================================================

script to create tables

============================================================================

DECLARE @.table1 TABLE (

searchword VARCHAR(255)

)

INSERT INTO @.table1 (

searchword

) VALUES ( 'Learn more about melons' )

INSERT INTO @.table1 (

searchword

) VALUES ( '%.txt' )

DECLARE @.table2 TABLE (

testname VARCHAR(255)

)

INSERT INTO @.table2 (

testname

) VALUES ( 'FKOV43C6.EXE' )

INSERT INTO @.table2 (

testname

) VALUES ('frusdr.txt' )

INSERT INTO @.table2 (

testname

) VALUES ('FRUSDR.TXT' )

INSERT INTO @.table2 ( testname

) VALUES ( 'SPGP_FWPkg_66G.zip' )

INSERT INTO @.table2 (

testname

) VALUES ( 'readme.txt' )

INSERT INTO @.table2 (testname

) VALUES ('README.TXT' )

INSERT INTO @.table2 (testname) VALUES (

'watermelon.exe' )

INSERT INTO @.table2 (

testname

) VALUES ('Learn more about melons read me.txt' )

SELECT * FROM @.table2

DECLARE @.table3 TABLE (

testname VARCHAR(255)

)

INSERT INTO @.table2 (

testname

) VALUES ('Melon release NOTES 321.xls' )

===================================================================================

Here it is:

DECLARE @.num AS int;
SELECT @.num = COUNT(*) FROM @.table1;

SELECT tb.[testname] FROM @.table1 ta
cross JOIN (select distinct * from @.table2) tb
where tb.[testname] LIKE + '%' + ta.searchword + '%'
group by tb.[testname] having count(ta.searchword) = @.num
|||

maybe you also should take an look on "full text index":

f. ex. contains-function

|||

Hi Zuomin,

Thank you very much.It worked perfectly. Thanks for spending your valuable time.