0

I'm creating an Excel report, which other users beside myself could manage.

At the moment I'm struggling with creating the parameters in MS Query, so that they would get the input from a cell in Excel.

Everytime I try to paste my code, where I have changed the original dates with the "question marks" I get the following error.

"Parameters are not allowed in queries that can't be displayed grpahically."

I have tried to create parameters for very simple queries. Like

select *
From dmbase.test
where ID = ?

And there the parameters work, I could add even 10 or more and it would still work.

In addition I have tried to paste the code as it is in SSMS and then later through Data-Properties->Query Properties ->Definitions to change the code and add parameters to the "Command Text" box, but Excel just gives an error.

Also tried to declare variables for my parameters, but that didn't work as well. Gave an error regarding symbols.

declare Parameter1 date(10)
set Parameter1 = ?
declare Parameter2 date(10)
set Parameter2 = ?

I'm not 100% sure that the joins are to blame, but that's my feeling.

My code can be seen below.

select
loc.LocationLang
,loc.DistrictLang
,loc.InventoryRegionLang
,cod.ContractNumber
,cus.CustomerLang
,cus.InternalCustomerType
,cus.CustomerGroupCode
,cus.Organizationalcode
,css.CustomerSubSegmentLang
,loc.AnalysisRegionLang
,dht.ContractHeaderTypeLang
,cod.RentalOutDate
from
dmbase.fContractOpenDetail as cod
left join dmbase.dLocation as loc
    on cod.LocationID = loc.LocationID
left join dmbase.dCustomer as cus
    on cod.CustomerID = cus.CustomerID
left join dmbase.dCustomerSubSegment as css
    on cus.CustomerSubsegmentID = css.CustomerSubSegmentID
left join dmbase.dContractHeaderType as dht
    on cod.ContractHeaderTypeAMID = dht.ContractHeaderTypeAMID
where
cod.CompanyID = '6'
and cod.RentalOutDate between ? and ?
and left(loc.LocationLang,4) = '7201'

order by cod.ContractNumber

I would appreciate if you could say, how I could put parameters in so that I would not get the error of "Parameters are not allowed...".

Jaanis Veinberg
  • 143
  • 1
  • 7
  • You need to show us the code where you add the parameters. Also, your where clause has logically turned your left join to dmbase.dLocation into an inner join. You should either make it an inner join or move the predicate to the join instead of the where clause. – Sean Lange Apr 16 '19 at 13:32
  • Hi Sean, this is the code where I want to add the parameters. I would like to have parameters for the cod.RentalOutDate. I want the user to be able to input in Excel the dates, that one wants to see. – Jaanis Veinberg Apr 17 '19 at 05:29

0 Answers0