Display group header with no items

I have to display group header inspite of not having records in that group.
Group1
values
group2
No valuesI did this, using a stored procedure, by inserting a row into the dataset
for each column heading you require with the name of the column heading as
the value for the "column heading" field in the dataset:
INSERT INTO tablename([column heading], [row heading], [detail])
VALUES("column heading", NULL, NULL)
If you want it to sort in a specific way, i.e. not alphabetical, add another
field (called "sort" perhaps) and then set the value of the sort field in
each row to the numbered location in the column groups you want it to appear
in. You then sort by the "sort" field. Its a bit messy, but it gets the
job done.
I am guessing somehow has a better idea, but if not then try this cos it
works.
Jarryd
"NAVIN.D" <NAVIND@.discussions.microsoft.com> wrote in message
news:F0FFA70C-AA27-44AE-9B83-BE0A62B6343E@.microsoft.com...
>I have to display group header inspite of not having records in that group.
> Group1
> values
> group2
> No values
>|||Is this an good idea if i have 4 different table results unioned by union.
Then the record in the first table might not match the same group record in
second table then. I am missing inspite of it.
"Jarryd" wrote:
> I did this, using a stored procedure, by inserting a row into the dataset
> for each column heading you require with the name of the column heading as
> the value for the "column heading" field in the dataset:
> INSERT INTO tablename([column heading], [row heading], [detail])
> VALUES("column heading", NULL, NULL)
> If you want it to sort in a specific way, i.e. not alphabetical, add another
> field (called "sort" perhaps) and then set the value of the sort field in
> each row to the numbered location in the column groups you want it to appear
> in. You then sort by the "sort" field. Its a bit messy, but it gets the
> job done.
> I am guessing somehow has a better idea, but if not then try this cos it
> works.
> Jarryd
> "NAVIN.D" <NAVIND@.discussions.microsoft.com> wrote in message
> news:F0FFA70C-AA27-44AE-9B83-BE0A62B6343E@.microsoft.com...
> >I have to display group header inspite of not having records in that group.
> >
> > Group1
> >
> > values
> >
> > group2
> >
> > No values
> >
>
>|||Why not dump the final result set to a virtaul table, and then insert the
columns as I explained into the virtual table. Then select all from the
virtual table and base the report on that dataset.
HTH.
Jarryd
"NAVIN.D" <NAVIND@.discussions.microsoft.com> wrote in message
news:48C1A492-50DA-41DA-AA1A-C3D046988600@.microsoft.com...
> Is this an good idea if i have 4 different table results unioned by union.
> Then the record in the first table might not match the same group record
> in
> second table then. I am missing inspite of it.
>
> "Jarryd" wrote:
>> I did this, using a stored procedure, by inserting a row into the dataset
>> for each column heading you require with the name of the column heading
>> as
>> the value for the "column heading" field in the dataset:
>> INSERT INTO tablename([column heading], [row heading], [detail])
>> VALUES("column heading", NULL, NULL)
>> If you want it to sort in a specific way, i.e. not alphabetical, add
>> another
>> field (called "sort" perhaps) and then set the value of the sort field in
>> each row to the numbered location in the column groups you want it to
>> appear
>> in. You then sort by the "sort" field. Its a bit messy, but it gets the
>> job done.
>> I am guessing somehow has a better idea, but if not then try this cos it
>> works.
>> Jarryd
>> "NAVIN.D" <NAVIND@.discussions.microsoft.com> wrote in message
>> news:F0FFA70C-AA27-44AE-9B83-BE0A62B6343E@.microsoft.com...
>> >I have to display group header inspite of not having records in that
>> >group.
>> >
>> > Group1
>> >
>> > values
>> >
>> > group2
>> >
>> > No values
>> >
>>|||It not giving results as expected.
"Jarryd" wrote:
> Why not dump the final result set to a virtaul table, and then insert the
> columns as I explained into the virtual table. Then select all from the
> virtual table and base the report on that dataset.
> HTH.
> Jarryd
> "NAVIN.D" <NAVIND@.discussions.microsoft.com> wrote in message
> news:48C1A492-50DA-41DA-AA1A-C3D046988600@.microsoft.com...
> > Is this an good idea if i have 4 different table results unioned by union.
> > Then the record in the first table might not match the same group record
> > in
> > second table then. I am missing inspite of it.
> >
> >
> >
> > "Jarryd" wrote:
> >
> >> I did this, using a stored procedure, by inserting a row into the dataset
> >> for each column heading you require with the name of the column heading
> >> as
> >> the value for the "column heading" field in the dataset:
> >>
> >> INSERT INTO tablename([column heading], [row heading], [detail])
> >> VALUES("column heading", NULL, NULL)
> >>
> >> If you want it to sort in a specific way, i.e. not alphabetical, add
> >> another
> >> field (called "sort" perhaps) and then set the value of the sort field in
> >> each row to the numbered location in the column groups you want it to
> >> appear
> >> in. You then sort by the "sort" field. Its a bit messy, but it gets the
> >> job done.
> >>
> >> I am guessing somehow has a better idea, but if not then try this cos it
> >> works.
> >>
> >> Jarryd
> >>
> >> "NAVIN.D" <NAVIND@.discussions.microsoft.com> wrote in message
> >> news:F0FFA70C-AA27-44AE-9B83-BE0A62B6343E@.microsoft.com...
> >> >I have to display group header inspite of not having records in that
> >> >group.
> >> >
> >> > Group1
> >> >
> >> > values
> >> >
> >> > group2
> >> >
> >> > No values
> >> >
> >>
> >>
> >>
>
>|||What does the virtual table look like? I am thinking something simple,
like:
Virt table fields = [Column_heading], [Row_heading], [Detail]
With data:
Column_heading | Row_Heading | Detail
----
CH1 | RH1 | $100
CH2 | RH1 | $125
CH3 | RH1 | $142
CH1 | RH2 | $95
CH2 | RH2 | $117
CH3 | RH2 | $102
CH1 | RH4 | $95
CH2 | RH4 | $117
CH3 | RH4 | $102
It doesn't really matter if it is more complicated than that cos the
principal should be the same. If you want to add a column heading that
inspite of there not being an entry for one in the final dataset simply add
one with NULL detail and NULL Row_Heading, or if you want to add a
Row_Heading do the same sort of thing:
INSERT INTO #Virt_Table*([Column_Heading], [Row_Heading], [Detail])
VALUES('CH4', NULL, NULL)
INSERT INTO #Virt_Table*([Column_Heading], [Row_Heading], [Detail])
VALUES(NULL, 'RH3', NULL)
You should then end up with a matrix (if that is what you are making) that
looks like this:
CH1 | CH2 | CH3 | CH4
RH1| $100 | $125 | $142 | NULL
RH2| $95 | $117 | $102 | NULL
RH3| NULL | NULL | NULL | NULL
RH4| $95 | $117 | $102 | NULL
Extend that method to however complicated your dataset or groups of columns
are. I have said column heading here, but you can interchange that with
Group_heading to make it clearer.
HTH,
Jarryd
"NAVIN.D" <NAVIND@.discussions.microsoft.com> wrote in message
news:41625D86-F099-44F6-A2BB-55F341CCD2F6@.microsoft.com...
> It not giving results as expected.
> "Jarryd" wrote:
>> Why not dump the final result set to a virtaul table, and then insert the
>> columns as I explained into the virtual table. Then select all from the
>> virtual table and base the report on that dataset.
>> HTH.
>> Jarryd
>> "NAVIN.D" <NAVIND@.discussions.microsoft.com> wrote in message
>> news:48C1A492-50DA-41DA-AA1A-C3D046988600@.microsoft.com...
>> > Is this an good idea if i have 4 different table results unioned by
>> > union.
>> > Then the record in the first table might not match the same group
>> > record
>> > in
>> > second table then. I am missing inspite of it.
>> >
>> >
>> >
>> > "Jarryd" wrote:
>> >
>> >> I did this, using a stored procedure, by inserting a row into the
>> >> dataset
>> >> for each column heading you require with the name of the column
>> >> heading
>> >> as
>> >> the value for the "column heading" field in the dataset:
>> >>
>> >> INSERT INTO tablename([column heading], [row heading], [detail])
>> >> VALUES("column heading", NULL, NULL)
>> >>
>> >> If you want it to sort in a specific way, i.e. not alphabetical, add
>> >> another
>> >> field (called "sort" perhaps) and then set the value of the sort field
>> >> in
>> >> each row to the numbered location in the column groups you want it to
>> >> appear
>> >> in. You then sort by the "sort" field. Its a bit messy, but it gets
>> >> the
>> >> job done.
>> >>
>> >> I am guessing somehow has a better idea, but if not then try this cos
>> >> it
>> >> works.
>> >>
>> >> Jarryd
>> >>
>> >> "NAVIN.D" <NAVIND@.discussions.microsoft.com> wrote in message
>> >> news:F0FFA70C-AA27-44AE-9B83-BE0A62B6343E@.microsoft.com...
>> >> >I have to display group header inspite of not having records in that
>> >> >group.
>> >> >
>> >> > Group1
>> >> >
>> >> > values
>> >> >
>> >> > group2
>> >> >
>> >> > No values
>> >> >
>> >>
>> >>
>> >>
>>