Hi,
I have a client who needs to integrate 'forms' that are requiered by
goverment and wants to populate them with the information that he has in the
database (MS SQL 2000), then save them for later use.
Does anyone knows of a good affordable solution for doing this ? Does
Infopath supports this ? Any other than PDF (which is not affordable).
AleksIf PDF is out of your price range, then you are not likely to find other
formats that are in your range. You should reconsider going PDF ... there ar
e
a handful of 3rd party tools that can read and write to them...
-- Alex Papadimoulis
"Aleks" wrote:
> Hi,
> I have a client who needs to integrate 'forms' that are requiered by
> goverment and wants to populate them with the information that he has in t
he
> database (MS SQL 2000), then save them for later use.
> Does anyone knows of a good affordable solution for doing this ? Does
> Infopath supports this ? Any other than PDF (which is not affordable).
> Aleks
>
>|||Have you take a look at SQL Reporting Services.
http://www.microsoft.com/sql/reporting/default.asp
-oj
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:%23ToPp7WBFHA.2640@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have a client who needs to integrate 'forms' that are requiered by
> goverment and wants to populate them with the information that he has in
> the database (MS SQL 2000), then save them for later use.
> Does anyone knows of a good affordable solution for doing this ? Does
> Infopath supports this ? Any other than PDF (which is not affordable).
> Aleks
>|||Like which one ?
A
"Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
news:45588097-6208-4BFB-A9FE-EFB36271EFF8@.microsoft.com...
> If PDF is out of your price range, then you are not likely to find other
> formats that are in your range. You should reconsider going PDF ... there
> are
> a handful of 3rd party tools that can read and write to them...
> -- Alex Papadimoulis
> "Aleks" wrote:
>|||Not only reports, they need to be able to type in the forms and save the
data that was typed.
A
"oj" <nospam_ojngo@.home.com> wrote in message
news:ePJa6MXBFHA.1188@.tk2msftngp13.phx.gbl...
> Have you take a look at SQL Reporting Services.
> http://www.microsoft.com/sql/reporting/default.asp
> --
> -oj
>
> "Aleks" <arkark2004@.hotmail.com> wrote in message
> news:%23ToPp7WBFHA.2640@.TK2MSFTNGP14.phx.gbl...
>
Showing posts with label client. Show all posts
Showing posts with label client. Show all posts
Monday, March 26, 2012
Wednesday, March 21, 2012
Formatting drill down report with varying depths of hierarchy
Good Morning, all,
I have been tasked to design reports for our budget data.
The client wants to be able to drill down through our company
hierarchy.
Some departments are five levels deep in the hierarchy, but others are
only three levels deep.
Right now a very simplified version of my dataset looks like
LEVEL0 LEVEL1 LEVEL2 LEVEL4 BUDGET
900
500000
900
600 200000
900
400 100000
900 400
250 50000
900 400
350 50000
900 400 350
612 40000
900 400 350
611 10000
So, what i want is if i drill down to 600, I shouldn't be able to
drill down any further, because there is no lower level. But if I
drill down to 400, I should be able drill down further. Right now, I
can keep drilling down two more levels below 600 until I get to the
detail level.
Can I control this with layout properties?
any ideas, suggestions, resources, war stories, or good clean jokes
would be appreciated.
KathrynFor archival purposes, I solved this by using Analysis Services to
create a cube out of my relational data. The recursive relationship
above became a dimension named Par. I created a report in Reporting
Services using the cube as a datasource. When defining the Group for
Par, you have to use two properties, UniqueName and ParentUniqueName.
These properties are created automatically when you create your MDX
query using Query Builder.. In my case, when I edited the Par Group,
I set the Group On expression to Fields!Par.UniqueName and the Parent
group to Fields!Par.ParentUniqueName. Set Visibility to Hidden and
have the Toggle Item be the textbox itself, in my case Par (which
seems recursive, but that's what you want.)
Good luck,
Kathrynsql
I have been tasked to design reports for our budget data.
The client wants to be able to drill down through our company
hierarchy.
Some departments are five levels deep in the hierarchy, but others are
only three levels deep.
Right now a very simplified version of my dataset looks like
LEVEL0 LEVEL1 LEVEL2 LEVEL4 BUDGET
900
500000
900
600 200000
900
400 100000
900 400
250 50000
900 400
350 50000
900 400 350
612 40000
900 400 350
611 10000
So, what i want is if i drill down to 600, I shouldn't be able to
drill down any further, because there is no lower level. But if I
drill down to 400, I should be able drill down further. Right now, I
can keep drilling down two more levels below 600 until I get to the
detail level.
Can I control this with layout properties?
any ideas, suggestions, resources, war stories, or good clean jokes
would be appreciated.
KathrynFor archival purposes, I solved this by using Analysis Services to
create a cube out of my relational data. The recursive relationship
above became a dimension named Par. I created a report in Reporting
Services using the cube as a datasource. When defining the Group for
Par, you have to use two properties, UniqueName and ParentUniqueName.
These properties are created automatically when you create your MDX
query using Query Builder.. In my case, when I edited the Par Group,
I set the Group On expression to Fields!Par.UniqueName and the Parent
group to Fields!Par.ParentUniqueName. Set Visibility to Hidden and
have the Toggle Item be the textbox itself, in my case Par (which
seems recursive, but that's what you want.)
Good luck,
Kathrynsql
Formatting dates using regional settings
When presenting a date in a report, I want Reporting Services to use the
regional settings on the client to determine this. Is this possible. The
following post from Charles Kangai on 8/12/2004 said that this was possible.
However when I follow his instructions, Reporting Services still presents
the date in the US format (presumably using the "Language" property of the
object, set to "default" in my report) whereas my regional settings are set
to UK date format (both short and long date).Thanks for that.
The report level language property was set to US which was why it wasn't
working.
"Charles Kangai" <CharlesKangai@.discussions.microsoft.com> wrote in message
news:9609E7E7-5091-4631-BE18-CE0C4B9B2F50@.microsoft.com...
> Hi,
> Make sure the Report's Language property and the textbox's Language
property
> are both set to Default. Then set the Format property of your textbox to
"d"
> (Short Date) or "D" (Long Date), without the double quotes. It should
display
> according to the setting in your Regional Settings. I have just tried it
> again after reading your post, and it works.
> Charles Kangai, MCDBA, MCT
> "GML" wrote:
> > When presenting a date in a report, I want Reporting Services to use the
> > regional settings on the client to determine this. Is this possible.
The
> > following post from Charles Kangai on 8/12/2004 said that this was
possible.
> > However when I follow his instructions, Reporting Services still
presents
> > the date in the US format (presumably using the "Language" property of
the
> > object, set to "default" in my report) whereas my regional settings are
set
> > to UK date format (both short and long date).
> >
> >
> >
regional settings on the client to determine this. Is this possible. The
following post from Charles Kangai on 8/12/2004 said that this was possible.
However when I follow his instructions, Reporting Services still presents
the date in the US format (presumably using the "Language" property of the
object, set to "default" in my report) whereas my regional settings are set
to UK date format (both short and long date).Thanks for that.
The report level language property was set to US which was why it wasn't
working.
"Charles Kangai" <CharlesKangai@.discussions.microsoft.com> wrote in message
news:9609E7E7-5091-4631-BE18-CE0C4B9B2F50@.microsoft.com...
> Hi,
> Make sure the Report's Language property and the textbox's Language
property
> are both set to Default. Then set the Format property of your textbox to
"d"
> (Short Date) or "D" (Long Date), without the double quotes. It should
display
> according to the setting in your Regional Settings. I have just tried it
> again after reading your post, and it works.
> Charles Kangai, MCDBA, MCT
> "GML" wrote:
> > When presenting a date in a report, I want Reporting Services to use the
> > regional settings on the client to determine this. Is this possible.
The
> > following post from Charles Kangai on 8/12/2004 said that this was
possible.
> > However when I follow his instructions, Reporting Services still
presents
> > the date in the US format (presumably using the "Language" property of
the
> > object, set to "default" in my report) whereas my regional settings are
set
> > to UK date format (both short and long date).
> >
> >
> >
Monday, March 12, 2012
Formatting @query results using xp_sendmail
I know formatting should be handled in the client app and not in SQL Server
but this is the situation I must develop in so any help would be
appreciated.
but this is the situation I must develop in so any help would be
appreciated.
I'm running the following in Query Analyzer
USE Pubs
DECLARE @.MessageSubject VARCHAR(50)
SELECT @.MessageSubject = 'Report'
EXEC master.dbo.xp_sendmail 'me@.mine.com',
@.query = 'SELECT au_fname, au_lname from pubs.dbo.authors',
@.subject = @.MessageSubject
The results in my email look like:
au_fname au_lname
------ ------------
Abraham Bennet
Reginald Blotchet-Halls
Cheryl Carson
I would like the results to be like:
First Name: Abraham
Last Name: Bennet
First Name: Reginald
Last Name: Blotchet-Halls
First Name: Cheryl
Last Name: Carson
Thanks"Terri" <Terri@.spamaway.com> wrote in message
news:c0u48d$tn2$1@.reader2.nmix.net...
> I know formatting should be handled in the client app and not in SQL
Server
> but this is the situation I must develop in so any help would be
> appreciated.
> I'm running the following in Query Analyzer
> USE Pubs
> DECLARE @.MessageSubject VARCHAR(50)
> SELECT @.MessageSubject = 'Report'
> EXEC master.dbo.xp_sendmail 'me@.mine.com',
> @.query = 'SELECT au_fname, au_lname from pubs.dbo.authors',
> @.subject = @.MessageSubject
> The results in my email look like:
> au_fname au_lname
> ------ ------------
> Abraham Bennet
> Reginald Blotchet-Halls
> Cheryl Carson
> I would like the results to be like:
> First Name: Abraham
> Last Name: Bennet
> First Name: Reginald
> Last Name: Blotchet-Halls
> First Name: Cheryl
> Last Name: Carson
> Thanks
See CHAR() in Books Online - you could try something like this:
select
'First Name: ' + au_fname + char(13) + 'Last Name: ' + au_lname +
char(13) + char(13)
from
pubs.dbo.authors
Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:403292f4$1_2@.news.bluewin.ch...
> See CHAR() in Books Online - you could try something like this:
> select
> 'First Name: ' + au_fname + char(13) + 'Last Name: ' + au_lname +
> char(13) + char(13)
> from
> pubs.dbo.authors
This works, thanks.
@.query = 'select ''First Name: '' + au_fname + char(13) + ''LastName: '' +
au_lname + char(13) from pubs.dbo.authors',
Formatted text in a column;
Hi All ,
Is it possible to insert the below text into a column and retrieve it in the same format ?
Thanks,
Hari Haran Arulmozhi
TEXT :
CLIENT NAME : ABC Corporation
CLIENT CITY : MUMBAI
================================================
INV_NO INV_DATE INV_AMT
================================================
I100 01-01-2006 Rs.600
I200 01-02-2006 Rs.800
I300 01-03-2006 Rs.1600
I400 01-04-2006 Rs.2600
I500 01-05-2006 Rs.9600
RECEIVED ADVANCE :Rs.10000u will need to take care of the formatting in the front end.|||Well, strictly speaking you CAN do the formatting on the back end. What harshall means is that you SHOULD do the formatting on the front end.
Formatting is a presentation issue and does not fall within the scope of a database server.
Read up on the datetime datatype and how it is stored in Books Online.|||Thanks Harshal & Blindman !!
Is it possible to insert the below text into a column and retrieve it in the same format ?
Thanks,
Hari Haran Arulmozhi
TEXT :
CLIENT NAME : ABC Corporation
CLIENT CITY : MUMBAI
================================================
INV_NO INV_DATE INV_AMT
================================================
I100 01-01-2006 Rs.600
I200 01-02-2006 Rs.800
I300 01-03-2006 Rs.1600
I400 01-04-2006 Rs.2600
I500 01-05-2006 Rs.9600
RECEIVED ADVANCE :Rs.10000u will need to take care of the formatting in the front end.|||Well, strictly speaking you CAN do the formatting on the back end. What harshall means is that you SHOULD do the formatting on the front end.
Formatting is a presentation issue and does not fall within the scope of a database server.
Read up on the datetime datatype and how it is stored in Books Online.|||Thanks Harshal & Blindman !!
Friday, February 24, 2012
Format Expression
My client's want certain areas of my expression to be bold and also a
different color. Via HTML or CSS this would be the solution:
="<b><font color=blue>Resource(s) Name:</font></b> " &
Fields!ResourceName.Value & vbcrlf &
"<b><font color=blue>Role:</font></b> " & Fields!Role.Value & vbcrlf &
vbcrlf & "<b><font color=blue>Other Resource(s) Name:</font></b> " &
Fields!OtherResourceName.Value & vbcrlf & "<b><font color=blue>Other Resource
Role:</font></b> " & Fields!OtherResourceRole.Value
However, this does not work with SSRS. Is this something that will be
allowed in SSRS 2008 or is it possible with 2005?
--
<moojjoo/>RS 2008 is going support rich format. I have not played with the CTPs
(general pre-release software) so I am not totally firm in my head on how
this support will show itself.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Moojjoo" <Moojjoo@.discussions.microsoft.com> wrote in message
news:522D7736-6B3C-48BB-8DC7-D9147A802978@.microsoft.com...
> My client's want certain areas of my expression to be bold and also a
> different color. Via HTML or CSS this would be the solution:
> ="<b><font color=blue>Resource(s) Name:</font></b> " &
> Fields!ResourceName.Value & vbcrlf &
> "<b><font color=blue>Role:</font></b> " & Fields!Role.Value & vbcrlf &
> vbcrlf & "<b><font color=blue>Other Resource(s) Name:</font></b> " &
> Fields!OtherResourceName.Value & vbcrlf & "<b><font color=blue>Other
> Resource
> Role:</font></b> " & Fields!OtherResourceRole.Value
> However, this does not work with SSRS. Is this something that will be
> allowed in SSRS 2008 or is it possible with 2005?
> --
> <moojjoo/>|||So basically I am up the river without a paddle on what my client wants at
this time with formating the font?
--
<moojjoo/>
"Bruce L-C [MVP]" wrote:
> RS 2008 is going support rich format. I have not played with the CTPs
> (general pre-release software) so I am not totally firm in my head on how
> this support will show itself.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Moojjoo" <Moojjoo@.discussions.microsoft.com> wrote in message
> news:522D7736-6B3C-48BB-8DC7-D9147A802978@.microsoft.com...
> > My client's want certain areas of my expression to be bold and also a
> > different color. Via HTML or CSS this would be the solution:
> >
> > ="<b><font color=blue>Resource(s) Name:</font></b> " &
> > Fields!ResourceName.Value & vbcrlf &
> > "<b><font color=blue>Role:</font></b> " & Fields!Role.Value & vbcrlf &
> > vbcrlf & "<b><font color=blue>Other Resource(s) Name:</font></b> " &
> > Fields!OtherResourceName.Value & vbcrlf & "<b><font color=blue>Other
> > Resource
> > Role:</font></b> " & Fields!OtherResourceRole.Value
> >
> > However, this does not work with SSRS. Is this something that will be
> > allowed in SSRS 2008 or is it possible with 2005?
> > --
> > <moojjoo/>
>
>|||You can do things like put multiple text boxes next to each other and format
each of those (which really only works for very simple scenarios).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Moojjoo" <Moojjoo@.discussions.microsoft.com> wrote in message
news:17509CDD-B058-44DF-8DD9-004D6B027AF4@.microsoft.com...
> So basically I am up the river without a paddle on what my client wants at
> this time with formating the font?
> --
> <moojjoo/>
>
> "Bruce L-C [MVP]" wrote:
>> RS 2008 is going support rich format. I have not played with the CTPs
>> (general pre-release software) so I am not totally firm in my head on how
>> this support will show itself.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Moojjoo" <Moojjoo@.discussions.microsoft.com> wrote in message
>> news:522D7736-6B3C-48BB-8DC7-D9147A802978@.microsoft.com...
>> > My client's want certain areas of my expression to be bold and also a
>> > different color. Via HTML or CSS this would be the solution:
>> >
>> > ="<b><font color=blue>Resource(s) Name:</font></b> " &
>> > Fields!ResourceName.Value & vbcrlf &
>> > "<b><font color=blue>Role:</font></b> " & Fields!Role.Value & vbcrlf &
>> > vbcrlf & "<b><font color=blue>Other Resource(s) Name:</font></b> " &
>> > Fields!OtherResourceName.Value & vbcrlf & "<b><font color=blue>Other
>> > Resource
>> > Role:</font></b> " & Fields!OtherResourceRole.Value
>> >
>> > However, this does not work with SSRS. Is this something that will be
>> > allowed in SSRS 2008 or is it possible with 2005?
>> > --
>> > <moojjoo/>
>>|||Bruce, I totally can understand that the only problem is I have this in a
table... and they are wanting multiple items in a cell along with that
formatting I am referrring to.
--
<moojjoo/>
"Moojjoo" wrote:
> So basically I am up the river without a paddle on what my client wants at
> this time with formating the font?
> --
> <moojjoo/>
>
> "Bruce L-C [MVP]" wrote:
> > RS 2008 is going support rich format. I have not played with the CTPs
> > (general pre-release software) so I am not totally firm in my head on how
> > this support will show itself.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Moojjoo" <Moojjoo@.discussions.microsoft.com> wrote in message
> > news:522D7736-6B3C-48BB-8DC7-D9147A802978@.microsoft.com...
> > > My client's want certain areas of my expression to be bold and also a
> > > different color. Via HTML or CSS this would be the solution:
> > >
> > > ="<b><font color=blue>Resource(s) Name:</font></b> " &
> > > Fields!ResourceName.Value & vbcrlf &
> > > "<b><font color=blue>Role:</font></b> " & Fields!Role.Value & vbcrlf &
> > > vbcrlf & "<b><font color=blue>Other Resource(s) Name:</font></b> " &
> > > Fields!OtherResourceName.Value & vbcrlf & "<b><font color=blue>Other
> > > Resource
> > > Role:</font></b> " & Fields!OtherResourceRole.Value
> > >
> > > However, this does not work with SSRS. Is this something that will be
> > > allowed in SSRS 2008 or is it possible with 2005?
> > > --
> > > <moojjoo/>
> >
> >
> >|||You are out of luck. This is very common requirement which is why it is
being implemented in RS 2008.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Moojjoo" <Moojjoo@.discussions.microsoft.com> wrote in message
news:C4A3785A-A03A-4855-B524-F6E9A65D39E4@.microsoft.com...
> Bruce, I totally can understand that the only problem is I have this in a
> table... and they are wanting multiple items in a cell along with that
> formatting I am referrring to.
> --
> <moojjoo/>
>
> "Moojjoo" wrote:
>> So basically I am up the river without a paddle on what my client wants
>> at
>> this time with formating the font?
>> --
>> <moojjoo/>
>>
>> "Bruce L-C [MVP]" wrote:
>> > RS 2008 is going support rich format. I have not played with the CTPs
>> > (general pre-release software) so I am not totally firm in my head on
>> > how
>> > this support will show itself.
>> >
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> >
>> > "Moojjoo" <Moojjoo@.discussions.microsoft.com> wrote in message
>> > news:522D7736-6B3C-48BB-8DC7-D9147A802978@.microsoft.com...
>> > > My client's want certain areas of my expression to be bold and also a
>> > > different color. Via HTML or CSS this would be the solution:
>> > >
>> > > ="<b><font color=blue>Resource(s) Name:</font></b> " &
>> > > Fields!ResourceName.Value & vbcrlf &
>> > > "<b><font color=blue>Role:</font></b> " & Fields!Role.Value & vbcrlf
>> > > &
>> > > vbcrlf & "<b><font color=blue>Other Resource(s) Name:</font></b> " &
>> > > Fields!OtherResourceName.Value & vbcrlf & "<b><font color=blue>Other
>> > > Resource
>> > > Role:</font></b> " & Fields!OtherResourceRole.Value
>> > >
>> > > However, this does not work with SSRS. Is this something that will
>> > > be
>> > > allowed in SSRS 2008 or is it possible with 2005?
>> > > --
>> > > <moojjoo/>
>> >
>> >
>> >
different color. Via HTML or CSS this would be the solution:
="<b><font color=blue>Resource(s) Name:</font></b> " &
Fields!ResourceName.Value & vbcrlf &
"<b><font color=blue>Role:</font></b> " & Fields!Role.Value & vbcrlf &
vbcrlf & "<b><font color=blue>Other Resource(s) Name:</font></b> " &
Fields!OtherResourceName.Value & vbcrlf & "<b><font color=blue>Other Resource
Role:</font></b> " & Fields!OtherResourceRole.Value
However, this does not work with SSRS. Is this something that will be
allowed in SSRS 2008 or is it possible with 2005?
--
<moojjoo/>RS 2008 is going support rich format. I have not played with the CTPs
(general pre-release software) so I am not totally firm in my head on how
this support will show itself.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Moojjoo" <Moojjoo@.discussions.microsoft.com> wrote in message
news:522D7736-6B3C-48BB-8DC7-D9147A802978@.microsoft.com...
> My client's want certain areas of my expression to be bold and also a
> different color. Via HTML or CSS this would be the solution:
> ="<b><font color=blue>Resource(s) Name:</font></b> " &
> Fields!ResourceName.Value & vbcrlf &
> "<b><font color=blue>Role:</font></b> " & Fields!Role.Value & vbcrlf &
> vbcrlf & "<b><font color=blue>Other Resource(s) Name:</font></b> " &
> Fields!OtherResourceName.Value & vbcrlf & "<b><font color=blue>Other
> Resource
> Role:</font></b> " & Fields!OtherResourceRole.Value
> However, this does not work with SSRS. Is this something that will be
> allowed in SSRS 2008 or is it possible with 2005?
> --
> <moojjoo/>|||So basically I am up the river without a paddle on what my client wants at
this time with formating the font?
--
<moojjoo/>
"Bruce L-C [MVP]" wrote:
> RS 2008 is going support rich format. I have not played with the CTPs
> (general pre-release software) so I am not totally firm in my head on how
> this support will show itself.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Moojjoo" <Moojjoo@.discussions.microsoft.com> wrote in message
> news:522D7736-6B3C-48BB-8DC7-D9147A802978@.microsoft.com...
> > My client's want certain areas of my expression to be bold and also a
> > different color. Via HTML or CSS this would be the solution:
> >
> > ="<b><font color=blue>Resource(s) Name:</font></b> " &
> > Fields!ResourceName.Value & vbcrlf &
> > "<b><font color=blue>Role:</font></b> " & Fields!Role.Value & vbcrlf &
> > vbcrlf & "<b><font color=blue>Other Resource(s) Name:</font></b> " &
> > Fields!OtherResourceName.Value & vbcrlf & "<b><font color=blue>Other
> > Resource
> > Role:</font></b> " & Fields!OtherResourceRole.Value
> >
> > However, this does not work with SSRS. Is this something that will be
> > allowed in SSRS 2008 or is it possible with 2005?
> > --
> > <moojjoo/>
>
>|||You can do things like put multiple text boxes next to each other and format
each of those (which really only works for very simple scenarios).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Moojjoo" <Moojjoo@.discussions.microsoft.com> wrote in message
news:17509CDD-B058-44DF-8DD9-004D6B027AF4@.microsoft.com...
> So basically I am up the river without a paddle on what my client wants at
> this time with formating the font?
> --
> <moojjoo/>
>
> "Bruce L-C [MVP]" wrote:
>> RS 2008 is going support rich format. I have not played with the CTPs
>> (general pre-release software) so I am not totally firm in my head on how
>> this support will show itself.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Moojjoo" <Moojjoo@.discussions.microsoft.com> wrote in message
>> news:522D7736-6B3C-48BB-8DC7-D9147A802978@.microsoft.com...
>> > My client's want certain areas of my expression to be bold and also a
>> > different color. Via HTML or CSS this would be the solution:
>> >
>> > ="<b><font color=blue>Resource(s) Name:</font></b> " &
>> > Fields!ResourceName.Value & vbcrlf &
>> > "<b><font color=blue>Role:</font></b> " & Fields!Role.Value & vbcrlf &
>> > vbcrlf & "<b><font color=blue>Other Resource(s) Name:</font></b> " &
>> > Fields!OtherResourceName.Value & vbcrlf & "<b><font color=blue>Other
>> > Resource
>> > Role:</font></b> " & Fields!OtherResourceRole.Value
>> >
>> > However, this does not work with SSRS. Is this something that will be
>> > allowed in SSRS 2008 or is it possible with 2005?
>> > --
>> > <moojjoo/>
>>|||Bruce, I totally can understand that the only problem is I have this in a
table... and they are wanting multiple items in a cell along with that
formatting I am referrring to.
--
<moojjoo/>
"Moojjoo" wrote:
> So basically I am up the river without a paddle on what my client wants at
> this time with formating the font?
> --
> <moojjoo/>
>
> "Bruce L-C [MVP]" wrote:
> > RS 2008 is going support rich format. I have not played with the CTPs
> > (general pre-release software) so I am not totally firm in my head on how
> > this support will show itself.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Moojjoo" <Moojjoo@.discussions.microsoft.com> wrote in message
> > news:522D7736-6B3C-48BB-8DC7-D9147A802978@.microsoft.com...
> > > My client's want certain areas of my expression to be bold and also a
> > > different color. Via HTML or CSS this would be the solution:
> > >
> > > ="<b><font color=blue>Resource(s) Name:</font></b> " &
> > > Fields!ResourceName.Value & vbcrlf &
> > > "<b><font color=blue>Role:</font></b> " & Fields!Role.Value & vbcrlf &
> > > vbcrlf & "<b><font color=blue>Other Resource(s) Name:</font></b> " &
> > > Fields!OtherResourceName.Value & vbcrlf & "<b><font color=blue>Other
> > > Resource
> > > Role:</font></b> " & Fields!OtherResourceRole.Value
> > >
> > > However, this does not work with SSRS. Is this something that will be
> > > allowed in SSRS 2008 or is it possible with 2005?
> > > --
> > > <moojjoo/>
> >
> >
> >|||You are out of luck. This is very common requirement which is why it is
being implemented in RS 2008.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Moojjoo" <Moojjoo@.discussions.microsoft.com> wrote in message
news:C4A3785A-A03A-4855-B524-F6E9A65D39E4@.microsoft.com...
> Bruce, I totally can understand that the only problem is I have this in a
> table... and they are wanting multiple items in a cell along with that
> formatting I am referrring to.
> --
> <moojjoo/>
>
> "Moojjoo" wrote:
>> So basically I am up the river without a paddle on what my client wants
>> at
>> this time with formating the font?
>> --
>> <moojjoo/>
>>
>> "Bruce L-C [MVP]" wrote:
>> > RS 2008 is going support rich format. I have not played with the CTPs
>> > (general pre-release software) so I am not totally firm in my head on
>> > how
>> > this support will show itself.
>> >
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> >
>> > "Moojjoo" <Moojjoo@.discussions.microsoft.com> wrote in message
>> > news:522D7736-6B3C-48BB-8DC7-D9147A802978@.microsoft.com...
>> > > My client's want certain areas of my expression to be bold and also a
>> > > different color. Via HTML or CSS this would be the solution:
>> > >
>> > > ="<b><font color=blue>Resource(s) Name:</font></b> " &
>> > > Fields!ResourceName.Value & vbcrlf &
>> > > "<b><font color=blue>Role:</font></b> " & Fields!Role.Value & vbcrlf
>> > > &
>> > > vbcrlf & "<b><font color=blue>Other Resource(s) Name:</font></b> " &
>> > > Fields!OtherResourceName.Value & vbcrlf & "<b><font color=blue>Other
>> > > Resource
>> > > Role:</font></b> " & Fields!OtherResourceRole.Value
>> > >
>> > > However, this does not work with SSRS. Is this something that will
>> > > be
>> > > allowed in SSRS 2008 or is it possible with 2005?
>> > > --
>> > > <moojjoo/>
>> >
>> >
>> >
Sunday, February 19, 2012
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
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
Subscribe to:
Posts (Atom)