Crystal Report query

[Former Member]

Hi

i facing problem during creation of crystal report.

when i run this query in sap it give output but when i run in crystal report using add command then it give me error like conversion failed during date/time from character string .

I attach below image...

Thanks

Nikunj

[daroy_alvin]

Hi,

Try this,

Where T0.Docdate between {?FromDate} and {?ToDate}

Thanks & Regards,

Alvin

[former_member184146]

Hi,

     create two parameter "FromDate" and "ToDate"  from  left side  "Create" Button from the command window   

replace your where Condition with

Where T0.Docdate between {?FromDate} to {?ToDate}

See in screen below

Regards,

Manish

[Former Member]

Hi Manish,

as per your suggestion i passed parameter but give me blank output.

Regards,

Nikunj

[raghavendra_hullur]

Hi,

DocDate from OINV table is of date data type right?

Can you explain about [%0] and [%1] values passed to DocDate and the format?

Thanks,

Raghavendra

[Former Member]

Hi Raghavendra,

DocDate is passed from OINV table.

[%0] and [%1] is use for date selection criteria. Based on selected date it give me output.

i also passed value on selection criteria but it will not give me output.

Regards,

nikunj

[raghavendra_hullur]

Hi Nikunj,

So, [%0] and [%1] are date data type prompts?

1. Try running the report by commenting the where clause for date part and check for which date of DocDate you have data.

2. Then try checking for the date range which includes the dates for which you got data in step 1.

3. If you still not getting the data, then you may have to check the other where clause conditions whether you are having data for those conditions.

Thanks,

Raghavendra

[Former Member]

Hi Raghavendra,

as per discussion, [%0] and [%1] date prompts .

As per your suggestion i committed where clause but it will give me output for whole year not a particular selection period.

Thanks,

Nikunj

[raghavendra_hullur]

Hi Nikunj,

Yes,

It will give the data for whole year if you comment date prompts. The reason for asking you to do that was, to check for which dates you have data.

Now you know that you are getting data for some dates if not all. Note down those dates and uncomment the date range parameter clause in your command query and try running the report again by passing a date range which includes the dates you have noted down.

Thanks,

Raghavendra

[Former Member]

Hi Raghavendra,

as per your conclusion,

i also try it but it will give me blank output on crystal report.

i attach query : 

SELECT sum(T0.[DocTotal] - T0.[VatSum]) 'assessable value' , Sum( T0.[VatSum]) 'Tax Amount' , ((sum(T0.[DocTotal] - T0.[VatSum]) * 4)/100) 'Vat @ 4%' , ((sum(T0.

[DocTotal] - T0.[VatSum]) * 1)/100) 'Vat @ 1%'  FROM [dbo].[OINV]  T0  Where T0.DocDate between '[%0]' and '[%1]'

UNION ALL

SELECT sum(T0.[DocTotal] - T0.[VatSum]) 'assessable value1' , Sum( T0.[VatSum]) 'Tax Amount1' , ((sum(T0.[DocTotal] - T0.[VatSum]) * 4)/100) 'Vat @ 4%1' , ((sum(T0.

[DocTotal] - T0.[VatSum]) * 1)/100) 'Vat @ 1%1'  FROM [dbo].[ORIN]  T0 

UNION ALL

SELECT sum(T0.[DocTotal] - T0.[VatSum]) 'assessable value2' , Sum( T0.[VatSum]) 'Tax Amount2' , ((sum(T0.[DocTotal] - T0.[VatSum]) * 4)/100) 'Vat @ 4%2' , ((sum(T0.

[DocTotal] - T0.[VatSum]) * 1)/100) 'Vat @ 1%2'  FROM [dbo].[OPCH]  T0

UNION ALL

SELECT sum(T0.[DocTotal] - T0.[VatSum]) 'assessable value3' , Sum( T0.[VatSum]) 'Tax Amount3' , ((sum(T0.[DocTotal] - T0.[VatSum]) * 4)/100) 'Vat @ 4%3' , ((sum(T0.

[DocTotal] - T0.[VatSum]) * 1)/100) 'Vat @ 1%3'  FROM [dbo].[ORPC]  T0

[raghavendra_hullur]

Hi Nikunj,

Best method to debug your query (as it's having 4 queries combined using Union ALL), is to try including one query at a time without any command level filtering using prompts.

If the query is giving some data, then try adding your prompts one by one and execute the report each time to make sure you are getting some data for the prompts you are including.

Say one query is perfect and giving output with all prompts. Then try the same steps for other queries also.

If everything goes well, try combining them using Union ALL.

If individual queries are giving output, then your combined query (using Union ALL) will also have data even if one of your queries is retrieving some data.

Hope you will get some hint on how to proceed.

Thanks,

Raghavendra