Can I hide data fields in a Chart?

I have a Bar/Line Chart with two data fields. Field #1 is displayed as a bar,
Field #2 a line. I would like to allow the user to choose via a parameter if
the line is displayed.
I tried the following expression for the Field:
=iif(Parameters!ShowTrend.Value=True,Fields!Trend.Value,False)
But rather than hiding the line, it makes all values zero with the line
still showing.
Is there some way to hide the line?
Thanks.You may want to try:
=iif(Parameters!ShowTrend.Value=True, Fields!Trend.Value, Nothing)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"bill" <bill@.discussions.microsoft.com> wrote in message
news:745AF66D-CC9D-4D91-845C-FD4227C08E59@.microsoft.com...
> I have a Bar/Line Chart with two data fields. Field #1 is displayed as a
bar,
> Field #2 a line. I would like to allow the user to choose via a parameter
if
> the line is displayed.
> I tried the following expression for the Field:
> =iif(Parameters!ShowTrend.Value=True,Fields!Trend.Value,False)
> But rather than hiding the line, it makes all values zero with the line
> still showing.
> Is there some way to hide the line?
> Thanks.
>|||Show/hide is not supported in charts.
But why don't you try a dynamic series grouping and filter those series
groupings that you don't want show (the Grouping&Sorting dialog for every
chart grouping has a filter tab where you can define expressions to filter
data). This should also take care of the chart legend issue.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"bill" <bill@.discussions.microsoft.com> wrote in message
news:6B4516F9-D415-4522-838F-BCBAF0789F30@.microsoft.com...
> Thanks for the reply, but "Nothing" gives me the same results as "False"
...
> the line shows-up on bottom with zero values.
> I can get the effect of hiding (sort of) by setting color to transparent.
> But the data series still displays in the legend box.
> Is there no way to dynamically let the user specify what data elements
they
> want to show/hide on a chart?
> Bill
> "Robert Bruckner [MSFT]" wrote:
> > You may want to try:
> > =iif(Parameters!ShowTrend.Value=True, Fields!Trend.Value, Nothing)
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "bill" <bill@.discussions.microsoft.com> wrote in message
> > news:745AF66D-CC9D-4D91-845C-FD4227C08E59@.microsoft.com...
> > > I have a Bar/Line Chart with two data fields. Field #1 is displayed as
a
> > bar,
> > > Field #2 a line. I would like to allow the user to choose via a
parameter
> > if
> > > the line is displayed.
> > >
> > > I tried the following expression for the Field:
> > >
> > > =iif(Parameters!ShowTrend.Value=True,Fields!Trend.Value,False)
> > >
> > > But rather than hiding the line, it makes all values zero with the
line
> > > still showing.
> > >
> > > Is there some way to hide the line?
> > >
> > > Thanks.
> > >
> > >
> >
> >
> >

Can I create Letter type reports within the Report Builder

Hi,

I need to create letter type reports using the report builder, is this possible?. I am trying to create a letter that embeds some variable fields, i.e,. name, address, contact number. I can't seem to find a way to create page headers, and embed report data fields within text. Is this not possible within the report builder? it seems that the only thing you can do within the report builder is add fields to their predesignated areas. I am being forced to use the report builder instead of the report designer in order to give the end user the ability to make modifications to the report/letter. This is very frustrating. not being able to allow the user to edit report designer files, and then having force them to use report builder which doesn't seem to support some simple features..

Any suggestions would be appreciated...

Sorry, the current version of Report Builder is very limited in its functionality and you can't do what you are trying to do.

There is an undocumented feature in Report Builder that may help. You can create a textbox and set its value to an RDL expression, by typing the RDL expression into the text area. This is very limited since you cannot create custom datasets to reference.

can I create a VIEW on the sesult of stored procedure ?

I have a store procedure which select/calculate fields from multiple tables,

can I create a VIEW on the sesult of the stored procedure ?

u can create a view using a select statement containing ur sp converted to a function....
but why would u want to do such a thing....do u want to permanently keep these values..//refer them just then...some more info may get u a better suggestion buddy...|||I want create a cube fact that is base on the result of a stored procedure, but the cube cannot accept SP as a source, so I want to create a view that can be the source of cube fact|||

In this case I think your best bet would probably be to simply create and populate a table with the results of your stored procedure. If you don't need to keep these results then you can truncate and/or drop the table when you don't need it anymore.

I don't think it's possible to create a view based on the output of a SP.

sql

Can i add SQL statement to Parameter Fields?

Hi,
I am new to Crystal Reports. May i know can i add SQL statement to parameter fields under setting default values?
WillyYes, you can add SQL statement, but the statement, is a litlle diferent... not much.
Menu -> Report -> Select Expert ->
Click Show formula >>> then hit the button Formula editor...

Hi,

I am new to Crystal Reports. May i know can i add SQL statement to parameter fields under setting default values?

Willy|||Thanks a lot

Can fields span TableGroups independantly of columns

Hi,

I'm trying to add a heading to my report using Table and TableGroups. But I don't want my group headings to line up with the detail rows. This seems like a common requirement in my mind but I can't figure out how to do it.

See made up example of a report below:

---

Person Name Address <-Report header

---

Area: A12 3BC, Newtown, Someplace <-TableGroup header

---

Mr Some Body 1 The Street <-Detail rows

Mrs Anne Other 2 The Street

---

Area: B23 4CD, Oldtown, Someplace <-TableGroup header

---

Mr Ran Dom 3 The Avenue <-Detail rows

---

So the TableGroup displays Area which is grouped by Post code, city etc.

The detail rows displays individual people and their street address.

NOTE: The Area fields do not line up with the Person fields in fact they overlap how can I do this in SSRS?

Thanks in advance for your help,

Mike G

I've found out how to do this now...

You need to have a single column in the table that spans the whole width of the report, (as opposed to having one column in the table control for each field)

Then add rectangle controls to each of the cells (Report header, table group headers and details etc.)

Then you can add textbox controls onto the rectangle controls and put them where you like within the cell.

It's a shame you have to do it this way because you loose the nice feature that lines up the details columns with the labels you've added in the report heading.

Hope that all makes sense, it's difficult to explain graphical layout issues in text!

-MIke G

|||

have you tried the list control?

Can fact table include fields that are neither dimension nor measurements?

I am new to Dimensional Modeling in SSAS!

Can fact table include fields that are neither dimension nor measurements?

I need this for a drillthrough Action that shows data other than the dimensions or the measurements!

Please help!

Aref

The answer is NO.

You cannot drill through into something that is not part of your model ( ether dimension or measure group measure)

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

So, there are something columns (like some detail information or description) in the fact table. And these columns must show in drill through action.

The fact table must become as a dimension?

|||

Take a look at the dimension with type Fact.

Looks like in your case you should be looking at creating new dimension with type Fact and adding the columns you would like to see in the drillthrough to this dimension as attributes.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Can fact table include fields that are neither dimension nor measurements?

I am new to Dimensional Modeling in SSAS!

Can fact table include fields that are neither dimension nor measurements?

I need this for a drillthrough Action that shows data other than the dimensions or the measurements!

Please help!

Aref

The answer is NO.

You cannot drill through into something that is not part of your model ( ether dimension or measure group measure)

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

So, there are something columns (like some detail information or description) in the fact table. And these columns must show in drill through action.

The fact table must become as a dimension?

|||

Take a look at the dimension with type Fact.

Looks like in your case you should be looking at creating new dimension with type Fact and adding the columns you would like to see in the drillthrough to this dimension as attributes.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Can fields span TableGroups independantly of columns

Hi,

I'm trying to add a heading to my report using Table and TableGroups. But I don't want my group headings to line up with the detail rows. This seems like a common requirement in my mind but I can't figure out how to do it.

See made up example of a report below:

---

Person Name Address <-Report header

---

Area: A12 3BC, Newtown, Someplace <-TableGroup header

---

Mr Some Body 1 The Street <-Detail rows

Mrs Anne Other 2 The Street

---

Area: B23 4CD, Oldtown, Someplace <-TableGroup header

---

Mr Ran Dom 3 The Avenue <-Detail rows

---

So the TableGroup displays Area which is grouped by Post code, city etc.

The detail rows displays individual people and their street address.

NOTE: The Area fields do not line up with the Person fields in fact they overlap how can I do this in SSRS?

Thanks in advance for your help,

Mike G

I've found out how to do this now...

You need to have a single column in the table that spans the whole width of the report, (as opposed to having one column in the table control for each field)

Then add rectangle controls to each of the cells (Report header, table group headers and details etc.)

Then you can add textbox controls onto the rectangle controls and put them where you like within the cell.

It's a shame you have to do it this way because you loose the nice feature that lines up the details columns with the labels you've added in the report heading.

Hope that all makes sense, it's difficult to explain graphical layout issues in text!

-MIke G

|||

have you tried the list control?

Can fields span TableGroups independantly of columns

Hi,

I'm trying to add a heading to my report using Table and TableGroups. But I don't want my group headings to line up with the detail rows. This seems like a common requirement in my mind but I can't figure out how to do it.

See made up example of a report below:

---

Person Name Address <-Report header

---

Area: A12 3BC, Newtown, Someplace <-TableGroup header

---

Mr Some Body 1 The Street <-Detail rows

Mrs Anne Other 2 The Street

---

Area: B23 4CD, Oldtown, Someplace <-TableGroup header

---

Mr Ran Dom 3 The Avenue <-Detail rows

---

So the TableGroup displays Area which is grouped by Post code, city etc.

The detail rows displays individual people and their street address.

NOTE: The Area fields do not line up with the Person fields in fact they overlap how can I do this in SSRS?

Thanks in advance for your help,

Mike G

I've found out how to do this now...

You need to have a single column in the table that spans the whole width of the report, (as opposed to having one column in the table control for each field)

Then add rectangle controls to each of the cells (Report header, table group headers and details etc.)

Then you can add textbox controls onto the rectangle controls and put them where you like within the cell.

It's a shame you have to do it this way because you loose the nice feature that lines up the details columns with the labels you've added in the report heading.

Hope that all makes sense, it's difficult to explain graphical layout issues in text!

-MIke G

|||have you tried the list control?

can database name be assigned as a variable?

I need to have a query like below to run to select all the fields from different databases that each database have a table named 'table1'

select * from test...table1

select * from test1..table1

I have the following code sample:

declare @.dbname varchar(50)
set @.dbname='test'
select @.dbname
select * from @.dbname..table1

However I received the error messge when I ran this code. Can anyone help to resolve this?

Thank you!

Hi

One way of doing this is to build the query dynamically and use the Exec, as shown in the example below:

Declare @.xQuery varchar(1000)
Select @.xQuery = 'Select * from test..table1'
Exec(@.xQuery)

Select @.xQuery = 'Select * from test1..table1'
Exec(@.xQuery)

---

Best regards
Georg
www.l4ndash.com - Log4Net Dashboard / Log4Net Viewer

can a textbox hold the value of more than 1 field? maybe use funct

Hello,
My question is if a textbox in a report can contain values from multiple
fields from the data source:
txt1.value
=Fields!txt1.Value & "-" & Fields!txt2.Value & "-" & Fields!txt3.Value
If this is doable, what is the method/correct method?
I can add multiple fields to one textbox in an MS Access Report. Can this
be done in a Reporting Services Report? I am thinking I could use a function
which would return the concatenated values of these fields as a string. What
would the code for that function look like?
Thanks,
RichI figured out my problem. I added some new fields to my dataset, but not to
the report. Gotta do that for them to compile without complaining.
"Rich" wrote:
> Hello,
> My question is if a textbox in a report can contain values from multiple
> fields from the data source:
> txt1.value
> =Fields!txt1.Value & "-" & Fields!txt2.Value & "-" & Fields!txt3.Value
> If this is doable, what is the method/correct method?
> I can add multiple fields to one textbox in an MS Access Report. Can this
> be done in a Reporting Services Report? I am thinking I could use a function
> which would return the concatenated values of these fields as a string. What
> would the code for that function look like?
> Thanks,
> Rich|||You are correct, your format looks correct to me.
Steve MunLeeuw
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:2D73C97A-9840-4D71-91CA-42B27A1D0B40@.microsoft.com...
> Hello,
> My question is if a textbox in a report can contain values from multiple
> fields from the data source:
> txt1.value
> =Fields!txt1.Value & "-" & Fields!txt2.Value & "-" & Fields!txt3.Value
> If this is doable, what is the method/correct method?
> I can add multiple fields to one textbox in an MS Access Report. Can this
> be done in a Reporting Services Report? I am thinking I could use a
> function
> which would return the concatenated values of these fields as a string.
> What
> would the code for that function look like?
> Thanks,
> Rich|||Thank you. I am still learning. Learn by doing. BTW, if I notice a bug,
who can I report that too?
My actual project is using the reportviewer control that comes with VS2005
(it is almost the same as RS except doesn't require a server - and a few
other things). It works pretty good, but when I select a tractor feeding
printer (one of those older wide paper - dotmatrix like printers) if I tell
the layout to print landscape when using US STD Fanfold paper , the little
icon in the dialog display portrait and it prints portrait. Then if I tell
it Portrait when using the US STD Fanfold papter with tractor feed printer -
the icon displays landscapte and prints landscape. It is pretty obvious that
someone mixed up the options.
So I am not trying to be mr. picky, but when the end user uses my product,
it needs to work according to the standards. Who can I report this too?
Thanks,
Rich
"Steve MunLeeuw" wrote:
> You are correct, your format looks correct to me.
> Steve MunLeeuw
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:2D73C97A-9840-4D71-91CA-42B27A1D0B40@.microsoft.com...
> > Hello,
> >
> > My question is if a textbox in a report can contain values from multiple
> > fields from the data source:
> >
> > txt1.value
> >
> > =Fields!txt1.Value & "-" & Fields!txt2.Value & "-" & Fields!txt3.Value
> >
> > If this is doable, what is the method/correct method?
> >
> > I can add multiple fields to one textbox in an MS Access Report. Can this
> > be done in a Reporting Services Report? I am thinking I could use a
> > function
> > which would return the concatenated values of these fields as a string.
> > What
> > would the code for that function look like?
> >
> > Thanks,
> > Rich
>
>|||http://connect.microsoft.com/SQLServer/Feedback
Yeah, dealing with different page sizes can be tricky from what I gather.
Luckily I haven't had to deal with that much. Adobe allows you to have
pages in both landscape and portrait in the same document I was asked if I
could do that the other day. I don't think I could.
Steve MunLeeuw
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:D2173E9D-E9AF-410D-AFEE-E919AF360951@.microsoft.com...
> Thank you. I am still learning. Learn by doing. BTW, if I notice a bug,
> who can I report that too?
> My actual project is using the reportviewer control that comes with VS2005
> (it is almost the same as RS except doesn't require a server - and a few
> other things). It works pretty good, but when I select a tractor feeding
> printer (one of those older wide paper - dotmatrix like printers) if I
> tell
> the layout to print landscape when using US STD Fanfold paper , the little
> icon in the dialog display portrait and it prints portrait. Then if I
> tell
> it Portrait when using the US STD Fanfold papter with tractor feed
> printer -
> the icon displays landscapte and prints landscape. It is pretty obvious
> that
> someone mixed up the options.
> So I am not trying to be mr. picky, but when the end user uses my product,
> it needs to work according to the standards. Who can I report this too?
> Thanks,
> Rich
> "Steve MunLeeuw" wrote:
>> You are correct, your format looks correct to me.
>> Steve MunLeeuw
>> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> news:2D73C97A-9840-4D71-91CA-42B27A1D0B40@.microsoft.com...
>> > Hello,
>> >
>> > My question is if a textbox in a report can contain values from
>> > multiple
>> > fields from the data source:
>> >
>> > txt1.value
>> >
>> > =Fields!txt1.Value & "-" & Fields!txt2.Value & "-" & Fields!txt3.Value
>> >
>> > If this is doable, what is the method/correct method?
>> >
>> > I can add multiple fields to one textbox in an MS Access Report. Can
>> > this
>> > be done in a Reporting Services Report? I am thinking I could use a
>> > function
>> > which would return the concatenated values of these fields as a string.
>> > What
>> > would the code for that function look like?
>> >
>> > Thanks,
>> > Rich
>>

Calulated Fields vs UDF's

Does anyone have any suggestions on the use of Calculated fields over scalar
User Defined functions? I would think that the calulated field may be faster
than a scalar UDF but I don't know much about the mechanisms SQL uses to run
each method. Also, where might Table-valued UDF's possibly fit into this
scenario?
thank you for your time.Scalar UDFs (in SQL Server 2000) can be fairly slow, depending on the
implementation. If the same code can be placed in-line in a Select, for
example, the select will run faster than the version calling the UDF. Often
much, much faster.
I have not done a performance comparison, but with that in mind I would
expect a calculated field to run faster than a scalar UDF.
Table-valued UDFs return tables, so they are not a good fit for
high-performance operations on a column. The performance of table-valued
UDFs can be summed up this way:
In-line table valued UDF = View with parameters, therefore the UDF is
'compiled into' the plan if used in a join.
Multi-statement table valued UDF = Stored Procedure that returns a table.
If included in a join, it executes first and returns a result set.
RLF
"J. Askey" <JAskey@.discussions.microsoft.com> wrote in message
news:44F255E8-8DE8-4449-9104-C3463EEFD3F4@.microsoft.com...
> Does anyone have any suggestions on the use of Calculated fields over
> scalar
> User Defined functions? I would think that the calulated field may be
> faster
> than a scalar UDF but I don't know much about the mechanisms SQL uses to
> run
> each method. Also, where might Table-valued UDF's possibly fit into this
> scenario?
>
> thank you for your time.|||Scalar UDFs are slow when they must do a lookup (i.e., they contain a
SELECT statement). When this happens it serializes your reads such that
your queries that contain the scalar UDF behave like cursor operations.
If your UDF simply performs a calculation without any lookups, the
performance should be similar to computed columns. I think you can
create indexes on computed columns though, but I'm not totally certain.
-Alan|||Thank you RLF and Alan for your explanations. My column is indeed a general
computation on other columns in the table so it sounds like they may be
similar in performance between both methods. The one advantage of using a
scalar UDF that I have figured out is that if I have multiple tables using
this similar computation, then I simply have to make the change in one place
to effect both table queries. I suppose I could simply call the UDF in the
calculated field as well to centralize the definition.
I typically would have created a veiw with a call tothe UDF with in it and
on top of the base table columns but I wanted to careful not to spread my
data access out all over the place for columns that might naturally seem to
be contained in the base table.
Maybe I will do a little playing with this today and post some varied
results using these different approaches. It might be interesting.
"Alan Samet" wrote:

> Scalar UDFs are slow when they must do a lookup (i.e., they contain a
> SELECT statement). When this happens it serializes your reads such that
> your queries that contain the scalar UDF behave like cursor operations.
> If your UDF simply performs a calculation without any lookups, the
> performance should be similar to computed columns. I think you can
> create indexes on computed columns though, but I'm not totally certain.
>
> -Alan
>|||A few more details.
SQL Server's Query Optimizer does not understand the output distributions of
UDFs. As such, it can cause cardinality estimates in query plan generation
to be sub-optimal.
(Eventually, we may be able to improve this story in a future release)
What I tell customers now - if you can write it as scalar logic, please do
so. If you need to use a UDF, please consider a computed column over the
result of this as well so that the optimzier can create the statistics it
needs to do a good job in plan generation.
I would specifically *not* recommend the use of UDFs to perform "singleton
(index) lookups" into another table. I've seen this pattern used in a
number of deployments. While it "works", it is very hard for the query
optimizer to pick a proper join order (which means that sometimes it picks a
poor order and plan performance can become very slow). If you can represent
these as joins, I recommend that you do so.
Best of luck to you,
Conor Cunningham
SQL Server Query Optimization Development Lead
"J. Askey" <JAskey@.discussions.microsoft.com> wrote in message
news:0940C18A-9E28-4F12-B926-95B00B336D42@.microsoft.com...
> Thank you RLF and Alan for your explanations. My column is indeed a
> general
> computation on other columns in the table so it sounds like they may be
> similar in performance between both methods. The one advantage of using a
> scalar UDF that I have figured out is that if I have multiple tables using
> this similar computation, then I simply have to make the change in one
> place
> to effect both table queries. I suppose I could simply call the UDF in the
> calculated field as well to centralize the definition.
> I typically would have created a veiw with a call tothe UDF with in it and
> on top of the base table columns but I wanted to careful not to spread my
> data access out all over the place for columns that might naturally seem
> to
> be contained in the base table.
> Maybe I will do a little playing with this today and post some varied
> results using these different approaches. It might be interesting.
> "Alan Samet" wrote:
>

Calulated Fields in DataSet

Does anyone know if Reporting Services 2005 support calculated fields
in the dataset with RunningValue and Previous functions? Everytime I
trie it gives me an error?
Here's what I did, on the dataset I added a calulated field and put the
following expression in "=RunningValue(fields!xxxx, Sum)" and when I
hit preview it returned an error?
Is this a limitation of reporting services?Amarnath,
Thanks for the reply, I know this function works in the tables but I
wanted to use the output in a chart. Do you have any suggestions on
doing that? Can I use table calculated data in a chart?
- tuong
On Jan 22, 2:29 am, Amarnath <Amarn...@.discussions.microsoft.com>
wrote:
> Dont give this in a calculated field instead insert say a "Sr.No" column in
> the left side of your table and just in the properties put this syntax and it
> will work.
> Amarnath
>
> "tuong.k...@.gmail.com" wrote:
> > Does anyone know if Reporting Services 2005 support calculated fields
> > in the dataset with RunningValue and Previous functions? Everytime I
> > trie it gives me an error?
> > Here's what I did, on the dataset I added a calulated field and put the
> > following expression in "=RunningValue(fields!xxxx, Sum)" and when I
> > hit preview it returned an error?
> > Is this a limitation of reporting services... Hide quoted text -- Show quoted text -|||Infact to give running value you need to give scope which you are not and you
cant because it is like global definition for the table.
Amarnath
"tuong.k.lam@.gmail.com" wrote:
> Amarnath,
> Thanks for the reply, I know this function works in the tables but I
> wanted to use the output in a chart. Do you have any suggestions on
> doing that? Can I use table calculated data in a chart?
> - tuong
> On Jan 22, 2:29 am, Amarnath <Amarn...@.discussions.microsoft.com>
> wrote:
> > Dont give this in a calculated field instead insert say a "Sr.No" column in
> > the left side of your table and just in the properties put this syntax and it
> > will work.
> >
> > Amarnath
> >
> >
> >
> > "tuong.k...@.gmail.com" wrote:
> > > Does anyone know if Reporting Services 2005 support calculated fields
> > > in the dataset with RunningValue and Previous functions? Everytime I
> > > trie it gives me an error?
> >
> > > Here's what I did, on the dataset I added a calulated field and put the
> > > following expression in "=RunningValue(fields!xxxx, Sum)" and when I
> > > hit preview it returned an error?
> >
> > > Is this a limitation of reporting services... Hide quoted text -- Show quoted text -
>