Custom code database query and connectionstring

I have some custom code in my report <Code> element that I use to query a database. I have hardcoded the connectionstring in this code but I would like to use the same datasource that the report uses. Is there some report property that allows me to access this datasource?There has got to be a way to do this.|||

Can't you just use a hidden parameter for this?

>L<

|||That's a good idea! I was able to make that work. I would like to take it a step further and point the hidden variable to the datasource that the report is associated with. Maybe an expression could be used to get that value somehow.|||

You can do this if you assign the value of the hidden parameter at runtime -- because you can check this in code outside the report, before the report run. Would that work for you, or do you need to assign the value within report code?

>L<

|||

It should be fine either way. I'm just not sure how to access the DataSource element. I am using a shared datasource but I don't have to.

This is what I have now.

<DataSource Name="sql1">
<DataSourceReference>sql1</DataSourceReference>
<rdataSourceID>ce1d79dc-8216-45de-9137-14bd3efa6c3b</rdataSourceID>
</DataSource>

I noticed that if I used a non-shared datasource that the connectionstring itself is in the element.

|||

OK. I was able to spend a little time on this now.

First, I was trying to do this through the object model. What i found was that you can access the Datasources collection, and there is a Type for a Datasource which, when it is "SQL", should allow you to talk to the CommandText string. However the CommandText String for the DataSourceImpl never seems to return anything. (There is also a DataSourceReference property, which you would use if it is a shared datasource -- keep reading).

This empty return seems consistent with the Fieldimpl (see this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=580327&SiteID=1) which makes me think that the implementations stub out some of the functionality but don't really do anything with it. I could be wrong... haven't found any docs that explains it either way.

So, that leaves us with the option of actually talking to the RDL from either within the report or outside, loading it as XML, and interrogating it. (To do this within the report, you could conceivably pass the full RDL or the chunk of it containing the Data information to the report, and do this work within the report -- I would prefer to do it outside -- but it is substantially the same work either way.)

Once having loaded the RDL, you can select the node containing the connectstring text information or, if it is shared, you can get the reference name of the shared set. If it is a shared data source, you can look up the connectstring string from the RDS file.

If this part gives you any trouble, I can help you with that -- it's not going to be a problem -- but I'm troubled as to whether this is the intended method of doing this type of work. I see that it would work, but it seemed to me that I should be able to get this information dynamically at runtime by interrogate things the report already "knows". I don't see why that wouldn't work. So far, I haven't succeeded, so I offer you the opportunity to interrogate the XML directly, which is a proven technology even if low-level.

Sorry, best I can do at the moment...

>L<

|||

Thanks for your help Lisa.

I too thought I could access the datasource through the object model. It seems it should be possible. I guess I will use the xml route after I research the object model more. I am looking at the ReportingService class right now.

Thanks.

|||

Have you gotten any further on this? I just found something in the docs http://msdn2.microsoft.com/en-us/library/ms157274(SQL.90).aspx that indicates that it should work pretty much as we expected -- check the syntax there -- but that the results are "not available in local preview". That might explain why I was getting blanks, because I don't think I tested any other way....

>L<