Trying to switch a report to a "DSN less" connection

[Former Member]

I have some reports that use DSNs. We are going to be calling these reports from some .Net apps and I am trying to switch them to a "connection string" (DSN less) type connection.

The only reports I am having issues with are ones that use a "SQL command" (and happen to call a stored procedure). When I click "Set Datasource Location" and choose "Add Command" in the bottom window, then add the exec to the sp and all the parameters and save it, nothing happens.

If I just go into the Database Expert and add a new command (same as the old one, just via cnnection string instead of DSN) it creates a NEW command callled "command_1" and all the report fields are already tied to the old "command".

Does anyone know how I can accomplish this?

[Former Member · Accepted solution]

Shayne,

Your best bet is to do as follows...

1) Make a copy of the report and work from the copy.

2) Open the existing Command and copy the SQL code to Notepad. Also make note of any Command Parameters that may be there.

3) Delete the command. (Don't worry... you are working in a copy)

4) Create the new connection the way you want it to be.

5) Add new command... paste the old SQL from Notepad into the new Command

6) Rename it to the old Command name

Jason

[Former Member · Accepted solution]

As soon as I delete the command (by clicking on the arrow to move it to the left in the database expert) all of the fields on the report disappear!

Of course, I could readd all the fields, but I have many of these to and and that would not be a good solution.

[Former Member · Accepted solution]

Sorry about that. You are correct. I always put formula versions of fields in the design instead of the actual fields (makes updating easier).

Ok. I just tried an alternate that does work.

...Again, start from a copy...

1) you'll still want to copy your SQL code to Notepad.

2) Go to Set Datasource Location.

3) On the top, highlight your command datasource.

4) In the bottom panel, find your new data source and choose Add Command.

5) Paste the SQL in there.

I just tested, it works.

Jason

[Former Member · Accepted solution]

Hmm.. I tried this like 3 or 4 times yesterday and it never worked. I will try it again, but I know I tried exactly what you described. Maybe I didn't realize it had worked.

I really appreciate the help! I will let you know shortly.

[Former Member · Accepted solution]

Unfortunately, this doesn't work for me. It lets me create the command and makes me add the proper parameters, then prompts for values in those parameters (fot its validation) then the "Add Command" window just goes away and I am left with no changes. It is still pointing to the DSN. It really seems as if it works, but then nothing....

I am running 10.0.0.533, by the way.

[Former Member · Accepted solution]

Shayne,

Not sure what to tell you. I can try it again using an SP in the FROM clause and see if makes a difference. I'm thinking that it may be either a bug or a version thing on your side.

I'm using CR XI R2.

Hate to say it but you may be looking at either, re-adding fields to the report, or trying to make the ODBC work.

Jason

[Former Member · Accepted solution]

Something you said earlier intrigues me. You said you always put "formula fields" on your reports. Wouldn't those also break when the Data Source changed?

[Former Member · Accepted solution]

No. The formula fields remain. You just have to go back into the formulas and make any necessary updates.

For example: I got a call from a VP of lending, wanting 3 new incentive reports based on an earlier report I had done. (basically she just wanted the SQL criteria changed so they could have 3 versions of the same thing.

None of the fields had to change. But because the report was based on a Business View, the data sources had to change.

All I had to do was:

Create 3 copies of the existing report

Create the new BV's

Remove the existing BV

and replace them with the new BV's

Because each of the new BVs had unique names, I had to go into the formulas and update the table names.

A little extra work up front can save TONS of time later on... Especially if you are developing reports for people who like to change their minds.

Jason

[Former Member · Accepted solution]

I understand, I just wasn't sure having to change all the formulas saved any time, but I guess it depends on the complexity of the report.

On a completely UNREALTED note. Is there a "product guide" for Business Objects/Crystal Reports products? I get lost when I try to decide what to download. There is Cyrstal 10, X1, Crystal Enterprise, Business Objects, Business Objects Enterprise, Crystal Reports 2008, etc. etc It is such a convaluted product line ever since Business Objects came into the picture.

(Not to mention the SAP web site, man how confusing is that? I mean I have been an IT professional for 20 years and I barely found this place. I feel for the newbie user that needs help. It all reminds me of navigating "Covisint", so many partners and products and descriptions it is like a "corn field maze".)

[Former Member · Accepted solution]

It saves me time because I spend a lot of time formatting my reports. It also keeps me from having to look in all of the other areas such as Groups, Selections or even other formulas. It keeps me from forgetting the "one" field that throws a wrench in the works.

As far as a product guide. I don't know of anything out there that gives a list of product versions. I do know that CR 2008 is the latest version of CR. I couldn't tell you, for example, the difference between Crystal Reports Server and Business Objects Enterprise.

I agree that they have things very convoluted. I think they keep it that way on purpose so that you have to contact them, so they can sell you more crap.

This link will give you a list of all BO products but not necessarily in any kind of order.

[https://www.sdn.sap.com/irj/sdn/businessobjects-downloads]

Jason