I'm creating a .NET crystal report sales report that lists the total # of each item sold and the percentage of total sales that the item contributed towards however, before I can calculate the percentage for each row, I need to know the total qty of the report, which I don't know how to retrieve.
So a sample report of 500 items sold may look like this:
------------------
Category | Qty Sold | Percentage
------------------
Electronics | 200 | 40%
Books | 100 | 20%
Movies | 150 | 30%
Games | 50 | 10%
------------------
Total | 500 | 100%
------------------
How can I calculate a percentage for something like this?
Thanks.
-Goalie35http://forums.belution.com/en/crystal/000/061/54.shtml
Showing posts with label creating. Show all posts
Showing posts with label creating. Show all posts
Sunday, March 25, 2012
Crystal reports to Reporting Services
Crystal reports to Reporting Services
Is there any way I can convert Crystal reports to Reporting Services instead
of creating reports again in Reporting Services?JIM. H. wrote:
> Crystal reports to Reporting Services
> Is there any way I can convert Crystal reports to Reporting Services instead
> of creating reports again in Reporting Services?
Aside from using a Migration partner, there is not much else that can
be done outside of maybe designing some sort of custom migration
application. A few of the migration partner links are as follows.
Sorry that I could not be of greater assistance.
http://www.hitachiconsulting.com/downloadPdf.cfm?ID=251
http://www.rpttordl.com/
http://www.microsoft.com/sql/technologies/reporting/partners/crystal-migration.mspx
Regards,
Enrique Martinez
Sr. Software Consultant
Is there any way I can convert Crystal reports to Reporting Services instead
of creating reports again in Reporting Services?JIM. H. wrote:
> Crystal reports to Reporting Services
> Is there any way I can convert Crystal reports to Reporting Services instead
> of creating reports again in Reporting Services?
Aside from using a Migration partner, there is not much else that can
be done outside of maybe designing some sort of custom migration
application. A few of the migration partner links are as follows.
Sorry that I could not be of greater assistance.
http://www.hitachiconsulting.com/downloadPdf.cfm?ID=251
http://www.rpttordl.com/
http://www.microsoft.com/sql/technologies/reporting/partners/crystal-migration.mspx
Regards,
Enrique Martinez
Sr. Software Consultant
Crystal reports to Reporting Services
Crystal reports to Reporting Services
Is there any way I can convert Crystal reports to Reporting Services instead of creating reports again in Reporting Services?
From http://blog.hundhausen.com/ConvertingCrystalReportsRptToReportingServicesReportsRdl.aspx (and I have no idea whether this is true):
There are way too many Crystal Reports out there in the world and I would love to see more of them converted to Reporting Services. Therein lies the problem. I had heard that Crystal (now BusinessObjects) had blocked companies, like Hitachi, from building converters, because they would be reverse engineering (or using the APIs to reverse engineer) the .rpt files, and that would violate the license agreement.
Microsoft only provides migration guidance, but I see that one company, KTL Solutions, has a conversion product available, and another company, Jeff-Net is offering a $25/report conversion service at rpttordl.com.
There do seem to be a lot of "migration partners" (gack!) listed in various places.
>L<
sqlCrystal reports to Reporting Services
Crystal reports to Reporting Services
Is there any way I can convert Crystal reports to Reporting Services instead of creating reports again in Reporting Services?
HI, JIM.H.
You can check out this great article. Hope it helps
Converting Crystal to Reporting Services
http://blogs.msdn.com/gsnowman/archive/2005/10/12/480305.aspx
If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.
I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance
|||
Hi Jim,
Microsoft created one Migration guide for Migrating Your Crystal Reports to SQL Server 2005 Reporting Services -
http://www.microsoft.com/sql/techinfo/whitepapers/migrate-crystal-to-reporting-services.mspx
And the webcast for this -
http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?culture=en-US&EventID=1032298257&CountryCode=US
Got this info from this blog -
http://blogs.msdn.com/bwelcker/archive/2006/05/31/612637.aspx
Thursday, March 22, 2012
crystal reports manual
Can anyone help me in finding a good crystal reports 10 manual which gives step by step procedure for creating simple and complex reports. Thanks in advanceI use ISBN 0-07-223166-1 (Crystal Reports 10, the complete reference, published by McGraw-Hill) www.osborne.com
I would suggest trying to check one out at the local library. (if possible) also I have found most of the "SAM'S" publications to be usefull|||I use ISBN 0-07-223166-1 (Crystal Reports 10, the complete reference, published by McGraw-Hill) www.osborne.com
I would suggest trying to check one out at the local library. (if possible) also I have found most of the "SAM'S" publications to be usefull
I have the same book. It's been very helpful.|||I also am looking for a how to book for crystal reports, but I am a total beginner, and was wondering if you would still recommend this book, or if you know of a better book/guide/tutorial type book for beginner's to learn Crystal Reports.
I would suggest trying to check one out at the local library. (if possible) also I have found most of the "SAM'S" publications to be usefull|||I use ISBN 0-07-223166-1 (Crystal Reports 10, the complete reference, published by McGraw-Hill) www.osborne.com
I would suggest trying to check one out at the local library. (if possible) also I have found most of the "SAM'S" publications to be usefull
I have the same book. It's been very helpful.|||I also am looking for a how to book for crystal reports, but I am a total beginner, and was wondering if you would still recommend this book, or if you know of a better book/guide/tutorial type book for beginner's to learn Crystal Reports.
Crystal Reports from VC++
Hi All,
I am creating crystal reports(version 8.5) using VC++ code.
Its working fine on the the machines which have crystal reports installed .
however same is not the case with non crystal machines even after registring the Crystl32.OCX used by the Crystal Reports Object.
I am getting error " Unable to logon to server" while trying to run the report.
DSNs are same on both type of machines.
(one with crystal reports and one without it )
I have already distributed crystal dlls with the application.
(Crystl32.OCX,Craxdrt.dll, CRViewer.dll,CRDesignerCtrl.dll,Craxddrt.dll)
Do I need to do some thing in addition to this ?
kindly help.
thanks
SanjayCoule you please provide me with your code, it helps me build my own app...
Rob
I am creating crystal reports(version 8.5) using VC++ code.
Its working fine on the the machines which have crystal reports installed .
however same is not the case with non crystal machines even after registring the Crystl32.OCX used by the Crystal Reports Object.
I am getting error " Unable to logon to server" while trying to run the report.
DSNs are same on both type of machines.
(one with crystal reports and one without it )
I have already distributed crystal dlls with the application.
(Crystl32.OCX,Craxdrt.dll, CRViewer.dll,CRDesignerCtrl.dll,Craxddrt.dll)
Do I need to do some thing in addition to this ?
kindly help.
thanks
SanjayCoule you please provide me with your code, it helps me build my own app...
Rob
Tuesday, March 20, 2012
Crystal reports
I am working on creating Crystal Reports from a SQL database.
In CR you can define a multiple range parameter i.e. 1-10, 100-200, 300-400 and Cr will filer records that satisfy this criteria.
How can I transfer this functionality using a stored procedure?What functionality? Filtering? A stored procedure can do that by being passed the Min and Max as paramters, of course.
In CR you can define a multiple range parameter i.e. 1-10, 100-200, 300-400 and Cr will filer records that satisfy this criteria.
How can I transfer this functionality using a stored procedure?What functionality? Filtering? A stored procedure can do that by being passed the Min and Max as paramters, of course.
Wednesday, March 7, 2012
Crystal Report
I am using VB 6 with MS Access as Back End. I saved a path name of a text file in a field of Access MDB. I am using Crystal report 8 for creating reports. Will anybody help me to print the contents of the file stored in the field in the crystal report? (For eg the field contains the value "c:\project\test.txt" How to print the contents in the "detail" portion of the crystal report. When I select the field, the file name comes not the contents). Pl. help me.
Meenakshi.RMeenakshi,
I dont think there is a way to do exactly you want. But you can use heperlink option. Right Click on any field; Goto Format Field and Select HyperLink and File and Select the text file. When running the report, if you click that Field, then the text file will be opened.|||Madhi,
Each record has a different text file. With your suggestion, I have to make a permanent link to that text file. How to get different text file links? pl. guide.
Meenakshi.R|||Meenakshi,
If each record has to show different file, then you can use formula.
Do the same procedure that I told you except that instead of selecting the file, there is button labelled x-2 near text box. Click that and if the field it self represents the file name, then write this
{FieldName}|||Madhi,
I tried with your option. But when the report is run, the value stored in the field (ie) the file name comes without any hyperlink. No provision for clicking. Kindly guide me.
Meenakshi.R|||Which version of Crystal Report are you using?|||Sir,
The version of the crystal report is 8.0
Meenakshi.R|||Meenakshi,
I dont know where it goes wrong
Refer this (http://support.businessobjects.com/library/kbase/articles/c2006151.asp)|||Madhi,
Referred the article. Done as per the instructions. But still I am getting the field value only without the hyperlink. Any other solution?
Meenakshi.R|||I have a report where each record brings back a field with the path of a different image file. Each record in the details section prints out the word "Image" as a link, which I have set up as follows:
1. Created a formula which has nothing more in it than the word "Image". Placed into details section of report.
2. Click Format Field on this formula, go to Hyperlink tab. Choose 'A Website on the Internet', then open the formula box for Website Address.
3. In here, place the field from the database.
My images are available via a http server, but you may choose to use one of the other options there instead (eg 'A File' instead of 'A Website on the Internet'.|||Hai,
Created a formula. Placed in the detail section. Using format option, I have made a hyperlink by choosing the option Hyperlink from a file. With the formula button (hyperlink screen), I specified the field value. During run time, the formula name comes (but without hyperlink option). What to do?|||Should work, try debugging. I'd suggest copying the formula you are using to create the hyperlink into a regular formula and seeing what comes out, ie what it is trying to use to create this hyperlink. You might find that it has an invalid path, or is coming back blank, etc...|||Tried tried. I saved the report with field in the detail section by choosing "a file" from the hyperlink option, run the report, the field value stored in the field comes without hyperlink option. When viewing the report in design view, the hyperlink option shows 'a website on the internet'. I don't know why? pl. explain in detail.
Meenakshi.R|||My suggestion is that your file path is invalid. Test to see what CR thinks is in that field, for starters.
Ie, if you have a url stored in your database field and http:// already exists, you could end up with something like http://http://google.com
In my report, I can click on the field to open a file, provided that file path is correct. I typed C:\temp\test.txt into the File Name box. However, changing it to C:\temp\test1.txt simply does nothing as this file does not exist.
Also note that the field may be a hyperlink but will appear to be just a regular field until you mouse over. (ie, if you want it to appear as a hyperlink should then you will have to underline and colour it yourself).|||Sir,
I have created a fresh report and do all the things. When I click the preview button in Crystal report screen, I am able to click the hyperlink, and the corresponding file stored in the field gets opened. When this report is accessed via a VB program coding, simply the file name is displayed and I could not click the hyperlink. Pl. help me.
Meenakshi.R
Meenakshi.RMeenakshi,
I dont think there is a way to do exactly you want. But you can use heperlink option. Right Click on any field; Goto Format Field and Select HyperLink and File and Select the text file. When running the report, if you click that Field, then the text file will be opened.|||Madhi,
Each record has a different text file. With your suggestion, I have to make a permanent link to that text file. How to get different text file links? pl. guide.
Meenakshi.R|||Meenakshi,
If each record has to show different file, then you can use formula.
Do the same procedure that I told you except that instead of selecting the file, there is button labelled x-2 near text box. Click that and if the field it self represents the file name, then write this
{FieldName}|||Madhi,
I tried with your option. But when the report is run, the value stored in the field (ie) the file name comes without any hyperlink. No provision for clicking. Kindly guide me.
Meenakshi.R|||Which version of Crystal Report are you using?|||Sir,
The version of the crystal report is 8.0
Meenakshi.R|||Meenakshi,
I dont know where it goes wrong
Refer this (http://support.businessobjects.com/library/kbase/articles/c2006151.asp)|||Madhi,
Referred the article. Done as per the instructions. But still I am getting the field value only without the hyperlink. Any other solution?
Meenakshi.R|||I have a report where each record brings back a field with the path of a different image file. Each record in the details section prints out the word "Image" as a link, which I have set up as follows:
1. Created a formula which has nothing more in it than the word "Image". Placed into details section of report.
2. Click Format Field on this formula, go to Hyperlink tab. Choose 'A Website on the Internet', then open the formula box for Website Address.
3. In here, place the field from the database.
My images are available via a http server, but you may choose to use one of the other options there instead (eg 'A File' instead of 'A Website on the Internet'.|||Hai,
Created a formula. Placed in the detail section. Using format option, I have made a hyperlink by choosing the option Hyperlink from a file. With the formula button (hyperlink screen), I specified the field value. During run time, the formula name comes (but without hyperlink option). What to do?|||Should work, try debugging. I'd suggest copying the formula you are using to create the hyperlink into a regular formula and seeing what comes out, ie what it is trying to use to create this hyperlink. You might find that it has an invalid path, or is coming back blank, etc...|||Tried tried. I saved the report with field in the detail section by choosing "a file" from the hyperlink option, run the report, the field value stored in the field comes without hyperlink option. When viewing the report in design view, the hyperlink option shows 'a website on the internet'. I don't know why? pl. explain in detail.
Meenakshi.R|||My suggestion is that your file path is invalid. Test to see what CR thinks is in that field, for starters.
Ie, if you have a url stored in your database field and http:// already exists, you could end up with something like http://http://google.com
In my report, I can click on the field to open a file, provided that file path is correct. I typed C:\temp\test.txt into the File Name box. However, changing it to C:\temp\test1.txt simply does nothing as this file does not exist.
Also note that the field may be a hyperlink but will appear to be just a regular field until you mouse over. (ie, if you want it to appear as a hyperlink should then you will have to underline and colour it yourself).|||Sir,
I have created a fresh report and do all the things. When I click the preview button in Crystal report screen, I am able to click the hyperlink, and the corresponding file stored in the field gets opened. When this report is accessed via a VB program coding, simply the file name is displayed and I could not click the hyperlink. Pl. help me.
Meenakshi.R
Saturday, February 25, 2012
Crosstabs queries
Hello,
I'm a newbie and have just setup my first sql server based database. I
access it was quite easy creating queries and attaching them to reports.
Moving from access to Access data project, I'm finding it difficult to create
crosstab queries.
Can anyone point me in the right direction, bearing in mind that the
database created is in SQL server 2000 with Access 2000 (ADP) Client.
Thanks in advance.
I'm pretty sure there is but try these following groups as they should have the
exact syntax:
news:microsoft.public.sqlserver.server
news:microsoft.public.sqlserver.misc
news:comp.databases.ms-sqlserver
news:bit.databases.mssql-l
"Hans" wrote:
> Hello,
> I'm a newbie and have just setup my first sql server based database. I
> access it was quite easy creating queries and attaching them to reports.
> Moving from access to Access data project, I'm finding it difficult to create
> crosstab queries.
> Can anyone point me in the right direction, bearing in mind that the
> database created is in SQL server 2000 with Access 2000 (ADP) Client.
> Thanks in advance.
|||Its not as easy as creating cross tab queries in Access... You may want to
use sub queries and use those results from the sub queries and turn it into a
case statement to display in the main select quary..! Since you havent give
any idea on what you are trying to cross tab... (columns names, tables,
criteria) its hard to give you a syntex...
"Hans" wrote:
> Hello,
> I'm a newbie and have just setup my first sql server based database. I
> access it was quite easy creating queries and attaching them to reports.
> Moving from access to Access data project, I'm finding it difficult to create
> crosstab queries.
> Can anyone point me in the right direction, bearing in mind that the
> database created is in SQL server 2000 with Access 2000 (ADP) Client.
> Thanks in advance.
|||There are several xtab reoprts that needto be created. Example of one that
need to be prepared is.
table : tbl_patientepisodes
fields : EpisodeID - Unique(Index)
DateRefRecved - Date
DateRefAccepted - Date
DateRefNotAccepted - Date
rpt format:-
Title: Referals <from> to <To>
Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar
No of referals reveived
No Of Referals Accepted
No Not Accepted
This reports will be based on a start and end date specified by the user.
hope this helps.
"SQLDBA" wrote:
[vbcol=seagreen]
> Its not as easy as creating cross tab queries in Access... You may want to
> use sub queries and use those results from the sub queries and turn it into a
> case statement to display in the main select quary..! Since you havent give
> any idea on what you are trying to cross tab... (columns names, tables,
> criteria) its hard to give you a syntex...
> "Hans" wrote:
|||PS sorry forgot to mention a graph need to be prepared with the reports as well
"SQLDBA" wrote:
[vbcol=seagreen]
> Its not as easy as creating cross tab queries in Access... You may want to
> use sub queries and use those results from the sub queries and turn it into a
> case statement to display in the main select quary..! Since you havent give
> any idea on what you are trying to cross tab... (columns names, tables,
> criteria) its hard to give you a syntex...
> "Hans" wrote:
|||"Hans" wrote:
> There are several xtab reoprts that needto be created. Example of one that
> need to be prepared is.
> table : tbl_patientepisodes
> fields : EpisodeID - Unique(Index)
> DateRefRecved - Date
> DateRefAccepted - Date
> DateRefNotAccepted - Date
Perhaps extracting data thusly:
-- Date Referred counts by month
SUM(CASE WHEN MONTH(DateRefRecvd) = 1 THEN 1 else 0 END) as JanRefRcvdCount,
SUM(CASE WHEN MONTH(DateRefRecvd) = 2 THEN 1 else 0 END) as FebRefRcvdCount,
..
..
..
SUM(CASE WHEN MONTH(DateRefRecvd) = 12 THEN 1 else 0 END) as DecRfRcvdCount,
-- Date Acepteded counts by month:
SUM(CASE WHEN MONTH(DateRefAccepted) = 1 THEN 1 else 0 END) as
JanRefAcceptedCount,
SUM(CASE WHEN MONTH(DateRefAccepted) = 2 THEN 1 else 0 END) as
FebRefAcceptedCount,
..
..
..
SUM(CASE WHEN MONTH(DateRefAccepted) = 12 THEN 1 else 0 END) as
DecRfAcceptedCount,
I'm a newbie and have just setup my first sql server based database. I
access it was quite easy creating queries and attaching them to reports.
Moving from access to Access data project, I'm finding it difficult to create
crosstab queries.
Can anyone point me in the right direction, bearing in mind that the
database created is in SQL server 2000 with Access 2000 (ADP) Client.
Thanks in advance.
I'm pretty sure there is but try these following groups as they should have the
exact syntax:
news:microsoft.public.sqlserver.server
news:microsoft.public.sqlserver.misc
news:comp.databases.ms-sqlserver
news:bit.databases.mssql-l
"Hans" wrote:
> Hello,
> I'm a newbie and have just setup my first sql server based database. I
> access it was quite easy creating queries and attaching them to reports.
> Moving from access to Access data project, I'm finding it difficult to create
> crosstab queries.
> Can anyone point me in the right direction, bearing in mind that the
> database created is in SQL server 2000 with Access 2000 (ADP) Client.
> Thanks in advance.
|||Its not as easy as creating cross tab queries in Access... You may want to
use sub queries and use those results from the sub queries and turn it into a
case statement to display in the main select quary..! Since you havent give
any idea on what you are trying to cross tab... (columns names, tables,
criteria) its hard to give you a syntex...
"Hans" wrote:
> Hello,
> I'm a newbie and have just setup my first sql server based database. I
> access it was quite easy creating queries and attaching them to reports.
> Moving from access to Access data project, I'm finding it difficult to create
> crosstab queries.
> Can anyone point me in the right direction, bearing in mind that the
> database created is in SQL server 2000 with Access 2000 (ADP) Client.
> Thanks in advance.
|||There are several xtab reoprts that needto be created. Example of one that
need to be prepared is.
table : tbl_patientepisodes
fields : EpisodeID - Unique(Index)
DateRefRecved - Date
DateRefAccepted - Date
DateRefNotAccepted - Date
rpt format:-
Title: Referals <from> to <To>
Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar
No of referals reveived
No Of Referals Accepted
No Not Accepted
This reports will be based on a start and end date specified by the user.
hope this helps.
"SQLDBA" wrote:
[vbcol=seagreen]
> Its not as easy as creating cross tab queries in Access... You may want to
> use sub queries and use those results from the sub queries and turn it into a
> case statement to display in the main select quary..! Since you havent give
> any idea on what you are trying to cross tab... (columns names, tables,
> criteria) its hard to give you a syntex...
> "Hans" wrote:
|||PS sorry forgot to mention a graph need to be prepared with the reports as well
"SQLDBA" wrote:
[vbcol=seagreen]
> Its not as easy as creating cross tab queries in Access... You may want to
> use sub queries and use those results from the sub queries and turn it into a
> case statement to display in the main select quary..! Since you havent give
> any idea on what you are trying to cross tab... (columns names, tables,
> criteria) its hard to give you a syntex...
> "Hans" wrote:
|||"Hans" wrote:
> There are several xtab reoprts that needto be created. Example of one that
> need to be prepared is.
> table : tbl_patientepisodes
> fields : EpisodeID - Unique(Index)
> DateRefRecved - Date
> DateRefAccepted - Date
> DateRefNotAccepted - Date
Perhaps extracting data thusly:
-- Date Referred counts by month
SUM(CASE WHEN MONTH(DateRefRecvd) = 1 THEN 1 else 0 END) as JanRefRcvdCount,
SUM(CASE WHEN MONTH(DateRefRecvd) = 2 THEN 1 else 0 END) as FebRefRcvdCount,
..
..
..
SUM(CASE WHEN MONTH(DateRefRecvd) = 12 THEN 1 else 0 END) as DecRfRcvdCount,
-- Date Acepteded counts by month:
SUM(CASE WHEN MONTH(DateRefAccepted) = 1 THEN 1 else 0 END) as
JanRefAcceptedCount,
SUM(CASE WHEN MONTH(DateRefAccepted) = 2 THEN 1 else 0 END) as
FebRefAcceptedCount,
..
..
..
SUM(CASE WHEN MONTH(DateRefAccepted) = 12 THEN 1 else 0 END) as
DecRfAcceptedCount,
Cross-tab Row Title
CR XI posted a work-around/solution for inserting a row title in a cross tab report by creating a separate "fake" group, overlaying it and copying the cross tab to the fake group's section. It also discussed horizontal pages.
How do you repeat the label on subsequent/multiple pages though?See if you find answer here
http://support.businessobjects.com/
How do you repeat the label on subsequent/multiple pages though?See if you find answer here
http://support.businessobjects.com/
Friday, February 24, 2012
Crosstab Report
I am creating report with total costs by job, it repeats "column totals"
two times, and at the end column "grand total". "grand total" column is okay.
Is there any way I can drop/disable repeating "column totals". See example below, help me to drop repeating column.
Thanks
Job 1 Job 2 GrandTotal
Total Total
Cost 1 177,855 177,855 162,755 162,755 340,610
Cost 2 169,847 169,847 161,063 161,063 330,910
Cost 3 37,404 37,404 102,416 102,416 139,820In CR,right click on the cross tabl. Choose format Cross tabl. U ve a tab like "Customize Style". Choose each and every row in Rows List and below that u ve Grouping Option. Suppres that grouping option. I think this may be a soln. Try it out.
two times, and at the end column "grand total". "grand total" column is okay.
Is there any way I can drop/disable repeating "column totals". See example below, help me to drop repeating column.
Thanks
Job 1 Job 2 GrandTotal
Total Total
Cost 1 177,855 177,855 162,755 162,755 340,610
Cost 2 169,847 169,847 161,063 161,063 330,910
Cost 3 37,404 37,404 102,416 102,416 139,820In CR,right click on the cross tabl. Choose format Cross tabl. U ve a tab like "Customize Style". Choose each and every row in Rows List and below that u ve Grouping Option. Suppres that grouping option. I think this may be a soln. Try it out.
Crosstab help
Hello,
I need to accomplish the turning column data into row data via SQL. I can sorta get what I want with creating the report as a matrix report. However, I always seem to need one little thing to happen to shape my data as I need it. Anyway, here goes...
I have a table with 4 columns
UNIQUE | Code | FieldID | CustomField
The Code column is the customer code. FieldID numbers 1-100 and CustomField has string data.
My problem. I need to be able to choose ALL Customers [Code] where FieldID/s ="6", "7" & "8" and the CustomField rowdata corresponding to the FieldID data. BUT I then need to be able to use a daterange parameter on any row with a FieldID of "6" but that is not a datetime format it is in a string format.
Currently my SQL is:
SELECT
[Unique ID], Code, [Field ID] AS IUdate, [Field ID] AS IUNote, [Field ID] AS IUReq, [Custom Field]
FROM
dbo.[Customer Custom Field]
WHERE
(Code = '07-8111')AND ([Field ID] = 6) OR (Code = '07-8111')AND ([Field ID] = 7) OR (Code = '07-8111')AND ([Field ID] = 8)
However, everytime I try to run a daterange against, I get all kinds of data I don't need because the parameter is running against all the fields and NOT just the stringdate.
My idea is to convert the column data into row data (like a crosstab query) to sharpen it up for parameter ranges.
Is there something i am missing here? Can someone point me in the right direction?
Thanks in advance!
phorest
It sounds like you need a pivot table. Here is a great article on building queries using dynamic pivot tables.
http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp
If you have trouble, provide an example table full of data and I will take a shot at building a sample query.
Good luck!
Larry
|||Thanks for your help.
Just as I expected, though I did not mention that the database is SQL2000 so the pivot is probably not an option. I suppose I need to use CAST in the SQL statement to accomplish this. Is it possible to do this simply without having to create a stored procedure -or- temporary table?
My sample data below: (NOTE: I am only using the FieldID numbers 6,7, & 8 as they are the only ones I need)
Unique ID Code FieldID Custom Field
-- -- --
5625 05-4256 8 C
5629 COLE0001 6 02/24/2006
5630 COLE0001 7 Need Invoice date & claim#
5631 COLE0001 8 C
5637 05-3945 6 09/07/2006
5638 05-3945 7 Auth expired 08/21/06 (11/18)
5660 05-3670 6 06/17/2006
5661 05-3670 7 auth expired 06/06/06 (3/10)
5670 HUNTER0000 6 04/27/2006
5671 HUNTER0000 7 Need Charges04/07/06 DOS (box checked: not listed on walkout)
5672 HUNTER0000 8 R
5680 REAVES0000 6 04/27/2006
5681 REAVES0000 7 Need Auth from 04/14/06 DOS (box checked: not listed)
5682 REAVES0000 8 R
5684 SMITH0003 6 04/27/2006
5685 SMITH0003 7 Need Auth from 04/14/06 DOS (box checked: not listed)
5686 SMITH0003 8 R
(602 row(s) affected)
|||I failed to mention that the article above shows both the 2000 and 2005 syntax to accomplish the goal. Let me give it a shot with the data you provided:
Code Snippet
SELECTDISTINCT T.CODE,
Eight.[Custom Field] as Eight,
Seven.[Custom Field] as Seven,
Six.[Custom Field] as Six
FROM
TEST t
LEFT OUTER JOIN (SELECT CODE, [Custom Field] FROM TEST where FieldID = 8) Eight ON t.Code = Eight.Code
LEFT OUTER JOIN (SELECT CODE, [Custom Field] FROM TEST where FieldID = 7) Seven ON t.Code = Seven.Code
LEFT OUTER JOIN (SELECT CODE, [Custom Field] FROM TEST where FieldID = 6) Six ON t.Code = Six.Code
where TEST is the name of the table I created to hold your data. It produces the following output:
CODE Eight Seven Six
-
05-3670 NULL auth expired 06/06/06 (3/10) 06/17/2006
05-3945 NULL Auth expired 08/21/06 (11/18) 09/07/2006
05-4256 C NULL NULL
COLE0001 C Need Invoice date & claim# 02/24/2006
HUNTER0000 R NULL 04/27/2006
REAVES0000 R NULL 04/27/2006
SMITH0003 R NULL 04/27/2006
(7 row(s) affected)
Please understand that I am not claiming this to be the best/most efficient way of handling your needs. My intuition tells me that there is a clever way to do this that I am overlooking.
Larry
|||Yes, that works marvelously!
Just for my learning benefit, are the following statements essentially true?
The line "FROM dbo.[Customer Custom Field] AS t" is just declaring "t" to be a temporary table with all the available fields from dbo.[Customer Custom Field]?
"t.Code" is declaring 3 new separate tables (stripped from "t" to be separate tables where the LEFT OUTER JOINS recombine all fields into the resulting SQL data?
SELECT DISTINCT t.Code, Six.[Custom Field] AS Six, Seven.[Custom Field] AS Seven, Eight.[Custom Field] AS Eight
FROM dbo.[Customer Custom Field] AS t
LEFT OUTER JOIN
(SELECT Code, [Custom Field]
FROM dbo.[Customer Custom Field]
WHERE ([Field ID] = 6)) AS Six ON t.Code = Six.Code
LEFT OUTER JOIN
(SELECT Code, [Custom Field]
FROM dbo.[Customer Custom Field] AS [Customer Custom Field_2]
WHERE ([Field ID] = 7)) AS Seven ON t.Code = Seven.Code
LEFT OUTER JOIN
(SELECT Code, [Custom Field]
FROM dbo.[Customer Custom Field] AS [Customer Custom Field_1]
WHERE ([Field ID] = 8)) AS Eight ON t.Code = Eight.Code
WHERE (Six.[Custom Field] BETWEEN '04/01/2007' AND '07/15/2007')
When I constructed the query from your generous guide and tested it, It pulled the data the way I need it. Then I opened the SQL in the design editor and applied the daterange filter and it worked beautifully...
Thank you ever so much!
|||The line "FROM dbo.[Customer Custom Field] AS t" is just declaring "t" to be a temporary table with all the available fields from dbo.[Customer Custom Field]?
Yes. It is setting up an alias. I would normally use something more descriptive or identifiable (ccf comes to mind) but there is only the one table so I got lazy.
"t.Code" is declaring 3 new separate tables (stripped from "t" to be separate tables where the LEFT OUTER JOINS recombine all fields into the resulting SQL data?
Not exactly. What we are doing is using the same table four times. Remove the distinct and put an * for the fields and you will see what I mean. We are joining the table to itself. The 'Left Outer' part says that all of the rows from the table to the left are to be in the resulting table, even if there isn't a corresponding row in the table on the right. I would suggest looking at Inside Microsoft SQL Server 2005: T-SQL Querying by Itzik Ben-Gan if you want to learn more about building complex queries.
You are very welcome. I am glad it worked out so well.
Larry
Subscribe to:
Posts (Atom)