Is it possible to link two datasets together ?

Saludos !
given two datasets:
---
dataset1: select * from pubs.dbo.titles
dataset2: select * from pubs.dbo.sales
---
My question now is, how can i establish a relation between this two datasets
? And where (data register, layout register) ?
Or only with Subreports ?
thanks for any suggestion, hint...
JackHi,
It isn't possiple to link two datasets together or use more than one dataset
on a control.
But what about one dataset which unions both sql queries? And then you can
use a filter on a control to get only data from a single table.
e.g. the dataset:
select *, 'Titles' as TableName from pubs.dbo.titles
UNION
select *, 'Sales' as TableName from pubs.dbo.sales
and as filter to get titles data:
=Fields!TableName.Value='Titles'
Be sure that both queries you use to union have the same column names, so
don't use the *.
--
| Jan Pieter Posthuma
--
"Jack" wrote:
> Saludos !
> given two datasets:
> ---
> dataset1: select * from pubs.dbo.titles
> dataset2: select * from pubs.dbo.sales
> ---
> My question now is, how can i establish a relation between this two datasets
> ? And where (data register, layout register) ?
> Or only with Subreports ?
> thanks for any suggestion, hint...
> Jack
>
>|||Thanks Jan, but that is not exactly touching my issue: "titles" and "sales"
are different tables. In my opinion a union is isn't a proper way to do
this. Anyway...
My central idea behind my question is: If I could solve this problem, I
would be more flexible in using data for several reports. For example, I 've
got one special view for clients (supposing there are more tables to build
this view) , a second special view for sales and many more views for other
matters. In every report where I now have to use client-data, I can take the
same view. Otherwise I have to change every select statement for every
special report if anything changes...
>> Its a thing of strategy, do you see ?
jack
"Jan Pieter Posthuma" <jan-pieterp.at.avanade.com> schrieb im Newsbeitrag
news:BF29CB19-BD2C-4218-A9AE-F970A4D6E011@.microsoft.com...
> Hi,
> It isn't possiple to link two datasets together or use more than one
> dataset
> on a control.
> But what about one dataset which unions both sql queries? And then you can
> use a filter on a control to get only data from a single table.
> e.g. the dataset:
> select *, 'Titles' as TableName from pubs.dbo.titles
> UNION
> select *, 'Sales' as TableName from pubs.dbo.sales
> and as filter to get titles data:
> =Fields!TableName.Value='Titles'
> Be sure that both queries you use to union have the same column names, so
> don't use the *.
> --
> | Jan Pieter Posthuma
> --
>
> "Jack" wrote:
>> Saludos !
>> given two datasets:
>> ---
>> dataset1: select * from pubs.dbo.titles
>> dataset2: select * from pubs.dbo.sales
>> ---
>> My question now is, how can i establish a relation between this two
>> datasets
>> ? And where (data register, layout register) ?
>> Or only with Subreports ?
>> thanks for any suggestion, hint...
>> Jack
>>|||> My central idea behind my question is: If I could solve this problem, I
> would be more flexible in using data for several reports. For example, I 've
> got one special view for clients (supposing there are more tables to build
> this view) , a second special view for sales and many more views for other
> matters. In every report where I now have to use client-data, I can take the
> same view. Otherwise I have to change every select statement for every
> special report if anything changes...
Not sure this answers your question but...
Use a stored procedure to provide your data, then this stored procedure
can be re-used for all reports. And only has to be edited once for all
reports.
sarah|||You're right. thats the way we all doing it for years. But now for several
reasons we would like to use views instead of procs... so our customers are
able to easily create their own reports and links between their required
tables. without any knowledge of the underlying structure.
Jack
<sarah.abnett@.jarvis-uk.com> schrieb im Newsbeitrag
news:1119879460.100655.234790@.g44g2000cwa.googlegroups.com...
>
>> My central idea behind my question is: If I could solve this problem, I
>> would be more flexible in using data for several reports. For example, I
>> 've
>> got one special view for clients (supposing there are more tables to
>> build
>> this view) , a second special view for sales and many more views for
>> other
>> matters. In every report where I now have to use client-data, I can take
>> the
>> same view. Otherwise I have to change every select statement for every
>> special report if anything changes...
> Not sure this answers your question but...
> Use a stored procedure to provide your data, then this stored procedure
> can be re-used for all reports. And only has to be edited once for all
> reports.
> sarah
>|||Hi,
First sorry for my late reply, but I must say your last reply explained your
needs perfectly.
What you can try is to create a datasource with integrated authentication
turned on. This would force SRS to use the users credentials to log in to the
database. In the database views (to which the users only have read rights)
you can create a user specified select statement by filtering with the SQL
function SYSTEM_USER (SUSER_SNAME).
Unfortunaly you can only use select statements in views. So if your database
structure can't cope with this kind of behaviour, you can only fall back to
stored procedures.
Hope this would help you further. Feel free to ask for more information.
--
| Jan Pieter Posthuma
--
"Jack" wrote:
> You're right. thats the way we all doing it for years. But now for several
> reasons we would like to use views instead of procs... so our customers are
> able to easily create their own reports and links between their required
> tables. without any knowledge of the underlying structure.
> Jack
> <sarah.abnett@.jarvis-uk.com> schrieb im Newsbeitrag
> news:1119879460.100655.234790@.g44g2000cwa.googlegroups.com...
> >
> >
> >> My central idea behind my question is: If I could solve this problem, I
> >> would be more flexible in using data for several reports. For example, I
> >> 've
> >> got one special view for clients (supposing there are more tables to
> >> build
> >> this view) , a second special view for sales and many more views for
> >> other
> >> matters. In every report where I now have to use client-data, I can take
> >> the
> >> same view. Otherwise I have to change every select statement for every
> >> special report if anything changes...
> >
> > Not sure this answers your question but...
> > Use a stored procedure to provide your data, then this stored procedure
> > can be re-used for all reports. And only has to be edited once for all
> > reports.
> > sarah
> >
>
>

Is it possible to join the fields value together?

I am new in crystal report. I have a dataset which like this:

FieldA
---
name1
name2
name3

Now, i want to join them together like this : "name1, name2, name3". I try to use the function join(list, delimiter) but fail. It seem that i should get all the data in place them in a array.
How can I do this? Thanks!!!!!check if this is possible

{database.name1}+{database.name2}+{database.name3}|||Thanks for your reply

However, in my case, all the data which is inside the same field.
For example:

Table1.field1
------
Record 1) A
Record 2) B
Record 3) C

I want to join them to "A, B, C" and display in the report
Anyone know how to do? Thanks!!!|||What groups these 3 records together, so that you know they are related? e.g. so that "A, B, C" comes out on a separate line to "D, E, F".
Group on this, and create 3 formulas:

@.init (in the suppressed group header)
whileprintingrecords;
stringvar group_list := "";

@.add (in the suppressed details)
whileprintingrecords;
stringvar group_list;
if group_list <> "" then group_list := group_list + ", ";
group_list := group_list + {table.field};

@.display (in the group footer)
whileprintingrecords;
stringvar group_list

Is it possible to append and sum together?

Hi there,

I was wondering if this is possible:

I need to update 'customers sales' in a database - can this be done so that when I enter the customers new sale to the database (via update page) it will add it to their current sales, giving them a new sales total.??

Angie.Originally posted by blondini3
Hi there,

I was wondering if this is possible:

I need to update 'customers sales' in a database - can this be done so that when I enter the customers new sale to the database (via update page) it will add it to their current sales, giving them a new sales total.??

Angie.
You could do that, using a database trigger. The syntax of a database trigger varies by DBMS.

However, I wouldn't do that. Don't store the sales total at all. When you want to see a sales total, just SUM up the individual sales. You could have a view that does this for you, if you like:

CREATE VIEW customer_total AS
SELECT cust.cust_id, SUM(sales.value) tot_value
FROM cust, sales
WHERE cust.cust_id = sales.cust_id
GROUP BY cust.cust_id;

You are probably concerned that calculating the total sales "on demand" like this is bad for performance. But really, it is what databases are good at. Only very exceptionally is it necessary to store summed values - only in very big, high-access databases. And the downside of doing so is data integrity - i.e. you have to do a lot of work to ensure that the stored total is accurate (e.g. if a sale is deleted, updated).|||This works, thanks...BUT......when I go to addanother column from the Merlot table either before or after the SUM column, i get a ERROR MESSAGE saying 'column' not part of aggregate function !!!

Can you only select two columns ? I want it to display the CardRecordID, LastName, FirstName from Merlot Table, and total sales from Sales Table...but i can't get it to work...

SELECT Merlot.CardRecordID, SUM(sales.sales_value)
FROM Merlot, sales
WHERE Merlot.CardRecordID = sales.CardRecordID
GROUP BY Merlot.CardRecordID

thanks...|||It should be straight forward only..

select Merlot.CardID, Merlot.firstname,Merlot.lastname, sum(sales.values)
from Merlot,sales
where.....etc etc

thats what i always do and works..mebbe its some syntax error like a comma or something..check it up..
also sometiems the "group by" attribute causes problems so first jus see your output without grouping and then try again...

And im a beginner at this too so dun count on my words..jus tryin to extend some help:)

-s|||Thanks for your reply....yeah, it should be straight forward allright, but it comes up STILL with an aggregate function error ?!!!!

when i run this:

SELECT Merlot.CardRecordID, Merlot.FirstName, Merlot.LastName, sum(Sales.Sales_Value)
FROM Merlot,Sales
WHERE Merlot.CardRecordID = Sales.CardRecordID

I get an error message saying 'cardrecordID' not part of an aggregate function.

BUT......ALTHOUGH THIS ONE BELOW WORKS...it doesn't total up the sales for each recordID - it displays each cardID's sales individually...

SELECT Merlot.CardRecordID, Merlot.FirstName, Merlot.LastName, Sales.Sales_Value
FROM Merlot,Sales
WHERE Merlot.CardRecordID = Sales.CardRecordID

it won't let me add any more than two fields in the SELECT statment or it gives me the aggregate error....because when I run this one below it totals the sales fine

SELECT merlot.cardrecordid, sum(sales.sales_value)
FROM merlot, sales
WHERE merlot.cardrecordID = sales.cardrecordid

how do I get it to total the sales for each cardID AND ALSO show more than two fields ??

I'm using dreamweaver MX to create the queries.

angie...|||SELECT Merlot.CardRecordID, Merlot.FirstName, Merlot.LastName, sum(Sales.Sales_Value)
FROM Merlot,Sales
WHERE Merlot.CardRecordID = Sales.CardRecordID
group by Merlot.CardRecordID, Merlot.FirstName, Merlot.LastName

Hope this will help u.|||THANK YOU THANK YOU THANK YOU KUMAR AP,,,,,,you are the best..........the best...............did i mention you're the greatest???.............i will always, always, remember to put the other fields in the GROUP BY statement from now on...

thankyou

Angie.|||Hi,
Whether your problem solved ?.. or else yet to be?.|||yep...it's all working now...thanks heaps.
Angie.

Is it possible not to keep together textfields?

Hi,

This all appears when exporting a report to PDF:

I've a report with a label and textfield in the upper third, below the textfield there are some other controls. My problem is, that all works fine as long as the text for the textfield is short enough to fit the page. If the text gets to long I get a nearly blank page with only the label on it and the textfield on the next page. I found no property to change this behaviour. I want my textfield to split up to multiple pages, just as Access does it!

I want to see the first part of the content on the first page and then page footer, page header and then the second part on the next page.

I tried a list-control and a table-control with a rect in it -> same problem.

I found a very ....uncommon... way to do it, I made a subreport with just a textfield on it which has the value "Parameters!Text" (add the report parameter "Text") and put this subreport into my report and passing the long text as a parameter to the subreport ;)

But I think that's not exactly brilliant..

Any other hints?

Benni

|||

Okay,
very similar problem, if I have a report header(rectangle with some labels in it) with a list below it. As long as the record in the list contains few data all looks fine. If the content reaches a specific length the whole record is keept together on the next page (there is no report header, so a bit more space). If the content gets even larger its okay again (some parts on the first page, the rest on the next page)!

I've NOT set KeepTogether vor any control. I also tried the subreport trick, but it doesn't work ;(

Why does Microsoft add so many rarely needed extra features (e.g. multiple datasets side by side) and loose sight of the basic features? If I don't get this running I'll have to choose a different product, because no user wants to have this strange reports.. Even Access 2000 (I've no older version available) has better keeptogether options ;(

|||

Benni,

Did really the textfield "nokeeptogether" work using a subreport?

It doesn't work for me, perhaps your text was more than one page long? Did you do any other settings to make it work?

/Dan

|||The official MS workaround is to make a few Textfields under each other and split the text right into them. We've made a service request at microsoft and they told us, that they could rise a DCR (Design Change Request). We'll see if they fix it..

Is it possible not to keep together textfields?

Hi,

This all appears when exporting a report to PDF:

I've a report with a label and textfield in the upper third, below the textfield there are some other controls. My problem is, that all works fine as long as the text for the textfield is short enough to fit the page. If the text gets to long I get a nearly blank page with only the label on it and the textfield on the next page. I found no property to change this behaviour. I want my textfield to split up to multiple pages, just as Access does it!

I want to see the first part of the content on the first page and then page footer, page header and then the second part on the next page.

I tried a list-control and a table-control with a rect in it -> same problem.

I found a very ....uncommon... way to do it, I made a subreport with just a textfield on it which has the value "Parameters!Text" (add the report parameter "Text") and put this subreport into my report and passing the long text as a parameter to the subreport ;)

But I think that's not exactly brilliant..

Any other hints?

Benni

|||

Okay,
very similar problem, if I have a report header(rectangle with some labels in it) with a list below it. As long as the record in the list contains few data all looks fine. If the content reaches a specific length the whole record is keept together on the next page (there is no report header, so a bit more space). If the content gets even larger its okay again (some parts on the first page, the rest on the next page)!

I've NOT set KeepTogether vor any control. I also tried the subreport trick, but it doesn't work ;(

Why does Microsoft add so many rarely needed extra features (e.g. multiple datasets side by side) and loose sight of the basic features? If I don't get this running I'll have to choose a different product, because no user wants to have this strange reports.. Even Access 2000 (I've no older version available) has better keeptogether options ;(

|||

Benni,

Did really the textfield "nokeeptogether" work using a subreport?

It doesn't work for me, perhaps your text was more than one page long? Did you do any other settings to make it work?

/Dan

|||The official MS workaround is to make a few Textfields under each other and split the text right into them. We've made a service request at microsoft and they told us, that they could rise a DCR (Design Change Request). We'll see if they fix it..

IS ASP.Net 1.x Working together with SQL 2005?

Hi

Should ASP.Net 1.x work togheter with sql 2005 without problems? I have try to open a web project but I got the following error:

Cannot open database "crm" requested by the login. The login failed. Login failed for user 'crmuser'.

This crmuser is "promoted" to owner of the crm base, but still I got the problem.

I upgrade this web project to ASP.Net 2.0 and I don't have the login problems, that's why I'm wondring.

Hope someone can answear me on this question. Thanks!

Jan

SQL Server 2005 does not care what version is your application all you need is correct permissions and the correct connection string for 1.1, there are two permissions in SQL Server both are covered in the thread below and look up connections string for 1.1 in the product docs. Hope this helps.

http://forums.asp.net/thread/1492092.aspx

|||You solve my problems, thanks!!|||

lsoljf:

You solve my problems, thanks!!

I am glad I could help.