As part of destroying a legacy system, we imported some data into our
SQL 2005 Archive. Now, some of the numeric data is stored as
nvarchar. Previously if I was running a report against Oracle or DB2 I
would put the following script in the cell to format it properly:
=format((Fields!data1.Value + 0) ,"N2")
That would go in the actual cell not in the format expression. Now I
have a major problem, this new report has like 50 cells and I cannot
go into each one and write that formula out! I've tried to put this
[=format((me.value + 0),"N2")] formula into the format property so
that I can just do it for all the cells but it doesn't work.
Any suggestions? I need to do a major make over to this report.
Thanks!On Dec 7, 9:35 am, SQL Guy <ayma...@.gmail.com> wrote:
> As part of destroying a legacy system, we imported some data into our
> SQL 2005 Archive. Now, some of the numeric data is stored as
> nvarchar. Previously if I was running a report against Oracle or DB2 I
> would put the following script in the cell to format it properly:
> =format((Fields!data1.Value + 0) ,"N2")
> That would go in the actual cell not in the format expression. Now I
> have a major problem, this new report has like 50 cells and I cannot
> go into each one and write that formula out! I've tried to put this
> [=format((me.value + 0),"N2")] formula into the format property so
> that I can just do it for all the cells but it doesn't work.
> Any suggestions? I need to do a major make over to this report.
> Thanks!
1. If you are converting to numerics, you should probably use the
CInt or CDbl VB commands instead of adding a zero. That's just me.
1a. If the data is really supposed to be a numeric, I would suggest
using CAST( fieldname AS FLOAT ) in the SQL itself, so the data is
returned to SSRS as a numeric and not a string.
2. In the Textbox Properties, go to the Format property and put in
N2. It will then format whatever numeric is in the Expression of the
Box. So, you can drag =Fields!data1.Value into the text box, set the
Format property to N2, and you wont need to explicitly change the
Expression.
3. I think you can select many TextBoxes by holding down the control
key and clicking them, then apply Format = N2 to all of them by
typeing N2 (no quotes) into the Format property.
-- Scott|||Thanks for your reply. I cannot do a cast or convert on 50 different
columns (maybe they're 45!) but there is a lot! If I put N2 in the
format property nothing will change since it's still a "string"
technically. This is why each one needs to be explicitly changed.
Since each cell is pulling from a different column, I would need to
format the values in each cell, but I wanted to use the format
property to just do a select all like you mentioned and change them
all at once.|||On Dec 7, 10:55 am, SQL Guy <ayma...@.gmail.com> wrote:
> Thanks for your reply. I cannot do a cast or convert on 50 different
> columns (maybe they're 45!) but there is a lot! If I put N2 in the
> format property nothing will change since it's still a "string"
> technically. This is why each one needs to be explicitly changed.
> Since each cell is pulling from a different column, I would need to
> format the values in each cell, but I wanted to use the format
> property to just do a select all like you mentioned and change them
> all at once.
Yuck. Shame you can't fix the database table, since that's where the
true problem lies. Then again, in the time that it takes to craft an
elegant solution, you probably could have just manually gone one by
one and fixed the SQL.
Step 1, make a copy of your report file.
Step 2, In the solution explorer, right click on the Report.rdl file,
and go to Open Code. Here I would suggest doing a Find-and-replace
of:
1. Replace all 'Fields!' with 'format((Fields!'
2. Replace all '.Value' with '.Value + 0) ,"N2") '
Step 3, Save and reopen the Report. Hopefully, it will now show all
of your fields wrapped with Format commands
-- Scott|||On Dec 7, 12:04 pm, Orne <polysilly...@.yahoo.com> wrote:
> On Dec 7, 10:55 am, SQL Guy <ayma...@.gmail.com> wrote:
> > Thanks for your reply. I cannot do a cast or convert on 50 different
> > columns (maybe they're 45!) but there is a lot! If I put N2 in the
> > format property nothing will change since it's still a "string"
> > technically. This is why each one needs to be explicitly changed.
> > Since each cell is pulling from a different column, I would need to
> > format the values in each cell, but I wanted to use the format
> > property to just do a select all like you mentioned and change them
> > all at once.
> Yuck. Shame you can't fix the database table, since that's where the
> true problem lies. Then again, in the time that it takes to craft an
> elegant solution, you probably could have just manually gone one by
> one and fixed the SQL.
> Step 1, make a copy of your report file.
> Step 2, In the solution explorer, right click on the Report.rdl file,
> and go to Open Code. Here I would suggest doing a Find-and-replace
> of:
> 1. Replace all 'Fields!' with 'format((Fields!'
> 2. Replace all '.Value' with '.Value + 0) ,"N2") '
> Step 3, Save and reopen the Report. Hopefully, it will now show all
> of your fields wrapped with Format commands
> -- Scott
I'm gonna try that, thanks. Unfortunately more work got piled on the
top so hopefully I can try it sometime this week.
