aldixit, Member
My Environment is as under ;
PHP Version 5.5.19
SQLSRV
sqlsrv
sqlsrv support enabled
Directive Local Value Master Value
sqlsrv.ClientBufferMaxKBSize 10240 10240
sqlsrv.LogSeverity 0 0
sqlsrv.LogSubsystems 0 0
sqlsrv.WarningsReturnAsErrors On On
The Driver used downloaded from Microsoft site
SQL Server 2008R2
Everything is fine but for all calculated fields I am getting following error
Non-supported datatype:
I know this is issue with sqlsrv on windows but any solution ?
Regards
aldixit, Member
My Sample Program;
IF object_id('dbr_chn_CXO004','P') IS NOT NULL
DROP PROCEDURE dbr_chn_CXO004
GO
CREATE PROCEDURE [dbo].[dbr_chn_CXO004]
AS
BEGIN
SELECT 'dbr.title', 'ClientWise Growth';
select 'dbr.column.filter', 'CLCODE', 'text';
select 'dbr.column.filter', 'CLNAME', 'text';
select 'dbr.column.filter', 'LTYPE', 'text';
select 'dbr.column.filter', 'CLPRICELISTTYPECD', 'text';
select 'dbr.hidecolumn', 'NoOfDays'
-- SELECT 'dbr.report', 'dbr_chn_CXO003', 'inCLCODE=CLCODE'
SELECT 'dbr.calc' , 'TYTotalWLPerDay' , '[TYTotalWL]/[NoOfDays]';
SELECT 'dbr.calc' , 'LYTotalWLPerDay' , '[LYTotalWL]/[NoOfDays]';
SELECT 'dbr.calc' , 'WLGrowth' , '(([TYTotalWL]-[LYTotalWL])/[LYTotalWL])*100';
SELECT 'dbr.calc' , 'TYTotalBizRsPerDay' , '[TYTotalBizRs]/[NoOfDays]';
SELECT 'dbr.calc' , 'LYTotalBizRsPerDay' , '[LYTotalBizRs]/[NoOfDays]';
SELECT 'dbr.calc' , 'BizGrowth' , '(([TYTotalBizRs]-[LYTotalBizRs])/[LYTotalBizRs])*100';
SELECT 'dbr.calc' , 'LossGain' , '([TYTotalBizRs]-[LYTotalBizRs])';
SELECT 'dbr.calc' , 'TYPerVisit' , '([TYTotalBizRs]/[TYTotalWL])';
SELECT 'dbr.calc' , 'LYPerVisit' , '([LYTotalBizRs]/[TYTotalWL])';
SELECT 'dbr.sum' , 'TYTotalWL' , 'TYTotalBizRs' , 'LYTotalWL' , 'LYTotalBizRs' , 'LossGain'
SELECT 'dbr.avg' , 'TYTotalWL' , 'TYTotalBizRs' , 'LYTotalWL' , 'LYTotalBizRs' , 'LossGain'
SELECT 'dbr.report', 'dbr_chn_CXO008', 'inCLCODE=CLCODE','inStartMonth=0' ,'inEndMonth=0' ;
select 'dbr.pager' , '50'
select 'dbr.sort', 'TYTotalBizRs', 'desc'
select 'dbr.sortmethod', 'TYTotalBizRs', 'number'
select 'dbr.sortorder', 'TYTotalBizRs', 'desc'
SELECT
View_Clients.CLCODE
, View_Clients.[CLNAME]
, View_Clients.[CLPRICELISTTYPECD]
, View_Clients.[LTYPE]
, SUM(TYTotalWL) [TYTotalWL]
, SUM(LYTotalWL) [LYTotalWL]
, NULL [TYTotalWLPerDay]
, NULL [LYTotalWLPerDay]
, NULL [WLGrowth]
, SUM(TYTotalBizRs) [TYTotalBizRs]
, SUM(LYTotalBizRs) [LYTotalBizRs]
, NULL [LossGain]
, NULL [TYTotalBizRsPerDay]
, NULL [LYTotalBizRsPerDay]
, NULL [BizGrowth]
, NULL [TYPerVisit]
, NULL [LYPerVisit]
, COUNT(DISTINCT RegistrationDate) [NoOfDays]
FROM dbo.[TMIS_B_002_DIXIT]
INNER JOIN View_Clients (NOLOCK) ON TMIS_B_002_DIXIT.CLCODE = View_Clients.CLCODE
GROUP BY View_Clients.CLCODE
, View_Clients.[CLNAME]
, View_Clients.[CLPRICELISTTYPECD]
, View_Clients.[LTYPE]
ORDER BY SUM(TYTotalBizRs) DESC
END
GO
myDBR Team, Key Master
Hi,
could you run the report (with limited data) and add '&export=sql' to the URL. Send the SQL output to support email. This way it is easier for us to replicate the situation.
--
myDBR Team
aldixit, Member
In report below Growth is Calculated Field
select cast('2014-12-01' as date) as 'FROMDATE', cast('2014-12-03' as date) as 'TODATE'
select 'dbr.hdr', 'BIZTYPE'
select 'dbr.sum', 'TYTotalWL', 'ActualInLacs', 'ActualInRs', 'LYInLacs', 'LYInRs'
select 'dbr.calc', 'GROWTH', '(([ActualInRs]-[LYInRs])/[LYInRs])*100'
select 'WHOLESALE ' as 'BIZTYPE', 'K0003 ' as 'MISCODE', 'RICHFEEL HEALTH BEAUTY PVT LTD ' as 'MISNAME', '9. B2b ' as 'MAINGROUP', 'MUMBAI ' as 'HQ', 'RSM ' as 'ASM', 'NEW ' as 'BTYPE', 1 as 'TYTotalWL', 0.020000 as 'ActualInLacs', 2295.000000 as 'ActualInRs', 0.000000 as 'LYInLacs', 0.000000 as 'LYInRs', null as 'GROWTH'
union all
select 'WHOLESALE ', 'L5001 ', 'ANKIT PATHOLOGICAL LABORATORY ', '9. B2b ', 'KALYAN - E ', 'CENTRAL - 1 ', 'EXISTING ', 2, 0.010000, 1125.000000, 0.010000, 790.000000, null
union all
select 'WHOLESALE ', 'L5021 ', 'A.D GOVINDANI ', '9. B2b ', 'NERUL ', 'CENTRAL - 3 ', 'EXISTING ', 2, 0.010000, 925.000000, 0.000000, 0.000000, null
union all
select 'WHOLESALE ', 'L5024 ', 'ASIAN INSTITUTE OF MEDICAL SCIENCES- DOM ', '9. B2b ', 'DOMBIVALI ', 'CENTRAL - 1 ', 'EXISTING ', 5, 0.030000, 2915.000000, 0.070000, 6980.000000, null
union all
select 'WHOLESALE ', 'L5029 ', 'ANKUR LAB ANDHERI ', '9. B2b ', 'ANDHERI EAST-1 ', 'WESTERN - 1 ', 'EXISTING ', 1, 0.010000, 560.000000, 0.000000, 0.000000, null
union all
select 'WHOLESALE ', 'L5052 ', 'ANJALI PATH LAB-KURLA ', '9. B2b ', 'KURLA ', 'CENTRAL - 2 ', 'EXISTING ', 8, 0.040000, 4010.000000, 0.020000, 1790.000000, null
union all
select 'WHOLESALE ', 'L5075 ', 'BIOTRON LABORATORY PVT LTD ', '9. B2b ', 'BHAYANDER ', 'WESTERN - 2 ', 'EXISTING ', 1, 0.000000, 400.000000, 0.020000, 1734.000000, null
union all
select 'WHOLESALE ', 'L5104 ', 'CHAKALA DIAGNOSTICS CENTRE-19 ', '9. B2b ', 'ANDHERI EAST-1 ', 'WESTERN - 1 ', 'EXISTING ', 1, 0.000000, 265.000000, 0.020000, 2049.600000, null
union all
select 'WHOLESALE ', 'L5112 ', 'SMITA DIXIT DR ', '9. B2b ', 'DADAR EAST ', 'SOUTH - 2 ', 'EXISTING ', 7, 0.050000, 4700.000000, 0.090000, 8530.000000, null
union all
select 'WHOLESALE ', 'L5116 ', 'KARNIK''S LABORATORY ', '9. B2b ', 'DOMBIVALI ', 'CENTRAL - 1 ', 'EXISTING ', 1, 0.010000, 1190.000000, 0.060000, 5837.000000, null
myDBR Team, Key Master
And if you run the code above inside a report, what is the problem? Do you still get the "Non-supported datatype" error?
--
myDBR Team
aldixit, Member
Yes I get same problem even if I run above SQL code inside report
myDBR Team, Key Master
Does any dbr.calc-formula work for you? If you simplify the query to:
select 'dbr.calc', 'GROWTH', '(([ActualInRs]-[LYInRs])/[LYInRs])*100' select 200 as 'ActualInRs', 100 as 'LYInRs', null as 'GROWTH'
--
myDBR Team
myDBR Team, Key Master
Run the updater to get the latest build.
--
myDBR Team
aldixit, Member
Done
thanks
ajitdixit, Member
This problem has come again
SQL Ootput
select 201604 as '', 201703 as '', 'AHMEDABAD ' as '', 'G0182 ' as '', 'N' as ''
select 'dbr.sum', 'WorkloadPerMonth'
select 'dbr.hsum', 'WorkloadPerMonth'
select 'dbr.calc', 'IndexWL1', '(SAPQuantity/Workload) * 100'
select 'dbr.calc', 'IndexWL2', '(Workload/SAPQuantity) * 100'
select 'dbr.calc', 'MaterialCostPerTest', '(SAPAmount/Workload)'
select 'dbr.calc', 'EarningPerTest', '(TotalBiz/Workload)'
select 'dbr.calc', 'MaterialPerc', '(SAPAmount/TotalBiz) * 100'
select 'AHMEDABAD' as 'PPRCTR', 'TSH(Ultrasensitive),serum by CMIA' as 'GROUPNAME', 39839.0 as 'SAPQuantity', 504722.42 as 'SAPAmount', 35826 as 'Workload', 2985 as 'WorkloadPerMonth', 2014496.59 as 'TotalBiz', null as 'IndexWL1', null as 'IndexWL2', null as 'MaterialCostPerTest', null as 'EarningPerTest', null as 'MaterialPerc'
select 'dbr.sum', 'SAPQuantity'
select 'dbr.sum', 'SAPAmount'
select 'dbr.sum', 'Workload'
select 'dbr.sum', 'TotalBiz'
select 'dbr.hsum', 'SAPQuantity'
select 'dbr.hsum', 'SAPAmount'
select 'dbr.hsum', 'Workload'
select 'dbr.hsum', 'TotalBiz'
select 'dbr.calc', 'IndexWL1', '(SAPQuantity/Workload) * 100'
select 'dbr.calc', 'IndexWL2', '(Workload/SAPQuantity) * 100'
select 'dbr.calc', 'MaterialCostPerTest', '(SAPAmount/Workload)'
select 'dbr.calc', 'EarningPerTest', '(TotalBiz/Workload)'
select 'dbr.calc', 'MaterialPerc', '(SAPAmount/TotalBiz) * 100'
select 'AHMEDABAD' as 'PPRCTR', 'TSH(Ultrasensitive),serum by CMIA' as 'GROUPNAME', '201604' as 'nmonth', 3427.0 as 'SAPQuantity', 53975.25 as 'SAPAmount', 2926 as 'Workload', 173510.95 as 'TotalBiz', null as 'IndexWL1', null as 'IndexWL2', null as 'MaterialCostPerTest', null as 'EarningPerTest', null as 'MaterialPerc'
union all
select 'AHMEDABAD', 'TSH(Ultrasensitive),serum by CMIA', '201605', 3294.0, 51880.5, 3006, 166545.59, null, null, null, null, null
union all
select 'AHMEDABAD', 'TSH(Ultrasensitive),serum by CMIA', '201606', 3433.0, 54069.75, 3080, 173600.19, null, null, null, null, null
union all
select 'AHMEDABAD', 'TSH(Ultrasensitive),serum by CMIA', '201607', 3323.0, 52337.25, 3011, 173880.77, null, null, null, null, null
union all
select 'AHMEDABAD', 'TSH(Ultrasensitive),serum by CMIA', '201608', 2866.0, 45139.5, 2594, 150951.4, null, null, null, null, null
union all
select 'AHMEDABAD', 'TSH(Ultrasensitive),serum by CMIA', '201609', 3409.0, 53691.75, 3107, 184488.84, null, null, null, null, null
union all
select 'AHMEDABAD', 'TSH(Ultrasensitive),serum by CMIA', '201610', 2670.0, 42052.5, 2313, 133840.79, null, null, null, null, null
union all
select 'AHMEDABAD', 'TSH(Ultrasensitive),serum by CMIA', '201611', 2466.0, 38839.5, 2249, 126722.56, null, null, null, null, null
union all
select 'AHMEDABAD', 'TSH(Ultrasensitive),serum by CMIA', '201612', 3206.0, 50494.5, 2912, 159901.77, null, null, null, null, null
union all
select 'AHMEDABAD', 'TSH(Ultrasensitive),serum by CMIA', '201701', 3186.0, 50179.5, 2799, 152816.45, null, null, null, null, null
union all
select 'AHMEDABAD', 'TSH(Ultrasensitive),serum by CMIA', '201702', 3846.0, 4961.4, 3395, 179478.52, null, null, null, null, null
union all
select 'AHMEDABAD', 'TSH(Ultrasensitive),serum by CMIA', '201703', 4713.0, 7101.02, 4434, 238758.76, null, null, null, null, null
Please help
myDBR Team, Key Master
What is the actual problem? Do you get an any error messages?
You have syntax error in your statements. In dbr.calc, you should include the ColumnReference inside the brackets. Use:
select 'dbr.calc', 'IndexWL1', '([SAPQuantity]/[Workload]) * 100'
instead of:
select 'dbr.calc', 'IndexWL1', '(SAPQuantity/Workload) * 100'
--
myDBR Team
You must log in to post.