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 ...
>
Showing posts with label item. Show all posts
Showing posts with label item. Show all posts
Thursday, March 29, 2012
Sunday, March 11, 2012
Can I access my data source in Custom Report Item during design mode?
Hi,
I'm developing a Custom Report Item and would like to fill my properties dialog in design mode with real data. I have the name of the data source and the command text for the corresponding report dataset, read from the report design.
Is it possible to create an ado.net dataset so that I can display and use real data in my properties dialog box? First problem I have is to get the connection string from the data source string. I found no docs for that, especially the Microsoft.ReportingServices.DataExtensions.ReportDataSource class is not documented. Am I on the right way with that?
Thomas
Check out.. http://msdn.microsoft.com/msdnmag/issues/06/10/SQLServer2005/default.aspx 
Sorry,
I can't see where that relates to my question. Did I miss the point?
After rereading the article, I didn't find any word on how to get actual data values in design mode.
Maybe I didn't state clear enough what I want to obtain:
I would like to get actual data values from the dataset in design mode in order to use them for a properties dialog. I have access to the sql statement from reportDataSet.CommandText already and I know the datasource name (that somehow is an alias for the db connection). What's missing is how to get the connection string from the datasource - then I can open the connection and read the data using ado.net.
Thomas
|||For connection string:
check the DataSourceDefinition class in Reporting Service web service
Can I access my data source in Custom Report Item during design mode?
Hi,
I'm developing a Custom Report Item and would like to fill my properties dialog in design mode with real data. I have the name of the data source and the command text for the corresponding report dataset, read from the report design.
Is it possible to create an ado.net dataset so that I can display and use real data in my properties dialog box? First problem I have is to get the connection string from the data source string. I found no docs for that, especially the Microsoft.ReportingServices.DataExtensions.ReportDataSource class is not documented. Am I on the right way with that?
Thomas
Check out.. http://msdn.microsoft.com/msdnmag/issues/06/10/SQLServer2005/default.aspx
Sorry,
I can't see where that relates to my question. Did I miss the point?
After rereading the article, I didn't find any word on how to get actual data values in design mode.
Maybe I didn't state clear enough what I want to obtain:
I would like to get actual data values from the dataset in design mode in order to use them for a properties dialog. I have access to the sql statement from reportDataSet.CommandText already and I know the datasource name (that somehow is an alias for the db connection). What's missing is how to get the connection string from the datasource - then I can open the connection and read the data using ado.net.
Thomas
|||For connection string:
check the DataSourceDefinition class in Reporting Service web service
|||Did you ever make any progress on this? I'm trying to get to the Connection String property of the data source from my Custom Report Item code.Any direction is appreciated.
Thanks
J
Friday, February 24, 2012
Can an Application be run using a Textbox Hyperlink?
I'd like to be able to run a client-side application by clicking on a text
item in a report, but none of the available Hyperlink actions on the
Navigation properties tab seem to apply. Is there any way to run something
having a filename and command line argument like
"C:\Program Files\TestApp\TestApp.exe param"?
The "param" would be the value of the TextBox.
Thanks!Hi Don,
Thank you for your post!
Based on my scope, I don't think you could run the client application
directly in the Hyperlink.
The workaround is write a html page which include some javascript to run
the client application and you could add the html page url in the Hyperlink.
Hope this will be helpful for you to resolve this issue.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei Lu,
It looks like I'd have to pass the value of the textbox as a parameter for
the html page that contains the javascript. Could you point me to an example
where this technique has been used to run a client application?
Thanks,
Don
"Wei Lu" wrote
> Based on my scope, I don't think you could run the client application
> directly in the Hyperlink.
> The workaround is write a html page which include some javascript to run
> the client application and you could add the html page url in the
Hyperlink.
> Hope this will be helpful for you to resolve this issue.
> Sincerely,
> Wei Lu|||Hi Don,
Thank you for the update.
Here is the sample to run your local IIS Manager:
&
var WshShell = new ActiveXObject("WScript.Shell");
var oExec = WshShell.Exec("C:\\WINDOWS\\system32\\inetsrv\\inetmgr.exe");
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei Lu,
Thanks for the example; works like a charm! Even permits passing a command
line argument to the local exe application.
Don
"Wei Lu" wrote in message
> &
>
>
>
>
>
> var WshShell = new ActiveXObject("WScript.Shell");
> var oExec = WshShell.Exec("C:\\WINDOWS\\system32\\inetsrv\\inetmgr.exe");
>
>
>
>|||Hi Don,
Glad to hear the information is helpful.
If you have any questions or concerns, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
item in a report, but none of the available Hyperlink actions on the
Navigation properties tab seem to apply. Is there any way to run something
having a filename and command line argument like
"C:\Program Files\TestApp\TestApp.exe param"?
The "param" would be the value of the TextBox.
Thanks!Hi Don,
Thank you for your post!
Based on my scope, I don't think you could run the client application
directly in the Hyperlink.
The workaround is write a html page which include some javascript to run
the client application and you could add the html page url in the Hyperlink.
Hope this will be helpful for you to resolve this issue.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei Lu,
It looks like I'd have to pass the value of the textbox as a parameter for
the html page that contains the javascript. Could you point me to an example
where this technique has been used to run a client application?
Thanks,
Don
"Wei Lu" wrote
> Based on my scope, I don't think you could run the client application
> directly in the Hyperlink.
> The workaround is write a html page which include some javascript to run
> the client application and you could add the html page url in the
Hyperlink.
> Hope this will be helpful for you to resolve this issue.
> Sincerely,
> Wei Lu|||Hi Don,
Thank you for the update.
Here is the sample to run your local IIS Manager:
&
var WshShell = new ActiveXObject("WScript.Shell");
var oExec = WshShell.Exec("C:\\WINDOWS\\system32\\inetsrv\\inetmgr.exe");
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei Lu,
Thanks for the example; works like a charm! Even permits passing a command
line argument to the local exe application.
Don
"Wei Lu" wrote in message
> &
>
>
>
>
>
> var WshShell = new ActiveXObject("WScript.Shell");
> var oExec = WshShell.Exec("C:\\WINDOWS\\system32\\inetsrv\\inetmgr.exe");
>
>
>
>|||Hi Don,
Glad to hear the information is helpful.
If you have any questions or concerns, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to:
Posts (Atom)