Formatting part of a text box

hey all,

does anyone know of a way to format part of a text box in sql reporting services 2005? i am creating a form letter and need to concatenate static text with a column and need just the column value to be bold. like this:

"This letter is confirmation that Acme Agency has been chosen..."

my expression to build this is:

="This letter is confirmation that " + Fields!agency_name.Value + " has been chosen..."

Is there a way to apply formatting just to Fields!agency_name.Value ?

thanks!

jen

Sorry, this is currently not supported.

-- Robert

|||Unable to format individual words within a text is avoiding us to totally move from Business Objects and Crystal Reports to SSRS. Is there a workaround or is it expected in a future release?

Thanks,

Jeroen van der Wal
BI Consultant

|||

It is on the list for a future release. You might want to check out related threads on the SSRS 2008 forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=1624&SiteID=1

-- Robert

Formatting part of a text box

hey all,

does anyone know of a way to format part of a text box in sql reporting services 2005? i am creating a form letter and need to concatenate static text with a column and need just the column value to be bold. like this:

"This letter is confirmation that Acme Agency has been chosen..."

my expression to build this is:

="This letter is confirmation that " + Fields!agency_name.Value + " has been chosen..."

Is there a way to apply formatting just to Fields!agency_name.Value ?

thanks!

jen

Sorry, this is currently not supported.

-- Robert

|||Unable to format individual words within a text is avoiding us to totally move from Business Objects and Crystal Reports to SSRS. Is there a workaround or is it expected in a future release?

Thanks,

Jeroen van der Wal
BI Consultant

|||

It is on the list for a future release. You might want to check out related threads on the SSRS 2008 forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=1624&SiteID=1

-- Robert

Formatting part of a text box

hey all,

does anyone know of a way to format part of a text box in sql reporting services 2005? i am creating a form letter and need to concatenate static text with a column and need just the column value to be bold. like this:

"This letter is confirmation that Acme Agency has been chosen..."

my expression to build this is:

="This letter is confirmation that " + Fields!agency_name.Value + " has been chosen..."

Is there a way to apply formatting just to Fields!agency_name.Value ?

thanks!

jen

Sorry, this is currently not supported.

-- Robert

|||Unable to format individual words within a text is avoiding us to totally move from Business Objects and Crystal Reports to SSRS. Is there a workaround or is it expected in a future release?

Thanks,

Jeroen van der Wal
BI Consultant

|||

It is on the list for a future release. You might want to check out related threads on the SSRS 2008 forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=1624&SiteID=1

-- Robert

sql

Formatting dates on a chart when sourced from SSAS2k5

I am creating a few charts in SSRS that are sourced from a cube in AS. On the x-axis is time, which is what I assume a date-like field in the cube. On the y-axis is the measure.

My problem is that I cannot seem to get the dates to format on the chart - regardless of the format code I use, the dates appear in long format, like "Thursday, November 23 2006".

Are the dates from SSAS just string values?

I feel that you may have mentioned some expression or modification done on that field.I faced this problem before where i came to know that Format expression which will be like (#000# Something...) when some operation is being performed on that date. Take out that any operation on that field and give only format option or use format function in the expression.

Hope it should work

Regards,

Raj Deep.A

|||Hi Raj,

I'm not sure I follow - I haven't modified the field at all, yet. I merely created an MDX Query using the designer, and Time is one of the dimensions I used to splice my measure by. I wanted to expose this data in a chart, but for some reason I can't apply any standard formatting codes to the Time label on the x-axis.|||

Based on your query in the MDX query generator, the date time value is provided as a string to Reporting Services. That's why format codes won't work.

You could look into either changing your MDX query to get the value as DateTime object (which means you have to hand-write the MDX) or you could try to convert the string back into a DateTime object in the chart category grouping expression by using an expression similar to this: =CDate(Fields!DateValue.Value)

But depending on the actual strings, the CDate() function may fail to convert and you may need to look into using a combination of several VB runtime conversion functions / DateTime functions to achieve the conversion.

-- Robert

|||That's what I feared...thanks for the confirmation. Hopefully something to look forward to in Katmai? [better integration between SSAS and SSRS, that is]|||

Another aproach is to assing a value to your attribute members and use the MemberValue MDX function.

1. In your cube, assign the Value property of the Date attribute hierarchy to a Date column in the underlying table.

2. Unfortunately, the RS SSAS data provider doesn't currently surface the MemberValue function so you need to create a calculated member, e.g. NativeDate with the following expression [Date].[Date].MemberValue.

3. Now you can format the calculated member as you wish, e.g. Format(Fields!NativeDate.Value, "MMM/dd/

|||How does this work with server time dimensions?|||Sorry, it won't b/c you won't be able to assign a value column to a server time dimension.

Formatting dates on a chart when sourced from SSAS2k5

I am creating a few charts in SSRS that are sourced from a cube in AS. On the x-axis is time, which is what I assume a date-like field in the cube. On the y-axis is the measure.

My problem is that I cannot seem to get the dates to format on the chart - regardless of the format code I use, the dates appear in long format, like "Thursday, November 23 2006".

Are the dates from SSAS just string values?

I feel that you may have mentioned some expression or modification done on that field.I faced this problem before where i came to know that Format expression which will be like (#000# Something...) when some operation is being performed on that date. Take out that any operation on that field and give only format option or use format function in the expression.

Hope it should work

Regards,

Raj Deep.A

|||Hi Raj,

I'm not sure I follow - I haven't modified the field at all, yet. I merely created an MDX Query using the designer, and Time is one of the dimensions I used to splice my measure by. I wanted to expose this data in a chart, but for some reason I can't apply any standard formatting codes to the Time label on the x-axis.|||

Based on your query in the MDX query generator, the date time value is provided as a string to Reporting Services. That's why format codes won't work.

You could look into either changing your MDX query to get the value as DateTime object (which means you have to hand-write the MDX) or you could try to convert the string back into a DateTime object in the chart category grouping expression by using an expression similar to this: =CDate(Fields!DateValue.Value)

But depending on the actual strings, the CDate() function may fail to convert and you may need to look into using a combination of several VB runtime conversion functions / DateTime functions to achieve the conversion.

-- Robert

|||That's what I feared...thanks for the confirmation. Hopefully something to look forward to in Katmai? [better integration between SSAS and SSRS, that is]|||

Another aproach is to assing a value to your attribute members and use the MemberValue MDX function.

1. In your cube, assign the Value property of the Date attribute hierarchy to a Date column in the underlying table.

2. Unfortunately, the RS SSAS data provider doesn't currently surface the MemberValue function so you need to create a calculated member, e.g. NativeDate with the following expression [Date].[Date].MemberValue.

3. Now you can format the calculated member as you wish, e.g. Format(Fields!NativeDate.Value, "MMM/dd/

|||How does this work with server time dimensions?|||Sorry, it won't b/c you won't be able to assign a value column to a server time dimension.

Formating

I am creating an extraction routine and need to pull out an invoice number
from a table. However as part of transferring this to a new system I need to
add leading zeroes to the front of the field.
eg 456 into 000456.
I`m sure I am having brain fade here but can`t think of the way to do this.
Any help gratefully received.SELECT RIGHT('000000' + CONVERT(varchar(20),456),6)
HTH, Jens Suessmeyer.

Format Masking for creating leading periods

Is there an elegant way to fill the empty space leading a textbox in a table with periods, similar to a table of contents effect?

When I do not allow the textbox to grow and just append a long string of periods it looks fine in my report preview, but after I deploy everything appended after the primary field in that text box is missing?

My report has a lot of data to the right but it is collapsible so the fields to the left are a good distance away. This is why I am trying to include light visual aids that assist lining up data values. I am not stuck on the leading period idea but it seems the least cluttered.

AaronBump...
Does anyone have any solutions for masking fields?|||

Hello Aaron,

Can you try something like this in your field's expression:

=StrDup(25 - len(Fields!FIELD1.Value), ".") & Fields!FIELD1.Value

The 25 in my example would have to be higher than the maximum number of characters in your FIELD1 field. This will fill the rest of space (up to 25 characters) on the left of the field with period's, it should be adjusted to fill the rest of your textbox.

Hope this helps.

Jarret

FORMAT ISSUE... what to do?

Hey,
I have a little format issue with Excel 2003:
I am working in reporting services - creating a report. The SQL store
procedure returns a date with the following format:
MM/DD/YYYY HH:MM PM/AM
In my report on that perticular text field I have the following format:
dd/mm/yyyy HH:MM:SS
Because I want it in military time.
It displays fine in the report but when I open it in Excel it brings up
the following ERROR:
"File Error. Some number formats may have been lost"
And it shows the date columns the following way:
"38692.46597"
The document is NOT huge and does't contain more than 2000 rows.
Any ideas on how to solve this?Alright solved this:
Changed the format
"dd/mm/yyyy HH:MM:SS"
to
"dd/mm/yyyy HH:MM:ss"
seems that did it ... weird
regards,
Sorcerdon

Format

How can I specify the format in check constraint while creating a table like
SSN format should be :- 000-00-0000
Thanks
NOOR
On Fri, 27 Aug 2004 07:58:13 -0700, Noor wrote:

>How can I specify the format in check constraint while creating a table like
>SSN format should be :- 000-00-0000
>Thanks
>NOOR
Hi Noor,
Check Books Online. The article about CREATE TABLE has some great examples
that you can easily adapt for this.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:1akui0pgv94e9lam9frjl0qefb918f3q1v@.4ax.com... [vbcol=seagreen]
> On Fri, 27 Aug 2004 07:58:13 -0700, Noor wrote:
like
> Hi Noor,
> Check Books Online. The article about CREATE TABLE has some great examples
> that you can easily adapt for this.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Form with 20,500 Fields

I'm running SQL Server 2000. I have an interesting form I am creating for a client which has on it literally 20,500 fields that need to be stored in the system which I then have to create reports off of for statistics and trends.

I'm not sure how I should go about storing that large amount of information in SQL Server with the limitations of the size of a table. Would it be best to create 20 some tables to store it, or is there a better fashion to store it. 90% of the fields are numbers ranging from 0 to 100.

Thanks for any suggestions you can come up with!What the hell does the form do?

Does anyone enter 20k+ fields?

If not and you populate it, who's gonna read it?

Anyway, what language are you using?

Sounds like you are thinking that the form is like 1 row...Is there anything else about the data that looks like a Key?

Is it really n sets of info tied together with the pageid?

I guess it goes back to what the form contains...

WOW

Never seen this before...|||20,500 fields ...

Man... i would really go down to sleep filling such a big form :)|||Believe it or not we're not going to populate it. We're a mysteryshopping firm and we're going to be paying an auditor to collect sales trend information and they're going to be going online and submitting this information. Typically we're using VB/ASP for coding. There is a JobID that is used for a key and is referenced throughout the DB, and a StoreID that is going to be used to link to our properties that can't really break up the large amount of data I need to collect.

The form is created already, I just don't know the best way to try and store it since it is all pertaining to one area of information, sale prices and percentages.|||Got a data modeler on staff?

You located in the Northeast?

What type of information are they going to be enetering?

You'll also need to think about data retrieval and data mining probably...

Man that's a BIG denormalized form|||You seriously need to rethink your design.

Seriously.|||NW Ohio is where we are located. They open the form with a JobID that has been assigned to them. The form opens displaying the location they were to have gone to and collected information about throughout the week.

They enter in each rack type encountered in a store (rolling racks, clearence racks, round racks) things that would hold stock (be it clothing or clocks) this is a dropdown of all the different types of racks and is being stored as a number.

Each rack then has associated sale amounts stored in either an Original Price, and sale percentage, the markdown price, and sale percentage, or just the sale percentage itself. Then the density of each rack is stored as a number (1 - 10) and a description dropdown is available containing the types of items they will encounter (given values 1-37). This data comprises about 20,000 of the rows.

The other 500 are mostly yes/no questions pertaining to the sales.|||Somebody's actually gonna fill up the fields ... wow !!!|||OK...when you say 20,000 rows...there you go...

Each row seems to be made up of only a handful of elements...those elements seem to describe a "rack"

You'll need a table for your rack

I'm laughing when I'm typing this now...wow what a rack...

anyway, you'll have other ancillary tables that hold the rack type, store id, ect...

First thing to do is list all of the unique elemenst, then categorize them in to entities...they'll became your tables...

Make sense?|||Yes and no... I was hoping to get around having to create 10 tables to store and reference the data. It seems like it would be easier to have a table with these columns:

Job ID, Rack_Type1_1, Rack_Density1_1, Rack_Items1_1, Rack_Org_Price1_1, Rack_Markdown_Price1_1, Rack_Markdown_Percentage1_1, Rack_Descriptor1_1, Rack_Type1_2, Rack_Density1_2, Rack_Items1_2, Rack_Org_Price1_2, Rack_Markdown_Price1_2, Rack_Markdown_Percentage1_2, Rack_Descriptor1_2

and so on... until I got to Rack_Type5_500, Rack_Density5_500, Rack_Items5_500, Rack_Org_Price5_500, Rack_Markdown_Price5_500, Rack_Markdown_Percentage5_500, Rack_Descriptor5_500

assuming 5 possible stores in the mall selected out of 12, and up to 500 different sales for the racks in each store.

Even if I break it down into 5 tables holding individual store data, it's still too many columns. That would still be 4,000 columns.|||You know EXECL right?

Your'e thinking Horizontally...you need to think vertically...

CREATE TABLE [WhatARack!] (
StoreId int
, RackId int
, Rack_Price money
, Rack_Density int
. Rack_WhateverPropertyYouNeedToCapture varchar(10)
)

Where you say _1 that would be an INSERT and 1 row would go in..._500 would be an insert and that would be row 500

Is there a way to id the racks?

If not, maybe you can use IDENTITY...but I usually like to define natural keys (things that make a unique description about things)

Make sense?|||Originally posted by bornweb
NW Ohio is where we are located.

You blind dude...go hope in the car and help this guy/gal out...|||I think I know what you mean. Just seems kind of strange.

Guess i'm used to the normal one page forms they throw at me here where I create one small client_name table with 75 fields or so that I link to my Invoices, Clients, Contacts, Properties, Auditors table for reporting.

Now to have to create a few tables to replace the normal client_name table and having to link those into the other tables I listed above to store and view data, well that just seems evil!

But that's cool. Better than the other suggestion I got from a programmer here. ("Oh just make an array.")

Thanks again,
Nickolas Smith|||Originally posted by bornweb
I think I know what you mean. Just seems kind of strange.

Guess i'm used to the normal one page forms they throw at me here where I create one small client_name table with 75 fields or so that I link to my Invoices, Clients, Contacts, Properties, Auditors table for reporting.

Now to have to create a few tables to replace the normal client_name table and having to link those into the other tables I listed above to store and view data, well that just seems evil!

But that's cool. Better than the other suggestion I got from a programmer here. ("Oh just make an array.")

Thanks again,
Nickolas Smith

Dude,

What language is the fron end written in?

Are you using recordsets?|||One giant web application wrote in VB and Java. Using ASP and ADO.

If you're referring to just this one form it's basically an ASP page submitting to itself where i'm going to use VBS/ADO to insert the objects.|||All I can say, Damn! I'm glad I'll never have to deal with this database...

Model the thing like Brett has suggested otherwise it's going to hell in a hand cart real fast...|||The database isn't that bad and is split-up into many tables, the database itself has presently 13,423 tables in it storing data, and data in one table is rarely repeated in another.

Just never had an instance like this where I had so many fields relating to the same thing that wasn't easily decernable into seperate tables.|||Well you should definately split things so that rack is a seperate table and location...

not sure what else without re-reading...|||No offense to bornweb,
But I'm betting no one will use the form!. 20K fields????
Heck even a hundred is unimaginable.......

Again..just voicing out.....don't bother me...|||:) You'd be surprised what people with no other job than mysteryshopping will do for money, especially when they're paid by hour (including the time it takes to fill out the online form).

And I should note that not all 20k fields are required. It is possible that their would only be a hundred or so. But there is the possibility that much much more data can be collected.|||They may use the form, but experience shows that the more difficult, arduous, and confusing the data entry process is, the lower the quality of data you receive.

A from with 20,000+ fields? A database with 13,000+ tables? I went to your website (it looks cool) and got the impression that this database will be accessed not only by your mystery shoppers, but also by your customers for both canned and ad-hoc reporting. Problems in your database design are definitely going to affect both the performance and functional limitations of this client-facing database.

If this database is as critical to your business as it appears, I strongly encourage you to seek the assistance of an experience database designer before interface development locks you into a schema.|||Guys/Gals,

It's NOT 20K Columns...it's ROWS...

Just curious, how long have you been a developer...(and I'm really trying hard not to sound like an ass...I know, I know, somethings are really hard for me...)

Been a LONG time from doing front end work, but really, You should have 1 row, ...they do entry, bam, commit it....

And back to the part about retrieval...how can the edit the data when the f-ck up?

And you know they're gonna...|||REally. It sounds like someone needs to learn how to design dynamic forms. Either what Bret suggested, or, something like...

Click Add button adds an editable row with Save and Cancel buttons.

After save, existing rows are displayed with Edit button on each for correction.

Again, click Add button for new row of data.

You could even have a grid display the existing data and a more intuative data entry form layout for new/edit data.

Oh, and the "just make an array" guy should be canned. Proper object oriented design should meld well with database design.|||Originally posted by bpdWork
Oh, and the "just make an array" guy should be canned. Proper object oriented design should meld well with database design.

Actually, don't the 2 NOT blend...I don't mind the array thing (although with 20k, you should worry about memory..)

You'd just loop throu the INSERTS...

PLEASE use sprocs though...

on second thought a singleton INSERT is an INSERT and probably would see an perf boost...

Where's the web site?

Can I get a deal on a new ski jacket?

How about a Margaritta set?

Or forget the set, how about a deal on a 1.75 of to-kill-ya