need the basics about modifying an SSRS report

I am looking at SSRS (pretty much for the first time) in VS 2008 (9.0.*), the report was e-mailed by a colleague. But I need a little help with the basics, such as

- where do I see the query (i.e., how do you modify this)?
SSRS editor- the report was sent to me with a connection to Prod, but I don't have access, I tried to point to test, but I got an error (Test Connection works)
SSRS preview error
- is it good practice to create a data source as "SBI_Prod_Admin" ? Wouldn't it be better to have the connection named "SBI_Admin", and the have a configuration file that gets modified to point to test or prod ? (By doing this, you have a generic name of "SBI_Admin", then when migrating you don't have to fiddle with the code, you just make sure the right configuration file is in the right place).

- when I ran "Preview", I got an error about still pointing to the connection "SBI_Prod_Admin.rds"
LVL 1
Alaska CowboyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Koen Van WielinkBusiness Intelligence SpecialistCommented:
where do I see the query (i.e., how do you modify this)?

Open the "report data" window. If you don't already see it go to View - Report Data (at the bottom of the menu) or press Ctrl + Alt + D. Expand the Datasets folder and right click on the dataset you want to check/modify. Select Dataset Properties to see the query.

- the report was sent to me with a connection to Prod, but I don't have access, I tried to point to test, but I got an error (Test Connection works)

The data source needs to be set in the same Dataset properties as described above. If you've already added your test data source you should be able to add it under the "Data Source" pull down menu in the dataset properties.

is it good practice to create a data source as "SBI_Prod_Admin"

I would use separate data sources for your test environment and your production environment. If you use the same name for your source, it might not be obvious which connection is being used.

when I ran "Preview", I got an error about still pointing to the connection "SBI_Prod_Admin.rds"

This should be solved if you update the data source in the dataset properties.

Hope this helps.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ValentinoVBI ConsultantCommented:
In addition to Kvwielink's excellent answer:

"is it good practice to create a data source as "SBI_Prod_Admin" ? Wouldn't it be better to have the connection named "SBI_Admin", and the have a configuration file that gets modified to point to test or prod ?"

There's a difference between the Shared Data Sources as shown in your screenshot and the Data Sources in the report itself (which are located in the Report Data window).  Shared Data Sources are "shared" over all reports in the project while a Data Source in the report itself references a shared data source.

So for the shared data sources it's normal to have several different ones for different environments.  But the name of the data source in the report itself should not contain PROD/DEV...

Just for the sake of completeness: a data source in the report can also be embedded.  In that case it doesn't reference a shared data source but contains the connection string directly.  In general this is not a good idea because that will obviously lead to configuration issues.

Additional reading material: How to: Create an Embedded or Shared Data Source (SSRS)
0
Alaska CowboyAuthor Commented:
Thank you for the detailed answers, I will review this morning and let you know.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Alaska CowboyAuthor Commented:
sorry for the delay, but I've been studying this and it's helped a lot.

Kvwielink, ok, I got my data sources set up, and my datasets pointing to test, but I'm still getting the error and can't figure out where it's reading SBI_Prod_Admin from . . .
preview error
I would use separate data sources for your test environment and your production environment. If you use the same name for your source, it might not be obvious which connection is being used.
- but when migrating from dev to test to prod, are you supposed to fiddle with the code at each environment ? That seems inefficient and couldn't you handle this with a config file ? Plus then you don't really have one version of the source . . . it's different everywhere . . .

ValentinoV, excellent, as I was poking around with Kvwielink's response and started to understand, I kept wondering what's up with the Shared Data Sources and the Report Data sources, so that's very helpful. But are you saying that the data source should be generic (like SBI_Admin), then point to the Shared Data Source ? If so, doesn't that imply that a step in migration from one environment to another is to modify the Data Source to point to the proper Shared Data Source ? this just means instead of a configuration file, you have to open the report, select the proper data source, and that's it. Not a big deal, I'm trying to understand (and have some pre-conceived notions about migration)
0
Alaska CowboyAuthor Commented:
Update,

I got the report to work :-)

since I posted this question, I got access to Prod, so I re-created my Report Project and then it viewed fine . . . so I will apply these lessons as I add my Test Shared Data Source.

But it would be great if you can still look at my questions above about migrating to different environments, thanks.
0
ValentinoVBI ConsultantCommented:
What I usually do is create the Shared Data Sources as I need them for testing purposes.  The DEV environment sometimes doesn't contain sufficient data to really test everything (although in perfect circumstances it should).  In those cases I'd create an additional shared data source that points to UAT.  But I would only use this for testing inside BIDS.

When deploying I deploy a generic shared data source which is called exactly the same in each environment.  That way you don't need to fiddle with the reports during deployment, all environments are exactly the same except for the configuration string inside the shared data sources.

Hope that clarifies it a little?  Let me know if in doubt about something.
0
Alaska CowboyAuthor Commented:
ValentinoV, ok, that all makes sense but let me make double sure I understand . . .

When deploying I deploy a generic shared data source which is called exactly the same in each environment.

So is this the case:
- Shared Data Source is called "SBI_Generic_Admin", which points to Dev-02. this file is on some common location that everyone uses, like "T:\Shared

- Data Source points to Shared Data Source, this too is called "SBI_Generic_Admin".

- then as you deploy to the UAT server, this has "T:\Shared", but the file points to UAT.

If this is the case, that is what I was originally thinking.

-------------
and in the circumstance where you are in UAT but they say 'can you just pull from prod ?", then you have to fiddle a bit.
0
Alaska CowboyAuthor Commented:
thanks a lot for helping this SSRS rookie . . .
0
ValentinoVBI ConsultantCommented:
Regarding your comment here: #a39506795

I'm not sure I'm fully with you here.  When you deploy a shared data source, it will be deployed to the Report Server and actually end up in a table in the ReportServer database, not in a shared folder.

Ref Publishing Reports to a Report Server

"thanks a lot for helping this SSRS rookie"

Anytime.  Don't hesitate to open some more, we're happy to help :)
0
Alaska CowboyAuthor Commented:
ValentinoV, ok, good deal ! So I got the basics and will likely open up other questions. Thanks again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.