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?
>

Gap between header and Body

Hello every body,

I am facing a smal formatting issue. the issue i have 7 text boxes attached with each other in the header to show the week days ,i.e Sunday,Monday,Tuesday,Wednesday,Thursay,Friday and Saturday

In Body,i have a matrix which will extend to have 7 columns for the weekdays.

Every thing is fine,Except a small gap between header and footer where i want the 7 text boxes to act as column header names.

I tried with various options like removing the header and placing the text boxes in Body followed by the matrix. 2) placing the seven column headers in a dummy table and attaching that with matrix,

But i am unable to remove the gap between them. if any one of you had experienced the same problem or knows how to solve.it's really a great help

Thank you,

Will it be possible for you to use a table instead? Place a table in the body. Use the table heade for the column names and the table details for the values.

|||

NO Tech Quest, the matrix doesn't stop with one week.

For Each week one matrix is reported.Its just like a calendar

Thanks

Raj deep.A

|||

Raj, Is there a reason that you are not using the "Columns" within the matrix for the headers. I know the alignment/ formatting gets harder when you separate them and use the headers and footers for this.

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.