multiple data sources... 1

[hneal98]

I posted this in another forum, but it did not seem like too many people were seeing it. So, here it is again. Hope someone can help me with this. Thanks in advance.

I have a problem where we need to add the sate name to a formula field. However, we do not want to hard code it because that would mean that we would have to go into each report and change it each time a new state comes on board.

What I was thinking is putting a table with a single field in it for the state name. Since each state has it own database, it would only have one row, which would be that state's name; like California.

The problem is that production data is on one db and tables that we (the programmers) create is on another. I would create this report and put some thing like this in Crystal's data source:

SELECT field1, field2, field3, statename FROM mytable, rptstate ON mytable.field1 <> rptstate.statename

This works fine as long as rptstate and mytable are in the same database, because I can go into the Link Options for the link and change it to Not Equal[!=]. However, if they are in different databases, the link options become disabled. Also, I can not access the Database|Show SQL Query... window.

Any thoughts of how to get around this. Let me know if I did not supply enough info.

 

[fredp1]

Hi,
What you could do is create a rptstate view in the same db as mytable.
Then in the rptstate view, you could do a select from a different db and or table.
You didn't specify the db type but if its Sql Server the view would look something like
Select T1.fieldname from newdatabasename.dbo.tablename as T1

that way you only need to ensure that the view lives in your prod data db. It also means you can have the same view name. The only maintenance will be the select statement in the view.
You could even write a elegant view that never needs to be maintained using the dbname of the prod db as a filter to get your state.
e.g.
In the 'programmers' db the table will need to have 2 fields, dbname and State to exclude.
The dbanme refers to the production db location
The view could then retrieve the dbname
e.g Select T1.state from newdatabasename.dbo.tablename as T1
where dbname=DB_NAME()

I hope thats clear


Cheers
Fred

 

[hneal98]

It is clear. Thanks for your input.

I would like to put a view or table in that database, but it is locked up so the developers can not make changes to it. The DBA does not want to add any objects because he does not want that DB to be cluttered (which you can't blame him.)

So, I take it that there is no way around the issue of linking from different DBs in Crystal?

 

[fredp1]

Dam those pesky dba's
The only other way I can think of is to create a sql expression that effctively does the same thing as the view.
(v8.5) in 9 I think its a command(not real sure though)
The limitaion with a sql exression it can only return one row. So as long as you are only returning 1 state to explude, you could use that.
( Select T1.state from newdatabasename.dbo.tablename as T1
where dbname=DB_NAME())
Then use the sql expression in the where clause/record selection formulae.

I think that should work. I'v only tried this in v8.5.



Cheers
Fred

 

[hneal98]

Thanks a bunch for your help on this. I do have another question. Once I enter this command, how do I use the results from it. My first thought on this was to use it in the FROM clause of the query and put the results in the select portion as a field, but as soon as you exit the "Show SQL Query" dialog box, the field in the select vanishes. It seems Crystal does not let you modify that.

Any thoughts?

Thanks again.

 

[fredp1]

What version of Crystal?
Which db?
If its 8.5 create a sql expression (under the field explorer form).
I'd don't recommend to modify the sql in Show SQL.
Crystal will automatically reset the Select part of the statement.

Cheers
Fred

 

[hneal98]

I have 8.5. Ok, let me give it a try. Thanks again.

 

[hneal98]

Well, now I get an error. "Invalid Descriptor Index" It looks like you have to have the table already in the report before using it in the SQL Expression Fields.

Any other thoughts, or am I doing something wrong?

Thanks.

 

[fredp1]

You need to have the table in the database, not the report.

Make sure your sql expression is enclosed in brackets.
The syntax of the expression is checked with the db as you save it.
pls paste the expression.
When are you getting the error? as you save the formulae or when you run the formulae?

Which db are you using?


Cheers
Fred

 

[hneal98]

Ok. The table is stored in a db called molinadb. That is the developer db. The report is based on tables stored in a db called plandata. Here is the SQL Expression I put in the field Explorer SQL Expression:

(Select T1.state from molinadb.dbo.rptState as T1
where StateName=DB_NAME())

When I checked the syntax, everything is fine, but after I save and try to run the report, I get the error mentioned in previous message.

 

[fredp1]

Hi,
The example that I was posted for for SQL Server.
Which DB are you using?

The where clause StateName=DB_Name() is a wrong too.
Note: DB_name() is a SQL server function to return the database name. It will not work with any other db.

In the developer db, your rptState table needs 2 columns
dbname, State
dbname will have the name of the database that the data is keept it.
ie. the db name where mytable lives in
StateToExclude may contain California.
So the select statement now becomes
(Select T1.State from molinadb.dbo.rptState as T1
where dbname=DB_NAME())

This will return 1 value, California which you can then use in the report.

I hope that clarifies it a bit better.


Cheers
Fred

 

[hneal98]

Sorry, SQL Server 2000. It does. However I have tried that already and it produced the same error.

Anyway, I verymuch appreciate your help on this. We were finally able to convince the DBA to add a table to the production database. He said since he is doing that, he would set it up in such a way that if we needed to add anything else there, we could. So, that will work out perfect for us becuase now we can do it with no problem.

Thanks again.

 

[fredp1]

Okay. The error message is wiered, but if you got a solution that great.
Fred

 

[hneal98]

Yep. Thanks again for your help. I learned something new with the SQL Expression.