can i have data of two datasets into a single table

In Report, I need to display PO Details such as pono,item names and
their item qty,item rate, po amount, dcno., dcdate.
PO Details TenderName,pono,item name,item qty, item rate available in a
table tblPurchaseOrder.
PO amount=sum(item qty*item rate) + sum(charge)+ sum(item tax amount).
charge amount, tax amount may/may not exist for PO.
Each PO can have more than one DC.
using single query if i am trying to retreive pono,item name,po amount,
dc date,dcno then i am getting po amount no. of times dc
occurs*original po amount.so i splitted po details and dc details into
two. now the data i am getting is correct. but using two tables the
data is not corresponding to each other. dc details getting ups and
downs.
Kindly give me solution ...
My query to retreive PO Details is as follows :
SELECT viewPurchaseOrderComponents.TenderName,
viewPurchaseOrderComponents.PONo, viewPurchaseOrderComponents.PODate,
viewPurchaseOrderComponents.ComponentName,
viewPurchaseOrderComponents.ComponentQty,
viewPurchaseOrderComponents.Rate,
viewPurchaseOrderComponents.ComponentQty *
viewPurchaseOrderComponents.Rate AS CompAmount,
viewPurchaseOrderComponents.UnitName,
B.TotalTaxAmount, A.Charge,
viewPurchaseOrderComponents.ProjectComponentId,
viewPurchaseOrderComponents.ComponentId,
viewPurchaseOrderComponents.IndentNo, tblPurchaseOrder.PaymentTerms,
tblPurchaseOrder.IndentDate,
tblPurchaseOrder.DeliveryPeriod, tblPurchaseOrder.Status,
tblPurchaseOrder.HasAnyAmendment,
tblPurchaseOrder.IsRegularize,
viewPurchaseOrderComponents.Type, viewPurchaseOrderComponents.Supplier
FROM viewPurchaseOrderComponents INNER JOIN
tblPurchaseOrder ON
viewPurchaseOrderComponents.PONo = tblPurchaseOrder.PONo LEFT OUTER
JOIN
(SELECT PONo, SUM(Amount) AS Charge
FROM tblPOCharges
GROUP BY PONo) AS A ON A.PONo = viewPurchaseOrderComponents.PONo LEFT OUTER JOIN
(SELECT POComponentId,
SUM(TaxValue) AS TotalTaxAmount
FROM tblPOComponentTaxes
GROUP BY POComponentId) AS B ON
B.POComponentId = viewPurchaseOrderComponents.ComponentId
WHERE (viewPurchaseOrderComponents.PODate >= @.StartDate) AND
(viewPurchaseOrderComponents.PODate < DATEADD(d, 1, @.EndDate))
For DC Details :
SELECT viewPurchaseOrderComponents.PONo,
viewPurchaseOrderComponents.TenderName,
viewPurchaseOrderComponents.PODate,
viewPurchaseOrderComponents.ComponentName,
tblPurchaseOrder.Status, tblPurchaseOrder.HasAnyAmendment,
tblPurchaseOrder.IsRegularize,
viewPurchaseOrderComponents.Type, viewPurchaseOrderComponents.Supplier,
tblComponentsOfDc.Qty AS DCQty,
tblDeliveryChallan.DCId, CONVERT(varchar(11),
tblDeliveryChallan.ChallanDate, 106) AS ChallanDate
FROM viewPurchaseOrderComponents LEFT OUTER JOIN
tblDeliveryChallan ON tblDeliveryChallan.PONo
= viewPurchaseOrderComponents.PONo INNER JOIN
tblPurchaseOrder ON
viewPurchaseOrderComponents.PONo = tblPurchaseOrder.PONo LEFT OUTER
JOIN
tblComponentsOfDc ON tblDeliveryChallan.DCId = tblComponentsOfDc.DCId
WHERE (viewPurchaseOrderComponents.PODate >= @.StartDate) AND
(viewPurchaseOrderComponents.PODate < DATEADD(d, 1, @.EndDate))
In a single table i need TenderName,PONO,item details name,qty,rate
POAmount, DCDate...
Waiting for solution ...Try looking at subreports. You can embed a subreport into a detail line of a
table. Add an extra detail line and then drag and drop your subreport onto
the cell. Then right mouse click, properties and set the parameters to the
field values that link the two together.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sarada" <sarada_devi_i@.yahoo.com> wrote in message
news:1155904374.494550.59260@.i3g2000cwc.googlegroups.com...
> In Report, I need to display PO Details such as pono,item names and
> their item qty,item rate, po amount, dcno., dcdate.
> PO Details TenderName,pono,item name,item qty, item rate available in a
> table tblPurchaseOrder.
> PO amount=sum(item qty*item rate) + sum(charge)+ sum(item tax amount).
> charge amount, tax amount may/may not exist for PO.
> Each PO can have more than one DC.
> using single query if i am trying to retreive pono,item name,po amount,
> dc date,dcno then i am getting po amount no. of times dc
> occurs*original po amount.so i splitted po details and dc details into
> two. now the data i am getting is correct. but using two tables the
> data is not corresponding to each other. dc details getting ups and
> downs.
> Kindly give me solution ...
> My query to retreive PO Details is as follows :
> SELECT viewPurchaseOrderComponents.TenderName,
> viewPurchaseOrderComponents.PONo, viewPurchaseOrderComponents.PODate,
> viewPurchaseOrderComponents.ComponentName,
> viewPurchaseOrderComponents.ComponentQty,
> viewPurchaseOrderComponents.Rate,
> viewPurchaseOrderComponents.ComponentQty *
> viewPurchaseOrderComponents.Rate AS CompAmount,
> viewPurchaseOrderComponents.UnitName,
> B.TotalTaxAmount, A.Charge,
> viewPurchaseOrderComponents.ProjectComponentId,
> viewPurchaseOrderComponents.ComponentId,
> viewPurchaseOrderComponents.IndentNo, tblPurchaseOrder.PaymentTerms,
> tblPurchaseOrder.IndentDate,
> tblPurchaseOrder.DeliveryPeriod, tblPurchaseOrder.Status,
> tblPurchaseOrder.HasAnyAmendment,
> tblPurchaseOrder.IsRegularize,
> viewPurchaseOrderComponents.Type, viewPurchaseOrderComponents.Supplier
> FROM viewPurchaseOrderComponents INNER JOIN
> tblPurchaseOrder ON
> viewPurchaseOrderComponents.PONo = tblPurchaseOrder.PONo LEFT OUTER
> JOIN
> (SELECT PONo, SUM(Amount) AS Charge
> FROM tblPOCharges
> GROUP BY PONo) AS A ON A.PONo => viewPurchaseOrderComponents.PONo LEFT OUTER JOIN
> (SELECT POComponentId,
> SUM(TaxValue) AS TotalTaxAmount
> FROM tblPOComponentTaxes
> GROUP BY POComponentId) AS B ON
> B.POComponentId = viewPurchaseOrderComponents.ComponentId
> WHERE (viewPurchaseOrderComponents.PODate >= @.StartDate) AND
> (viewPurchaseOrderComponents.PODate < DATEADD(d, 1, @.EndDate))
> For DC Details :
> SELECT viewPurchaseOrderComponents.PONo,
> viewPurchaseOrderComponents.TenderName,
> viewPurchaseOrderComponents.PODate,
> viewPurchaseOrderComponents.ComponentName,
> tblPurchaseOrder.Status, tblPurchaseOrder.HasAnyAmendment,
> tblPurchaseOrder.IsRegularize,
> viewPurchaseOrderComponents.Type, viewPurchaseOrderComponents.Supplier,
> tblComponentsOfDc.Qty AS DCQty,
> tblDeliveryChallan.DCId, CONVERT(varchar(11),
> tblDeliveryChallan.ChallanDate, 106) AS ChallanDate
> FROM viewPurchaseOrderComponents LEFT OUTER JOIN
> tblDeliveryChallan ON tblDeliveryChallan.PONo
> = viewPurchaseOrderComponents.PONo INNER JOIN
> tblPurchaseOrder ON
> viewPurchaseOrderComponents.PONo = tblPurchaseOrder.PONo LEFT OUTER
> JOIN
> tblComponentsOfDc ON tblDeliveryChallan.DCId => tblComponentsOfDc.DCId
> WHERE (viewPurchaseOrderComponents.PODate >= @.StartDate) AND
> (viewPurchaseOrderComponents.PODate < DATEADD(d, 1, @.EndDate))
> In a single table i need TenderName,PONO,item details name,qty,rate
> POAmount, DCDate...
> Waiting for solution ...
>

Can I handle hierarchies like Oracle ?

Hi SQL Server Experts,
Oracle has a 'Connect by prior' extension to SQL that is neat for display hierarchies.
Does SQL Server have anything simialr or would I use T-SQL or ?
I will appreciate any suggestions and advice.
B.Dimple
Junior DBANo. I faced a similar problem in the past.|||As long as you have the base of the hierarcy defined you can add levels to the table information and create your hierarchy.

This is not an elegant solution but it does work.

Assume the following Table and Field names.

tblDepartment - table of organization departments
departmentNumber - department number of the selected organization
parentDepartmentNumber - department number of preceding branch
deptLevel - hierachical level from 1

(Only the base of the hierarchy will NOT have a parent associated.

step 1--
UPDATE tblDepartment
SET tblDepartment.deptLevel = 1
WHERE (tblDepartment.parentDepartmentNumber IS NULL)

step 2--
/* L2 Levels */
UPDATE tblDepartment
SET tblDepartment.deptLevel = 2
FROM tblDepartment
INNER JOIN tblDepartment as tblParent
ON tblParent.departmentNumber = tblDepartment.parentDepartmentNumber
WHERE tblParent.deptLevel = 1

continue step 2 for all levels

referencing--
SELECT DISTINCT
level1.departmentNumber AS departmentNumber1,
Level2.departmentNumber AS departmentNumber2,
Level3.departmentNumber AS departmentNumber3,
Level4.departmentNumber AS departmentNumber4,
Level5.departmentNumber AS departmentNumber5,
Level6.departmentNumber AS departmentNumber6,
Level7.departmentNumber AS departmentNumber7
FROM tblDepartment AS level7
RIGHT
JOIN
tblDepartment AS level6 ON
level7.PARENT_departmentNumber = Level6.departmentNumber
RIGHT JOIN
tblDepartment AS level5 ON
level6.PARENT_departmentNumber = Level5.departmentNumber
RIGHT JOIN
tblDepartment AS level4 ON
level5.PARENT_departmentNumber = Level4.departmentNumber
RIGHT JOIN
tblDepartment AS level3 ON
level4.PARENT_departmentNumber = Level3.departmentNumber
RIGHT JOIN
tblDepartment AS level2 ON
level3.PARENT_departmentNumber = Level2.departmentNumber
RIGHT JOIN
tblDepartment AS level1 ON
level2.PARENT_departmentNumber = Level1.departmentNumber|||Appologies BUT the field

"PARENT_departmentNumber" in the Joins should read

"parentDepartmentNumber"

I inadvertently copied my code incorrently|||What if you have 10 levels?

You actually need a loop to go to n levels...

but if you wait awhile...Yukon will have this...

And there are many ways to denormalize this...

Check this out:

http://www.sqlteam.com/item.asp?ItemID=8866|||Brett,

I agree. My first comment was that it was not an elegant solution.

I actually use the structure defined in the artice from your link to create a lineage (a DN in my case) for populating an X500 directory.

For me it was simple because the directory has a max of 6 levels off of the root DN.

I wish I had seen this article before I spent the time devloping the logic myself. ;)

can i Display Database report from SQL 2005 using .Net 2005 ?

Can I generate database report from SQL 2005 using .Net 2005 ?

I want report contain the following:

1. How many schemas I have in database.

2. Display schemas list with its information.

I need the SQL Statement or query that return the above result.

Best regards,

Z. A. A.

Hi,

this should be something like:

Select * FROM sys.schemas

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

can I bind a Label control to a SqlDataSource to display one field from a table?

Hi,

What's the 'new' declarative way to fetch one field from a SQL table and display the result in a Label control?

I have a users table with a fullname field. I want to query the table and retrieve the fullname where the usertable.username = Session("loggedinuser")

I can make the SqlDataSource ok that has the correct Select query, but not sure how to bind the label to that.

thanks,
BruceThe only way I know of, and I hope there is a better way, but I don't know it is to put a formview control on the page. Bind the formview to the datasource, and place the label within the formview, then databind the label to the fullname column.|||Thanks Motley,

I did end up doing that approach. It works ok, just feels a little cumbersome..but maybe it's the correct way for now.

I just feel like there's a missing control that lets you easily bind to a singe output parameter from a query/stored procedure.

-Bruce|||I agree bruce, but unfortunely I lost my inventation to the meeting where the MS team discussed this|||If you're wanting to retrieve the value from a return (output) parameter - that is possible. Take a look at the SqlDataSource controls Selected event. In that event, after the select has taken place, you can retrieve the output paramter's value...

Protected Sub SqlDataSource1_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Selected
Me.txtWhatEver.Text = e.Command.Parameters("parameterNameHere").Value
End Sub

This might help too... http://www.asp.net/QuickStart/util/srcview.aspx?path=~/aspnet/samples/data/RetValAndOutputParams.src

I do wish there were a way to retrieve data other than what can be returned in an output parameter... like a field in a DataRow for example. The data controls (like a FormView) are doing this somehow... so why can't I get access to that some functionality in code-behind?

Can I add an autonumber to a view?

Hi, I wish to build a datagrid, in an ASP.NET web page, in which I can display a row from a table and when I press a button it will move forward one row. The table is not currently sorted and I need it to be alpahbetical by surname so I thought if I created a view I could sort the table alphabetically on the 'Surname' column. I know that ADO.NET when used in disconnected mode doesn't have a cursor which remembers the last row it was on so I thought if I could create an autonumber on the view then I could use this in the 'where' clause to tell SQLserver which line I wished to select by taking the previous number and adding one. Is this the best way to do this and if so could some one give advice on how to write the line of the query that will add in an autonumber column

thanks
Johnwhy don't you just use the standard paging method with a pagesize of one?

(no views, don't have autonumbering unless you feel like generating one from a cursor)|||The table has over 100,000 rows in it so I don't wish to pull all these into a dataset as I thought this would be far too big if lots of users were going to use the website at once. I thought using paging it stepped through a dataset not the actual SQL table. I just want a way that I can find the next row each time.

I will look at the paging method. thanks

John

Can drill through reports display parameters

I have a top level report in which I am select a particular run. The id for
that run is passed to a child report which uses the id from that run to
generate a list of possible values to generate the drill through report. My
problem is that I seem to have to set both values from the top level report
and then the second report does not show the parameters. If I browse
directly to the second report then I have the option of entering parameters.
Should I be able to see parameters when drill into a reportI forgot to mention that when I am in the designer the parameters are always
visible, but when I deploy to website and try to browse the report, that is
when they ar not visible.
"slumley" wrote:
> I have a top level report in which I am select a particular run. The id for
> that run is passed to a child report which uses the id from that run to
> generate a list of possible values to generate the drill through report. My
> problem is that I seem to have to set both values from the top level report
> and then the second report does not show the parameters. If I browse
> directly to the second report then I have the option of entering parameters.
> Should I be able to see parameters when drill into a report|||I have the same problem but only with the latest CTP (Sept), it worked fine
in the June version.
"slumley" wrote:
> I forgot to mention that when I am in the designer the parameters are always
> visible, but when I deploy to website and try to browse the report, that is
> when they ar not visible.
> "slumley" wrote:
> > I have a top level report in which I am select a particular run. The id for
> > that run is passed to a child report which uses the id from that run to
> > generate a list of possible values to generate the drill through report. My
> > problem is that I seem to have to set both values from the top level report
> > and then the second report does not show the parameters. If I browse
> > directly to the second report then I have the option of entering parameters.
> >
> > Should I be able to see parameters when drill into a report

Can have two measure values?

Hi,
Can we have two measure values in a Matrix?
for example that I want to display both unit Sales and Sales Count in a
Matrix.Yes you can. Just right-click on a matrix cell and select "Add Column" or
"Add Row". You can then calculate the sales count in the new cell.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:OY%23xy6nlEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Can we have two measure values in a Matrix?
> for example that I want to display both unit Sales and Sales Count in a
> Matrix.
>|||Why is it that when you add more than one value to a matrix report like this,
it will no longer export to HTMLOWC?
"Robert Bruckner [MSFT]" wrote:
> Yes you can. Just right-click on a matrix cell and select "Add Column" or
> "Add Row". You can then calculate the sales count in the new cell.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "ad" <ad@.wfes.tcc.edu.tw> wrote in message
> news:OY%23xy6nlEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> > Can we have two measure values in a Matrix?
> > for example that I want to display both unit Sales and Sales Count in a
> > Matrix.
> >
> >
>
>|||Please read this section in BOL:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_dc_v1_43vn.asp
When adding additional "measures", you add static columns or static rows to
the matrix.
When a report is rendered to HTML with OWC, and you use static rows /
columns then you might run into one of these limitations:
* the matrix has both static columns and static rows
* dynamic columns or rows are nested inside static columns or rows
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"clortex" <clortex@.discussions.microsoft.com> wrote in message
news:657353CE-0AEE-4EFD-B795-005F0BD06DB0@.microsoft.com...
> Why is it that when you add more than one value to a matrix report like
this,
> it will no longer export to HTMLOWC?
> "Robert Bruckner [MSFT]" wrote:
> > Yes you can. Just right-click on a matrix cell and select "Add Column"
or
> > "Add Row". You can then calculate the sales count in the new cell.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "ad" <ad@.wfes.tcc.edu.tw> wrote in message
> > news:OY%23xy6nlEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > > Hi,
> > > Can we have two measure values in a Matrix?
> > > for example that I want to display both unit Sales and Sales Count in
a
> > > Matrix.
> > >
> > >
> >
> >
> >|||Thank, it is good guestion to me!
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:#WEWpFulEHA.2588@.TK2MSFTNGP12.phx.gbl...
> Please read this section in BOL:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_dc_v1_43vn.asp
> When adding additional "measures", you add static columns or static rows
to
> the matrix.
> When a report is rendered to HTML with OWC, and you use static rows /
> columns then you might run into one of these limitations:
> * the matrix has both static columns and static rows
> * dynamic columns or rows are nested inside static columns or rows
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "clortex" <clortex@.discussions.microsoft.com> wrote in message
> news:657353CE-0AEE-4EFD-B795-005F0BD06DB0@.microsoft.com...
> > Why is it that when you add more than one value to a matrix report like
> this,
> > it will no longer export to HTMLOWC?
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> > > Yes you can. Just right-click on a matrix cell and select "Add Column"
> or
> > > "Add Row". You can then calculate the sales count in the new cell.
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > >
> > > "ad" <ad@.wfes.tcc.edu.tw> wrote in message
> > > news:OY%23xy6nlEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > > > Hi,
> > > > Can we have two measure values in a Matrix?
> > > > for example that I want to display both unit Sales and Sales Count
in
> a
> > > > Matrix.
> > > >
> > > >
> > >
> > >
> > >
>

Can AS-Actions be used in RS 2005?

Hello,
we defined a drillthrough action in AS to display some detail-information:
If we browse a cube inside Management Studio and do a right click on a
corresponding value, the defined action shows up in the context menu, so we
can display a list with the details. Now we want to have the same thing
inside reporting services: Just click on a value and show the corresponding
details. Is it possible to use the "resultset" of the AS-Action inside a
report? Where can we find detailed information on how to do that?
Regards,
R. Watermannshort answer: no
in RS you have to create your own navigation which is independent from the
cube definition.
RS is a reporting tool not a data navigation tool (like OWC proclarity
etc...).
maybe using some code you can retrieve these actions, but by default RS
can't do this.
"Ralph Watermann" <We.Want@.NoSpam.de> wrote in message
news:OIaULjrGGHA.3624@.TK2MSFTNGP09.phx.gbl...
> Hello,
> we defined a drillthrough action in AS to display some detail-information:
> If we browse a cube inside Management Studio and do a right click on a
> corresponding value, the defined action shows up in the context menu, so
> we can display a list with the details. Now we want to have the same thing
> inside reporting services: Just click on a value and show the
> corresponding details. Is it possible to use the "resultset" of the
> AS-Action inside a report? Where can we find detailed information on how
> to do that?
> Regards,
> R. Watermann
>

can anyone show me how to combine these two SQL queries into one

Hello-

i have a fairly big SQL query that is used to display data into a datagrid. Each query grabs data from two seperate databases. Is there anyway to combine these queries into one so all the data appears in 1 datagrid and not 2.

here is the 1st query:

SQL = "SELECT sum(case when month(pb_report_shippers.shipper_date_time) = 1 then pb_report_shippers_lots.quantity else 0 end) as Jan ,sum(case when month(pb_report_shippers.shipper_date_time) = 2 then pb_report_shippers_lots.quantity else 0 end) as Feb ,sum(case when month(pb_report_shippers.shipper_date_time) = 3 then pb_report_shippers_lots.quantity else 0 end) as Mar ,sum(case when month(pb_report_shippers.shipper_date_time) = 4 then pb_report_shippers_lots.quantity else 0 end) as Apr ,sum(case when month(pb_report_shippers.shipper_date_time) = 5 then pb_report_shippers_lots.quantity else 0 end) as May ,sum(case when month(pb_report_shippers.shipper_date_time) = 6 then pb_report_shippers_lots.quantity else 0 end) as Jun ,sum(case when month(pb_report_shippers.shipper_date_time) = 7 then pb_report_shippers_lots.quantity else 0 end) as Jul ,sum(case when month(pb_report_shippers.shipper_date_time) = 8 then pb_report_shippers_lots.quantity else 0 end) as Aug ,sum(case when month(pb_report_shippers.shipper_date_time) = 9 then pb_report_shippers_lots.quantity else 0 end) as Sept ,sum(case when month(pb_report_shippers.shipper_date_time) = 10 then pb_report_shippers_lots.quantity else 0 end) as Oct ,sum(case when month(pb_report_shippers.shipper_date_time) = 11 then pb_report_shippers_lots.quantity else 0 end) as Nov ,sum(case when month(pb_report_shippers.shipper_date_time) = 12 then pb_report_shippers_lots.quantity else 0 end) as Dec FROM pb_customers INNER JOIN pb_jobs ON pb_customers.customer_id = pb_jobs.customer_id INNER JOIN pb_recipes_sub_recipes ON pb_jobs.recipe_id = pb_recipes_sub_recipes.recipe_id INNER JOIN pb_jobs_lots ON pb_jobs.job_id = pb_jobs_lots.job_id INNER JOIN pb_sub_recipes ON pb_recipes_sub_recipes.sub_recipe_id = pb_sub_recipes.sub_recipe_id INNER JOIN pb_report_shippers_lots ON pb_jobs_lots.intrack_lot_id = pb_report_shippers_lots.intrack_lot_id INNER JOIN pb_report_shippers ON pb_report_shippers_lots.job_id = pb_report_shippers.job_id AND pb_report_shippers_lots.shipper_id = pb_report_shippers.shipper_id WHERE pb_customers.customer_deleted <> 1 AND pb_jobs.job_deleted <> 1 AND pb_jobs_lots.lot_deleted <> 1 AND pb_report_shippers.shipper_date_time between cast('01/01/2003 00:01AM' as datetime) and cast('12/31/2003 23:59PM' as datetime)"

Here is the 2nd query:

SQL = "SELECT ISNULL(sum(case when month(nonconformance.nc_date) = 1 then nonconformance.nc_wafer_qty else 0 end),0) as Jan , ISNULL(sum(case when month(nonconformance.nc_date) = 2 then nonconformance.nc_wafer_qty else 0 end),0) as Feb ,ISNULL(sum(case when month(nonconformance.nc_date) = 3 then nonconformance.nc_wafer_qty else 0 end),0) as Mar ,ISNULL(sum(case when month(nonconformance.nc_date) = 4 then nonconformance.nc_wafer_qty else 0 end),0) as Apr , ISNULL(sum(case when month(nonconformance.nc_date) = 5 then nonconformance.nc_wafer_qty else 0 end),0) as May ,ISNULL(sum(case when month(nonconformance.nc_date) = 6 then nonconformance.nc_wafer_qty else 0 end),0) as Jun ,ISNULL(sum(case when month(nonconformance.nc_date) = 7 then nonconformance.nc_wafer_qty else 0 end),0) as Jul ,ISNULL(sum(case when month(nonconformance.nc_date) = 8 then nonconformance.nc_wafer_qty else 0 end),0) as Aug ,ISNULL(sum(case when month(nonconformance.nc_date) = 9 then nonconformance.nc_wafer_qty else 0 end),0) as Sept ,ISNULL(sum(case when month(nonconformance.nc_date) = 10 then nonconformance.nc_wafer_qty else 0 end),0) as Oct ,ISNULL(sum(case when month(nonconformance.nc_date) = 11 then nonconformance.nc_wafer_qty else 0 end),0) as Nov ,ISNULL(sum(case when month(nonconformance.nc_date) = 12 then nonconformance.nc_wafer_qty else 0 end),0) as Dec FROM nonconformance INNER JOIN nc_department on nonconformance.department_id = nc_department.department_id INNER JOIN nc_major_category ON nonconformance.major_category_id = nc_major_category.major_category_id AND nonconformance.status_id <> '5' WHERE nc_department.scrap_category = '1' AND nonconformance.nc_date between cast('01/01/2004 00:01AM' as datetime) and cast('12/31/2004 23:59PM' as datetime)"

I know there has to be someway to combine these into 1. The issue I have is they are in different databases.

ANY HELP would be appreciated.Hi, Andrew,

You may use union query to merge the two sql statements. Make sure they return the same number of columns. If not, add some dummy columns to make them equal(dummy column could be 0 or empty string '')

the issue of diffeent database can be resolved using sytax like
db_name..table_name.
Kind like fully qualified name:)

Take you first query as example: replace 'pb_customers' with 'DatabaseName..pb_customers'.

The query is too big. I am sorry I can't prototype it in my computer. You have to try youself. Let me know if got error message.

Good Luck

-David J.|||thanks figured it out.

Can a datagrid display more than one table?

See this picture. I only see table '2' can add more table? how?
conn.Open()
Dim sql(3) As String

sql(0) = "select * from products;"
sql(1) = "select * from orders;"
sql(2) = "select * from orders;"
sql(3) = "select * from orders;"

Dim da As System.Data.OleDb.OleDbDataAdapter
Dim ds As DataSet
Dim dt As DataTable
Dim i As Integer

For i = 0 To sql.GetUpperBound(0)
Try
da = New OleDb.OleDbDataAdapter(sql(i), conn)
Catch e As Exception
MessageBox.Show(e.Message)
End Try
Try
ds = New DataSet("dsTable")
dt = New DataTable(i.ToString)
da.Fill(dt)
'ds.Tables.Add(dt)
ds.Tables.Add(dt)
ds.Merge(dt)
'grd.SetDataBinding(ds, i.ToString)
'MessageBox.Show(ds.Tables(i).TableName)
grd.DataSource = ds
Catch eds As Exception
MessageBox.Show(eds.Message)
End Try
MessageBox.Show(i)
Next
conn.Close()
conn = Nothinglet me answer it myself.

for i = 0 to n
da.Fill(dt)
ds.Merge(dt)
next
datagrid.datasource = ds

Camel Case

I need to display text in camel case e.g. How Are You....Because of some deign limitation i can't make chnages in my SP. So i need to do it ar SSRS end....Pl let me know if y know the ans...Thanks

Hi Amit,

You can write custom code(embedded code) to distplay the text in Came Case. To do so, go to Report --> Report properties --> Code tab and then write a function that will convert the input text to camel case as required.

The Function for this purpose is shown below

Code Snippet

Public Function ConvertToCamelCase(inputStr) as String
dim inputStrArr
dim tempWord
dim outputStr
dim i
inputStrArr = split(inputStr," ")

for i=0 to UBound(inputStrArr)
tempWord =inputStrArr(i)
tempWord = Ucase(Left( tempWord ,1)) & Right(tempWord,len(tempWord) -1)
if outputStr="" then
outputStr =tempWord
else
outputStr = outputStr & " " & tempWord

end if

Next
return outputStr
End Function

Then right click on the cell where u want to print this string and enter the following expression

Code Snippet

=Code.ConvertToCamelCase("how are you")

Hope it helps.

Rajiv

|||

public function tocamelcase(inputstring) as string
dim inputstrarr
dim outputstr
dim tempstr
dim i
inputstrarr=split(inputstring," ")
for i=0 to UBound(InputStrArr)
tempstr=InputstrArr(i)
tempstr=UCase(left(tempstr,1)) & lcase(right(tempstr,len(tempstr)-1))
if outputstr=" " then
outputstr=tempstr
Else
outputstr=outputstr & " " & tempstr
End if
Next
return outputstr
End fuction

It is throwing Error

An unexpected error occurred while compiling expressions. Native compiler return value: ‘[BC30289] Statement cannot appear within a method body. End of method assumed.’.

Can you please corrrect this

Thank you

Raj Deep.A

|||

Hi Raj Deep,

The above error is occuring because of the Typo error in the code. It should be End Function and not End fuction. I have rectified the error below.

Code Snippet

public function tocamelcase(inputstring) as string
dim inputstrarr
dim outputstr
dim tempstr
dim i
inputstrarr=split(inputstring," ")
for i=0 to UBound(InputStrArr)
tempstr=InputstrArr(i)
tempstr=UCase(left(tempstr,1)) & lcase(right(tempstr,len(tempstr)-1))
if outputstr=" " then
outputstr=tempstr
Else
outputstr=outputstr & " " & tempstr
End if
Next
return outputstr
End function

Best Regards,

Rajiv

|||

I am not able to perform below step

=Code.ConvertToCamelCase("how are you")

As soon as i type =Code. (i can't see custom function in the list)....Any comments...

Amit

|||

Hi Amit,

It is O.K. even if the custom function doesn't appear in the list, type it manually and the report should work fine.

Though I am not sure why it doesn't come in the list, even I didn't get my function to come in the list.

Rajiv

|||

ya i tried and it is not working....

=code.ConvertToCamelCase(Fields!salesman_name.Value)...

It's showing in upper case only...

Thanks for your help

|||

ya i tried and it is not working....

=code.ConvertToCamelCase(Fields!salesman_name.Value)...

It's showing in upper case only...

Thanks for your help

|||

Sorry ,i didn't observe that much keenly.

Thank you Rajiv,Code is Working.

|||

Looks like the data in your field is already uppercase. This will uppercase the first letter, and lowercase the rest. I think Rajiv missed the part in red in his first post.

Try this:

Code Snippet

Public Function ConvertToCamelCase(inputStr) as String

dim inputStrArr

dim tempWord

dim outputStr

dim i

inputStrArr = Split(inputStr, " ")

for i = 0 to UBound(inputStrArr)

tempWord = inputStrArr(i)

tempWord = UCase(Left(tempWord, 1)) & LCase(Right(tempWord, len(tempWord) - 1))

if outputStr = "" then

outputStr = tempWord

else

outputStr = outputStr & " " & tempWord

end if

Next

return outputStr

End Function

Then, you can use this in your field's expression.

Code Snippet

=code.ConvertToCamelCase(Fields!salesman_name.Value)

Hope this helps.

Jarret

Calling XML web service from a reporting services report (RDL)

Hi,

I wanted to display data returned by xml webservice in a sql reporting service report(RDL).

How I can implement the same, Please let me know the steps.

Thanks,

Anees

Check this: http://msdn.microsoft.com/library/en-us/dnsql90/html/RepServXMLDS.asp

Calling XML web service from a reporting services report (RDL)

Hi,

I wanted to display data returned by xml webservice in a sql reporting service report(RDL).

How I can implement the same, Please let me know the steps.

Thanks,

Anees

Check this: http://msdn.microsoft.com/library/en-us/dnsql90/html/RepServXMLDS.asp