Displays commas in type FLOAT

I'm writing a SQL script. I'd like to display the value I have in a
column of type FLOAT so that it appears with the commas in the correct
place. It displays now as 10000000.0; I'd like it to display as
10,000,000. Thanks for any help."Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1cffedcae86a60059898e0@.msnews.microsoft.com...
> I'm writing a SQL script. I'd like to display the value I have in a
> column of type FLOAT so that it appears with the commas in the correct
> place. It displays now as 10000000.0; I'd like it to display as
> 10,000,000. Thanks for any help.
Your front-end application should really be doing this type of work, not the
database itself.
Rick Sawtell
MCT, MCSD, MCDBA|||Formatting should be done client side, not in SQL.
"Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1cffedcae86a60059898e0@.msnews.microsoft.com...
> I'm writing a SQL script. I'd like to display the value I have in a
> column of type FLOAT so that it appears with the commas in the correct
> place. It displays now as 10000000.0; I'd like it to display as
> 10,000,000. Thanks for any help.|||Do this in the reporting tool / client application, or cast the value to
money (not good) and then use function "convert" to cast it to varchar with
style 1.
Example:
use northwind
go
select
convert(varchar(25), cast(cast(orderid as float) as money), 1)
from
dbo.orders
AMB
"Rick Charnes" wrote:

> I'm writing a SQL script. I'd like to display the value I have in a
> column of type FLOAT so that it appears with the commas in the correct
> place. It displays now as 10000000.0; I'd like it to display as
> 10,000,000. Thanks for any help.
>

Displaying various instances of the same report in one report

Here's my problem. I have a report that displays information for one
office. The users are now asking for a master report where they can
display all of the offices in their group. I'm thinking I can do this
with a sub report but I don't know enough about subreports to set it
the passing of multiple report parameters and the sql server book's
how to is a piece of crap.
Where else can I find some examples of how to set this up?
Is there a better way to do this?
Thanks in advance for the help and if you need something cleared up let
me know.
MathiasHi, Mathias
from what I understand, it seems like you are gonna have the following
scenario:
create a report that will query for all offices, and then group by office
group categoriy.
If so, you can create you query (sproc or adhoc) to take in the office group
identifier/name as a parameter, and filter with a Where clause in the query.
The report rdl will have a parameter for the office group id/name which you
pass into that query, and will retrieve a list of all offices for that
particular group.
On the item that displays, say, the name of the office, you can go to the
textbox properties window and setup a hyperlink to another report (your
existing one), passing the appropriate office id/name, which your current
office report will use
to retrieve detailed information on.
Another way to go about this: in you "master report", drop a sub report
element, and in the properties, set the report rdl of the original report,
and for parameters, pass in the field from the master report containing the
office id into the sub-report (your current report).
so you will have the following layout:
user enters report parameter Office Group: <value>;
query executes filtered to that office group, and returns a list of office
name/ids;
rdl renders the list of office names returned by the query in a table layout;
within table, the detail row will contain a cell with a subreport pointing
to your original "office info" rdl, and passing the office id field to the
office id parameter of the subreport:
<begin table>
<begindetailrow>
pass Fields!officeID.value =>subreport (current report)
parameter Parameters!officeid.value
<enddetailrow>
<endtable>
Note, you could modify your original query so that your grouping and logic
is done on the query side in one stored procedure, and avoid having to deal
w/ subreports, by joining the appropriate tables and building your result set
in the query w/ all of the office details there. This reduces some of the
overhead on the report server having to render subreports.
hope this helps you out.
--
Regards,
Thiago Silva
"Mathias" wrote:
> Here's my problem. I have a report that displays information for one
> office. The users are now asking for a master report where they can
> display all of the offices in their group. I'm thinking I can do this
> with a sub report but I don't know enough about subreports to set it
> the passing of multiple report parameters and the sql server book's
> how to is a piece of crap.
> Where else can I find some examples of how to set this up?
> Is there a better way to do this?
> Thanks in advance for the help and if you need something cleared up let
> me know.
> Mathias
>|||Thiago Silva
Thank you very much for taking the time to respond. I think i'm going
to have to go with the second option and use the subreport. my users
want to be able to see the various reports all at one time. I set up
the query to bring back the office id's in their respective groups
however when I pass that id to the subreport only the first offices'
report is generated.
for example there are 10 offices comming back it will only display the
first office. How do I tell the subreport to move on to the next
offices?
I tried using the value straight from the query by doing this
=Fields!ReportingOfficeID.Value which did not work.
I also tried placing the value returened from the query into a report
parameter this also did not work. I tried with the multi- value box
selected and with the box not selected.
where am I going wrong?
on a side note that I should have mentioned to start with I am using
Reporting server 2005.
thanks once again for all of the help. And If you need me to clear
something up let me know.
Mathias|||Mathias,
could you provide a sample of the data that you're using for the report, and
how you want the report layout to be? I am trying to understand exactly what
you want versus what you're getting right now.
--
Regards,
Thiago Silva
"Mathias" wrote:
> Thiago Silva
> Thank you very much for taking the time to respond. I think i'm going
> to have to go with the second option and use the subreport. my users
> want to be able to see the various reports all at one time. I set up
> the query to bring back the office id's in their respective groups
> however when I pass that id to the subreport only the first offices'
> report is generated.
> for example there are 10 offices comming back it will only display the
> first office. How do I tell the subreport to move on to the next
> offices?
> I tried using the value straight from the query by doing this
> =Fields!ReportingOfficeID.Value which did not work.
> I also tried placing the value returened from the query into a report
> parameter this also did not work. I tried with the multi- value box
> selected and with the box not selected.
> where am I going wrong?
> on a side note that I should have mentioned to start with I am using
> Reporting server 2005.
> thanks once again for all of the help. And If you need me to clear
> something up let me know.
> Mathias
>|||Thiago Silva
I figured out how to solve my problem. I ened up following the steps
laid out in this forum post.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=299810&SiteID=1
what I did was place the subreport in a list and then pass the list of
10-15 office id's to the list / subreport. this allowed the master
report to display all 10-15 different subreports.
Thank you very much for all of you help with this.
Mathiassql

Displaying Top N subtotal as well as grand total

I am currently migrating Crystal Report XI reports over to the
Reporting Services and came across a problem.
The Crystal Report displays Top N records, subtotal of these Top N
records, and the grand total of all the records. SQL stored procedure
underneath returns all the records.
>From what I have been reading in online groups and helps, these types
of features are not directly supported in the Reporting Services. The
workaround I have found so far involves returning two sets of results
("Top N" results and "All" results) and combining the two result
sets.
Since some query takes up significant amount of time, I would like to
avoid calling the stored procedures twice if necessary. Does anyone
know how I can avoid this and work with just one result set?
Thanks in advance.
ShoheiBring all the records and use filter "Top N" to filter for Top N records only.
Amarnath [MVP]
"Shohei.Yamauchi@.gmail.com" wrote:
> I am currently migrating Crystal Report XI reports over to the
> Reporting Services and came across a problem.
> The Crystal Report displays Top N records, subtotal of these Top N
> records, and the grand total of all the records. SQL stored procedure
> underneath returns all the records.
> >From what I have been reading in online groups and helps, these types
> of features are not directly supported in the Reporting Services. The
> workaround I have found so far involves returning two sets of results
> ("Top N" results and "All" results) and combining the two result
> sets.
> Since some query takes up significant amount of time, I would like to
> avoid calling the stored procedures twice if necessary. Does anyone
> know how I can avoid this and work with just one result set?
> Thanks in advance.
> Shohei
>|||If you are using SQL Server 2005, you can use ROW_NUMBER () in a Data
set so that you can get top N

Displaying max values of each group in SQL

Hello everyone;

I have here a code that displays the most recent date for each group of records. But the problem is, I am not able to include some fields of the table.

There are 3 tables named CUST, ACCT, and TRAN:

CUST:
CNO NAME
CN101 DAN
CN102 AAA

ACCT:
ANO CNO
AN101 CN101
AN102 CN102

TRAN:
TNO ANO TDATE BAL
TN101 AN101 01/25/2006 3,000
TN102 AN101 02/15/2006 5,000
TN103 AN102 02/01/2006 4,000
TN104 AN102 02/27/2006 8,000
TN105 AN102 03/18/2006 2,000

And the resultant table should look something like this:

ANO NAME TDATE BAL
AN101 AAA 02/15/2006 5,000
AN102 BBB 03/18/2006 2,000

Now, here's my code:

SELECT DISTINCT

B.NAME,
C.ANO,
MAX(A.TDATE)

FROM TRAN A,
CUST B,
ACCT C

WHERE B.CNO = C.CNO
AND C.ANO = A.ANO

GROUP BY B.CNO,
B.NAME,
C.ANO;

And the resultant table is:

NAME ANO MAX(TDATE)
AN101 AAA 02/15/2006
AN102 BBB 03/18/2006

The problem is, I want to add the field 'BAL' to the resultant table but when I insert 'BAL' to the 'SELECT' clause, the result will look something like this:

ANO NAME TDATE BAL
AN101 AAA 01/25/2006 3,000
AN101 AAA 02/15/2006 5,000
AN102 BBB 02/01/2006 4,000
AN102 BBB 02/27/2006 8,000
AN102 BBB 03/18/2006 2,000

I will really appreciate any help.

Thnks,
dan15phselect B.NAME
, C.ANO
, A.TDATE
, A.BAL
from TRAN A
inner
join ACCT C
on C.ANO = A.ANO
inner
join CUST B
on C.CNO = B.CNO
where A.TDATE
= ( select max(TDATE)
from TRAN
where ANO = A.ANO )|||Sorry for taking so looong to reply. But anyway, thanks for the help r937 (http://www.dbforums.com/member.php?find=lastposter&t=1606412). I finally made it. just made a couple of changes to the code. Actually, I'm still a newbie in SQL and havent used 'inner join' (just recently) and seldom in using inner queries. thanks a lot for the help.:D

Displaying lists horizontally

I am writing a report that displays small lists of data. The lists themselves are 2 inches wide or so and since the lists repeat vertically, I am essentially wasting a lot of space on the right side of the report. Is there a way to display lists horizontally on a page (but still within the page width)?

Thanks!

Did you get anywhere with this as I need to do the same? I would have thought that this is a pretty obvious requirement and should be easy to do, but I'm having alot of trouble.|||If your report is quite simple, you might be able to use the matrix layout instead of the table layout, and have your list values as the columns.|||Thanks for the reply. Will the matrix keep within the width of the page for long lists? i.e. will it snake onto a new line when it hits the page border?|||As far as I know, that's not possible unfortunately. The best bet might be to use SQL to lay the data out exactly how you want it before you put it on the report.

Displaying image in table

Hi,
I have a table which displays the list of employees in my company. I am
trying to display the picture of the employee alongside with the
information.
Is this possible in Reporting Services? The pictures will be stored in
a folder and the name of the picture correspond to the name of the
employee.
Any help would be greatly appreciated.
Thanks.
--
JordanHi Jordan,
You can place an image in the details section of the table. Place it as a
"web" image, but don't worry about entering the URL. Once it has been placed
in the table, goto the image properties tab and find the "Data" section.
Leave MIMEType blank, and make sure the source is external. Now in the
value, you need to build a string that points to the image on disk, so it
could be something like this:
="\\ServerName\Photos\" & Fields!PhotoName.Value & ".jpg"
Where the name of the photo is stored in the db, else you can build the name
from whetever your photo indexing system is (maybe an ID or something). I
store our images based on an ID field in the db, then just use that field to
get the image.
Hope this gives you a start.
Andre
"Jordan" wrote:
> Hi,
> I have a table which displays the list of employees in my company. I am
> trying to display the picture of the employee alongside with the
> information.
> Is this possible in Reporting Services? The pictures will be stored in
> a folder and the name of the picture correspond to the name of the
> employee.
> Any help would be greatly appreciated.
> Thanks.
> --
> Jordan
>|||Hi Andre,
It works like a charm.
Thanks.
--
Jordan|||I am trying to do the same thing but having trouble, Does the image control
support .tif files' I do not see them as an option. Seems weird that they
would not be supported!'
"Jordan" wrote:
> Hi Andre,
> It works like a charm.
> Thanks.
> --
> Jordan
>|||I am trying to do the same thing but having trouble, Does the image control
support .tif s'
"Jordan" wrote:
> Hi Andre,
> It works like a charm.
> Thanks.
> --
> Jordan
>|||I am trying to do the same thing but having trouble, Does the image control
support .tif files' I do not see them as an option. Seems weird that they
would not be supported!'
"Jordan" wrote:
> Hi Andre,
> It works like a charm.
> Thanks.
> --
> Jordan
>

Displaying detail and summary in same bar chart

I have a bar chart that displays quality averages for all plants by
year.
Category = Plant
Series = Year
Detail = average
I would like to add a bar for the total company. Is there an easy way
to do this?
I am using VS 2005.
Thanks.Hello Heather,
You could not do this in the Chart setting.
The only thing you could do is that add a column in the dataset to include
the total data.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||I thought that might be the answer. I wanted to make sure. Thanks.

Displaying data using correct culture

I'm working on a report that displays datetime columns and no matter what I set as my currentculture the datetime columns always use the US format: mm/dd/yyyy.

On the report designer I see there's a "Language" property but I cannot find how to set this from the code.

Note that I'm not talking about localizing the report interface. I know there's adowloadable language pack for this.
What I need is the that the data is displayed using the correct formating for numbers and dates for the current active locale.

Any idea?

Yeah, that's Globalization (as you know)... are you returning a DateTime field in a GridView and supplying the DataFormatString yourself... or are you doing a CONVERT(VARCHAR, MyDate, 101) in SQL? (Because if SQL is the one formatting it, you're getting a string... not a DateTime)

Thanks,

|||

I'm using a ReportViewer in LocalMode and the stored proc that returns data does not format it in any way:

select
a.Date,...
from table a
where ...

Where a.Date is a datetime field.

|||

HI,vmasanas :

As localization is not a built-in feature for Reporting Services, people have tried a a variety of techniques for localizing reports. One technique is to use the LocID propertie to create a version of the RDL in each language. Another approach is to have a single report and create a custom assembly to load the strings for each label. Here is an relatively easy technique for providing localized reports using hidden parameters.

First, you will need to create a table to hold the translations. The following T-SQL will create the table and add a few labels for translating the Product Line Sales sample that is included with the product.

CREATE TABLE [dbo].[Translations](
[Label] [nvarchar](150) NOT NULL,
[Language] [nvarchar](10) NOT NULL,
[Translation] [nvarchar](150) NOT NULL
)
GO
INSERT [dbo].[Translations]
VALUES ('Top Stores','fr-fr','Les meilleurs magasins')
INSERT [dbo].[Translations]
VALUES ('Top Employees','fr-fr','Les meilleurs employés')
INSERT [dbo].[Translations]
VALUES ('Top Stores','en-US','Top Stores')
INSERT [dbo].[Translations]
VALUES ('Top Employees','en-US','Top Employees')
INSERT [dbo].[Translations]
VALUES ('Top Stores','de-DE','Oberseite Speicher')
INSERT [dbo].[Translations]
VALUES ('Top Employees','de-DE','Obere Angestellte')
GO


Now, create a query that will return the set of labels for a given language. Add a dataset named 'Labels' that has the following query:

SELECT Label, Language, Translation
FROM Translations
WHERE (Language = @.Language)

By default, a new report parameter will be created named Language. If you would like to automatically bind the translations to the user's language, you can delete this parameter and bind it to User!Language in the dataset properties dialog. However, for testing purposes it is easier to just type it in for preview.

Next, you will need to create a hidden, multi-valued parameter called 'Labels'. Set the Available Values to the Labels dataset, the Value field to 'Labels' and the Label field to 'Translation'. Set the default values to the same dataset and the Value field to 'Labels'. This is important as you don't have access to the available values from within the report, only the actual values. When the user runs the report, the parameter value will contain all of the labels.

Now, add a function to the report (from the code tab of the Report->Report Properties menu item)

Public Function GetLabel(P as Parameter, Label as String) as String
Dim i As Integer
For i = 0 to Ubound(P.Value)
If (P.Value(i) = Label) Then Return P.Label(i)
Next i
Return Label
End Function

This function will find the translated label within the supplied multi-valued parameter. If the label is not found, the passed in value is returned. This is important as you may have a user language for which you have not created the translations.

The only thing left is to change the static labels in your report to use this new function. For example, to translate the Top Employees label, use the function

=Code.GetLabel(Parameters!Labels,"Top Employees")

If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be

of assistance

|||

Rex,

thanks for you comments, but what I was asking for is slightly different. What I need is that special data types are formated accordingly to the current culture. For example if I get this data:

SELECT aDate, aNumber FROM ...

I'd expect that when the user is using a en-US locale the date is displayed as mm/dd/yyyy and the number "##,###.##". Otherwise, if current culture is es-ES I'd expect to see dd/mm/yyyy and "##.###,##" respectively.

Either I'm doing something wrong or I'm missing a point because this does not work for me. I have a language selector on the page and no matter what I use I always see the data formated as en-US. And, no, I don't do any formating, convertion or modification on these fields. They come right from the db.

|||

As for theReportingService 2005, it does support some localization
features, they include:

1)the localization of the built-in UI components, such as SSRS's
htmlviewer, report designer

2) some simple localization on the SSRS report's data

For 1), the SSRS has done the work for us already, for example, when we
visit the html report, the UI elements on the htmlviewer(button or other UI
element's text) will render the localized representation according to
client-side browser's user-language setting.

For 2), if we want to do some simple localization on the static data/text
displayed on our report, we can dynamically format them according to the
"User!Language" parameter in our report expression. Or you can even build
custom assembly that has custom code logic to generate localizaed text(from
net resource ) accordin to this parameter.

You can find all the localization support of SSRS 2005 in the BOL:

#International Considerations forReporting Services
http://msdn2.microsoft.com/en-us/library/ms156493.aspx

While leveraging User!Language certainly will work in terms of allowing you localize your reports, doing so is admittedly a pain in the tail – you essentially have to replace all your label text with expressions that call into code that you write to do "label localization" ala:

= MyNameSpace.MyConvertingClass.MyConverter("someLabelName", User!Language)

Your code takes the name of a label and the language to use, then convert the date or the symbol to be the the currect one and return it

|||

HI,vmasanas:

We are marking this issue as "Answered". If you have any new findings or concerns, please feel free to unmark the issue.
Thank you for your understanding!

|||

Rex,

thanks for your support but I think we are talking of different things here.

What I'm trying to get is that a datetime column displays it's data using a format accordingly to the current culture selected by the user at the time.

When I'm designing a report I see a property called "Language" when, if changed, displays data correctly. What I would need is access to this property at runtime so the report is configured dinamically depending on the user preferences.

I've not been able to figure where in the object hierarchy I can get access to this property.

|||

I had a similar problem and here is how to solve it:

CultureInfo ci;
// Format the current date and time in various ways.

// Display the thread current culture, which is used to format the values.
ci = Thread.CurrentThread.CurrentCulture;
ci = new CultureInfo("en-IE");
Thread.CurrentThread.CurrentCulture = ci;
Thread.CurrentThread.CurrentUICulture = ci; //I guess you did'nt set this one

Hanin

|||

I wish you were right but no :(

Protected Overrides Sub OnInit(ByVal eAs System.EventArgs)
' Set the current culture
Thread.CurrentThread.CurrentUICulture = PageCulture
Thread.CurrentThread.CurrentCulture = PageCulture
' Localize portalsettings
Services.Localization.Localization.LocalizePortalSettings()
MyBase.OnInit(e)
End Sub

I'm trying to use this inside aDotNetNuke module but seems there's something missing here since I've never had any problem with localization inside DNN.

Displaying Character Format in Numeric Field

I have a Calculated Member that displays values of 999.0 when the calculation cannot be computed based on certain criteria. I would like to be able to format the numeric field so that is displays a character such as "X" when the value of the Calculated Member is 999.0. Does anyone know how to do this?

If this is in Analysis Services 2005, you should be able to use the IIF() MDX function to return either the value or "x".|||

Thanks. That worked!

Displaying Character Format in Numeric Field

I have a Calculated Member that displays values of 999.0 when the calculation cannot be computed based on certain criteria. I would like to be able to format the numeric field so that is displays a character such as "X" when the value of the Calculated Member is 999.0. Does anyone know how to do this?

If this is in Analysis Services 2005, you should be able to use the IIF() MDX function to return either the value or "x".|||

Thanks. That worked!

Displaying a Message if no chart data present

Hi, I currently have a report that displays a chart among other things. If
the report is called but no data is available for the chart i get a big
block of white space. Is it at all possible to display something in it's
place rather than just nothing at all if there is no data for the chart to
display?
Thanks, SimonYes. The chart has a "NoRows" property in the properties window. If the
chart's dataset has no rows and the NoRows property is set, a textbox with
the NoRows message will be shown instead of the chart.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Simon Dingley" <me@.example.com> wrote in message
news:OmWqrRrYFHA.3320@.TK2MSFTNGP12.phx.gbl...
> Hi, I currently have a report that displays a chart among other things.
> If
> the report is called but no data is available for the chart i get a big
> block of white space. Is it at all possible to display something in it's
> place rather than just nothing at all if there is no data for the chart to
> display?
> Thanks, Simon
>

Displaying a List Horizontally

Hi All,

I'm new to reporting services and I need some help. I've created a list which contains a photo and user name. Currently, it displays all the information I want however, the list expands down the page in one column. I would like it to expand across the page horizontally (4 columns) and then skip down to the next line. Can someone tell me how to do this using report designer? Thank youReporting Services doesn't support listing horizontally natively.

You could workaround by trying something like the following (conceptually this is easier to understand):
1) inserting a constructed column in your dataset (right click on your fields windows and select add)
2) set the value of the constructed column so it increases by 1 for every 4th value of RowNumber()... maybe =Floor(RowNumber()/4)
3) Add another constructed column in your data set
4) set the value to be the remainder =Mod(RowNumber()/4) this should give values from 1 - 4.
5) add a grouping to your list based on the constructed column created in #2
6) Add 4 lists into your list. Let's call these "inner lists"
7) for each inner list, add a filter on for the appropriate value of the constructed column created in #3.
8) in each inner list display your data the way you want it to look

(take a coffee break cause this was hard :-))

You might also be able to do something similar with a Matrix control where you put the constructed column in #4 as the column grouping and the constructed column in #2 as the row grouping. A little harder to understand maybe but probably easier to maintain in the long run.

Good luck,
-Lukasz|||I'm trying to use the solution provided by lukasz but I seem to be getting an error when creating the constructed field as suggested:

A sort expression for the field ‘=RowNumber("table")’ uses the function RowNumber. RowNumber cannot be used in sort expressions.

Anyone have any idea why?

Thanks.

|||

Hello,

This does not work for me as well. I get the same error as described by Pumm3l.

Can someone please provide a reliable answer?

Thanks.

Displaying a List Horizontally

Hi All,

I'm new to reporting services and I need some help. I've created a list which contains a photo and user name. Currently, it displays all the information I want however, the list expands down the page in one column. I would like it to expand across the page horizontally (4 columns) and then skip down to the next line. Can someone tell me how to do this using report designer? Thank youReporting Services doesn't support listing horizontally natively.

You could workaround by trying something like the following (conceptually this is easier to understand):
1) inserting a constructed column in your dataset (right click on your fields windows and select add)
2) set the value of the constructed column so it increases by 1 for every 4th value of RowNumber()... maybe =Floor(RowNumber()/4)
3) Add another constructed column in your data set
4) set the value to be the remainder =Mod(RowNumber()/4) this should give values from 1 - 4.
5) add a grouping to your list based on the constructed column created in #2
6) Add 4 lists into your list. Let's call these "inner lists"
7) for each inner list, add a filter on for the appropriate value of the constructed column created in #3.
8) in each inner list display your data the way you want it to look

(take a coffee break cause this was hard :-))

You might also be able to do something similar with a Matrix control where you put the constructed column in #4 as the column grouping and the constructed column in #2 as the row grouping. A little harder to understand maybe but probably easier to maintain in the long run.

Good luck,
-Lukasz|||I'm trying to use the solution provided by lukasz but I seem to be getting an error when creating the constructed field as suggested:

A sort expression for the field ‘=RowNumber("table")’ uses the function RowNumber. RowNumber cannot be used in sort expressions.

Anyone have any idea why?

Thanks.

|||

Hello,

This does not work for me as well. I get the same error as described by Pumm3l.

Can someone please provide a reliable answer?

Thanks.

Displaying '$' currency in a graph

I am populating a graph from a stored procedure. My graph displays
without a problem, except for the fact that it will not show a $ dollar
sign. I have tried using FormatCurrency() function on the value, but
it does not work. I have also tried just inserting a $ in front of the
vaue "$" & Fields!Total.Value, but it did not work either. Any
suggestions?
TwoNevermind, found it
Twobridge wrote:
> I am populating a graph from a stored procedure. My graph displays
> without a problem, except for the fact that it will not show a $ dollar
> sign. I have tried using FormatCurrency() function on the value, but
> it does not work. I have also tried just inserting a $ in front of the
> vaue "$" & Fields!Total.Value, but it did not work either. Any
> suggestions?
> Two

Display X number of records per page

Hi
I have a report which displays skills per staff, I have placed staff details
in the textbox with a list and also I have placed a table of skills for
grouped by staffid. Basically I need to print out certificates like :
Staff X
--
then skills listed
1.skill A
2.skill B
--
footer which has a signature.
I have got everything working except if any staff has more than 10 skills it
just blows out of the page size and everything goes crazy. My question is how
can I display only 5 skills per page with header and footer and if the skills
exceed more than 5 then create a new page with header and footer and the
remaining skills.
I have used this function in the visibility of the skills table
=iif(rownumber(nothing)>5,False,True) but it just stops displaying anyskills
if the skills exceed more than 5 skills.
Please any advice or direction be most appreciated.
CheersCreate a grouping on the list as follows:
=Ceiling(RowNumber(nothing)/5)
and designate a page break after the group.
This will split the list into 5 per page.
--C17
"shahab" <shahab@.discussions.microsoft.com> wrote in message
news:4C9E5928-5BA9-49D0-B0C7-0F85EF42B927@.microsoft.com...
> Hi
> I have a report which displays skills per staff, I have placed staff
> details
> in the textbox with a list and also I have placed a table of skills for
> grouped by staffid. Basically I need to print out certificates like :
> Staff X
> --
> then skills listed
> 1.skill A
> 2.skill B
> --
> footer which has a signature.
> I have got everything working except if any staff has more than 10 skills
> it
> just blows out of the page size and everything goes crazy. My question is
> how
> can I display only 5 skills per page with header and footer and if the
> skills
> exceed more than 5 then create a new page with header and footer and the
> remaining skills.
> I have used this function in the visibility of the skills table
> =iif(rownumber(nothing)>5,False,True) but it just stops displaying
> anyskills
> if the skills exceed more than 5 skills.
> Please any advice or direction be most appreciated.
> Cheers|||Thank you very much it worked.
merry xmas
"C17" wrote:
> Create a grouping on the list as follows:
> =Ceiling(RowNumber(nothing)/5)
> and designate a page break after the group.
> This will split the list into 5 per page.
> --C17
>
> "shahab" <shahab@.discussions.microsoft.com> wrote in message
> news:4C9E5928-5BA9-49D0-B0C7-0F85EF42B927@.microsoft.com...
> > Hi
> > I have a report which displays skills per staff, I have placed staff
> > details
> > in the textbox with a list and also I have placed a table of skills for
> > grouped by staffid. Basically I need to print out certificates like :
> > Staff X
> > --
> > then skills listed
> > 1.skill A
> > 2.skill B
> > --
> > footer which has a signature.
> > I have got everything working except if any staff has more than 10 skills
> > it
> > just blows out of the page size and everything goes crazy. My question is
> > how
> > can I display only 5 skills per page with header and footer and if the
> > skills
> > exceed more than 5 then create a new page with header and footer and the
> > remaining skills.
> >
> > I have used this function in the visibility of the skills table
> > =iif(rownumber(nothing)>5,False,True) but it just stops displaying
> > anyskills
> > if the skills exceed more than 5 skills.
> > Please any advice or direction be most appreciated.
> > Cheers
>
>

Display text as HTML?

Is it possible to display text saved with HTML tags as true HTML inside a text box? For example I have some text saved that displays in red when viewed on a web page, but in the report it actually prints the HTML tags that set the color to red. Any ideas? Thanks!
JeffNOPE. Reporting Services just prints out the text AS IS and is not parsed by the browser. I have spent a very good amount of time on this issue for one of the projects I worked on.
I even brought it up during one of the MSDN events with a Microsoft SQL Server MVP speaker. He asked me to email him in detail about it and when I did he never replied back.|||Thanks for the reply. That's what I was afraid of. Guess I'll probably end up using Active Reports for now. Thanks again!

Display Rowid in Select of a view with double values

Hi there,
I have the following problem. I would like to add an key to my view in the
form of a rowid. My view displays values that can come more then once.
In Oracle there is one database kolumn called RowId what can be used in any
select statements. Is there also something in SQL Server 2000?
Or what can I do to get the rownumbers? There are no keys in my view and the
values can be come more then once (so nothing is unique.. that's why I need a
nice autonumber number)
Alex
SQL Server 2005 has the same function row_number if I remember well
In SQL Server 2000 you can try
SELECT OrderId,(SELECT COUNT(*) FROM Orders O WHERE
O.OrderId<=Orders.Orderid) AS rnk
FROM Orders ORDER BY rnk ASC
"Alex." <Alex@.discussions.microsoft.com> wrote in message
news:228C9AE8-542C-4792-8E64-CDEE7AEE440A@.microsoft.com...
> Hi there,
> I have the following problem. I would like to add an key to my view in the
> form of a rowid. My view displays values that can come more then once.
> In Oracle there is one database kolumn called RowId what can be used in
> any
> select statements. Is there also something in SQL Server 2000?
> Or what can I do to get the rownumbers? There are no keys in my view and
> the
> values can be come more then once (so nothing is unique.. that's why I
> need a
> nice autonumber number)
|||There is no such thing as ROWID in SQL 2000.
Also, if a row cannot be uniquely identified then the database is not
correctly normalized. i.e. It is not in 2NF so you are going to find it
difficult to use SQL (which relies on good normalization) to provide a
solution.
Nik Marshall-Blank MCSD/MCDBA
"Alex." <Alex@.discussions.microsoft.com> wrote in message
news:228C9AE8-542C-4792-8E64-CDEE7AEE440A@.microsoft.com...
> Hi there,
> I have the following problem. I would like to add an key to my view in the
> form of a rowid. My view displays values that can come more then once.
> In Oracle there is one database kolumn called RowId what can be used in
> any
> select statements. Is there also something in SQL Server 2000?
> Or what can I do to get the rownumbers? There are no keys in my view and
> the
> values can be come more then once (so nothing is unique.. that's why I
> need a
> nice autonumber number)
|||Thnks all,
Yes cant do a unique identified the rows.. So I can do use with a temp table
and use an identify column.
"Nik Marshall-Blank (delete fcom for my e" wrote:

> There is no such thing as ROWID in SQL 2000.
> Also, if a row cannot be uniquely identified then the database is not
> correctly normalized. i.e. It is not in 2NF so you are going to find it
> difficult to use SQL (which relies on good normalization) to provide a
> solution.
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Alex." <Alex@.discussions.microsoft.com> wrote in message
> news:228C9AE8-542C-4792-8E64-CDEE7AEE440A@.microsoft.com...
>
>

Display Rowid in Select of a view with double values

Hi there,
I have the following problem. I would like to add an key to my view in the
form of a rowid. My view displays values that can come more then once.
In Oracle there is one database kolumn called RowId what can be used in any
select statements. Is there also something in SQL Server 2000?
Or what can I do to get the rownumbers? There are no keys in my view and the
values can be come more then once (so nothing is unique.. that's why I need a
nice autonumber number)Alex
SQL Server 2005 has the same function row_number if I remember well
In SQL Server 2000 you can try
SELECT OrderId,(SELECT COUNT(*) FROM Orders O WHERE
O.OrderId<=Orders.Orderid) AS rnk
FROM Orders ORDER BY rnk ASC
"Alex." <Alex@.discussions.microsoft.com> wrote in message
news:228C9AE8-542C-4792-8E64-CDEE7AEE440A@.microsoft.com...
> Hi there,
> I have the following problem. I would like to add an key to my view in the
> form of a rowid. My view displays values that can come more then once.
> In Oracle there is one database kolumn called RowId what can be used in
> any
> select statements. Is there also something in SQL Server 2000?
> Or what can I do to get the rownumbers? There are no keys in my view and
> the
> values can be come more then once (so nothing is unique.. that's why I
> need a
> nice autonumber number)|||There is no such thing as ROWID in SQL 2000.
Also, if a row cannot be uniquely identified then the database is not
correctly normalized. i.e. It is not in 2NF so you are going to find it
difficult to use SQL (which relies on good normalization) to provide a
solution.
--
Nik Marshall-Blank MCSD/MCDBA
"Alex." <Alex@.discussions.microsoft.com> wrote in message
news:228C9AE8-542C-4792-8E64-CDEE7AEE440A@.microsoft.com...
> Hi there,
> I have the following problem. I would like to add an key to my view in the
> form of a rowid. My view displays values that can come more then once.
> In Oracle there is one database kolumn called RowId what can be used in
> any
> select statements. Is there also something in SQL Server 2000?
> Or what can I do to get the rownumbers? There are no keys in my view and
> the
> values can be come more then once (so nothing is unique.. that's why I
> need a
> nice autonumber number)|||Thnks all,
Yes cant do a unique identified the rows.. So I can do use with a temp table
and use an identify column.
"Nik Marshall-Blank (delete fcom for my e" wrote:
> There is no such thing as ROWID in SQL 2000.
> Also, if a row cannot be uniquely identified then the database is not
> correctly normalized. i.e. It is not in 2NF so you are going to find it
> difficult to use SQL (which relies on good normalization) to provide a
> solution.
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Alex." <Alex@.discussions.microsoft.com> wrote in message
> news:228C9AE8-542C-4792-8E64-CDEE7AEE440A@.microsoft.com...
> > Hi there,
> >
> > I have the following problem. I would like to add an key to my view in the
> > form of a rowid. My view displays values that can come more then once.
> >
> > In Oracle there is one database kolumn called RowId what can be used in
> > any
> > select statements. Is there also something in SQL Server 2000?
> >
> > Or what can I do to get the rownumbers? There are no keys in my view and
> > the
> > values can be come more then once (so nothing is unique.. that's why I
> > need a
> > nice autonumber number)
>
>

Display Rowid in Select of a view with double values

Hi there,
I have the following problem. I would like to add an key to my view in the
form of a rowid. My view displays values that can come more then once.
In Oracle there is one database kolumn called RowId what can be used in any
select statements. Is there also something in SQL Server 2000?
Or what can I do to get the rownumbers? There are no keys in my view and the
values can be come more then once (so nothing is unique.. that's why I need
a
nice autonumber number)Alex
SQL Server 2005 has the same function row_number if I remember well
In SQL Server 2000 you can try
SELECT OrderId,(SELECT COUNT(*) FROM Orders O WHERE
O.OrderId<=Orders.Orderid) AS rnk
FROM Orders ORDER BY rnk ASC
"Alex." <Alex@.discussions.microsoft.com> wrote in message
news:228C9AE8-542C-4792-8E64-CDEE7AEE440A@.microsoft.com...
> Hi there,
> I have the following problem. I would like to add an key to my view in the
> form of a rowid. My view displays values that can come more then once.
> In Oracle there is one database kolumn called RowId what can be used in
> any
> select statements. Is there also something in SQL Server 2000?
> Or what can I do to get the rownumbers? There are no keys in my view and
> the
> values can be come more then once (so nothing is unique.. that's why I
> need a
> nice autonumber number)|||There is no such thing as ROWID in SQL 2000.
Also, if a row cannot be uniquely identified then the database is not
correctly normalized. i.e. It is not in 2NF so you are going to find it
difficult to use SQL (which relies on good normalization) to provide a
solution.
--
Nik Marshall-Blank MCSD/MCDBA
"Alex." <Alex@.discussions.microsoft.com> wrote in message
news:228C9AE8-542C-4792-8E64-CDEE7AEE440A@.microsoft.com...
> Hi there,
> I have the following problem. I would like to add an key to my view in the
> form of a rowid. My view displays values that can come more then once.
> In Oracle there is one database kolumn called RowId what can be used in
> any
> select statements. Is there also something in SQL Server 2000?
> Or what can I do to get the rownumbers? There are no keys in my view and
> the
> values can be come more then once (so nothing is unique.. that's why I
> need a
> nice autonumber number)|||Thnks all,
Yes cant do a unique identified the rows.. So I can do use with a temp table
and use an identify column.
"Nik Marshall-Blank (delete fcom for my e" wrote:

> There is no such thing as ROWID in SQL 2000.
> Also, if a row cannot be uniquely identified then the database is not
> correctly normalized. i.e. It is not in 2NF so you are going to find it
> difficult to use SQL (which relies on good normalization) to provide a
> solution.
> --
> Nik Marshall-Blank MCSD/MCDBA
> "Alex." <Alex@.discussions.microsoft.com> wrote in message
> news:228C9AE8-542C-4792-8E64-CDEE7AEE440A@.microsoft.com...
>
>

Display Reporting Services in Blackberry

Attempting to render a report in mobile blackberry. The report viewer
displays, and the report generates, but eventually freezes and no data
appears.
Blackberry 5 OS
SQL Reporting Services 2005 (SP2)
Blackberry browser with IE emulation and javascripts enabled on
client.
Any other blackberry users attempting to view reporting services?
Thanks.
RobThough I have not yet discovered the procedure for successfully
displaying a dynamic report within a reportviewer, it did discover a
work around that might be the next best thing. For a little
background, I do have reporting services integrated with sharepoint
services 3.0. A user can go to his/her web site or workspace shared
documents and build a report or subscribe to an existing report in
their list.
So, for example: Customers.rdl and it's datasource actually exist as
documents in the shared document list. This is accomplished by adding
these content types to the shared documents list.
1) I subscribed to the report using the sharepoint web form.
2) Select the report from the dropdown list in the shared documents
list.
3) Select "Manage subscriptions"
4) Set up a subscription to run "hourly" and select "web page" as the
format type.
5) Give your subscription document a name. i.e. myreport.html
5) Have the subscription sent to //<yoursharepointserverpath>/shared
documents/
Now from your blackberry browser, navigate to the name
(myreport.html).
Rob