|
basically i m creating vb.net window application which can show the result in rdlc report...
at the back end it is SQL server 2008.
Is there any easy way to fill RDLC tablix based on row and column grouping.
The situation is as follows.
I have a table in SQL server like this
Course Adcat sub1 sub2 sub3 sub4 sub5
Ba1 Reg HL Hlit His PS Env
Ba1 Reg EL Hlit HS Socio Env
Ba1 Pri HL His Hlit HS Env
Ba1 Reg SL Socio Slit PS Env
This is just like a different course student registered in different categories and opted different subjects and it is stored in five columns as shown above.
I just want to see the report in RDLC like this
Course subject Reg Pri
BA1 HL 1 1
EL 1 0
SL 1 0
Hlit 2 1
His 1 2
PS 1 1
HS 1 0
Socio 2 0
AND SO NO.
I just want to count the number of subjects from above mention five columns.
Please help me for this
i am using VB.NET window application and Sql server 2008. Thanx in advance.
|
|
|
|
|
Your output doesn't seem to match your input data, but something like this should work:
SELECT
T.Course,
S.subject,
SUM(CASE T.Adcat WHEN 'Reg' THEN 1 ELSE 0 END) As Reg,
SUM(CASE T.Adcat WHEN 'Pri' THEN 1 ELSE 0 END) As Pri
FROM
YourTable As T
CROSS APPLY
(
VALUES (sub1), (sub2), (sub3), (sub4), (sub5)
) As S (subject)
GROUP BY
T.Course,
S.subject
ORDER BY
T.Course,
S.subject
;
http://sqlfiddle.com/#!3/095e8/1[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
but it is in vb.net window application.
where to use the cade????????
|
|
|
|
|
Use the query to create a view in your SQL database. Add the view to the report as a dataset.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
thanks for quick response..("_")
is there a way to use grouping in rdlc report tablix.
|
|
|
|
|
|
sorry but its not i am looking for.....
|
|
|
|
|
The query I posted will produce the output you said you wanted.
If that's not what you're looking for, then you'll need to explain what you are looking for.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
but how the output comes to my application
because i am using course form my form combobox.
please help
modified 5-Feb-15 11:27am.
|
|
|
|
|
DHARM PAL wrote: but how the output comes to my application
In exactly the same way as any other RDLC report.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
thank for response..
i have created a sql query and saved it as subsummary in view.
now how to call this from my vb.net application using button click.
a code i was used are:
Public Sub Show_SubjectSummary()
sqlQRY = "SELECT * FROM tblstudetail where" _
& "[session] = '2015' AND" _
& "[course] = 'B.A. I' AND" _
& "[ADstatus] LIKE 'OK'"
ds = New DataSet
da = New SqlDataAdapter(sqlQRY, Conn)
da.Fill(ds, "tblstudetail")
RDLCreportview.ReportViewer1.LocalReport.ReportEmbeddedResource = Application.StartupPath & "\Report\SubjectSummary.rdlc"
sReportDataSource.Name = "DataSet1"
sReportDataSource.Value = ds.Tables(0)
RDLCreportview.ReportViewer1.LocalReport.ReportPath = Application.StartupPath & "\Report\SubjectSummary.rdlc"
RDLCreportview.ReportViewer1.SetDisplayMode(DisplayMode.PrintLayout)
RDLCreportview.ReportViewer1.RefreshReport()
ds.Dispose()
ds = Nothing
RDLCreportview.ShowDialog()
RDLCreportview.Dispose()
Conn.Close()
End Sub
|
|
|
|
|
Just change the query that you're loading the data from:
da = new SqlDataAdapter("SELECT * FROM subsummary WHERE [course] = @Course", Conn)
da.SelectCommand.Parameters.AddWithValue("@Course", ddlCourse.SelectedValue)
ds = New DataSet()
da.Fill(ds, "subsummary")
sReportDataSource.Name = "DataSet1"
sReportDataSource.Value = ds.Tables(0)
RDLCreportview.ReportViewer1.LocalReport.ReportPath = Application.StartupPath & "\Report\SubjectSummary.rdlc"
RDLCreportview.ReportViewer1.SetDisplayMode(DisplayMode.PrintLayout)
RDLCreportview.ReportViewer1.RefreshReport()
ds.Dispose()
ds = Nothing
RDLCreportview.ShowDialog()
RDLCreportview.Dispose()
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
i applied like this:
Public Sub Show_SubjectSummary()
da = New SqlDataAdapter("SELECT * FROM viewsubsummary WHERE [course] = 'B.A. I'", Conn)
ds = New DataSet()
da.Fill(ds, "viewsubsummary")
sReportDataSource.Name = "DataSet1"
sReportDataSource.Value = ds.Tables(0)
RDLCreportview.ReportViewer1.LocalReport.ReportPath = Application.StartupPath & "\Report\SubjectSummary.rdlc"
RDLCreportview.ReportViewer1.SetDisplayMode(DisplayMode.PrintLayout)
RDLCreportview.ReportViewer1.RefreshReport()
ds.Dispose()
ds = Nothing
RDLCreportview.ShowDialog()
RDLCreportview.Dispose()
End Sub
but it is showing error:
A data source instance has not been supplied for the data source'DataSet1’
viewsubsummary = my sqlview as u suggested
tblstudetail= my original table where all records stored
kindly help
|
|
|
|
|
According to the error message, you don't have a link between the report and the data source. I would usually expect to see the data source added to the report:
RDLCreportview.ReportViewer1.LocalReport.DataSources.Clear()
RDLCreportview.ReportViewer1.LocalReport.DataSources.Add(New ReportDataSource("DataSet1", ds.Tables(0)))
If it still doesn't work, then check that the report file you're loading is the one you've updated to use the new query.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
thanks man its working...
but only one problem, it is showing one blank column at the top and also a count value like:
course reg pri
empty 0 2
B.a.I 20 1
like this
kindly help me i am on last stage
waiting 4 ur response
|
|
|
|
|
Try running the query in SQL Server Management Studio:
SELECT * FROM viewsubsummary WHERE [course] = 'B.A. I'
If you get the blank row there, then there's something wrong with the view definition. The query I posted earlier works as expected with the sample data you provided.
If you don't get the blank row in SSMS, then there's something wrong in your report.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
i found that there is two pri column rows are empty.
is this the reason it is counting.
if so how can i avoid this....
|
|
|
|
|
OK, change the query in the view to exclude empty subjects:
SELECT
T.Course,
S.subject,
SUM(CASE T.Adcat WHEN 'Reg' THEN 1 ELSE 0 END) As Reg,
SUM(CASE T.Adcat WHEN 'Pri' THEN 1 ELSE 0 END) As Pri
FROM
YourTable As T
CROSS APPLY
(
VALUES (sub1), (sub2), (sub3), (sub4), (sub5)
) As S (subject)
WHERE
S.subject Is Not Null
And
S.subject != ''
GROUP BY
T.Course,
S.subject
ORDER BY
T.Course,
S.subject
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
|
thanks man..
u made my day..
i was searching and trying this from one week.
its becoz i am not aware of view in sql server..
1 question if u have time??
how can i add statement to also count number of students in every category..
like this:
Course subject Reg Pri
BA1 HL 1 1
EL 1 0
SL 1 0
Hlit 2 1
His 1 2
PS 1 1
HS 1 0
Socio 2 0
Count adcat 2 1
where my column adcat store reg, pri.
One more Question:
is there a way to add where clause based on column ADstatus and OLstatus AND EXstatus programetically because i have three combobox in my form which is having all status data like 'OK'/ 'NO'/'0'.
now based on status selected from these three comboboxes COUNT and fill the report.
|
|
|
|
|
DHARM PAL wrote: how can i add statement to also count number of students in every category..
It's not clear from your example output where that data is coming from.
DHARM PAL wrote: is there a way to add where clause based on column ADstatus and OLstatus AND EXstatus programetically
Not easily with a grouped view, since you presumably want to filter on those columns before grouping the data. You could try using a stored procedure instead.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
thanks sir for helping me.......
i have solve the issue...
thanks again..
expecting help for the next problem....
thanks..... 
if u have time please reply
|
|
|
|
|
Hello,
I am developing a simple website where I click on a button and initiate a Remote Desktop Connection to a remote host. The command button will have the hostname of the remote system. How do I start MSTSC.exe and pass it the hostname I want to connect to?
Thanks in advanc,
Rich
|
|
|
|
|
|
I have a dll written for C# and I'm trying to use on my vb.net project.
I add the reference , and also I add the Imports.......
But when I try to call function and methods on VB.net , I get errors. ( on C# project everything works ok )
Just for information , this is an open source dll project in this link : https://github.com/MikaelEliasson/EntityFramework.Utilities
that can be added using NUGET in any visual studio .net applications.
... and I'm trying to use IncludeEFU method.
Any solution will be very helpful.
Thank you !
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
