How to create a group on selected report parameters?

Hello,
Using SSRS 2005 SP2, I have a report that has a multivalue parameter of US
states in a report that returns orders.
I successfully can return order numbers along with the associated state
where the order was placed.
However, if I select the states of NH, VT, and MA and there are no orders
from VT, I still need a group header for VT to show that there was no data
for VT. So my thought was to create a report grouping based on the states
selected in the multivalue State parameter. I can't find a way to do this.
Also, I know I could use a subreport to resolve this but it is not an option
here. I need this report to be exported to MS Excel and subreports do not
export to Excel.
Thank you,
JustinOn Oct 3, 3:48 pm, justinsaraceno
<justinsarac...@.discussions.microsoft.com> wrote:
> Hello,
> Using SSRS 2005 SP2, I have a report that has a multivalue parameter of US
> states in a report that returns orders.
> I successfully can return order numbers along with the associated state
> where the order was placed.
> However, if I select the states of NH, VT, and MA and there are no orders
> from VT, I still need a group header for VT to show that there was no data
> for VT. So my thought was to create a report grouping based on the states
> selected in the multivalue State parameter. I can't find a way to do this.
> Also, I know I could use a subreport to resolve this but it is not an option
> here. I need this report to be exported to MS Excel and subreports do not
> export to Excel.
> Thank you,
> Justin
>From your description, you will most likely want to insert dummy rows
in the returned dataset from the stored procedure/query that is
sourcing the report based on the parsing out of the multi-select
parameter. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thank you Enrique. It's not the answer I wanted to hear but I suppose it's a
possible solution. I wish there was a better way than having to manipulate
my dataset with dummy data. Thank you again.
"EMartinez" wrote:
> On Oct 3, 3:48 pm, justinsaraceno
> <justinsarac...@.discussions.microsoft.com> wrote:
> > Hello,
> > Using SSRS 2005 SP2, I have a report that has a multivalue parameter of US
> > states in a report that returns orders.
> >
> > I successfully can return order numbers along with the associated state
> > where the order was placed.
> >
> > However, if I select the states of NH, VT, and MA and there are no orders
> > from VT, I still need a group header for VT to show that there was no data
> > for VT. So my thought was to create a report grouping based on the states
> > selected in the multivalue State parameter. I can't find a way to do this.
> >
> > Also, I know I could use a subreport to resolve this but it is not an option
> > here. I need this report to be exported to MS Excel and subreports do not
> > export to Excel.
> >
> > Thank you,
> > Justin
>
> >From your description, you will most likely want to insert dummy rows
> in the returned dataset from the stored procedure/query that is
> sourcing the report based on the parsing out of the multi-select
> parameter. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||Hi,
I have the same problem. Did you find any solution to that?
--
Thanks,
Tabbey
"justinsaraceno" wrote:
> Thank you Enrique. It's not the answer I wanted to hear but I suppose it's a
> possible solution. I wish there was a better way than having to manipulate
> my dataset with dummy data. Thank you again.
> "EMartinez" wrote:
> > On Oct 3, 3:48 pm, justinsaraceno
> > <justinsarac...@.discussions.microsoft.com> wrote:
> > > Hello,
> > > Using SSRS 2005 SP2, I have a report that has a multivalue parameter of US
> > > states in a report that returns orders.
> > >
> > > I successfully can return order numbers along with the associated state
> > > where the order was placed.
> > >
> > > However, if I select the states of NH, VT, and MA and there are no orders
> > > from VT, I still need a group header for VT to show that there was no data
> > > for VT. So my thought was to create a report grouping based on the states
> > > selected in the multivalue State parameter. I can't find a way to do this.
> > >
> > > Also, I know I could use a subreport to resolve this but it is not an option
> > > here. I need this report to be exported to MS Excel and subreports do not
> > > export to Excel.
> > >
> > > Thank you,
> > > Justin
> >
> >
> > >From your description, you will most likely want to insert dummy rows
> > in the returned dataset from the stored procedure/query that is
> > sourcing the report based on the parsing out of the multi-select
> > parameter. Hope this helps.
> >
> > Regards,
> >
> > Enrique Martinez
> > Sr. Software Consultant
> >
> >|||I didn't find anything better than creating some dummy data via something
like a temp table. If SSRS supported export to excel for reports with
subreports, there wouldn't be such a hack to get around this.
"tabbey" wrote:
> Hi,
> I have the same problem. Did you find any solution to that?
> --
> Thanks,
> Tabbey
>
> "justinsaraceno" wrote:
> > Thank you Enrique. It's not the answer I wanted to hear but I suppose it's a
> > possible solution. I wish there was a better way than having to manipulate
> > my dataset with dummy data. Thank you again.
> >
> > "EMartinez" wrote:
> >
> > > On Oct 3, 3:48 pm, justinsaraceno
> > > <justinsarac...@.discussions.microsoft.com> wrote:
> > > > Hello,
> > > > Using SSRS 2005 SP2, I have a report that has a multivalue parameter of US
> > > > states in a report that returns orders.
> > > >
> > > > I successfully can return order numbers along with the associated state
> > > > where the order was placed.
> > > >
> > > > However, if I select the states of NH, VT, and MA and there are no orders
> > > > from VT, I still need a group header for VT to show that there was no data
> > > > for VT. So my thought was to create a report grouping based on the states
> > > > selected in the multivalue State parameter. I can't find a way to do this.
> > > >
> > > > Also, I know I could use a subreport to resolve this but it is not an option
> > > > here. I need this report to be exported to MS Excel and subreports do not
> > > > export to Excel.
> > > >
> > > > Thank you,
> > > > Justin
> > >
> > >
> > > >From your description, you will most likely want to insert dummy rows
> > > in the returned dataset from the stored procedure/query that is
> > > sourcing the report based on the parsing out of the multi-select
> > > parameter. Hope this helps.
> > >
> > > Regards,
> > >
> > > Enrique Martinez
> > > Sr. Software Consultant
> > >
> > >|||On Oct 4, 8:08 am, justinsaraceno
<justinsarac...@.discussions.microsoft.com> wrote:
> Thank you Enrique. It's not the answer I wanted to hear but I suppose it's a
> possible solution. I wish there was a better way than having to manipulate
> my dataset with dummy data. Thank you again.
> "EMartinez" wrote:
> > On Oct 3, 3:48 pm, justinsaraceno
> > <justinsarac...@.discussions.microsoft.com> wrote:
> > > Hello,
> > > Using SSRS 2005 SP2, I have a report that has a multivalue parameter of US
> > > states in a report that returns orders.
> > > I successfully can return order numbers along with the associated state
> > > where the order was placed.
> > > However, if I select the states of NH, VT, and MA and there are no orders
> > > from VT, I still need a group header for VT to show that there was no data
> > > for VT. So my thought was to create a report grouping based on the states
> > > selected in the multivalue State parameter. I can't find a way to do this.
> > > Also, I know I could use a subreport to resolve this but it is not an option
> > > here. I need this report to be exported to MS Excel and subreports do not
> > > export to Excel.
> > > Thank you,
> > > Justin
> > >From your description, you will most likely want to insert dummy rows
> > in the returned dataset from the stored procedure/query that is
> > sourcing the report based on the parsing out of the multi-select
> > parameter. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Sorry that I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

how to correctly finding memory usage.

i have a box running very slow. it has 16 gb of ram, running
a SQL 2005 Enterprise Edition (64-bit), build 9.00.3161.00, SP2 on
a win2003 R2 enterprise x64 with SP2.
during the non-busy hours, Task Manager shows 4-50 KB ram available. what
is the right way to free up the memory? is there a way to see what are the
resources not released? (like top or ps -ef |grep....)
tried the neat Activity Monitor, nothing shows where the 15 gb are used.
thanks.You need to ensure that the max memory option of sql server is set to leave
memory for the OS and any other apps that may run on the server. With 16GB
total I would recommend setting it to 14GB and see how that goes. You can
set this via sp_configure or SSMS.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:FD5F89D0-9942-4BB1-A08C-D87516983F85@.microsoft.com...
>i have a box running very slow. it has 16 gb of ram, running
> a SQL 2005 Enterprise Edition (64-bit), build 9.00.3161.00, SP2 on
> a win2003 R2 enterprise x64 with SP2.
> during the non-busy hours, Task Manager shows 4-50 KB ram available.
> what
> is the right way to free up the memory? is there a way to see what are
> the
> resources not released? (like top or ps -ef |grep....)
> tried the neat Activity Monitor, nothing shows where the 15 gb are used.
> thanks.|||setting the max memory is great.
how will i know what will be occupying the 14 GB?|||That 14GB will be used by the buffer pool of the instance. For the
distribution of the buffer pool buffers, I find DBCC MEMORYSTATUS handy.
Linchi
"light_wt" wrote:
> setting the max memory is great.
> how will i know what will be occupying the 14 GB?
>|||wow...
thank you so much Linchi. that command is so cool.
i've g* and played with it a bit. it pulls very useful info.
just wondering here, if i want to pin point to see if certain BI things
(such as: cubes, proc, partition, aggregation, and etc) is the casue of the
memory issue. will there be a way to zoom in/ narrow down the issue?
afterall, the box is a sql05 for data mining...
Thanks.|||The memory will all be used by SQL Servers buffer pool. To see how that is
broken down have a look at the sys.dm_os_buffer_descriptors DMV in
BooksOnLine.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:790A960F-9625-447C-A09E-A1818101F1FD@.microsoft.com...
> setting the max memory is great.
> how will i know what will be occupying the 14 GB?
>