Displaying parameter list in report header

I'm trying to display a parameter list of items to a textbox in the header.
Somehow it never evaluates anything after the first item in the array. 1st
below is the expression... 2nd is the custom code. I don't know what I'm
doing wrong...
1.
=code.ParameterList(Parameters!ProgCatCode.value(0))
2.
Public Function ParameterList(ByVal Parameter as Object) as String
Dim sParamItem as Object
Dim sParamVal as String = " "
For Each sParamItem in Parameter
If sParamItem Is Nothing then Exit For
sParamVal &= sParamItem & ", "
Next
Return sParamVal
End FunctionFigured it out..
"gdjoshua" wrote:
> I'm trying to display a parameter list of items to a textbox in the header.
> Somehow it never evaluates anything after the first item in the array. 1st
> below is the expression... 2nd is the custom code. I don't know what I'm
> doing wrong...
> 1.
> =code.ParameterList(Parameters!ProgCatCode.value(0))
> 2.
> Public Function ParameterList(ByVal Parameter as Object) as String
> Dim sParamItem as Object
> Dim sParamVal as String = " "
> For Each sParamItem in Parameter
> If sParamItem Is Nothing then Exit For
> sParamVal &= sParamItem & ", "
> Next
> Return sParamVal
> End Function
>|||What was it that you figured out. Share it with us.
EROK
"gdjoshua" <gdjoshua@.discussions.microsoft.com> wrote in message
news:1D6BB58A-C00C-496C-A675-3FBFBED4E332@.microsoft.com...
> Figured it out..
> "gdjoshua" wrote:
>> I'm trying to display a parameter list of items to a textbox in the
>> header.
>> Somehow it never evaluates anything after the first item in the array.
>> 1st
>> below is the expression... 2nd is the custom code. I don't know what
>> I'm
>> doing wrong...
>> 1.
>> =code.ParameterList(Parameters!ProgCatCode.value(0))
>> 2.
>> Public Function ParameterList(ByVal Parameter as Object) as String
>> Dim sParamItem as Object
>> Dim sParamVal as String = " "
>> For Each sParamItem in Parameter
>> If sParamItem Is Nothing then Exit For
>> sParamVal &= sParamItem & ", "
>> Next
>> Return sParamVal
>> End Function|||I had a similar issue, so I can tell you the things I tried.
1. His primary issue with the custom code was what he was passing into the
function. Parameters!ProgCatCode.value(0) will only give the value at index
0, but Parameters!ProgCatCode.value without the parentethis will pass in the
entire array
2. One way around having to write custom code is to use the Join function.
Join(Parameters!ProgCatCode.value, ", ") gives you all the selected Values.
Join(Parameters!ProgCatCode.label, ", ") gives you all the selected Labels,
which might be better in some cases. Your Value might be numeric data and
meaningless to the user, but the Lable you made to display in your drop down
list might be what the user really wants to see.
"Erok" wrote:
> What was it that you figured out. Share it with us.
> EROK
> "gdjoshua" <gdjoshua@.discussions.microsoft.com> wrote in message
> news:1D6BB58A-C00C-496C-A675-3FBFBED4E332@.microsoft.com...
> > Figured it out..
> >
> > "gdjoshua" wrote:
> >
> >> I'm trying to display a parameter list of items to a textbox in the
> >> header.
> >> Somehow it never evaluates anything after the first item in the array.
> >> 1st
> >> below is the expression... 2nd is the custom code. I don't know what
> >> I'm
> >> doing wrong...
> >>
> >> 1.
> >> =code.ParameterList(Parameters!ProgCatCode.value(0))
> >>
> >> 2.
> >>
> >> Public Function ParameterList(ByVal Parameter as Object) as String
> >> Dim sParamItem as Object
> >> Dim sParamVal as String = " "
> >> For Each sParamItem in Parameter
> >> If sParamItem Is Nothing then Exit For
> >> sParamVal &= sParamItem & ", "
> >> Next
> >> Return sParamVal
> >> End Function
> >>
>
>|||Rob,
sicne the others did not thank you, I'll say it for them.
THANKS!
"Rob 'Spike' Stevens" wrote:
> I had a similar issue, so I can tell you the things I tried.
> 1. His primary issue with the custom code was what he was passing into the
> function. Parameters!ProgCatCode.value(0) will only give the value at index
> 0, but Parameters!ProgCatCode.value without the parentethis will pass in the
> entire array
> 2. One way around having to write custom code is to use the Join function.
> Join(Parameters!ProgCatCode.value, ", ") gives you all the selected Values.
> Join(Parameters!ProgCatCode.label, ", ") gives you all the selected Labels,
> which might be better in some cases. Your Value might be numeric data and
> meaningless to the user, but the Lable you made to display in your drop down
> list might be what the user really wants to see.

Displaying Multi-valued parameters

Hi There.

I am struggling with an issue with multi-valued parameters. I have a parameter that is a list of several hundred items and when someone selects all of them, I display the huge list in the report header vias the join command.

This works great for a few parameters, but overwrites my data when the list is large. I want to do something in the expression where I determine if all items are selected and then just display 'All' instead of the whole list. Any ideas would be very helpful!

Thanks, Mike

see this code from msdn, modify it to suit your need, if parameter.Value(i) <> " " then increase a counter value, at the end check if counter = total count in parameter then display all

to call the function in the expression

=Code. ShowParameterValues(Parameter!SomePara)

hope this helps

Public Function ShowParameterValues(ByVal parameter as Parameter)
as String
Dim s as String
If parameter.IsMultiValue then
s = "Multivalue: "
For i as integer = 0 to parameter.Count-1
s = s + CStr(parameter.Value(i)) + " "
Next
Else
s = "Single value: " + CStr(parameter.Value)
End If
Return s
End Function

|||

Thanks for the guidance Yashant!

I have not done custom code in a report. I think the

=Code. ShowParameterValues(Parameter!SomePara)

goes in the expression of the textbox correct?

Where does the function go? Is it under the report properties code tab?

Thanks, Mike

|||yes, the expression goes into the text box and code goes in reports properties code tab. you need to modify the code to work for you.

Display of Data category wise

Hi,

I have a requirement where I need to display the items category wise

I have 3 tables,

one stores category_id, parent_id and category_name

second table stored item_id,item_name and proce

3rd table stores category_id and item_id

I need to display the values like this

Category name, Item name

with first parent category ans ites items should be displayed and next sub category and its item

By category table with contain 2 levels of sub category

i.e Category1

Category11

Category111

any help with the query would be much appreciated,

I wrote the query something like this...

select c.category_name,parent_id,item_namefrom category c,items i,Category_item ciwhere

ci.item_id=i.item_idand c.category_id=ci.category_idorderby parent_id, category_name

But it will display all parent category items then the sub category items I want to display like thiscategory1 items1category11 item11category1 item12category11 item111category11 item112 category2 item2

Thanks

What you want is called "grouping".

Seehttp://www.google.com/search?q=gridview+grouping

Jos

|||

Please check the below mentioned URL:

http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm

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
>> >> >
>> >>
>> >>
>> >>
>>