Question about reporting services

Is it possible to achieve the following:

First OLAP dataset with two report parameters, first one single- select parameters and second one is multi-select parameters. Second OLAP dataset I want to apply the first single-select parameter and the second multi-select parameters from the first OLAP dataset with an additional parameter from one of the column of the first OLAP dataset. If so, how to do it? e.g. the first OLAP dataset returns the id column with the selected first and second report parameters and I want to apply the same report parameters and the id column to filter out the second OLAP cube.

The first and second dataset are from different OLAP cubes.

You cannot apply parameters from one report to another, by the sounds of it your only solution here is to build your second report as a subreport and pass through the params.

question about reporting service

Hi,
After playing around with reporting service for a few days,
I can see that it's very easy to create dataset and design page styles
within
the reporting service.
Now I have a question, what if a third party company send me a dataset (not
belong to reporting service),
then I want to pass this dataset to reporting service and do further
formatting and style. is that possible?oh... to be more specific, it seems like you always have to get the dataset
from databases for reporting service,
can I get it from different application (application level) then pass it
into reporting service to use it?
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:uNLTsJn0EHA.1300@.TK2MSFTNGP14.phx.gbl...
> Hi,
> After playing around with reporting service for a few days,
> I can see that it's very easy to create dataset and design page styles
> within
> the reporting service.
> Now I have a question, what if a third party company send me a dataset
(not
> belong to reporting service),
> then I want to pass this dataset to reporting service and do further
> formatting and style. is that possible?
>
>|||Read up on data processing extension. That is how to do this sort of thing.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:uh0RTMn0EHA.1392@.TK2MSFTNGP14.phx.gbl...
> oh... to be more specific, it seems like you always have to get the
dataset
> from databases for reporting service,
> can I get it from different application (application level) then pass it
> into reporting service to use it?
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:uNLTsJn0EHA.1300@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> > After playing around with reporting service for a few days,
> > I can see that it's very easy to create dataset and design page styles
> > within
> > the reporting service.
> > Now I have a question, what if a third party company send me a dataset
> (not
> > belong to reporting service),
> > then I want to pass this dataset to reporting service and do further
> > formatting and style. is that possible?
> >
> >
> >
>|||As far as I know, I dont think it is possible, because the data
regions(table/matrix) on the report has to be assigned to a dataset at design
time.
Surekha.
"Britney" wrote:
> oh... to be more specific, it seems like you always have to get the dataset
> from databases for reporting service,
> can I get it from different application (application level) then pass it
> into reporting service to use it?
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:uNLTsJn0EHA.1300@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> > After playing around with reporting service for a few days,
> > I can see that it's very easy to create dataset and design page styles
> > within
> > the reporting service.
> > Now I have a question, what if a third party company send me a dataset
> (not
> > belong to reporting service),
> > then I want to pass this dataset to reporting service and do further
> > formatting and style. is that possible?
> >
> >
> >
>
>|||As Bruce mentioned it is possible but it is not an easy process as one can
expect.
Read more in Books online for reporting service â' there is an update for
books online as well.
I am generating a dataset and saving it in XML format file with unique name.
Then I am passing that unique name to Report via parameter (url) and with
custom data processing extension I am able to read that xml file convert it
to a dataset which Reporting Services uses to render a report.
You will need to adjust example that you can find on the net to meet your
application needs.
Best of luck
"hf" wrote:
> As far as I know, I dont think it is possible, because the data
> regions(table/matrix) on the report has to be assigned to a dataset at design
> time.
> Surekha.
> "Britney" wrote:
> > oh... to be more specific, it seems like you always have to get the dataset
> > from databases for reporting service,
> > can I get it from different application (application level) then pass it
> > into reporting service to use it?
> >
> > "Britney" <britneychen_2001@.yahoo.com> wrote in message
> > news:uNLTsJn0EHA.1300@.TK2MSFTNGP14.phx.gbl...
> > > Hi,
> > > After playing around with reporting service for a few days,
> > > I can see that it's very easy to create dataset and design page styles
> > > within
> > > the reporting service.
> > > Now I have a question, what if a third party company send me a dataset
> > (not
> > > belong to reporting service),
> > > then I want to pass this dataset to reporting service and do further
> > > formatting and style. is that possible?
> > >
> > >
> > >
> >
> >
> >|||But as Surekha mentioned you can not use report design to acess dataset
schema structure to design a report. You will need to know a schema in
advance while designing it.
Dejan
"Pendula" wrote:
> As Bruce mentioned it is possible but it is not an easy process as one can
> expect.
> Read more in Books online for reporting service â' there is an update for
> books online as well.
> I am generating a dataset and saving it in XML format file with unique name.
> Then I am passing that unique name to Report via parameter (url) and with
> custom data processing extension I am able to read that xml file convert it
> to a dataset which Reporting Services uses to render a report.
> You will need to adjust example that you can find on the net to meet your
> application needs.
> Best of luck
> "hf" wrote:
> > As far as I know, I dont think it is possible, because the data
> > regions(table/matrix) on the report has to be assigned to a dataset at design
> > time.
> >
> > Surekha.
> >
> > "Britney" wrote:
> >
> > > oh... to be more specific, it seems like you always have to get the dataset
> > > from databases for reporting service,
> > > can I get it from different application (application level) then pass it
> > > into reporting service to use it?
> > >
> > > "Britney" <britneychen_2001@.yahoo.com> wrote in message
> > > news:uNLTsJn0EHA.1300@.TK2MSFTNGP14.phx.gbl...
> > > > Hi,
> > > > After playing around with reporting service for a few days,
> > > > I can see that it's very easy to create dataset and design page styles
> > > > within
> > > > the reporting service.
> > > > Now I have a question, what if a third party company send me a dataset
> > > (not
> > > > belong to reporting service),
> > > > then I want to pass this dataset to reporting service and do further
> > > > formatting and style. is that possible?
> > > >
> > > >
> > > >
> > >
> > >
> > >|||With version 1.0 you have to write a custom data extension to do so.
Accidentally, I wrote one which allows you to "bind" the report to an
ADO.NET dataset. And yes, it allows you drag-and-drop fields in the Report
Designer just like you would do it with standard RS datasets.
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
Moving forward, version 2005 will comes with WinForm and ASP.NET controls
which will support a disconnected mode where the application will be able to
bind the report to ADO.NET datasets.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Pendula" <Pendula@.discussions.microsoft.com> wrote in message
news:66B0063A-0BE4-4A73-B875-2227187A1580@.microsoft.com...
> But as Surekha mentioned you can not use report design to acess dataset
> schema structure to design a report. You will need to know a schema in
> advance while designing it.
> Dejan
> "Pendula" wrote:
> > As Bruce mentioned it is possible but it is not an easy process as one
can
> > expect.
> > Read more in Books online for reporting service - there is an update for
> > books online as well.
> > I am generating a dataset and saving it in XML format file with unique
name.
> > Then I am passing that unique name to Report via parameter (url) and
with
> > custom data processing extension I am able to read that xml file convert
it
> > to a dataset which Reporting Services uses to render a report.
> >
> > You will need to adjust example that you can find on the net to meet
your
> > application needs.
> >
> > Best of luck
> >
> > "hf" wrote:
> >
> > > As far as I know, I dont think it is possible, because the data
> > > regions(table/matrix) on the report has to be assigned to a dataset at
design
> > > time.
> > >
> > > Surekha.
> > >
> > > "Britney" wrote:
> > >
> > > > oh... to be more specific, it seems like you always have to get the
dataset
> > > > from databases for reporting service,
> > > > can I get it from different application (application level) then
pass it
> > > > into reporting service to use it?
> > > >
> > > > "Britney" <britneychen_2001@.yahoo.com> wrote in message
> > > > news:uNLTsJn0EHA.1300@.TK2MSFTNGP14.phx.gbl...
> > > > > Hi,
> > > > > After playing around with reporting service for a few days,
> > > > > I can see that it's very easy to create dataset and design page
styles
> > > > > within
> > > > > the reporting service.
> > > > > Now I have a question, what if a third party company send me a
dataset
> > > > (not
> > > > > belong to reporting service),
> > > > > then I want to pass this dataset to reporting service and do
further
> > > > > formatting and style. is that possible?
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >|||I have service pack 1, is there already a custom data extension build-in in
reporting service?
"Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
news:%23uV08k80EHA.1400@.TK2MSFTNGP11.phx.gbl...
> With version 1.0 you have to write a custom data extension to do so.
> Accidentally, I wrote one which allows you to "bind" the report to an
> ADO.NET dataset. And yes, it allows you drag-and-drop fields in the Report
> Designer just like you would do it with standard RS datasets.
>
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
> Moving forward, version 2005 will comes with WinForm and ASP.NET controls
> which will support a disconnected mode where the application will be able
to
> bind the report to ADO.NET datasets.
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Pendula" <Pendula@.discussions.microsoft.com> wrote in message
> news:66B0063A-0BE4-4A73-B875-2227187A1580@.microsoft.com...
> > But as Surekha mentioned you can not use report design to acess dataset
> > schema structure to design a report. You will need to know a schema in
> > advance while designing it.
> >
> > Dejan
> >
> > "Pendula" wrote:
> >
> > > As Bruce mentioned it is possible but it is not an easy process as one
> can
> > > expect.
> > > Read more in Books online for reporting service - there is an update
for
> > > books online as well.
> > > I am generating a dataset and saving it in XML format file with unique
> name.
> > > Then I am passing that unique name to Report via parameter (url) and
> with
> > > custom data processing extension I am able to read that xml file
convert
> it
> > > to a dataset which Reporting Services uses to render a report.
> > >
> > > You will need to adjust example that you can find on the net to meet
> your
> > > application needs.
> > >
> > > Best of luck
> > >
> > > "hf" wrote:
> > >
> > > > As far as I know, I dont think it is possible, because the data
> > > > regions(table/matrix) on the report has to be assigned to a dataset
at
> design
> > > > time.
> > > >
> > > > Surekha.
> > > >
> > > > "Britney" wrote:
> > > >
> > > > > oh... to be more specific, it seems like you always have to get
the
> dataset
> > > > > from databases for reporting service,
> > > > > can I get it from different application (application level) then
> pass it
> > > > > into reporting service to use it?
> > > > >
> > > > > "Britney" <britneychen_2001@.yahoo.com> wrote in message
> > > > > news:uNLTsJn0EHA.1300@.TK2MSFTNGP14.phx.gbl...
> > > > > > Hi,
> > > > > > After playing around with reporting service for a few days,
> > > > > > I can see that it's very easy to create dataset and design page
> styles
> > > > > > within
> > > > > > the reporting service.
> > > > > > Now I have a question, what if a third party company send me a
> dataset
> > > > > (not
> > > > > > belong to reporting service),
> > > > > > then I want to pass this dataset to reporting service and do
> further
> > > > > > formatting and style. is that possible?
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
>|||I read the custom data extension 's readme file,
it said the following,
URL
Due to the URL size limitations, passing a large dataset as a query
parameter is not possible.
so my question is, what is the size limit?
"Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
news:%23uV08k80EHA.1400@.TK2MSFTNGP11.phx.gbl...
> With version 1.0 you have to write a custom data extension to do so.
> Accidentally, I wrote one which allows you to "bind" the report to an
> ADO.NET dataset. And yes, it allows you drag-and-drop fields in the Report
> Designer just like you would do it with standard RS datasets.
>
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
> Moving forward, version 2005 will comes with WinForm and ASP.NET controls
> which will support a disconnected mode where the application will be able
to
> bind the report to ADO.NET datasets.
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Pendula" <Pendula@.discussions.microsoft.com> wrote in message
> news:66B0063A-0BE4-4A73-B875-2227187A1580@.microsoft.com...
> > But as Surekha mentioned you can not use report design to acess dataset
> > schema structure to design a report. You will need to know a schema in
> > advance while designing it.
> >
> > Dejan
> >
> > "Pendula" wrote:
> >
> > > As Bruce mentioned it is possible but it is not an easy process as one
> can
> > > expect.
> > > Read more in Books online for reporting service - there is an update
for
> > > books online as well.
> > > I am generating a dataset and saving it in XML format file with unique
> name.
> > > Then I am passing that unique name to Report via parameter (url) and
> with
> > > custom data processing extension I am able to read that xml file
convert
> it
> > > to a dataset which Reporting Services uses to render a report.
> > >
> > > You will need to adjust example that you can find on the net to meet
> your
> > > application needs.
> > >
> > > Best of luck
> > >
> > > "hf" wrote:
> > >
> > > > As far as I know, I dont think it is possible, because the data
> > > > regions(table/matrix) on the report has to be assigned to a dataset
at
> design
> > > > time.
> > > >
> > > > Surekha.
> > > >
> > > > "Britney" wrote:
> > > >
> > > > > oh... to be more specific, it seems like you always have to get
the
> dataset
> > > > > from databases for reporting service,
> > > > > can I get it from different application (application level) then
> pass it
> > > > > into reporting service to use it?
> > > > >
> > > > > "Britney" <britneychen_2001@.yahoo.com> wrote in message
> > > > > news:uNLTsJn0EHA.1300@.TK2MSFTNGP14.phx.gbl...
> > > > > > Hi,
> > > > > > After playing around with reporting service for a few days,
> > > > > > I can see that it's very easy to create dataset and design page
> styles
> > > > > > within
> > > > > > the reporting service.
> > > > > > Now I have a question, what if a third party company send me a
> dataset
> > > > > (not
> > > > > > belong to reporting service),
> > > > > > then I want to pass this dataset to reporting service and do
> further
> > > > > > formatting and style. is that possible?
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
>|||Britney,
There isn't a builtin extension that will allow you do what you want. There
is a sample File Share data extension which comes with the RS sample and can
be used as a starting point. However, you can save yourself a lot of effort
by using mine.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:eq9Opei1EHA.1452@.TK2MSFTNGP11.phx.gbl...
> I have service pack 1, is there already a custom data extension build-in
in
> reporting service?
>
> "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
> news:%23uV08k80EHA.1400@.TK2MSFTNGP11.phx.gbl...
> > With version 1.0 you have to write a custom data extension to do so.
> > Accidentally, I wrote one which allows you to "bind" the report to an
> > ADO.NET dataset. And yes, it allows you drag-and-drop fields in the
Report
> > Designer just like you would do it with standard RS datasets.
> >
> >
>
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
> >
> > Moving forward, version 2005 will comes with WinForm and ASP.NET
controls
> > which will support a disconnected mode where the application will be
able
> to
> > bind the report to ADO.NET datasets.
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MVP [SQL Server], MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ---
> >
> > "Pendula" <Pendula@.discussions.microsoft.com> wrote in message
> > news:66B0063A-0BE4-4A73-B875-2227187A1580@.microsoft.com...
> > > But as Surekha mentioned you can not use report design to acess
dataset
> > > schema structure to design a report. You will need to know a schema in
> > > advance while designing it.
> > >
> > > Dejan
> > >
> > > "Pendula" wrote:
> > >
> > > > As Bruce mentioned it is possible but it is not an easy process as
one
> > can
> > > > expect.
> > > > Read more in Books online for reporting service - there is an update
> for
> > > > books online as well.
> > > > I am generating a dataset and saving it in XML format file with
unique
> > name.
> > > > Then I am passing that unique name to Report via parameter (url)
and
> > with
> > > > custom data processing extension I am able to read that xml file
> convert
> > it
> > > > to a dataset which Reporting Services uses to render a report.
> > > >
> > > > You will need to adjust example that you can find on the net to meet
> > your
> > > > application needs.
> > > >
> > > > Best of luck
> > > >
> > > > "hf" wrote:
> > > >
> > > > > As far as I know, I dont think it is possible, because the data
> > > > > regions(table/matrix) on the report has to be assigned to a
dataset
> at
> > design
> > > > > time.
> > > > >
> > > > > Surekha.
> > > > >
> > > > > "Britney" wrote:
> > > > >
> > > > > > oh... to be more specific, it seems like you always have to get
> the
> > dataset
> > > > > > from databases for reporting service,
> > > > > > can I get it from different application (application level) then
> > pass it
> > > > > > into reporting service to use it?
> > > > > >
> > > > > > "Britney" <britneychen_2001@.yahoo.com> wrote in message
> > > > > > news:uNLTsJn0EHA.1300@.TK2MSFTNGP14.phx.gbl...
> > > > > > > Hi,
> > > > > > > After playing around with reporting service for a few days,
> > > > > > > I can see that it's very easy to create dataset and design
page
> > styles
> > > > > > > within
> > > > > > > the reporting service.
> > > > > > > Now I have a question, what if a third party company send me
a
> > dataset
> > > > > > (not
> > > > > > > belong to reporting service),
> > > > > > > then I want to pass this dataset to reporting service and do
> > further
> > > > > > > formatting and style. is that possible?
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> >
> >
>|||The IE limit is 2K and due to this limitation passing a dataset by URL is
not practical. Instead, my dataset extension supports:
1. Passing the serialized dataset XML (typically used with SOAP
addressability).
2. Reporting from saved as files datasets. In this case, you can pass the
file path as a parameter and render the report via URL.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:O%23%23ZXki1EHA.2824@.TK2MSFTNGP09.phx.gbl...
> I read the custom data extension 's readme file,
> it said the following,
> URL
> Due to the URL size limitations, passing a large dataset as a query
> parameter is not possible.
> so my question is, what is the size limit?
>
> "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
> news:%23uV08k80EHA.1400@.TK2MSFTNGP11.phx.gbl...
> > With version 1.0 you have to write a custom data extension to do so.
> > Accidentally, I wrote one which allows you to "bind" the report to an
> > ADO.NET dataset. And yes, it allows you drag-and-drop fields in the
Report
> > Designer just like you would do it with standard RS datasets.
> >
> >
>
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
> >
> > Moving forward, version 2005 will comes with WinForm and ASP.NET
controls
> > which will support a disconnected mode where the application will be
able
> to
> > bind the report to ADO.NET datasets.
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MVP [SQL Server], MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ---
> >
> > "Pendula" <Pendula@.discussions.microsoft.com> wrote in message
> > news:66B0063A-0BE4-4A73-B875-2227187A1580@.microsoft.com...
> > > But as Surekha mentioned you can not use report design to acess
dataset
> > > schema structure to design a report. You will need to know a schema in
> > > advance while designing it.
> > >
> > > Dejan
> > >
> > > "Pendula" wrote:
> > >
> > > > As Bruce mentioned it is possible but it is not an easy process as
one
> > can
> > > > expect.
> > > > Read more in Books online for reporting service - there is an update
> for
> > > > books online as well.
> > > > I am generating a dataset and saving it in XML format file with
unique
> > name.
> > > > Then I am passing that unique name to Report via parameter (url)
and
> > with
> > > > custom data processing extension I am able to read that xml file
> convert
> > it
> > > > to a dataset which Reporting Services uses to render a report.
> > > >
> > > > You will need to adjust example that you can find on the net to meet
> > your
> > > > application needs.
> > > >
> > > > Best of luck
> > > >
> > > > "hf" wrote:
> > > >
> > > > > As far as I know, I dont think it is possible, because the data
> > > > > regions(table/matrix) on the report has to be assigned to a
dataset
> at
> > design
> > > > > time.
> > > > >
> > > > > Surekha.
> > > > >
> > > > > "Britney" wrote:
> > > > >
> > > > > > oh... to be more specific, it seems like you always have to get
> the
> > dataset
> > > > > > from databases for reporting service,
> > > > > > can I get it from different application (application level) then
> > pass it
> > > > > > into reporting service to use it?
> > > > > >
> > > > > > "Britney" <britneychen_2001@.yahoo.com> wrote in message
> > > > > > news:uNLTsJn0EHA.1300@.TK2MSFTNGP14.phx.gbl...
> > > > > > > Hi,
> > > > > > > After playing around with reporting service for a few days,
> > > > > > > I can see that it's very easy to create dataset and design
page
> > styles
> > > > > > > within
> > > > > > > the reporting service.
> > > > > > > Now I have a question, what if a third party company send me
a
> > dataset
> > > > > > (not
> > > > > > > belong to reporting service),
> > > > > > > then I want to pass this dataset to reporting service and do
> > further
> > > > > > > formatting and style. is that possible?
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> >
> >
>

Question about How Data can be rendered on Report

I have a dataset that is comprised of several records of employees leave
types and hours. Each leave type record is unique(sicks on record and
vacation another). 2 fields of each record,I group on (Employee and
TimePeriod) and the Leave type hours I want to display in separate columns.
What I am trying to do is group by Employee with a page break after each
employee(this was easy), then group by period(same page, this was done too),
but I also need to put the hours in different columns depending on the Leave
type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT RECORD TO GO IN
THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW ROW
CREATED.
i.e)
NAME Time Sick Vacation
Employee Name
1/1/2005-1/8/2005 8 8
1/8/2005-1/15/2005 4 0
BUT INSTEAD I AM GETTING THIS
NAME Time Sick Vacation
Employee Name
1/1/2005-1/8/2005 8 0
1/1/2005-1/8/2005 0 8
1/8/2005-1/15/2005 4 0
1/8/2005-1/15/2005 0 4
I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE HELP'?Create the two text boxes for Sick and Vacation... For the values use an
expression like
for Sick
=Iif(Fields!LeaveType.Value="Sick",Hours,0)
for Leave textbox
=Iif(Fields!LeaveTypeValue="Vacation",Hours,0)
Hope this helps...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"dillig" <dillig@.discussions.microsoft.com> wrote in message
news:889F62C5-3617-464D-BCEA-267787AAB37E@.microsoft.com...
> I have a dataset that is comprised of several records of employees leave
> types and hours. Each leave type record is unique(sicks on record and
> vacation another). 2 fields of each record,I group on (Employee and
> TimePeriod) and the Leave type hours I want to display in separate
columns.
> What I am trying to do is group by Employee with a page break after each
> employee(this was easy), then group by period(same page, this was done
too),
> but I also need to put the hours in different columns depending on the
Leave
> type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT RECORD TO GO
IN
> THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW
ROW
> CREATED.
> i.e)
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 8
> 1/8/2005-1/15/2005 4 0
>
> BUT INSTEAD I AM GETTING THIS
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 0
> 1/1/2005-1/8/2005 0 8
> 1/8/2005-1/15/2005 4 0
> 1/8/2005-1/15/2005 0 4
> I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE HELP'?
>|||I used the Iif statement you wrote. It works fine, but what I don't want it
to do is to put the next record in the dataset onto the next row. I want the
next record to go into the next column as in previous post example. I only
want the next dataset record to go into the next row if the group changes.
See the example for clarification.
"Wayne Snyder" wrote:
> Create the two text boxes for Sick and Vacation... For the values use an
> expression like
> for Sick
> =Iif(Fields!LeaveType.Value="Sick",Hours,0)
> for Leave textbox
> =Iif(Fields!LeaveTypeValue="Vacation",Hours,0)
> Hope this helps...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "dillig" <dillig@.discussions.microsoft.com> wrote in message
> news:889F62C5-3617-464D-BCEA-267787AAB37E@.microsoft.com...
> > I have a dataset that is comprised of several records of employees leave
> > types and hours. Each leave type record is unique(sicks on record and
> > vacation another). 2 fields of each record,I group on (Employee and
> > TimePeriod) and the Leave type hours I want to display in separate
> columns.
> > What I am trying to do is group by Employee with a page break after each
> > employee(this was easy), then group by period(same page, this was done
> too),
> > but I also need to put the hours in different columns depending on the
> Leave
> > type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT RECORD TO GO
> IN
> > THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW
> ROW
> > CREATED.
> > i.e)
> > NAME Time Sick Vacation
> > Employee Name
> > 1/1/2005-1/8/2005 8 8
> > 1/8/2005-1/15/2005 4 0
> >
> >
> > BUT INSTEAD I AM GETTING THIS
> > NAME Time Sick Vacation
> > Employee Name
> > 1/1/2005-1/8/2005 8 0
> > 1/1/2005-1/8/2005 0 8
> > 1/8/2005-1/15/2005 4 0
> > 1/8/2005-1/15/2005 0 4
> > I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE HELP'?
> >
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_01AB_01C4F4BE.37F23400
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
Create a grouping on Employee and DateRange and drop the Employee name, =date range (week, whatever it's called), and the Sum() for Vacation and =Sick in the Group Footer. Make sure you use the group name as the scope =for the Sum (IOW, =3DSum(Fields!SickTime.Value, "GroupName") where ="GroupName" is the name fo the group).
You won't have any values dropping into the detail rows and you can =place the Employee Name in the group header row if you don't want it to =repeat for each row. Make sure you also sort the data set by Employee =and Time.
HTH.
-- TIM ELLISON
"dillig" <dillig@.discussions.microsoft.com> wrote in message =news:1C382976-466F-4AF5-9688-CCCAEB1460AB@.microsoft.com...
I used the Iif statement you wrote. It works fine, but what I don't =want it to do is to put the next record in the dataset onto the next row. I =want the next record to go into the next column as in previous post example. I =only want the next dataset record to go into the next row if the group =changes. See the example for clarification.
"Wayne Snyder" wrote:
> Create the two text boxes for Sick and Vacation... For the values =use an
> expression like
> for Sick
> =3DIif(Fields!LeaveType.Value=3D"Sick",Hours,0)
> for Leave textbox
> =3DIif(Fields!LeaveTypeValue=3D"Vacation",Hours,0)
> > Hope this helps...
> > -- > Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> > I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> > "dillig" <dillig@.discussions.microsoft.com> wrote in message
> news:889F62C5-3617-464D-BCEA-267787AAB37E@.microsoft.com...
> > I have a dataset that is comprised of several records of employees =leave
> > types and hours. Each leave type record is unique(sicks on record =and
> > vacation another). 2 fields of each record,I group on (Employee =and
> > TimePeriod) and the Leave type hours I want to display in separate
> columns.
> > What I am trying to do is group by Employee with a page break =after each
> > employee(this was easy), then group by period(same page, this was =done
> too),
> > but I also need to put the hours in different columns depending on =the
> Leave
> > type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT =RECORD TO GO
> IN
> > THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I =WANT A NEW
> ROW
> > CREATED.
> > i.e)
> > NAME Time Sick =Vacation
> > Employee Name
> > 1/1/2005-1/8/2005 8 8
> > 1/8/2005-1/15/2005 4 0
> >
> >
> > BUT INSTEAD I AM GETTING THIS
> > NAME Time Sick =Vacation
> > Employee Name
> > 1/1/2005-1/8/2005 8 0
> > 1/1/2005-1/8/2005 0 8
> > 1/8/2005-1/15/2005 4 0
> > 1/8/2005-1/15/2005 0 4
> > I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE =HELP'?
> >
> > >
--=_NextPart_000_01AB_01C4F4BE.37F23400
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Create a grouping on Employee and =DateRange and drop the Employee name, date range (week, whatever it's called), and the =Sum() for Vacation and Sick in the Group Footer. Make sure you use the =group name as the scope for the Sum (IOW, ==3DSum(Fields!SickTime.Value, "GroupName") where "GroupName" is the name fo the =group).
You won't have any values dropping into =the detail rows and you can place the Employee Name in the group header row if you =don't want it to repeat for each row. Make sure you also sort the data set by =Employee and Time.
HTH.
-- TIM ELLISON
"dillig" wrote in message news:1C3=82976-466F-4AF5-9688-CCCAEB1460AB@.microsoft.com...I used the Iif statement you wrote. It works fine, but what I don't want =it to do is to put the next record in the dataset onto the next row. =I want the next record to go into the next column as in previous post =example. I only want the next dataset record to go into the next row if the =group changes. See the example for clarification."Wayne Snyder" wrote:> Create the two text boxes for Sick and Vacation... =For the values use an> expression like> for Sick> =3DIif(Fields!LeaveType.Value=3D"Sick",Hours,0)> for Leave =textbox> =3DIif(Fields!LeaveTypeValue=3D"Vacation",Hours,0)> > =Hope this helps...> > -- > Wayne Snyder, MCDBA, SQL Server MVP> Mariner, Charlotte, NC>">http://www.mariner-usa.com">www.mariner-usa.com> =(Please respond only to the newsgroups.)> > I support the =Professional Association of SQL Server (PASS) and it's> community of SQL =Server professionals.>">http://www.sqlpass.org">www.sqlpass.org> > ="dillig" wrote in message> news:889=F62C5-3617-464D-BCEA-267787AAB37E@.microsoft.com...> > I have a dataset that is comprised of several records of =employees leave> > types and hours. Each leave type record is =unique(sicks on record and> > vacation another). 2 fields of each record,I =group on (Employee and> > TimePeriod) and the Leave type hours I want =to display in separate> columns.> > What I am trying to =do is group by Employee with a page break after each> > =employee(this was easy), then group by period(same page, this was done> =too),> > but I also need to put the hours in different columns depending =on the> Leave> > type. BUT I DONT WANT IT TO CREATE A =NEW ROW. I WANT THE NEXT RECORD TO GO> IN> > THE NEXT COLUMN =UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW> ROW> => CREATED.> > i.e)> = > =NAME &nb=sp; =Time &nb=sp; Sick Vacation> > Employee Name> => &nb=sp; &nbs=p; 1/1/2005-1/8/2005 =8 = 8> => &nb=sp; &nbs=p; 1/8/2005-1/15/2005 =4 = 0> >> >> > BUT INSTEAD I AM GETTING =THIS> > =NAME &nb=sp; =Time &nb=sp; Sick Vacation> > Employee Name> => &nb=sp; &nbs=p; 1/1/2005-1/8/2005 =8 = 0> => &nb=sp; &nbs=p; 1/1/2005-1/8/2005 =0 = 8> => &nb=sp; &nbs=p; 1/8/2005-1/15/2005 =4 = 0> => &nb=sp; &nbs=p; 1/8/2005-1/15/2005 =0 = 4> > I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. =CAN ANYONE HELP'?> >> > =>

---

--=_NextPart_000_01AB_01C4F4BE.37F23400--|||Have you tried using a Matrix instead of a table? Put time in the rows and
leave type in the columns.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"dillig" <dillig@.discussions.microsoft.com> wrote in message
news:889F62C5-3617-464D-BCEA-267787AAB37E@.microsoft.com...
>I have a dataset that is comprised of several records of employees leave
> types and hours. Each leave type record is unique(sicks on record and
> vacation another). 2 fields of each record,I group on (Employee and
> TimePeriod) and the Leave type hours I want to display in separate
> columns.
> What I am trying to do is group by Employee with a page break after each
> employee(this was easy), then group by period(same page, this was done
> too),
> but I also need to put the hours in different columns depending on the
> Leave
> type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT RECORD TO GO
> IN
> THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW
> ROW
> CREATED.
> i.e)
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 8
> 1/8/2005-1/15/2005 4 0
>
> BUT INSTEAD I AM GETTING THIS
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 0
> 1/1/2005-1/8/2005 0 8
> 1/8/2005-1/15/2005 4 0
> 1/8/2005-1/15/2005 0 4
> I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE HELP'?
>|||Thanks to all who replied to this post. Creating a second grouping from
timeperiod and putting the totals for the inside group in group2's footer
worked great.
Your guys are awesome.
"dillig" wrote:
> I have a dataset that is comprised of several records of employees leave
> types and hours. Each leave type record is unique(sicks on record and
> vacation another). 2 fields of each record,I group on (Employee and
> TimePeriod) and the Leave type hours I want to display in separate columns.
> What I am trying to do is group by Employee with a page break after each
> employee(this was easy), then group by period(same page, this was done too),
> but I also need to put the hours in different columns depending on the Leave
> type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT RECORD TO GO IN
> THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW ROW
> CREATED.
> i.e)
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 8
> 1/8/2005-1/15/2005 4 0
>
> BUT INSTEAD I AM GETTING THIS
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 0
> 1/1/2005-1/8/2005 0 8
> 1/8/2005-1/15/2005 4 0
> 1/8/2005-1/15/2005 0 4
> I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE HELP'?
>

Question about a SQL Reporting Tutorial Example

In the BOL "Walkthrough - Using a Dynamic Query in a Report" it says:
In Data view, select the Employees dataset, and then use the generic
query designer to replace the original query with the following
expression:
="SELECT FirstName, LastName, Title FROM Employee" &
IIf(Parameters!Department.Value = 0,""," WHERE (DepartmentID = " &
Parameters!Department.Value & ")") & " ORDER BY LastName"
This expression
If I put that in the generic query designer it will not validate the
SQL.
Can anyone help with this?Expression-based queries cannot be validated and executed in the generic
query designer, because it does not evaluate the VB.NET expressions. They
are only evaluated at report runtime.
The general recommendation for dynamic queries using VB.NET expressions is
to start with a static query, then add a textbox to the report with the
dynamic query expression you want to use. Run the report to verify that the
expression-based commandtext evaluates to valid SQL, and then in the last
step replace the static query with the dynamic expression-based query. In
addition, the fields list is only auto-detected from non expression-based
queries.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris" <cmay@.walshgroup.com> wrote in message
news:44f6e099.0409051123.191910b6@.posting.google.com...
> In the BOL "Walkthrough - Using a Dynamic Query in a Report" it says:
> In Data view, select the Employees dataset, and then use the generic
> query designer to replace the original query with the following
> expression:
> ="SELECT FirstName, LastName, Title FROM Employee" &
> IIf(Parameters!Department.Value = 0,""," WHERE (DepartmentID = " &
> Parameters!Department.Value & ")") & " ORDER BY LastName"
> This expression
> If I put that in the generic query designer it will not validate the
> SQL.
> Can anyone help with this?

Question

Hello all,

I have the following dataset returning top 20 records for a day, changing daily

STATE

USER ID

SALES REP

SALES

QLD

6758

Liam Maddrell

12

QLD

6677

Lisa-Maree Findlay

11

QLD

6133

Benjamin Matthews

11

QLD

6299

Simon Tabet

11

QLD

6112

Deborah Williams

10

VIC

3428

Peter Mahar

10

QLD

5134

Russell Den Engelse

9

WA

5279

Julie Isitt

8

NSW

5677

Pranav Agarwal

8

NSW

6093

Rosa Delizia

8

QLD

6684

Ron Prasad

8

WA

7578

Francis Williams

8

Qld

7511

LigIA Borodi

7

QLD

6804

Rupert Ryan

7

QLD

6856

Maxwell MacLean

7

QLD

6904

Trent Sticklen

7

QLD

7236

Drazen Knezevic

7

QLD

7406

Amy Donaldson

7

NSW

5601

Michael Phillips

7

QLD

5940

Harnake guraya

7

I would like too now sum up the figures to get in a separate layout (total sales off the above reps by state)

Queensland 168

South
Australia

0

Western
Australia

176

Victoria

40

New South
Wales

15

Northern
Territory

0

I have tried a different dataset but it doesnt allow me to do this due to the use off top 20 in the query which needs to be grouped by state this time and to do that it doesnt know the topn 20 records.

...I am trying to figure out a way by using textboxes , tables etc, I have managed to get the results coming out in a table using grouping by state and sum of the values but because there are no records for Sth Australia and Northern Territory these are not on the list...and i need these also in the list...can someone please help

thanks

Can someone help me with some code to achieve the above ?

thanks

|||

I don't have code for you, but in your query, you need to specify something like isnull(NorthernTerritory,0)

This will return a 0 (zero) instead of a null and then the data should show up on your report.

Since you are doing a topN, why don't you use a group by in your report?

|||

I cant do a group by as a rep can have more than 1 sale and to get the top 20 it is adding up all his sales already

ie

wa John 20 item 15

wa john 15 item 20

To get the top 20 i add the sum off the values together to get the rep...

ie

wa john 35

if i group by state john will be treated as 2 records and the numbers wont be for the top 20 sales rep...but just the top 20 sales

thanks for trying can anyone else help?

|||

Hi,

I found it hard to understand why cant you use grouping and othere data sets,

I have a solution but It is an ugly one.... (-:

first change your Data set to return a sum colums
by addind a join in your SQL to a query tha group by the State and sum the sales

after you do that you will have all the results you need in the same data set

now you jost have to add the colums to the feilds and use the First function in the expresions to

get the currect total sales for the state

STATE

USER ID

SALES REP

SALES sum

QLD

6758

Liam Maddrell

12 168

QLD

6677

Lisa-Maree Findlay

11 168

QLD

6133

Benjamin Matthews

11 168

QLD

6299

Simon Tabet

11

QLD

6112

Deborah Williams

10

VIC

3428

Peter Mahar

10

QLD

5134

Russell Den Engelse

9

WA

5279

Julie Isitt

8

NSW

5677

Pranav Agarwal

8

NSW

6093

Rosa Delizia

8

QLD

6684

Ron Prasad

8

WA

7578

Francis Williams

8

Qld

7511

LigIA Borodi

7

QLD

6804

Rupert Ryan

7

QLD

6856

Maxwell MacLean

7

QLD

6904

Trent Sticklen

7

QLD

7236

Drazen Knezevic

7

QLD

7406

Amy Donaldson

7

NSW

5601

Michael Phillips

7

QLD

5940

Harnake guraya

7