Change data source for mutiple datasets at once

Hi All,

In the BI development studio when I have to change the data source for data sets within a report, I have to go to each of the datasets individually to do this. Is there a quicker way to do this. Say I want to change data source for the entire report in BI dev studio.

thanks

Sonny

Use shared data sources across the reports.

Then you will only have to change the data in the data source and not the datasets.

This will automatically be reflected in the IDE.

Obviously this will not be updated on the report server until you redeploy, be aware of the project based setting "OverwriteDataSources" when deploying!|||

thanks Adolf , that helped.

sonny

Change data source for mutiple datasets at once

Hi All,

In the BI development studio when I have to change the data source for data sets within a report, I have to go to each of the datasets individually to do this. Is there a quicker way to do this. Say I want to change data source for the entire report in BI dev studio.

thanks

Sonny

Use shared data sources across the reports.

Then you will only have to change the data in the data source and not the datasets.

This will automatically be reflected in the IDE.

Obviously this will not be updated on the report server until you redeploy, be aware of the project based setting "OverwriteDataSources" when deploying!|||

thanks Adolf , that helped.

sonny

Cellset.open hangs, query works fine in Management Studio?

Hi All,

We are experiencing a very strange Cellset behaviour.

Trying to open a cellset in VBA (same in VB6) in Excel 2003, using ADOMD.Catalog, accessing a AS2005SP2, the system 'sometimes' hangs, depending on the 'dynamic' MDX query string. While the query works fine in the SQL Management Studio.

For example, this MDX works fine:

select descendants {[Measures].[NumberOfA]} on columns, [Time].[Month].[200502] on rows

from [DWH]
where {([Client].[Hierarchy].[Group].&[G000879])}

This one causes Excel (and VB6) to freeze, but nicely returns a result in SQL Management Studio!!!?

select descendants {[Measures].[NumberOfA]} on columns, [Time].[Month].[200501] on rows

from [DWH]
where {([Client].[Hierarchy].[Group].&[G000879])}

There is nothing wrong with the data, it's more connection related (I expect...)?

Does somebody have any advice?

PS: we have msxml6,asoledb9 installed

It is a firewall issue. See also: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1558402&SiteID=1

Cheers,

Tom

Cast Problem

If I run the following query in SQL Server Management Studio it returns the correct results: (Searching the table for the field "SpecimenID (an INT)" against the data entered (a Text Field - "7575-01") from the submitted form.

SELECT ClinicalID, SpecimenID, PatientID, LabID, Accession, Bacillus, Francisella, Yersinia, Brucella, Burkholderia, Coxiella, Staphylococcus, Other,
OtherExplanation, CollectionDate, strddlTransportMedium, strddlSpecimenSource, UserName, Test, SpecimenCount, DateAndTime
FROM ClinicalSpecimen
WHERE (SpecimenID = CAST('7575-01' AS VARCHAR(50)))
ORDER BY SpecimenID DESC

However, when I try to use the same logic in the ASPX.VB code behind page, as follows below, I either get an error message (Syntax error converting the varchar value '' to a column of data type int.) or record not found... Can someone please explain what I am missing here...

MySQL ="SELECT * FROM ClinicalSpecimen WHERE SpecimenID = CAST(('" & AccessionPresent &"') AS VARCHAR(50))"

*"AccessionPresent" is the value of the text field retrieved from the form.

I guess what I am really asking is how can I search for an INT value in a table using a VARCHAR Field.

Thank you for any or all assistance !!!

Looks like you have a blank value that you are trying to convert to int. Check for NULL/Blanks in your application before you CAST as varchar.

|||

Instead of:
MySQL ="SELECT * FROM ClinicalSpecimen WHERE SpecimenID = CAST(('" & AccessionPresent &"') AS VARCHAR(50))"

Try:
MySQL ="SELECT * FROM ClinicalSpecimen WHERE SpecimenID = CAST('" & AccessionPresent &"' AS VARCHAR(50))"

Remove the Paren's, also as shark said, do something like if string.isnullorempty(AccessionPresent) then put a dummy value their or whatever

|||

Try this (use this query in your MySQL):

1SELECT *2FROM ClinicalSpecimen3WHERE SpecimenID =CAST(4ISNULL(5 ('" & AccessionPresent & "')6 , -1-- you will get -1 for Null cases7 )8AS VARCHAR(50)9 )1011-- I splited the query to make it more readable (you can put it in one line)

Hope this will help.

Good luck.

|||

You are casting the wrong side to a varchar. The right side of your comparision is always a varchar already. It's the int side that isn't.

Try:

Dim conn as new SqlConnection(...)

Dim cmd as new SqlCommand("SELECT * FROM ClinicalSpecimen WHERE CAST(SpecimenID AS varchar(50))=@.SpecimenID",conn)

cmd.Parameters.Add("@.SpecimenID",SqlDbType.varchar).Value=AccessionPresent

Note, this also removes the SQL Injection problem you had.

|||

Motley:

You are casting the wrong side to a varchar. The right side of your comparision is always a varchar already. It's the int side that isn't......

Good catch Motley.

|||

ndinakar:

Motley:

You are casting the wrong side to a varchar. The right side of your comparision is always a varchar already. It's the int side that isn't......

Good catch Motley.

Yes, good catch Motley

How we did not realise that!!