I am passing a SQL statement from asp.net to crystal report though sql command. It is a lot easier when the columns in the table are already defined, when the columns are customized or formulated, what I do is create a formula in the formula field in crystal report and it works well. The only problem I am facing is the column called GCS. This column uses case statement to find the existence of data in another table, if there is a data, it will create a computation, otherwise, it will perform another computation. Please see the sql statement

What can I do? Can formula field be still able to cater my need on the GSC Column? How about SQL expression field? Please help. Thanks in advance

UPDATE:

For a more detailed explantion, My goal is to show a monthly sales reporting of all the tenants in one location, showing the tenants name, and other description, also includes a FORMULATED COLUMNS like Current and Previous Month overall sales, the percentage diffrence of two months name INC/DEC column.

// i used temp table several times
select b.tenantcode, b.date , SUM(a.other)as discount
INTO #NewDiscountTable
from DAILY a INNER JOIN DISCOUNT b on a.date = b.date and a.tenantcode = b.tenantcode
WHERE b.date between @PreviousMonthStart AND @CurrentMonthEnd and A.location = @Location
group by b.date, b.tenantcode
order by b.tenantcode

select tenantcode , SUM(discount) as Approved_Disc
into #NewDiscountTableFinal
from #NewDiscountTable
where date between @PreviousMonthStart AND @PreviousMonthEnd
group by tenantcode

select tenantcode , SUM(discount) as Approved_Disc2
into #NewDiscountTableFinal2
from #NewDiscountTable
where date between @CurrentMonthStart AND @CurrentMonthEnd
group by tenantcode

select [FLOOR AREA/SQM], [RETAIL PARTNERS] , Tenantcode, SUM(GSC) as GSCwithOtherDisc
into #NewDailySalesTenderTableFinal
from #NewDailySalesTenderTable
where date BETWEEN @PreviousMonthStart and @PreviousMonthEnd
GROUP BY [Retail Partners], tenantcode, [FLOOR AREA/SQM]
ORDER BY [Retail Partners]

select [FLOOR AREA/SQM], [RETAIL PARTNERS] , Tenantcode, SUM(GSC) as GSCwithOtherDisc2
into #NewDailySalesTenderTableFinal2
from #NewDailySalesTenderTable
where date between @CurrentMonthStart AND @CurrentMonthEnd
GROUP BY [Retail Partners], tenantcode, [FLOOR AREA/SQM]
ORDER BY [Retail Partners]

Of course in the .rpt file, I am setting the datasource to the STORED PROCEDURE, while doing this, I am stopped by the asking of CR for me to enter values

struggle is, when i Dont input anything, there is no column created in crystal report, and leave me with no option but to input a predefined value which supposed to be not because values should be coming from User's options. and TO NOTE THAT TWO MONTHS COLUMN SHOULD BE CHANGING OR DYNAMIC.

After you create your procedure, go to your .rpt report , open and change the data source to SQL data source, navigate through and select your procedure , then update just like with ttx but here Sql data source. When you update, Your columns will come under the report and under the parameter , u would have have three parameters. @datFrom, @datTo, @hdnRP . Modify your page load you will not be needing the dataset.

I think i have seen something similar. Its bcos of the complexity of ur procedure. There are similar questions on how to supressed this here and here

Update

If your store-procedure does what you want. You dont have to change datasource each time. Actually i forgot since during ttx . You can just use Dataset and some trick i normally do. Create and empty database table which you dont need. Add your report columns in the table just like ttx file and set your report source to the emty table which you will never use. And then from the source code. Pass Dataset as your report source example.

If your report has three columns . Name, user name and password. Then create SQL table with columns
name , user name, password. Use your table just for column names. Set the report datasource to the empty table. Make sure its empty.