|
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 !
|
|
|
|
|
...sigh... Why did you leave out the error message from your post? It's only the most important piece of information used to solved the problem.
|
|
|
|
|
Thank you !
From the dll in the link I posted above ( I repeat is an open source and everybody can see the code ) ,
I try to use the "IncludeEFU" method:
In the link I have posted before , is an example that in a C# project works :
var result = db.Contacts
.IncludeEFU(db, x => x.PhoneNumbers
.Where(n => n.Number == "10134")
.OrderBy(p => p.ContactId)
.ThenByDescending(p => p.Number))
.ToList();
I try to use this code on my vb.net project :
Dim result = db.Contacts _
.IncludeEFU(db, Function(x) x.PhoneNumbers _
.Where(Function(n) n.Number = "10134") _
.OrderBy(Function(p) p.ContactId) _
.ThenByDescending(Function(p) p.Number)).ToList()
But I'm getting thiserror :
An unhandled exception of type 'System.ArgumentException' occurred in EntityFramework.Utilities.dll
Additional information: Could not find a MemberExpression
What is the problem ?
Thank you !
|
|
|
|
|
The error sounds like it's caused by a difference between how C# and VB.NET compile lambda expressions. It sounds like that project hasn't been tested with VB.NET lambdas, and has a bug.
The best place to report problems with that project would be on the project's "issues" page:
https://github.com/MikaelEliasson/EntityFramework.Utilities/issues[^]
In fact, it looks like someone's beaten you to it:
Hello !
I'm using VB.net and entity framework 6.1
I try to use the IncludeEFU.
This is the Code :
Dim MyList as IeNumerable(Of Article)
MyList= context.Articles.IncludeEFU(context, Function(t2) t2.F1_item).ToList
But I'm getting this error :
An unhandled exception of type 'System.ArgumentException' occurred in EntityFramework.Utilities.dll
Additional information: Could not find a MemberExpression
( note that the standart .Include() command works correctly ).
What can I do ?
Thank you !
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
These are the codes in that dll related to my method :
Quote: using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.Core.Metadata.Edm;
using System.Data.Entity.Core.Objects;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
namespace EntityFramework.Utilities
{
public static class EFQueryHelpers
{
///
/// Loads a child collection in a more efficent way than the standard Include. Will run all involved queries as NoTracking
///
/// <typeparam name="T">
/// <typeparam name="TChild">
/// <param name="query" />
/// <param name="context" />
/// <param name="collectionSelector" />The navigation property. It can be filtered and sorted with the methods Where,OrderBy(Descending),ThenBy(Descending)
/// <returns>
public static EFUQueryable<t> IncludeEFU<t, tchild="">(this IQueryable<t> query, DbContext context, Expression<func<t, ienumerable<tchild="">>> collectionSelector)
where T : class
where TChild : class
{
var octx = (context as IObjectContextAdapter).ObjectContext;
var cSpaceTables = octx.MetadataWorkspace.GetItems<entitytype>(DataSpace.CSpace);
var cSpaceType = cSpaceTables.Single(t => t.Name == typeof(T).Name); //Use single to avoid any problems with multiple tables using the same type
var keys = cSpaceType.KeyProperties;
if (keys.Count > 1)
{
throw new InvalidOperationException("The include method only works on single key entities");
}
var fkGetter = GetForeignKeyGetter<t, tchild="">(cSpaceTables);
PropertyInfo pkInfo = typeof(T).GetProperty(keys.First().Name);
var pkGetter = MakeGetterDelegate<t>(pkInfo);
var childCollectionModifiers = new List<methodcallexpression>();
var childProp = SetCollectionModifiersAndGetChildProperty<t, tchild="">(collectionSelector, childCollectionModifiers);
var setter = MakeSetterDelegate<t>(childProp);
var e = new IncludeExecuter<t>
{
ElementType = typeof(TChild),
SingleItemLoader = (parent) =>
{
if (parent == null)
{
return;
}
var children = octx.CreateObjectSet<tchild>();
var lambdaExpression = GetRootEntityToChildCollectionSelector<t, tchild="">(cSpaceType);
var q = ApplyChildCollectionModifiers<tchild>(children, childCollectionModifiers);
var rootPK = pkGetter((T)parent);
var param = Expression.Parameter(typeof(TChild), "x");
var fk = GetFKProperty<t, tchild="">(cSpaceTables);
var body = Expression.Equal(Expression.Property(param, fk), Expression.Constant(rootPK));
var where = Expression.Lambda<func<tchild, bool="">>(body, param);
q = q.AsNoTracking().Where(where);
setter((T)parent, q.ToList());
},
Loader = (rootFilters, parents) =>
{
var baseType = typeof(T).BaseType != typeof(object) ? typeof(T).BaseType : typeof(T);
dynamic dynamicSet = octx.GetType()
.GetMethod("CreateObjectSet", new Type[] { })
.MakeGenericMethod(baseType)
.Invoke(octx, new Object[] { });
var set = dynamicSet.OfType<t>() as ObjectQuery<t>;
IQueryable<t> q = set;
foreach (var item in rootFilters)
{
var newSource = Expression.Constant(q);
var arguments = Enumerable.Repeat(newSource, 1).Concat(item.Arguments.Skip(1)).ToArray();
var newMethods = Expression.Call(item.Method, arguments);
q = q.Provider.CreateQuery<t>(newMethods);
}
var lambdaExpression = GetRootEntityToChildCollectionSelector<t, tchild="">(cSpaceType);
var childQ = q.SelectMany(lambdaExpression);
childQ = ApplyChildCollectionModifiers<tchild>(childQ, childCollectionModifiers);
var dict = childQ.AsNoTracking().ToLookup(fkGetter);
var list = parents.Cast<t>().ToList();
foreach (var parent in list)
{
var prop = pkGetter(parent);
var childs = dict.Contains(prop) ? dict[prop].ToList() : new List<tchild>();
setter(parent, childs);
}
}
};
return new EFUQueryable<t>(query.AsNoTracking()).Include(e);
}
private static IQueryable<tchild> ApplyChildCollectionModifiers<tchild>(IQueryable<tchild> childQ, List<methodcallexpression> childCollectionModifiers) where TChild : class
{
foreach (var item in childCollectionModifiers)
{
switch (item.Method.Name)
{
case "Where":
childQ = childQ.Where((Expression<func<tchild, bool="">>)item.Arguments[1]);
break;
case "OrderBy":
case "ThenBy":
case "OrderByDescending":
case "ThenByDescending":
childQ = SortQuery(childQ, item, item.Method.Name);
break;
default:
throw new NotSupportedException("The method " + item.Method.Name + " is not supported in the child query");
}
}
return childQ;
}
private static PropertyInfo SetCollectionModifiersAndGetChildProperty<t, tchild="">(Expression<func<t, ienumerable<tchild="">>> collectionSelector, List<methodcallexpression> childCollectionModifiers)
where T : class
where TChild : class
{
var temp = collectionSelector.Body;
while (temp is MethodCallExpression)
{
var mce = temp as MethodCallExpression;
childCollectionModifiers.Add(mce);
temp = mce.Arguments[0];
}
childCollectionModifiers.Reverse(); //We parse from right to left so reverse it
if (!(temp is MemberExpression))
{
throw new ArgumentException("Could not find a MemberExpression", "collectionSelector");
}
var childProp = (temp as MemberExpression).Member as PropertyInfo;
return childProp;
}
private static Func<tchild, object=""> GetForeignKeyGetter<t, tchild="">(System.Collections.ObjectModel.ReadOnlyCollection<entitytype> cSpaceTables)
where T : class
where TChild : class
{
var fkInfo = GetFKProperty<t, tchild="">(cSpaceTables);
var fkGetter = MakeGetterDelegate<tchild>(fkInfo);
return fkGetter;
}
private static PropertyInfo GetFKProperty<t, tchild="">(System.Collections.ObjectModel.ReadOnlyCollection<entitytype> cSpaceTables)
where T : class
where TChild : class
{
var cSpaceChildType = cSpaceTables.Single(t => t.Name == typeof(TChild).Name); //Use single to avoid any problems with multiple tables using the same type
var fk = cSpaceChildType.NavigationProperties.First(n => n.ToEndMember.GetEntityType().Name == typeof(T).Name).GetDependentProperties().First();
var fkInfo = typeof(TChild).GetProperty(fk.Name);
return fkInfo;
}
private static IQueryable<tchild> SortQuery<tchild>(IQueryable<tchild> query, MethodCallExpression item, string method)
{
var body = (item.Arguments[1] as LambdaExpression);
MethodCallExpression call = Expression.Call(
typeof(Queryable),
method,
new[] { typeof(TChild), body.Body.Type },
query.Expression,
Expression.Quote(body));
return (IOrderedQueryable<tchild>)query.Provider.CreateQuery<tchild>(call);
}
private static Expression<func<t, ienumerable<tchild="">>> GetRootEntityToChildCollectionSelector<t, tchild="">(EntityType cSpaceType)
where T : class
where TChild : class
{
var parameter = Expression.Parameter(typeof(T), "t");
var memberExpression = Expression.Property(parameter, cSpaceType.NavigationProperties.First(p => p.ToEndMember.GetEntityType().Name == typeof(TChild).Name).Name);
var lambdaExpression = Expression.Lambda<func<t, ienumerable<tchild="">>>(memberExpression, parameter);
return lambdaExpression;
}
static Action<t, object=""> MakeSetterDelegate<t>(PropertyInfo property)
{
MethodInfo setMethod = property.GetSetMethod();
if (setMethod != null && setMethod.GetParameters().Length == 1)
{
var target = Expression.Parameter(typeof(T));
var value = Expression.Parameter(typeof(object));
var body = Expression.Call(target, setMethod,
Expression.Convert(value, property.PropertyType));
return Expression.Lambda<action<t, object="">>(body, target, value)
.Compile();
}
else
{
return null;
}
}
static Func<x, object=""> MakeGetterDelegate<x>(PropertyInfo property)
{
MethodInfo getMethod = property.GetGetMethod();
if (getMethod != null)
{
var target = Expression.Parameter(typeof(X));
var body = Expression.Call(target, getMethod);
Expression conversion = Expression.Convert(body, typeof(object));
return Expression.Lambda<func<x, object="">>(conversion, target)
.Compile();
}
else
{
return null;
}
}
}
}
|
|
|
|
|