how to link different dataset in one report or...

i have a report that needed to create many dataset(coz its complicated
to combine all of the using union).the problem is how can i combine it
all at the layout and make a link to all of them...i have try using
many table but in the end, the result is not what i expected...so how
do i link it?if using table how can i link the table with each
other...<<<<tq>>You can't really combine datasets. You are going to have to either generate a
query in the report designer or write a stored procedure which extracts data
from your different tables/data sources.
The only other thing you can do is design a subreport of your main report.
This can use a different dataset, but there must be some link between the two
which you specify.
HTH,
Magendo_man
"wiraperkasa" wrote:
> i have a report that needed to create many dataset(coz its complicated
> to combine all of the using union).the problem is how can i combine it
> all at the layout and make a link to all of them...i have try using
> many table but in the end, the result is not what i expected...so how
> do i link it?if using table how can i link the table with each
> other...<<<<tq>>
>|||tq magendo_man..ok i know how to generate a query in the report
designer...but write a stored procedure...i'm not quite sure about
it..can you explain to me...
and also..how to use the subreport(is it at the layout?) and hoe to
link between the two dataset..if u have any website that u want to
recommend...:)...tq|||You need to have access to the SQL server database to write a stored
procedure. It can take the form of a query, very similar to those you
generate in Visual Studio for Reporting Services. If you are using SQL 2000
you will probably need to use SQL Server Enterprise Manager and/or SQL Query
Analyzer.
You create a sub-report as you would any normal report in Visual Studio,
i.e. as a seperate report. You then need to go to your main report design and
drag a sub-report control on to the report body and configure the link to
your previously created sub-report. You should get info about this in the
help for Reporting Services in Visual Studio.
HTH,
Magend_man
"wiraperkasa" wrote:
> tq magendo_man..ok i know how to generate a query in the report
> designer...but write a stored procedure...i'm not quite sure about
> it..can you explain to me...
> and also..how to use the subreport(is it at the layout?) and hoe to
> link between the two dataset..if u have any website that u want to
> recommend...:)...tq
>|||maybe we can try to use sub-report...
"magendo_man" wrote:
> You need to have access to the SQL server database to write a stored
> procedure. It can take the form of a query, very similar to those you
> generate in Visual Studio for Reporting Services. If you are using SQL 2000
> you will probably need to use SQL Server Enterprise Manager and/or SQL Query
> Analyzer.
> You create a sub-report as you would any normal report in Visual Studio,
> i.e. as a seperate report. You then need to go to your main report design and
> drag a sub-report control on to the report body and configure the link to
> your previously created sub-report. You should get info about this in the
> help for Reporting Services in Visual Studio.
> HTH,
> Magend_man
> "wiraperkasa" wrote:
> > tq magendo_man..ok i know how to generate a query in the report
> > designer...but write a stored procedure...i'm not quite sure about
> > it..can you explain to me...
> > and also..how to use the subreport(is it at the layout?) and hoe to
> > link between the two dataset..if u have any website that u want to
> > recommend...:)...tq
> >
> >

How to link datasets for reporting?

I have 2 datasets for a single report. One dataset(1) returns value(s), the
second dataset(2) has many records for each value(s) of dataset(1). Yes, one
to many. On the report I wish to display a result of dataset(1) with a
wrapping text box with the many values of dataset(2), then pagebreak and page
two will be the next result of dataset(1) with the many records of dataset(2)
in a wrapbox etc... I have a key column in both datasets. I need to first
figure out how to link them and use the data on report as I described. thanks
!!Read up on subreports. That is how to solve this problem.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"T" <T@.discussions.microsoft.com> wrote in message
news:5722C5FE-4DD1-4FAA-ACF8-86FCA2B15ACA@.microsoft.com...
>I have 2 datasets for a single report. One dataset(1) returns value(s),
>the
> second dataset(2) has many records for each value(s) of dataset(1). Yes,
> one
> to many. On the report I wish to display a result of dataset(1) with a
> wrapping text box with the many values of dataset(2), then pagebreak and
> page
> two will be the next result of dataset(1) with the many records of
> dataset(2)
> in a wrapbox etc... I have a key column in both datasets. I need to
> first
> figure out how to link them and use the data on report as I described.
> thanks
> !!|||I have come across similar scenarios and tried to solved it with two
datasets, but each time I come back to include alla data in one "flat"
dataset and use gropuing instead.
Ex. If you have one dataset with adresses and one dataset with related phone
numbers. Like:
Adress
1, "MyStreet", "MyCity"
2, "MySecondStreet", "MyCity"
Phone
1, 1, "Phone", "+46-123456"
2, 1, "Fax", "+46-8-232323"
3, 2, "Phone", "+46-654321"
4, 2, "Fax", "+46-8-44444"
Then join them into one dataset instead of two, like:
1, "MyStreet", "MyCity", 1, 1, "Phone", "+46-123456"
1, "MyStreet", "MyCity", 2, 1, "Fax", "+46-8-232323"
2, "MySecondStreet", "MyCity", 3, 2, "Phone", "+46-654321"
2, "MySecondStreet", "MyCity", 4, 2, "Fax", "+46-8-44444"
Create a new Report using this Dataset.
Set property "HideDuplicates = true" on the Adress columns.
Create a group with "AddressId" as group value, if you like to SUM some
values one on "adress-level"
This way you "simulate" related datasets.
Regards Martin Bring
**********************
"T" wrote:
> I have 2 datasets for a single report. One dataset(1) returns value(s), the
> second dataset(2) has many records for each value(s) of dataset(1). Yes, one
> to many. On the report I wish to display a result of dataset(1) with a
> wrapping text box with the many values of dataset(2), then pagebreak and page
> two will be the next result of dataset(1) with the many records of dataset(2)
> in a wrapbox etc... I have a key column in both datasets. I need to first
> figure out how to link them and use the data on report as I described. thanks
> !!

How to Limit the number of columns in the Matrix ?

HI all !

I am having a bit of a problem trying to limit a number of columns in a matrix appearing on a page.

At the moment, I have a dataset that lists the month and the mail packages that were sent during the month
The matrix works great HOWEVER, if there were more than 8 months in the matrix columns, it does not break and would make the page look like a huge landscape page.

I am trying to limit the number of columns appearing (this is the months column) on the matrix so that the pages stay in a potrait position. IE: every 8 columns appear on one page. Is there an option or an expression I could use in the Matrix ?
Thanks!

BErnard Ong

An update on this situation.

After figuring what keywords to use, I think I might have found the answer to this dilemma.

This is the post that might be useful to anyone who might want to wrap the number of columns in the matrix so that it will print a new page !

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=934445&SiteID=1

Can anyone suggest what would be a better way to title my thread so that other users can find this easily if they have this problem ? never thought of using the wrap keyword, and I actually had to find this thread by using "Columns in Matrix" and searching through a couple of pages.

Thanks !

Bernard Ong

How to limit the MDX dataset for the last six months

I have the following MDX query and I'd like to put in another condition that the "[Account Period].[True Prescription Date].[True Prescription Date].ALLMEMBERS" has to be GREATER than OR EQUAL TO last six months. Eg. using today date as 21-Nov-2006 and the dataset should only include the records from 21-May-2006 onward.

SELECT NON EMPTY { [Measures].[Pharmacy DW Count] } ON COLUMNS, NON EMPTY { ([AgencyID].[Agency Id].[Agency Id].ALLMEMBERS * [Account Period].[True Prescription Date].[True Prescription Date].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@.DrugProtocolCode, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.DrugDrugName, CONSTRAINED) ) ON COLUMNS FROM [Patient Hospital and Drug])) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Thanks

It depends how you build your True Prescription Date attribute. If we will assume, that it is rebiult daily, and the last attribute member is today's date (i.e. no dates go to the future), then it would be something like

Lag([Account Period].[True Prescription Date].[All True Prescription Dates].LastChild, 182):[Account Period].[True Prescription Date].[All True Prescription Dates].LastChild)

If the rules are more complex, then it is probably best to build named set inside MDX Script which will resolve to the last 6 months worth of dates, and use it instead in the queries.