Hi,
I have two datatables in the dataset and I want to use the fields from both datatables to create a Crystal Report. But I use max and group by in the select query which when I run in access works fine. But, I don't know how to combine the datatables using aggregate functions.
I have two datatables:NalogNov and MagNov and the result query I made is:
SELECT NalogNov.DATA, Max(IIf([MagNov].[DATA]=[NalogNov].[DATA],[MagNov].[Gorivo],0)) AS Gorivo1, Max(IIf([MagNov].[DATA]=[NalogNov].[DATA],[MagNov].[Addblue],0)) AS Addblue1, Max(IIf([MagNov].[DATA]=[NalogNov].[DATA],[MagNov].[Antifriz],0)) AS Antifriz1, Max(IIf([MagNov].[DATA]=[NalogNov].[DATA],[MagNov].[Motmaslo],0)) AS Motmaslo1, NalogNov.pockm, NalogNov.krajkm, [krajkm]-[pockm] AS RAZLIKA, Max(NalogNov.Poslprov) AS MaxOfPoslprov, Max(NalogNov.Poslserv) AS MaxOfPoslserv, Max(NalogNov.KMS) AS MaxOfKMS, Max(NalogNov.KMP) AS MaxOfKMP
FROM NalogNov LEFT JOIN MagNov ON NalogNov.GBRV = MagNov.GBR
WHERE (((MagNov.GBR)=[NalogNov].[GBRV]))
GROUP BY NalogNov.DATA, NalogNov.pockm, NalogNov.krajkm, [krajkm]-[pockm]
ORDER BY NalogNov.DATA;
But how to use this query in C# creating the CrystalReport? I tried to use the CR wizard, but I have lot of rows for one data, and the values in the result CR are not correct. Can anybody help me please?Thanks
Loading
NelPosted Dec 9, 2012, 3:26 AM
NelPosted Dec 7, 2012, 6:58 AM
DataSet3 dataSet2 = new DataSet3();
DataTable baraniotselect = dataSet2.baraniotselect;
DataTable PocKrajRazl1 = dataSet2.PocKrajRazl1;
DataTable Table1 = dataSet2.Table1;
string comstring3="SELECT PocKrajRazl1.DATA, PocKrajRazl1.GBRV, Max(IIf([baraniotselect].[DATA]=[PocKrajRazl1].[DATA],[baraniotselect].[Gorivo],0)) AS Gorivo1, Max(IIf([baraniotselect].[DATA]=[PocKrajRazl1].[DATA],[baraniotselect].[Addblue],0)) AS Addblue1, Max(IIf([baraniotselect].[DATA]=[PocKrajRazl1].[DATA],[baraniotselect].[Antifriz],0)) AS Antifriz1, Max(IIf([baraniotselect].[DATA]=[PocKrajRazl1].[DATA],[baraniotselect].[Motmaslo],0)) AS Motmaslo1, PocKrajRazl1.pockm, PocKrajRazl1.krajkm, Max(PocKrajRazl1.Poslprov) AS MaxOfPoslprov, Max(PocKrajRazl1.Poslserv) AS MaxOfPoslserv, Max(PocKrajRazl1.KMS) AS MaxOfKMS, Max(PocKrajRazl1.KMP) AS MaxOfKMP FROM PocKrajRazl1 LEFT JOIN baraniotselect ON PocKrajRazl1.GBRV = baraniotselect.GBR WHERE (((baraniotselect.GBR)=[PocKrajRazl1].[GBRV])) GROUP BY PocKrajRazl1.DATA, PocKrajRazl1.pockm, PocKrajRazl1.krajkm ORDER BY PocKrajRazl1.DATA";
command.CommandText = comstring3;
OleDbDataAdapter oleDBDataAdapter1 = new OleDbDataAdapter();
dataSet2.Clear();
oleDBDataAdapter1.SelectCommand = command1;
oleDBDataAdapter1.Fill(dataSet2, "baraniotselect");
oleDBDataAdapter1.SelectCommand = command2;
oleDBDataAdapter1.Fill(dataSet2, "PocKrajRazl1");
oleDBDataAdapter1.SelectCommand = command;
oleDBDataAdapter1.Fill(dataSet2, "Table1");
but I received this error message OleDbException was unabled:
"An action query cannot be used as a row source."
venkata kumarPosted Dec 7, 2012, 5:41 AM
you can create one data table with all filed ever is there in those two data table.
i think u create data set like this
Dataset ds=new Dataset();
you can create dataset(.xsd) file
there u can make it one table
NelPosted Dec 7, 2012, 5:16 AM
Thanks
venkata kumarPosted Dec 7, 2012, 4:35 AM
two table of data set not possible to create crystal report
for that 2 solutions are there.
1. in data set u can merge 2 tables in to one table
2. create view in db. directly u can attach crystal report
Thanks & Regards
Ravi