Reporting NIGHTMARE

Hello all,
I have a report that has a list of citys on the left: eg.
Vancouver
Melbourne
Auckland
New York
And the body of the report lists sales in Year to Date and Period to Date
which is set by a parameter.
e.g
City PTD YTD
Vancouver 10000 30000
Melbourne 5000 12000
Auckland 2000 3000
New York 23000 50000
Total 40000 95000
The Period to date data comes from one dataset that is configured to use the
parameter... the Year to date data comes from another dataset that doesn't
use the parameter.
However the cities come from another dataset... What ends up happening is I
get the total of PTD in every city...
eg.
City PTD YTD
Vancouver 40000 30000
Melbourne 40000 12000
Auckland 40000 3000
New York 40000 50000
Total 40000
Rather than the correct amounts. What can I do to fix this?
ThanksHave you considered basing the dataset on a view which joins the City, PTD
and YTD columns together?
Alan
"AshVsAOD" <.> wrote in message news:#1LMFS$VEHA.808@.tk2msftngp13.phx.gbl...
> Hello all,
> I have a report that has a list of citys on the left: eg.
> Vancouver
> Melbourne
> Auckland
> New York
> And the body of the report lists sales in Year to Date and Period to Date
> which is set by a parameter.
> e.g
> City PTD YTD
> Vancouver 10000 30000
> Melbourne 5000 12000
> Auckland 2000 3000
> New York 23000 50000
> Total 40000 95000
> The Period to date data comes from one dataset that is configured to use
the
> parameter... the Year to date data comes from another dataset that doesn't
> use the parameter.
> However the cities come from another dataset... What ends up happening is
I
> get the total of PTD in every city...
> eg.
> City PTD YTD
> Vancouver 40000 30000
> Melbourne 40000 12000
> Auckland 40000 3000
> New York 40000 50000
> Total 40000
> Rather than the correct amounts. What can I do to fix this?
> Thanks
>|||Yes I have considered that, However Period to Date relies on a Parameter
from RS so I can't see how this would work...
Anyone else got any ideas?
"Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
news:OLs24ZAWEHA.1164@.tk2msftngp13.phx.gbl...
> Have you considered basing the dataset on a view which joins the City, PTD
> and YTD columns together?
> Alan
> "AshVsAOD" <.> wrote in message
news:#1LMFS$VEHA.808@.tk2msftngp13.phx.gbl...
> > Hello all,
> >
> > I have a report that has a list of citys on the left: eg.
> >
> > Vancouver
> > Melbourne
> > Auckland
> > New York
> >
> > And the body of the report lists sales in Year to Date and Period to
Date
> > which is set by a parameter.
> > e.g
> >
> > City PTD YTD
> > Vancouver 10000 30000
> > Melbourne 5000 12000
> > Auckland 2000 3000
> > New York 23000 50000
> > Total 40000 95000
> >
> > The Period to date data comes from one dataset that is configured to use
> the
> > parameter... the Year to date data comes from another dataset that
doesn't
> > use the parameter.
> >
> > However the cities come from another dataset... What ends up happening
is
> I
> > get the total of PTD in every city...
> > eg.
> > City PTD YTD
> > Vancouver 40000 30000
> > Melbourne 40000 12000
> > Auckland 40000 3000
> > New York 40000 50000
> > Total 40000
> >
> > Rather than the correct amounts. What can I do to fix this?
> >
> > Thanks
> >
> >
>|||Reporting Services does not join datasets together. For the second data set,
you are most likely getting First(), the default aggregate expression. You
need to merge these together before they get to reporting services. You can
take a report parameter and bind it into a data set.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"AshVsAOD" <.> wrote in message
news:%23oIOXVBWEHA.808@.tk2msftngp13.phx.gbl...
> Yes I have considered that, However Period to Date relies on a Parameter
> from RS so I can't see how this would work...
> Anyone else got any ideas?
> "Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
> news:OLs24ZAWEHA.1164@.tk2msftngp13.phx.gbl...
> > Have you considered basing the dataset on a view which joins the City,
PTD
> > and YTD columns together?
> >
> > Alan
> >
> > "AshVsAOD" <.> wrote in message
> news:#1LMFS$VEHA.808@.tk2msftngp13.phx.gbl...
> > > Hello all,
> > >
> > > I have a report that has a list of citys on the left: eg.
> > >
> > > Vancouver
> > > Melbourne
> > > Auckland
> > > New York
> > >
> > > And the body of the report lists sales in Year to Date and Period to
> Date
> > > which is set by a parameter.
> > > e.g
> > >
> > > City PTD YTD
> > > Vancouver 10000 30000
> > > Melbourne 5000 12000
> > > Auckland 2000 3000
> > > New York 23000 50000
> > > Total 40000 95000
> > >
> > > The Period to date data comes from one dataset that is configured to
use
> > the
> > > parameter... the Year to date data comes from another dataset that
> doesn't
> > > use the parameter.
> > >
> > > However the cities come from another dataset... What ends up
happening
> is
> > I
> > > get the total of PTD in every city...
> > > eg.
> > > City PTD YTD
> > > Vancouver 40000 30000
> > > Melbourne 40000 12000
> > > Auckland 40000 3000
> > > New York 40000 50000
> > > Total 40000
> > >
> > > Rather than the correct amounts. What can I do to fix this?
> > >
> > > Thanks
> > >
> > >
> >
> >
>|||Brian,
Thanks for the advice, I was worried that would be the answer.
Ash
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:%23gddTHCWEHA.1380@.TK2MSFTNGP09.phx.gbl...
> Reporting Services does not join datasets together. For the second data
set,
> you are most likely getting First(), the default aggregate expression. You
> need to merge these together before they get to reporting services. You
can
> take a report parameter and bind it into a data set.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "AshVsAOD" <.> wrote in message
> news:%23oIOXVBWEHA.808@.tk2msftngp13.phx.gbl...
> > Yes I have considered that, However Period to Date relies on a Parameter
> > from RS so I can't see how this would work...
> >
> > Anyone else got any ideas?
> > "Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
> > news:OLs24ZAWEHA.1164@.tk2msftngp13.phx.gbl...
> > > Have you considered basing the dataset on a view which joins the City,
> PTD
> > > and YTD columns together?
> > >
> > > Alan
> > >
> > > "AshVsAOD" <.> wrote in message
> > news:#1LMFS$VEHA.808@.tk2msftngp13.phx.gbl...
> > > > Hello all,
> > > >
> > > > I have a report that has a list of citys on the left: eg.
> > > >
> > > > Vancouver
> > > > Melbourne
> > > > Auckland
> > > > New York
> > > >
> > > > And the body of the report lists sales in Year to Date and Period to
> > Date
> > > > which is set by a parameter.
> > > > e.g
> > > >
> > > > City PTD YTD
> > > > Vancouver 10000 30000
> > > > Melbourne 5000 12000
> > > > Auckland 2000 3000
> > > > New York 23000 50000
> > > > Total 40000 95000
> > > >
> > > > The Period to date data comes from one dataset that is configured to
> use
> > > the
> > > > parameter... the Year to date data comes from another dataset that
> > doesn't
> > > > use the parameter.
> > > >
> > > > However the cities come from another dataset... What ends up
> happening
> > is
> > > I
> > > > get the total of PTD in every city...
> > > > eg.
> > > > City PTD YTD
> > > > Vancouver 40000 30000
> > > > Melbourne 40000 12000
> > > > Auckland 40000 3000
> > > > New York 40000 50000
> > > > Total 40000
> > > >
> > > > Rather than the correct amounts. What can I do to fix this?
> > > >
> > > > Thanks
> > > >
> > > >
> > >
> > >
> >
> >
>|||Maybe I'm missing something.
I developed a report several years ago that shows performance for a list of
stocks with columns for Date, Stock,Weekt TD, MonthTD, QuarterTD, Yeat TD,
all side by side coumns.
The columns are each calculated in separate tables and then joined together
in a view as one table on Date and Stock..
It sounds like that's the type of table you want to report on.
Alan
"AshVsAOD" <.> wrote in message news:#oIOXVBWEHA.808@.tk2msftngp13.phx.gbl...
> Yes I have considered that, However Period to Date relies on a Parameter
> from RS so I can't see how this would work...
> Anyone else got any ideas?
> "Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
> news:OLs24ZAWEHA.1164@.tk2msftngp13.phx.gbl...
> > Have you considered basing the dataset on a view which joins the City,
PTD
> > and YTD columns together?
> >
> > Alan
> >
> > "AshVsAOD" <.> wrote in message
> news:#1LMFS$VEHA.808@.tk2msftngp13.phx.gbl...
> > > Hello all,
> > >
> > > I have a report that has a list of citys on the left: eg.
> > >
> > > Vancouver
> > > Melbourne
> > > Auckland
> > > New York
> > >
> > > And the body of the report lists sales in Year to Date and Period to
> Date
> > > which is set by a parameter.
> > > e.g
> > >
> > > City PTD YTD
> > > Vancouver 10000 30000
> > > Melbourne 5000 12000
> > > Auckland 2000 3000
> > > New York 23000 50000
> > > Total 40000 95000
> > >
> > > The Period to date data comes from one dataset that is configured to
use
> > the
> > > parameter... the Year to date data comes from another dataset that
> doesn't
> > > use the parameter.
> > >
> > > However the cities come from another dataset... What ends up
happening
> is
> > I
> > > get the total of PTD in every city...
> > > eg.
> > > City PTD YTD
> > > Vancouver 40000 30000
> > > Melbourne 40000 12000
> > > Auckland 40000 3000
> > > New York 40000 50000
> > > Total 40000
> > >
> > > Rather than the correct amounts. What can I do to fix this?
> > >
> > > Thanks
> > >
> > >
> >
> >
>|||Alan,
That does sound like what I want... But...
I need the user's to be able to enter a Parameter that Specifies the Period
to Date and Year to Date.
Example:
To Date: 16/06/04 From Date 01/06/04
So Period to date would be the data between 01/06 and 16/06
Year to date has to be the entire year.
Does this make sense?
"Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
news:OFZD5vDWEHA.3016@.tk2msftngp13.phx.gbl...
> Maybe I'm missing something.
> I developed a report several years ago that shows performance for a list
of
> stocks with columns for Date, Stock,Weekt TD, MonthTD, QuarterTD, Yeat TD,
> all side by side coumns.
> The columns are each calculated in separate tables and then joined
together
> in a view as one table on Date and Stock..
> It sounds like that's the type of table you want to report on.
> Alan
>
>
>
> "AshVsAOD" <.> wrote in message
news:#oIOXVBWEHA.808@.tk2msftngp13.phx.gbl...
> > Yes I have considered that, However Period to Date relies on a Parameter
> > from RS so I can't see how this would work...
> >
> > Anyone else got any ideas?
> > "Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
> > news:OLs24ZAWEHA.1164@.tk2msftngp13.phx.gbl...
> > > Have you considered basing the dataset on a view which joins the City,
> PTD
> > > and YTD columns together?
> > >
> > > Alan
> > >
> > > "AshVsAOD" <.> wrote in message
> > news:#1LMFS$VEHA.808@.tk2msftngp13.phx.gbl...
> > > > Hello all,
> > > >
> > > > I have a report that has a list of citys on the left: eg.
> > > >
> > > > Vancouver
> > > > Melbourne
> > > > Auckland
> > > > New York
> > > >
> > > > And the body of the report lists sales in Year to Date and Period to
> > Date
> > > > which is set by a parameter.
> > > > e.g
> > > >
> > > > City PTD YTD
> > > > Vancouver 10000 30000
> > > > Melbourne 5000 12000
> > > > Auckland 2000 3000
> > > > New York 23000 50000
> > > > Total 40000 95000
> > > >
> > > > The Period to date data comes from one dataset that is configured to
> use
> > > the
> > > > parameter... the Year to date data comes from another dataset that
> > doesn't
> > > > use the parameter.
> > > >
> > > > However the cities come from another dataset... What ends up
> happening
> > is
> > > I
> > > > get the total of PTD in every city...
> > > > eg.
> > > > City PTD YTD
> > > > Vancouver 40000 30000
> > > > Melbourne 40000 12000
> > > > Auckland 40000 3000
> > > > New York 40000 50000
> > > > Total 40000
> > > >
> > > > Rather than the correct amounts. What can I do to fix this?
> > > >
> > > > Thanks
> > > >
> > > >
> > >
> > >
> >
> >
>|||"AshVsAOD" <.> wrote in message
news:udJBVqKWEHA.2340@.TK2MSFTNGP09.phx.gbl...
> Alan,
> That does sound like what I want... But...
> I need the user's to be able to enter a Parameter that Specifies the
Period
> to Date and Year to Date.
> Example:
> To Date: 16/06/04 From Date 01/06/04
> So Period to date would be the data between 01/06 and 16/06
> Year to date has to be the entire year.
>
Have a parameter each for beginning day/month, ending day/month, and year,
used to set a BETWEEN date range in a SQL statement. Is it as simple as
that or are you trying to do something more complex?
Or just two parameterts for beeginning and ending date.
Alan|||I am positive you are on to something... but I don't quite understand what
you mean...
How can I use parameters in T-SQL... I understand them in RS but in SQL I am
quite new.
Thanks!
"Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
news:e9bns1LWEHA.1048@.tk2msftngp13.phx.gbl...
> "AshVsAOD" <.> wrote in message
> news:udJBVqKWEHA.2340@.TK2MSFTNGP09.phx.gbl...
> > Alan,
> >
> > That does sound like what I want... But...
> >
> > I need the user's to be able to enter a Parameter that Specifies the
> Period
> > to Date and Year to Date.
> >
> > Example:
> >
> > To Date: 16/06/04 From Date 01/06/04
> >
> > So Period to date would be the data between 01/06 and 16/06
> > Year to date has to be the entire year.
> >
> Have a parameter each for beginning day/month, ending day/month, and year,
> used to set a BETWEEN date range in a SQL statement. Is it as simple as
> that or are you trying to do something more complex?
> Or just two parameterts for beeginning and ending date.
> Alan
>|||> How can I use parameters in T-SQL... I understand them in RS but in SQL I
am
> quite new.
Now, after all the messages, the real issue is discovered. You have put the
cart before the horse.
1. Reports are only a window on data.
2. Hence, you should put Rpt Svcs aside and concentrate on the data.
3. First you have to get a table with your required PTD and YTD columns
side-by-side with cols for Date and City.
4. As for parameters in T-SQL, it's up to you to learn this very elementary
subject. There are so many books as well as the excellent online
documentation that comes with SQLServer.
5. Parameters aren't even relevant until you get the table together.
Alan|||This will work.
Make an outer table
Here's your header row:
----
| City | PDT | YDT
|
----
In your detail row:
Column 1 (CITY) = User the table dataset like you are now to get the
city
Column 2 (PDT) = Make this a sub report. Pass in your date range
values and your city id as your sub report parameters.
Column 3 (YDT) = Make this a sub report. Pass in your date range
values and your city id as your sub report parameters.
To make your subreport just make another report and be sure to make it
the same page width as your table column.
The other city dataset will loop through each subreport and give you
the values you want.
"AshVsAOD" <.> wrote in message news:<#1LMFS$VEHA.808@.tk2msftngp13.phx.gbl>...
> Hello all,
> I have a report that has a list of citys on the left: eg.
> Vancouver
> Melbourne
> Auckland
> New York
> And the body of the report lists sales in Year to Date and Period to Date
> which is set by a parameter.
> e.g
> City PTD YTD
> Vancouver 10000 30000
> Melbourne 5000 12000
> Auckland 2000 3000
> New York 23000 50000
> Total 40000 95000
> The Period to date data comes from one dataset that is configured to use the
> parameter... the Year to date data comes from another dataset that doesn't
> use the parameter.
> However the cities come from another dataset... What ends up happening is I
> get the total of PTD in every city...
> eg.
> City PTD YTD
> Vancouver 40000 30000
> Melbourne 40000 12000
> Auckland 40000 3000
> New York 40000 50000
> Total 40000
> Rather than the correct amounts. What can I do to fix this?
> Thanks