Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Default Selection (Report) Expand / Collapse
Author
Message
Posted Monday, September 30, 2013 1:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:26 AM
Points: 81, Visits: 173
Hello SQL Gurus

I am currently working on a report that a default selection used to select customers. I use Visual studio business intelligence development studio. Here's a pre-selection of a parameter;

SQL syntax dataset customer:

select branch_n
from incident
join sectn_dept
on incident.sectn_dept_id = sectn_dept.sectn_dept_id
join sectn
on sectn_dept.sectn_id = sectn.sectn_id
join branch
on sectn.branch_id = branch.branch_id
where branch.stat_flag = 'n'
group by branch_n
order by branch_n

Parameter @client: Picture

Available Values​​: Picture

Default Values​​: Picture

You see that in my default values ​​I've manually done my customers and this may not work for a change, because the data in the default value is no longer correct. This in an environment where each day a change occurs.

I am now trying to set it to search by ID;

Each customer has an ID that they get for one year. This year, a customer a number 3953 .... With several numbers behind but it is important that the first 4 digits only to have by searching the customer.

My idea is to create than parameter @customer makes a selection in Default value that goes into a 2nd dataset that look those numbers.

select branch_n, sectn_dept.dept_sc
from incident
join sectn_dept
on incident.sectn_dept_id = sectn_dept.sectn_dept_id
join sectn
on sectn_dept.sectn_id = sectn.sectn_id
join branch
on sectn.branch_id = branch.branch_id
where branch.stat_flag = 'n'
group by branch_n, dept_sc
order by branch_n

So it looks like I used the same qeury but dept_sc does in the select statement. Can you tell me how I should address qeury and what I should build.



Thanks in advance :)


  Post Attachments 
Customer.JPG (1 view, 39.68 KB)
Default Value.JPG (1 view, 32.10 KB)
available customers.JPG (2 views, 39.91 KB)
Post #1499876
Posted Monday, September 30, 2013 1:42 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:26 AM
Points: 81, Visits: 173
I just make a dataset 2 with the following Syntax;

select sectn_dept.dept_sc
from incident
join sectn_dept
on incident.sectn_dept_id = sectn_dept.sectn_dept_id
join sectn
on sectn_dept.sectn_id = sectn.sectn_id
join branch
on sectn.branch_id = branch.branch_id
where branch.stat_flag = 'n' and dept_sc LIKE '3953%'
group by dept_sc

Now i get all the customers with a number 3953...

I just custom the parameter and it look now this; See pictures

I want now that the customers with the number 3953 will be select when I opening the report. When I opening know I get no pre-selection? How can I fix that (Look picture)






  Post Attachments 
Customer.JPG (2 views, 38.89 KB)
available customers.JPG (2 views, 39.91 KB)
Default Value.JPG (3 views, 36.76 KB)
no selection...JPG (2 views, 9.90 KB)
Post #1499877
Posted Monday, September 30, 2013 1:52 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:26 AM
Points: 81, Visits: 173
By selecting a customer I get a follwoing error (ofcourse I get this when I matching a Nvarchar with a INT value)



  Post Attachments 
error.JPG (2 views, 16.68 KB)
Post #1499882
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse