How to show all the fields in a dataset.

I have a sp that returns a different dataset depending on the parameter
values, it is dyanamic sp which has different field names and also the
number of fields.
Is there a way to incorporate this in reporting services, like in .Net
we can display the dataset by just binding it to the datagrid and
letting datagrid handle the rest.
Thanks,
TonyNo. RS does not work that way. It expects a consistent number/name of
fields.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bubaa118@.yahoo.com> wrote in message
news:1107965810.679249.274270@.c13g2000cwb.googlegroups.com...
> I have a sp that returns a different dataset depending on the parameter
> values, it is dyanamic sp which has different field names and also the
> number of fields.
> Is there a way to incorporate this in reporting services, like in .Net
> we can display the dataset by just binding it to the datagrid and
> letting datagrid handle the rest.
> Thanks,
> Tony
>|||Thanks for the prompt reply Bruce, Is there any other workaround for
the issue I have any ideas or suggestions would be great.
Thanks again,
Tony|||You can have multiple datasets in a report, each of them calling the
procedure with the appropriate parameter. This does mean the SP will get
called multiple times. Then you should be able to hide the table on the
report if there is no data. I haven't tried this but I think you can. You
still might end up with some blank lines though.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<bubaa118@.yahoo.com> wrote in message
news:1107967055.899883.298640@.f14g2000cwb.googlegroups.com...
> Thanks for the prompt reply Bruce, Is there any other workaround for
> the issue I have any ideas or suggestions would be great.
> Thanks again,
> Tony
>|||You could also at the end of your proc pivot (or is this really
unpivoting) the data. So for a table with structure
# keycol # col1 # col2 # col3 # col4 # ... # col<n> #
you would end up with
# keycolvalue # colname # colvalue #
this will have n rows for each row in the original table. You may want
to modify your proc to build that to begin with rather than pivot data
later.
then you could drop that into a matrix.
This would work fairly well if all variable columns are of the same
type. Otherwise you'd have to do some formatting in the stored
procedure.

How to set week start number in RS

How to set week start number in RS.
I would like to use WeekdayName and Weekday RS function but it returns
wrong values (Tuesday instead of Monday)
TIA,
KamelOn 14 Lut, 16:32, "kamel" <kwic...@.gmail.com> wrote:
> How to set week start number in RS.
> I would like to use WeekdayName and Weekday RS function but it returns
> wrong values (Tuesday instead of Monday)
> TIA,
> Kamel
OK don't mind...
you can set it as attribute in Weekday function
Kamel

How to set the decimal places

I have a calculation in a stored procedure that returns a percentage like (3
2
* 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as float.
I need to set or convert the result to have 2 decimal places like 1.86,
rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
variations (examples from the internet), nothing is working for me. Is ther
e
a way to achieve this in SQL? I would really appreciate the help."HLong" <HLong@.discussions.microsoft.com> wrote in message
news:C7A3278F-FB47-451C-855E-8B0978DC5930@.microsoft.com...
>I have a calculation in a stored procedure that returns a percentage like
>(32
> * 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as
> float.
> I need to set or convert the result to have 2 decimal places like 1.86,
> rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
> variations (examples from the internet), nothing is working for me. Is
> there
> a way to achieve this in SQL? I would really appreciate the help.
Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Thanks Dejan. I tried that same function yesterday so many times but I
always got
1.00, which was not good. However, now I tried 32*100.00 and it worked
fine. I don't know why it worked. May be because the 100.00 is taken as a
decimal type, instead of 32*100 where both are int. Could you explain this
a
bit more?
"Dejan Sarka" wrote:

> "HLong" <HLong@.discussions.microsoft.com> wrote in message
> news:C7A3278F-FB47-451C-855E-8B0978DC5930@.microsoft.com...
> Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>|||> Thanks Dejan. I tried that same function yesterday so many times but I
> always got
> 1.00, which was not good. However, now I tried 32*100.00 and it worked
> fine. I don't know why it worked. May be because the 100.00 is taken as
> a
> decimal type, instead of 32*100 where both are int. Could you explain
> this a
> bit more?
In T-SQL we do not denote data types for literal values like, for example,
in C#. So SQL Server uses it's own logic, and takes 32 and 100 as integers.
First operand data type is then used for result as well. You can also check
topics on data types precendence in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

How to set the decimal places

I have a calculation in a stored procedure that returns a percentage like (32
* 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as float.
I need to set or convert the result to have 2 decimal places like 1.86,
rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
variations (examples from the internet), nothing is working for me. Is there
a way to achieve this in SQL? I would really appreciate the help.
"HLong" <HLong@.discussions.microsoft.com> wrote in message
news:C7A3278F-FB47-451C-855E-8B0978DC5930@.microsoft.com...
>I have a calculation in a stored procedure that returns a percentage like
>(32
> * 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as
> float.
> I need to set or convert the result to have 2 decimal places like 1.86,
> rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
> variations (examples from the internet), nothing is working for me. Is
> there
> a way to achieve this in SQL? I would really appreciate the help.
Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Thanks Dejan. I tried that same function yesterday so many times but I
always got
1.00, which was not good. However, now I tried 32*100.00 and it worked
fine. I don't know why it worked. May be because the 100.00 is taken as a
decimal type, instead of 32*100 where both are int. Could you explain this a
bit more?
"Dejan Sarka" wrote:

> "HLong" <HLong@.discussions.microsoft.com> wrote in message
> news:C7A3278F-FB47-451C-855E-8B0978DC5930@.microsoft.com...
> Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>
|||> Thanks Dejan. I tried that same function yesterday so many times but I
> always got
> 1.00, which was not good. However, now I tried 32*100.00 and it worked
> fine. I don't know why it worked. May be because the 100.00 is taken as
> a
> decimal type, instead of 32*100 where both are int. Could you explain
> this a
> bit more?
In T-SQL we do not denote data types for literal values like, for example,
in C#. So SQL Server uses it's own logic, and takes 32 and 100 as integers.
First operand data type is then used for result as well. You can also check
topics on data types precendence in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

How to set the decimal places

I have a calculation in a stored procedure that returns a percentage like (32
* 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as float.
I need to set or convert the result to have 2 decimal places like 1.86,
rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
variations (examples from the internet), nothing is working for me. Is there
a way to achieve this in SQL? I would really appreciate the help."HLong" <HLong@.discussions.microsoft.com> wrote in message
news:C7A3278F-FB47-451C-855E-8B0978DC5930@.microsoft.com...
>I have a calculation in a stored procedure that returns a percentage like
>(32
> * 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as
> float.
> I need to set or convert the result to have 2 decimal places like 1.86,
> rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
> variations (examples from the internet), nothing is working for me. Is
> there
> a way to achieve this in SQL? I would really appreciate the help.
Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Thanks Dejan. I tried that same function yesterday so many times but I
always got
1.00, which was not good. However, now I tried 32*100.00 and it worked
fine. I don't know why it worked. May be because the 100.00 is taken as a
decimal type, instead of 32*100 where both are int. Could you explain this a
bit more?
"Dejan Sarka" wrote:
> "HLong" <HLong@.discussions.microsoft.com> wrote in message
> news:C7A3278F-FB47-451C-855E-8B0978DC5930@.microsoft.com...
> >I have a calculation in a stored procedure that returns a percentage like
> >(32
> > * 100/1722) = 1.85830429732 is a COUNT(field) and 1722 is a @.total as
> > float.
> > I need to set or convert the result to have 2 decimal places like 1.86,
> > rounding it up to the nearest 1/100. I have tried CAST, CONVERT in many
> > variations (examples from the internet), nothing is working for me. Is
> > there
> > a way to achieve this in SQL? I would really appreciate the help.
> Is this what ou want - SELECT CAST(32.0 * 100/1722 AS decimal(5,2)) ?
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>|||> Thanks Dejan. I tried that same function yesterday so many times but I
> always got
> 1.00, which was not good. However, now I tried 32*100.00 and it worked
> fine. I don't know why it worked. May be because the 100.00 is taken as
> a
> decimal type, instead of 32*100 where both are int. Could you explain
> this a
> bit more?
In T-SQL we do not denote data types for literal values like, for example,
in C#. So SQL Server uses it's own logic, and takes 32 and 100 as integers.
First operand data type is then used for result as well. You can also check
topics on data types precendence in Books OnLine.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com