Need to add line feeds

I am working on an "invoice" print, and it has to be on a pre-printed form, I need to print the totals at the bottom of the page and need help figuring out how to add specific amount of line feeds. I tried printing chr(13) , chr(10), conbination of both, also tried Environment.NewLine, no luck.

Using SQLRS2000, developing report with VS2003.

Anyone's suggestions would be greatly appreciated!.

Also try using vbCrLf.|||

I tried it just now, no luck yet.

I am doing it on the group footer, simply entering :

= vBCrLf

I have never had this need, not sure if this is the proper syntax?

|||

Hello,

Are you trying to use it in an expression? Can you try to wrap the vbCrLf with some dummy text just so you can see that it's working. Like this:

="Some text on line 1." + vbCrLf + "Next, line number 2."

The result should be:

Some text on line 1.

Next, line number 2.

Jarret

|||

thanks everyone! it works with text around it.

thank you so much!

need some help, making many rows out of one, millions of times

so here's the deal:

i'm getting data in the form of an access db, which may be changed to a txt file due to size. each record has 2 columns at the end, the fields are EffFrom and EffTo, which are of type date and specify the date range for which the rest of the data in the record is valid. Here's the problem, i need to take those ranges and create a row for each day. i.e. if the range is 9/1/2003 to 9/15/2003 i would need 15 rows all with the same data except for a new date field which will replace efffrom and effto. seems like a cursor/loop issue to me, BUT there will eventually be millions of rows that need to be manipulated in this fashion. i started writing a stored procedure that will convert the data, do the necessary lookups [a few of the fields need to be resolved into numerical values before inserting them into the main table], but when i get to the point where i'm pulling the temp table into a cursor then going through row by row and making anywhere from 1 to 365 rows out of each row in the cursor, i'm shaking my head and feeling like there has to be a better way.

Ultimately, i'd like to do it through DTS, but i'm not very crafty with VBScript and opted to go the stored procedure/temp table route.

Here's what the data looks like

location_code1 varchar (will become int through lookup)
location_code2 varchar (will become int through lookup)
deptime varchar (string manipulation being done to add ':')
arrtime varchar (string manipulation being done to add ':')
carriercode varchar (will become int through lookup)
efffrom date
effto date -- described above

does anybody have some quick/dirty code or methods of creating multiple rows from one based on a date range [i know this goes against normalization, but the application requires the data to be this way and it cannot be rewritten]..or some DTS advice?

i'm stumped and in dire need of some inspiration. thanks in advance.See this thread for help on using a table of sequential values to "fill in" dates in a date range:

http://dbforums.com/showthread.php?threadid=914261

Once you create your sequential value table, use it in a query like this:

Select YourFields,
dateadd(dd, SequentialValue, EffFrom) as OnDate
from YourTable, SequentialValues
where SequentialValues.SequentialValue < DateDiff(dd, EffFrom, EffTo)

I didn't check this code for one-off errors or parameter order, but you should be able to get an idea of what you need to do.

Note: this method works well, but if you are going to use it against a table with millions of rows, don't include sequential values in your table greater than the largest datespan you expect, in order to keep the runtime down.

blindman|||awesome...the data is definitely lookin good as far as generating the multiple rows...now to incorporate this into a huge data load.

would you suggest a stored procedure with a variable of type table then a mass insert? or something through DTS? i'll prob try a few different methods and evaluate the speed, but any advice would be appreciated.

thanks!!!!|||I've never liked DTS, and use it mostly for simple data transfers.

A temporary table would probably process fastest, but a permanent table would only need to be created once. Either way probably won't make a large difference.

blindman

Need some help with a query

Hello everyone, I have a table that is setup to record a page of web
form elements. The form elements are dynamically created. Each page
contains x number of questions. (x depends on many different things
There are 4 pages. Since we never know how many form elements are on
the page, the DB was design as such
GroupID int
PageID int
QuestionID int
FormElementID int
FormElementValue varchar(500)
The problem is when trying to grab a combination of forms to display in
a report I have to write a query like this (let's assume I am trying to
grab all records that have $5/10 years) On page 1, question 4, the
form element 1 is the dollar amount and the form element 2 is the
number of years.
SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
FormElementID=1 AND FormElementValue=5 AND GroupID IN (SELECT GroupID
from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
FormElementValue=10)
This works fine. My problem is when I have to grab all other
combinations. So let's say my client wants $5/10 years, $10/15 years,
$15/20 years and all other combinations as four distinct numbers. How
can I accomplish this? Currently I am doing this (which I know is poor
as it just looks wrong and takes forever to run.
SELECT GroupID FROM thistable WHERE GroupID NOT IN (
SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
FormElementID=1 AND FormElementValue=5 AND GroupID IN (SELECT GroupID
from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
FormElementValue=10)
AND GroupID NOT IN (
SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
FormElementID=1 AND FormElementValue=10 AND GroupID IN (SELECT GroupID
from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
FormElementValue=15)
AND GroupID NOT IN (
SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
FormElementID=1 AND FormElementValue=15 AND GroupID IN (SELECT GroupID
from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
FormElementValue=20)
Let me know if you have any ideas, thanks for your help in advance
(please note that I wrote the script above based on my real script, I
can't display the exact real code, but above is very close.night_day (night_day_8@.yahoo.com) writes:
> This works fine. My problem is when I have to grab all other
> combinations. So let's say my client wants $5/10 years, $10/15 years,
> $15/20 years and all other combinations as four distinct numbers. How
> can I accomplish this? Currently I am doing this (which I know is poor
> as it just looks wrong and takes forever to run.
> SELECT GroupID FROM thistable WHERE GroupID NOT IN (
> SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
> FormElementID=1 AND FormElementValue=5 AND GroupID IN (SELECT GroupID
> from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
> FormElementValue=10)
> AND GroupID NOT IN (
> SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
> FormElementID=1 AND FormElementValue=10 AND GroupID IN (SELECT GroupID
> from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
> FormElementValue=15)
> AND GroupID NOT IN (
> SELECT GroupID FROM thistable WHERE PageID=1 and QuestionID=4 AND
> FormElementID=1 AND FormElementValue=15 AND GroupID IN (SELECT GroupID
> from thistable WHERE PageID=1 AND Question_ID=4 AND FormElementID=2 AND
> FormElementValue=20)
You should be able to sort this out, if you learn to master EXISTS/NOT
EXISTS. I show example with your first query to get you going:
SELECT t1.GroupID
FROM thistable t1
WHERE t1.PageID=1
and t1.QuestionID=4
AND t1.FormElementID=1
AND t1.FormElementValue=5
AND EXISTS (SELECT *
from thistable t2
WHERE t2.PageID=1
AND t2.Question_ID=4
AND t2.FormElementID=2
AND t2.FormElementValue=10
AND t1.GroupID = t2.GroupID)
The point here is that you may not need multiple subqueries, but could
then use OR conditions.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
Thank you for your response. I've spend some time converting my
original query to use EXISTS, I can now generate the same output using
a version running NOT EXISTS as my current NOT IN statements.
Unfortunately, I've run the 2 queries in query analyzer and the NOT
EXISTS statements takes 12 seconds whereas my current NOT IN statement
takes 7 seconds so I am not seeing any benefit from using NOT EXISTS.
My query looks like so
SELECT d.GroupID
FROM thistable d
WHERE d.PageID=1
and d.QuestionID=4
AND d.FormElementID=1
AND d.FormElementValue=5
and NOT EXISTS (SELECT * FROM thistable t1 WHERE t1.PageID=1 AND
t1.Question_ID=4 AND t1.FormElementID=1 AND FormElementValue = 5
AND EXISTS (SELECT * from thistable t2 WHERE t2.PageID=1 AND
t2.Question_ID=4 AND t2.FormElementID=2 AND FormElementValue = 10 AND
t1.GroupID = t2.GroupID) and d.GroupID=t1.GroupID)|||night_day (night_day_8@.yahoo.com) writes:
> Thank you for your response. I've spend some time converting my
> original query to use EXISTS, I can now generate the same output using
> a version running NOT EXISTS as my current NOT IN statements.
> Unfortunately, I've run the 2 queries in query analyzer and the NOT
> EXISTS statements takes 12 seconds whereas my current NOT IN statement
> takes 7 seconds so I am not seeing any benefit from using NOT EXISTS.
> My query looks like so
> SELECT d.GroupID
> FROM thistable d
> WHERE d.PageID=1
> and d.QuestionID=4
> AND d.FormElementID=1
> AND d.FormElementValue=5
> and NOT EXISTS (SELECT * FROM thistable t1 WHERE t1.PageID=1 AND
> t1.Question_ID=4 AND t1.FormElementID=1 AND FormElementValue = 5
> AND EXISTS (SELECT * from thistable t2 WHERE t2.PageID=1 AND
> t2.Question_ID=4 AND t2.FormElementID=2 AND FormElementValue = 10 AND
> t1.GroupID = t2.GroupID) and d.GroupID=t1.GroupID)
Eh, the query is not exactly trivial to understand. I feel quite
bewildered. It would help if you posted:
o CREATE TABLE statement for the table.
o The output of sp_helpindex for the table.
o Some indication of number of rows and distribution.
It could also be interesting to see the query plans. You can this
by surrounding the query in SET SHOWPLAN_ALL ON. (This will not execute
the query.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Need rdl help

Can you launch a sql report rdl for a menu form you default page if so I know how to launch a crystal.aspx but do not know how to launch the rdl files do what do I need to to to make this work.

You have to deploy the report to your report server. You can upload the .rdl file through Report Manager but I've always had problems with this concerning the data sources. (I'm new at this too). I usually create the report in VS and then deploy it from there.

After it is on the report server you can access it usually from this urlhttp://localhost/reportserver or you can use the ReportViewer control in your .aspx page if you are using Visual Studio 2.0. Or you can link directly to the report server from your .aspx page.

If you need more help make sure you read the tutorials on Microsoft's site to get you started.

|||

I am inheriting a web site (whose original author is currently unavailable) that runs an winform app behind the scene to send emails or faxes to certain recipients based on what users enter onto the site. The win app calls a SSRS web service to generate the text contents for email/fax. Recently I have received new requirements and need to modify the SSRS text contents but have not been able to find the related rdl files. Could the SRSS reports be deployed,without the rdl files being copied to the server? What can I do now? Thanks.

Need help with UNION and SUM

Hi!

I am trying to join to different queries into one table ( I accomplished this)

Next I need to ADD or SUM the results of 2 rows to form a single row.

As you can see in the query below, I run 2 separate queries and use ' ' as a place holder for the UNION to work. I get duplicate rows, one with a value and the other with a '0'. I want to have a single row.

Any help is greatly appreciated!

(SELECT
v_gs_supportedpackages.ProdID0 as 'Product Name',
v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 as 'Site',
Count(ProdID0) as '# copies installed',
'' as '# legitimate copies installed'

FROM
v_R_System SYS,
v_GS_Workstation_Status HWSCAN,
v_gs_SupportedPackages
inner join
v_RA_System_SMSInstalledSites on v_RA_System_SMSInstalledSites.ResourceID = v_gs_SupportedPackages.ResourceID

WHERE
SYS.ResourceId = HWSCAN.ResourceId
AND
SYS.ResourceId = v_gs_SupportedPackages.ResourceId
AND
v_gs_supportedpackages.ProdID0 = substring('MS Security Patch MS04-030,031,032,034,037,038',1,60)

GROUP BY

v_gs_supportedpackages.ProdID0,
v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 )

UNION

(SELECT
v_gs_supportedpackages.ProdID0 as 'Product Name',
v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 as 'Site',
'' as '# copies installed',
Count(ProdID0) as '# legitimate copies installed'

FROM
v_R_System SYS,
v_GS_Workstation_Status HWSCAN,
v_gs_SupportedPackages
inner join
v_RA_System_SMSInstalledSites on v_RA_System_SMSInstalledSites.ResourceID = v_gs_SupportedPackages.ResourceID

WHERE
SYS.ResourceId = HWSCAN.ResourceId
AND
SYS.ResourceId = v_gs_SupportedPackages.ResourceId
AND
v_gs_supportedpackages.ProdID0 = substring('MS Security Patch MS04-030,031,032,034,037,038',1,60)
AND
DateDiff(Day,HWSCAN.LastHWScan,GetDate()) <= '20'

GROUP BY

v_gs_supportedpackages.ProdID0,
v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 )Change your null string place holder to a zero and use a table variable to return only 1 row for each instance of ColA and ColB. For example

DECLARE @.myTable table
(
ColA varchar(50), CoB varchar(50), ColC int, ColD int
)

INSERT INTO @.myTable
(ColA, ColB, ColC, ColD)

SELECT ColA, ColB, SUM(ColC), 0
FROM TableA

UNION ALL

SELECT ColA, ColB, 0, Sum(ColD)
FROM TableB
GROUP BY ColA, ColB

ORDER BY 1, 2

SELECT ColA, ColB, SUM(ColC) AS ColC, SUM(ColD) AS ColD
FROM @.myTable
GROUP BY ColA, ColB
ORDER BY 1, 2
GO|||First, fix your queries to eliminate joining tables in your WHERE clauses.
Second, you don't (and shouldn't) enclose numeric values in quotes.
Third, you don't need UNION here. You need a full outer join:

SELECT COPIESINSTALLED.ProdID0 as 'Product Name', COPIESINSTALLED.SMS_Installed_Sites0 as 'Site', COPIESINSTALLED.TOTAL as '# copies installed', LEGITCOPIESINSTALLED.TOTAL as '# legitimate copies installed'
FROM
(SELECT v_gs_supportedpackages.ProdID0, v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 , Count(ProdID0) TOTAL
FROM v_R_System SYS,
INNER JOIN v_GS_Workstation_Status HWSCAN ON SYS.ResourceId = HWSCAN.ResourceId
INNER JOIN v_gs_SupportedPackages ON SYS.ResourceId = v_gs_SupportedPackages.ResourceId
INNER JOIN v_RA_System_SMSInstalledSites ON v_RA_System_SMSInstalledSites.ResourceID = v_gs_SupportedPackages.ResourceID
WHERE v_gs_supportedpackages.ProdID0 = substring('MS Security Patch MS04-030,031,032,034,037,038',1,60)
GROUP BY v_gs_supportedpackages.ProdID0, v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 ) COPIESINSTALLED
FULL OUTER JOIN
(SELECT v_gs_supportedpackages.ProdID0, v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 , Count(ProdID0) TOTAL
FROM v_R_System SYS,
INNER JOIN v_GS_Workstation_Status HWSCAN ON SYS.ResourceId = HWSCAN.ResourceId
INNER JOIN v_gs_SupportedPackages ON SYS.ResourceId = v_gs_SupportedPackages.ResourceId
INNER JOIN v_RA_System_SMSInstalledSites ON v_RA_System_SMSInstalledSites.ResourceID = v_gs_SupportedPackages.ResourceID
WHERE v_gs_supportedpackages.ProdID0 = substring('MS Security Patch MS04-030,031,032,034,037,038',1,60)
AND DateDiff(Day,HWSCAN.LastHWScan,GetDate()) <= 20
GROUP BY v_gs_supportedpackages.ProdID0, v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 ) LEGITCOPIESINSTALLED
ON COPIESINSTALLED.ProdID0 = LEGITCOPIESINSTALLED.ProdID0
AND COPIESINSTALLED.SMS_Installed_Sites0 = LEGITCOPIESINSTALLED.SMS_Installed_Sites0

Finally, you could probably do the whole thing in a single SELECT with a CASE statement in the result set, but I'm not clear enough on your task to create the SQL for you.

...and one other thing. What is "substring('MS Security Patch MS04-030,031,032,034,037,038',1,60)" supposed to be doing for you?|||Many thanks to you. I knew you guys would knock this out.

Thanks again.

Semper Fi,

JP|||Blindman,

Thanks again for your help. In regards to:

Finally, you could probably do the whole thing in a single SELECT with a CASE statement in the result set, but I'm not clear enough on your task to create the SQL for you.

...and one other thing. What is "substring('MS Security Patch MS04-030,031,032,034,037,038',1,60)" supposed to be doing for you?
__________________

The substring was the only way I could pump in a variable with commas. On our web reports this value is dynamic and due to the naming conventions of our admins, some put commas in the title to separate the different versions of a package.

If you know a better way to capture commas and not have SQL try to evaluate it as an expression, please let me know!

Many thanks again,

JP