How would I format a query parameter and a query so that if the data in the table I am querying has a login_name field that matches the DNN username field, I can filter the data on those being matched?

Thanks

Paul,

Im a little unclear of the question, but I assume what you are attempting to do is filter a query based on login_name where the login_name matches the Username in the Users table - and the user is the currenlty logged in user.

1. First - Create a Query Variable to use within your query.
• Select the Variable Type as UserId.
• When a system variable is selected, the Source is not utilized, but it is common practice to set the Source to the same name as the variable - so set the Source to UserId.
• Set [CurrentUserId] as the target replacement value for the query.
2. Next, you will need to consume the Query Variable within the Query. Since I don't know the structure or format of your tables, I will just give you a demonstration of the usage of this variable in a general sense.
• Modify the current Query to consume the Query Variable and join the obtained UserId against the DotNetNuke User table. Example: Select * from MyTable join Users on MyTable.login_name = Users.username and Users.UserId = [UserId]

Thats it, pretty simple and easy to accomplish -

Finally back at this. It's still not working. Followed your instructions exactly as above but from debug and the original sql to the actual sql, [UserId] is not being translated by the query variable. This query has been tested with literal text in place of the tag. again, query variable exists with UserId as type, UserId as source and [CurrentUserId] as target.

Actual:Expand/Collapse
SELECT s.Company_Name,
s.Support_Incident_Code As Incident_Number,
s.rn_descriptor,
CONVERT(CHAR(10), s.Date_Recorded, 101) as Date_Recorded,
s.Support_Incident_Name,
s.Severity_Text,
s.Product_Type as Product,
s.Next_Action_Text,
s.Status_Text
FROM productioned.dbo.Contact as c INNER JOIN
productioned.dbo.Contact_Web_Details as cwd ON c.Contact_Id =
cwd.Contact_Id INNER JOIN
productioned.dbo.Support_Incident as s INNER JOIN
productioned.dbo.Company as co ON S.Company_Id =
Co.Company_Id OR
S.Partner_Company_Id =
Co.Company_Id ON C.Company_Id =
Co.Company_Id
WHERE Cwd.Login_Name = [UserId] AND 1=1 ORDER BY s.rn_create_date DESC, Incident_Number , Date_Recorded DESC, Support_Incident_Name , Severity_Text , Product , Next_Action_Text , Status_Text

Error:Expand/Collapse

System.Data.SqlClient.SqlException: Invalid column name 'UserId'.

Thanks,

Paul

Well, I kinda figured it out. I changed my query after rereading your post. Data comes back now but my column header sorting no longer works. Here's the current query if you could take a look. Thanks

SELECT s.Company_Name,
s.Support_Incident_Code As Incident_Number,
s.rn_descriptor,
CONVERT(CHAR(10), s.Date_Recorded, 101) as Date_Recorded,
s.Support_Incident_Name,
s.Severity_Text,
s.Product_Type as Product,
s.Next_Action_Text,
s.Status_Text
FROM productioned.dbo.Contact as c INNER JOIN
productioned.dbo.Contact_Web_Details as cwd ON c.Contact_Id =
cwd.Contact_Id INNER JOIN
productioned.dbo.Support_Incident as s INNER JOIN
productioned.dbo.Company as co ON S.Company_Id =
Co.Company_Id OR
S.Partner_Company_Id =
Co.Company_Id ON C.Company_Id =
Co.Company_Id
JOIN CustomerHub.dbo.users as chub on cwd.login_name = chub.username and chub.userid = [UserId]
WHERE [FILTERTAG] ORDER BY s.rn_create_date DESC, [SORTTAG]