Gather Format and Store - Right or Wrong

The IT group that I work with has the habit of gathering data,
formatting (i.e. in reports) and then storing the same formated data in
the same database.
I think the practice is wrong. I think the activity is fundamentally
wrong because we are storing the exact same data in a database in two
different locations. Somehow I have the impression that database design
is about "oneness".
I believe that collecting the data and then storing summerized data for
reporting into a data warehouse would be the right solution.
I am getting flack for my viewpoint.
Am I all washed up?That sounds weird. IF the formatted data is stored for performance reasons,
I'd at least have it in
another database. But I prefer to do the report off of the production databa
se (if low activity and
doesn't have perf impact), or have a different database better suited for re
porting off of.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"rlm" <groups@.rlmoore.net> wrote in message
news:1146747407.524001.97330@.j33g2000cwa.googlegroups.com...
> The IT group that I work with has the habit of gathering data,
> formatting (i.e. in reports) and then storing the same formated data in
> the same database.
> I think the practice is wrong. I think the activity is fundamentally
> wrong because we are storing the exact same data in a database in two
> different locations. Somehow I have the impression that database design
> is about "oneness".
> I believe that collecting the data and then storing summerized data for
> reporting into a data warehouse would be the right solution.
> I am getting flack for my viewpoint.
> Am I all washed up?
>

Gather Format and Store - Right or Wrong

The IT group that I work with has the habit of gathering data,
formatting (i.e. in reports) and then storing the same formated data in
the same database.

I think the practice is wrong. I think the activity is fundamentally
wrong because we are storing the exact same data in a database in two
different locations. Somehow I have the impression that database design
is about "oneness".

I believe that collecting the data and then storing summerized data for
reporting into a data warehouse would be the right solution.

I am getting flack for my viewpoint.

Am I all washed up?That sounds weird. IF the formatted data is stored for performance reasons, I'd at least have it in
another database. But I prefer to do the report off of the production database (if low activity and
doesn't have perf impact), or have a different database better suited for reporting off of.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

"rlm" <groups@.rlmoore.net> wrote in message
news:1146747407.524001.97330@.j33g2000cwa.googlegro ups.com...
> The IT group that I work with has the habit of gathering data,
> formatting (i.e. in reports) and then storing the same formated data in
> the same database.
> I think the practice is wrong. I think the activity is fundamentally
> wrong because we are storing the exact same data in a database in two
> different locations. Somehow I have the impression that database design
> is about "oneness".
> I believe that collecting the data and then storing summerized data for
> reporting into a data warehouse would be the right solution.
> I am getting flack for my viewpoint.
> Am I all washed up?

Gather Format and Store - Right or Wrong

The IT group that I work with has the habit of gathering data,
formatting (i.e. in reports) and then storing the same formated data in
the same database.
I think the practice is wrong. I think the activity is fundamentally
wrong because we are storing the exact same data in a database in two
different locations. Somehow I have the impression that database design
is about "oneness".
I believe that collecting the data and then storing summerized data for
reporting into a data warehouse would be the right solution.
I am getting flack for my viewpoint.
Am I all washed up?That sounds weird. IF the formatted data is stored for performance reasons, I'd at least have it in
another database. But I prefer to do the report off of the production database (if low activity and
doesn't have perf impact), or have a different database better suited for reporting off of.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"rlm" <groups@.rlmoore.net> wrote in message
news:1146747407.524001.97330@.j33g2000cwa.googlegroups.com...
> The IT group that I work with has the habit of gathering data,
> formatting (i.e. in reports) and then storing the same formated data in
> the same database.
> I think the practice is wrong. I think the activity is fundamentally
> wrong because we are storing the exact same data in a database in two
> different locations. Somehow I have the impression that database design
> is about "oneness".
> I believe that collecting the data and then storing summerized data for
> reporting into a data warehouse would be the right solution.
> I am getting flack for my viewpoint.
> Am I all washed up?
>

Further Question on American Date Format

Thanks Scott and Aron for the info on American date formatting. However I'm
still slightly confused.
recap of problem:
One of my table's in SQL Server contains a field of a field type 'datetime'
and recording of dates are stored in American format and not Irish/Uk
format. As sugessted by previous posting that the client application control
this, I used Enterprise Manager to insert a record into the table and the
date format is still storing as American format. i.e." 8/16/2004 4:34:52PM"
not as "16/08/2004 4:34:52PM"
This is causing me major proiblems as I'm trying to run simple reporting
form a web base client base on dates. The only way the reporting will work if
I change the date format on my machine to american format.
Any ideas ?
Thanks,
Liam
Hi
SQL does not store the date in an human readable format and does not persist
the formatting you used to enter it. It is up to you, when you select the
data, to format it to what you want.
Look at CAST or CONVERT in BOL for formatting information.
Regards
Mike
"Liam Mac" wrote:

> Thanks Scott and Aron for the info on American date formatting. However I'm
> still slightly confused.
> recap of problem:
> One of my table's in SQL Server contains a field of a field type 'datetime'
> and recording of dates are stored in American format and not Irish/Uk
> format. As sugessted by previous posting that the client application control
> this, I used Enterprise Manager to insert a record into the table and the
> date format is still storing as American format. i.e." 8/16/2004 4:34:52PM"
> not as "16/08/2004 4:34:52PM"
> This is causing me major proiblems as I'm trying to run simple reporting
> form a web base client base on dates. The only way the reporting will work if
> I change the date format on my machine to american format.
> Any ideas ?
> Thanks,
> Liam
sql

Further Question on American Date Format

Thanks Scott and Aron for the info on American date formatting. However I'm
still slightly confused.
recap of problem:
One of my table's in SQL Server contains a field of a field type 'datetime'
and recording of dates are stored in American format and not Irish/Uk
format. As sugessted by previous posting that the client application control
this, I used Enterprise Manager to insert a record into the table and the
date format is still storing as American format. i.e." 8/16/2004 4:34:52PM"
not as "16/08/2004 4:34:52PM"
This is causing me major proiblems as I'm trying to run simple reporting
form a web base client base on dates. The only way the reporting will work if
I change the date format on my machine to american format.
Any ideas '
Thanks,
LiamHi
SQL does not store the date in an human readable format and does not persist
the formatting you used to enter it. It is up to you, when you select the
data, to format it to what you want.
Look at CAST or CONVERT in BOL for formatting information.
Regards
Mike
"Liam Mac" wrote:
> Thanks Scott and Aron for the info on American date formatting. However I'm
> still slightly confused.
> recap of problem:
> One of my table's in SQL Server contains a field of a field type 'datetime'
> and recording of dates are stored in American format and not Irish/Uk
> format. As sugessted by previous posting that the client application control
> this, I used Enterprise Manager to insert a record into the table and the
> date format is still storing as American format. i.e." 8/16/2004 4:34:52PM"
> not as "16/08/2004 4:34:52PM"
> This is causing me major proiblems as I'm trying to run simple reporting
> form a web base client base on dates. The only way the reporting will work if
> I change the date format on my machine to american format.
> Any ideas '
> Thanks,
> Liam

Funky formatting of percentages in Excel export

I've created a report that has cells in it that I want to be formatted as percentages. I set the format code in the cell properties to "P1" and everything appears to work fine until I export to Excel. Some cells show up with only one digit after the decimal and some show up with two digits after the decimal.

I looked at the formatting in Excel (right click, Format Cells...) and it is set to "[$-1010409]#,##0.0#%". I don't have the first clue what that is "supposed" to do but what it does is this:

If I enter 100.00 in the cell it shows up as 100.0%. Expected

If I enter 100.10 in the cell it shows up as 100.1%. Expected

If I enter 100.15 in the cell it is displayed as 100.15%. Not expected

I have formatted the report to only show one digit after the decimal, but RS exports to Excel with some funky formatting that sometimes shows one digit and sometimes two digits after the decimal. Why wouldn't it simply format the cell as a percent? How can I get it to only show a single digit after the decimal?

This is Excel 2007 but Excel 2003 has the same behavior.

Thanks

--John

John T. Williams wrote:

"[$-1010409]#,##0.0%"

Try that in Excel.

|||

I'm not sure what that reply is supposed to be suggesting. The format code [$-1010409]#,##0.0% already exists in an Excel spreadsheet that was created when exporting a report.

The problem is that the format code in Excel (created by Reporting Services) is causing inconsistent formatting of percentages.

|||

You said that excel formated the cell to this:

[$-1010409]#,##0.0#%

I'm saying to try this:

[$-1010409]#,##0.0%

I'm thinking the last # tells it to display the second decimal place if the number has one.

|||

Sorry, I didn't see that anything was different in the code you included. Yes, that does work. However, the problem is that I don't know of a way to control what Reporting Services tells Excel to use as a formatting code. So, the question remains, why does Reporting Services create an Excel formatting code that causes inconsistent behavior? Is there a workaround for this? If this is a bug, how can I report it so that it gets looked at?

This report runs and exports using a subscription and is emailed out automatically to a group of people. The whole point of automating this was that nobody would have to go in and change anything. This certainly isn't a critical issue, it just looks unprofessional.

Funky formatting of percentages in Excel export

I've created a report that has cells in it that I want to be formatted as percentages. I set the format code in the cell properties to "P1" and everything appears to work fine until I export to Excel. Some cells show up with only one digit after the decimal and some show up with two digits after the decimal.

I looked at the formatting in Excel (right click, Format Cells...) and it is set to "[$-1010409]#,##0.0#%". I don't have the first clue what that is "supposed" to do but what it does is this:

If I enter 100.00 in the cell it shows up as 100.0%. Expected

If I enter 100.10 in the cell it shows up as 100.1%. Expected

If I enter 100.15 in the cell it is displayed as 100.15%. Not expected

I have formatted the report to only show one digit after the decimal, but RS exports to Excel with some funky formatting that sometimes shows one digit and sometimes two digits after the decimal. Why wouldn't it simply format the cell as a percent? How can I get it to only show a single digit after the decimal?

This is Excel 2007 but Excel 2003 has the same behavior.

Thanks

--John

John T. Williams wrote:

"[$-1010409]#,##0.0%"

Try that in Excel.

|||

I'm not sure what that reply is supposed to be suggesting. The format code [$-1010409]#,##0.0% already exists in an Excel spreadsheet that was created when exporting a report.

The problem is that the format code in Excel (created by Reporting Services) is causing inconsistent formatting of percentages.

|||

You said that excel formated the cell to this:

[$-1010409]#,##0.0#%

I'm saying to try this:

[$-1010409]#,##0.0%

I'm thinking the last # tells it to display the second decimal place if the number has one.

|||

Sorry, I didn't see that anything was different in the code you included. Yes, that does work. However, the problem is that I don't know of a way to control what Reporting Services tells Excel to use as a formatting code. So, the question remains, why does Reporting Services create an Excel formatting code that causes inconsistent behavior? Is there a workaround for this? If this is a bug, how can I report it so that it gets looked at?

This report runs and exports using a subscription and is emailed out automatically to a group of people. The whole point of automating this was that nobody would have to go in and change anything. This certainly isn't a critical issue, it just looks unprofessional.

function to format Time (24 hours- 5 digits)

Hi

I know this is not a complicated matter.

I'm using this code in my SP to return the time formated in 24 hours

DECLARE @.Hora VARCHAR(5)
SET @.Hora = CONVERT(char(2), DatePart (hh,GetDate())) + ':' + CONVERT(char(2), DatePart (mi,GetDate()))
print @.Hora

te problem is that when the time is any before 10 am for instance 7:23 am the sp returns 7 :23 and I need it ro return 07:23.

What function allows me to insert that '0' to complete the 5 digits format in this case?

thanks

Try this instead:

SELECT My24HrTime = convert( char(5), getdate(), 114 )

For future reference, you may wish to refer to the 'style' chart in Books Online, Topic: 'Cast and Convert'.

(The number 114, above is the 'style' used in the convert() function.)

Function or SP for date conversion

i have a field that date stored in it, format of this date is somethin like timestamp. for example today(9Nov2005) saved as 38665.
how can i convert this value to a normal date format?
any function or sp?i have a field that date stored in it, format of this date is somethin like timestamp. for example today(9Nov2005) saved as 38665.
how can i convert this value to a normal date format?
any function or sp?

SELECT CAST ( 38665 as datetime)

Function call to Progress linked server

I have a Progress DB set up as a linked server.

To get the data through to SQL Server 2005 in a useable format i need to use the progress PRO_ELEMENT function call. How do I delimit this so it gets passed to the progress DB.

I've tried

SELECT

{fn PRO_ELEMENT(fldarr1,1,1)} as fld1

from ls1..pub.tab

This just returns an unknown function message which I believe is on the SQL Server end of the call.

This statement works fine through Business Objects.

Any help greatfully received.

try using OPENQUERY function.

select * from openquery (lnkd_server, 'SELECT {fn PRO_ELEMENT(fldarr1,1,1)} as fld1 from ls1..pub.tab')