How to create a PopUp Report

Hi Friends,

No I m not looking for a drill down report.

I want a hiperlink on my country sales reports. When user will click on this hiperlink one popup window will open(Popup report). This popup report will contain some input parameter eg list box of sales person name and list of country to be selected select.

One user will select country germany and sales person as Mr Bin and click go on the poup report I want the popup report to be closed and based on the input(country germany and sales person as Mr Bin ) want my country sales reports to get updated.

Hope you undestand my qn.

thanks

Novin

You should be able to achieve this, except the popup would not be a report. It would need to be some custom html page that collects user input and then submits it to opener page with the selection.

To navigate to the popup:

In your report add a textbox with an action of "Jump to URL" and set this to

javascript:window.open()

OR

javascript:window.showModalDialog()

To pass parameters from the popup:

Call a javascript function onclick of the submit button that does something like

Code Snippet

function loadReport(param1)

{

window.opener.location.href = opener.location.href + "&param1=" + param1;

window.opener.focus();

window.close();

}

I've not tried this but theoretically it should work.

|||Hi Adam,

Thanks a lot for your kind and quick reply.

I will try this out. One qn. then the popup page(which must not be a report) need to be a php or JSP page which will have some database connection code and it will fill the input parameters listboxes.

Correct me if I am wrong and also suggest me the popup page implementation bcas I m new to microsoft technologies.

Thanks
Novin
|||

You are in control of the popup and it's implementation is totally up to you as long as your web server can handle it. Possible options are:

static html with hard coded lists of options

dynamic webpage, the technology is immaterial but you're likely to get better integration using ASP.NET 2.0

parametrs could be queried directly from database tables

OR you can connect to the RS web service and use the queries and connections defined within the report RDL to retrieve a collection of ValidValues (recommended)

|||Hi Adam,

As per your explaination it looks possible to me.

But I have one more doubt regarding the parameter passing to the parent report from the popup report and auto refreshing the parent report.

Using following code I can pass the selectede parameters back to the detail report but I want that detail report be get refreshed(hit the dabase and re executing the report sql) without user intervantion with new parameters. Is that possible or the user need to manually again click the some controll to get the new data with passed parameters from the popup page.

function loadReport(param1)

{

window.opener.location.href = opener.location.href + "&param1=" + param1;

window.opener.focus();

window.close();

}

Thanks
Novin
|||

Whenever you change the value of location.href in javascript IE automatiacally posts back. This would be driven from the javascript and no user intervention is required.

How to create a PopUp Report

Hi Friends,

No I m not looking for a drill down report.

I want a hiperlink on my country sales reports. When user will click on this hiperlink one popup window will open(Popup report). This popup report will contain some input parameter eg list box of sales person name and list of country to be selected select.

One user will select country germany and sales person as Mr Bin and click go on the poup report I want the popup report to be closed and based on the input(country germany and sales person as Mr Bin ) want my country sales reports to get updated.

Hope you undestand my qn.

thanks

Novin

You should be able to achieve this, except the popup would not be a report. It would need to be some custom html page that collects user input and then submits it to opener page with the selection.

To navigate to the popup:

In your report add a textbox with an action of "Jump to URL" and set this to

javascript:window.open()

OR

javascript:window.showModalDialog()

To pass parameters from the popup:

Call a javascript function onclick of the submit button that does something like

Code Snippet

function loadReport(param1)

{

window.opener.location.href = opener.location.href + "&param1=" + param1;

window.opener.focus();

window.close();

}

I've not tried this but theoretically it should work.

|||Hi Adam,

Thanks a lot for your kind and quick reply.

I will try this out. One qn. then the popup page(which must not be a report) need to be a php or JSP page which will have some database connection code and it will fill the input parameters listboxes.

Correct me if I am wrong and also suggest me the popup page implementation bcas I m new to microsoft technologies.

Thanks
Novin
|||

You are in control of the popup and it's implementation is totally up to you as long as your web server can handle it. Possible options are:

static html with hard coded lists of options

dynamic webpage, the technology is immaterial but you're likely to get better integration using ASP.NET 2.0

parametrs could be queried directly from database tables

OR you can connect to the RS web service and use the queries and connections defined within the report RDL to retrieve a collection of ValidValues (recommended)

|||Hi Adam,

As per your explaination it looks possible to me.

But I have one more doubt regarding the parameter passing to the parent report from the popup report and auto refreshing the parent report.

Using following code I can pass the selectede parameters back to the detail report but I want that detail report be get refreshed(hit the dabase and re executing the report sql) without user intervantion with new parameters. Is that possible or the user need to manually again click the some controll to get the new data with passed parameters from the popup page.

function loadReport(param1)

{

window.opener.location.href = opener.location.href + "&param1=" + param1;

window.opener.focus();

window.close();

}

Thanks
Novin
|||

Whenever you change the value of location.href in javascript IE automatiacally posts back. This would be driven from the javascript and no user intervention is required.

How to create a PopUp Report

Hi Friends,

No I m not looking for a drill down report.

I want a hiperlink on my country sales reports. When user will click on this hiperlink one popup window will open(Popup report). This popup report will contain some input parameter eg list box of sales person name and list of country to be selected select.

One user will select country germany and sales person as Mr Bin and click go on the poup report I want the popup report to be closed and based on the input(country germany and sales person as Mr Bin ) want my country sales reports to get updated.

Hope you undestand my qn.

thanks

Novin

You should be able to achieve this, except the popup would not be a report. It would need to be some custom html page that collects user input and then submits it to opener page with the selection.

To navigate to the popup:

In your report add a textbox with an action of "Jump to URL" and set this to

javascript:window.open()

OR

javascript:window.showModalDialog()

To pass parameters from the popup:

Call a javascript function onclick of the submit button that does something like

Code Snippet

function loadReport(param1)

{

window.opener.location.href = opener.location.href + "&param1=" + param1;

window.opener.focus();

window.close();

}

I've not tried this but theoretically it should work.

|||Hi Adam,

Thanks a lot for your kind and quick reply.

I will try this out. One qn. then the popup page(which must not be a report) need to be a php or JSP page which will have some database connection code and it will fill the input parameters listboxes.

Correct me if I am wrong and also suggest me the popup page implementation bcas I m new to microsoft technologies.

Thanks
Novin
|||

You are in control of the popup and it's implementation is totally up to you as long as your web server can handle it. Possible options are:

static html with hard coded lists of options

dynamic webpage, the technology is immaterial but you're likely to get better integration using ASP.NET 2.0

parametrs could be queried directly from database tables

OR you can connect to the RS web service and use the queries and connections defined within the report RDL to retrieve a collection of ValidValues (recommended)

|||Hi Adam,

As per your explaination it looks possible to me.

But I have one more doubt regarding the parameter passing to the parent report from the popup report and auto refreshing the parent report.

Using following code I can pass the selectede parameters back to the detail report but I want that detail report be get refreshed(hit the dabase and re executing the report sql) without user intervantion with new parameters. Is that possible or the user need to manually again click the some controll to get the new data with passed parameters from the popup page.

function loadReport(param1)

{

window.opener.location.href = opener.location.href + "&param1=" + param1;

window.opener.focus();

window.close();

}

Thanks
Novin
|||

Whenever you change the value of location.href in javascript IE automatiacally posts back. This would be driven from the javascript and no user intervention is required.

HOw to covert horizontal resultset to vertical resultset

Hi!
I have a table like this

Continent Country State
Asia India Tn
Asia India AP
Asia India Ka
Asia Pakistan Lh
Asia Pakistan Kh
Asia China Ka
Asia China SS
Asia China OP
Asia China KO
America Canada ON
America Canada TO

Now i want to generate a resulset like this

Continent Country State1 State2 State3 State4
Asia India Tn AP Ka
Asia Pakistan Lh Kh
Asia China Ka SS OP Ko
America Canada ON TO

HOw to do it?
I can do it using the resultset and building the query dynamically by using "Case Statement" but , is there any other way of directly reversing horizontal resultset to vertical ?????.

Kindly reply me

Thank You
Venkat LakshminarayananDo your state have to put in separate columns, or can they be formatted as a delimited string?

Do you have a column which indicates whether a given state is a "state1", "state2", "state3"... state. Otherwise, how do you want to assign states to columns?

Is there a limit on the number of states per country? Do they all have the same number of states?

I'm trying to decide whether a cross-tab query is possible or appropriate.

blindman|||i'd love to see a crosstab query done in sql server

in access, i can do, but in sql server, it is messy code, yes?

i always just tell people to handle it with application code

which includes the "cursor in a stored proc" approach

edit: p.s. toronto is not (yet) a state|||Crosstabs are not that complicated in TSQL. You just have to predefine the columns, which is good practice in Access too even though Access will construct the result set dynamically.

Books Online does a pretty good job of explaining and demonstrating cross-tabl calculation.

In my experience though, cross-tabulation is usually the last step complete in a process prior to formatting the output, and so it is often best handled by the interface (crystal, Access, whatever).

blindman|||heh

"just" predefine the columns...

:rolleyes:|||I agree about this being a presntation layer issue...

but I couldn't resist...just wish I could figure out a set based method...

USE Northwind
GO

CREATE TABLE myTable99 (col1 varchar(20), col2 varchar(20), col3 varchar(20))
GO

INSERT INTO myTable99 (Col1, Col2, Col3)
SELECT 'Asia', 'India', 'Tn' UNION ALL
SELECT 'Asia', 'India', 'AP' UNION ALL
SELECT 'Asia', 'India', 'Ka' UNION ALL
SELECT 'Asia', 'Pakistan', 'Lh' UNION ALL
SELECT 'Asia', 'Pakistan', 'Kh' UNION ALL
SELECT 'Asia', 'China', 'Ka' UNION ALL
SELECT 'Asia', 'China', 'SS' UNION ALL
SELECT 'Asia', 'China', 'OP' UNION ALL
SELECT 'Asia', 'China', 'KO' UNION ALL
SELECT 'America', 'Canada', 'ON' UNION ALL
SELECT 'America', 'Canada', 'TO'
GO

SET NOCOUNT ON

DECLARE IdontWantToUseACursor CURSOR
FOR
SELECT DISTINCT Col1, Col2 FROM myTable99

DECLARE @.Col1 varchar(20), @.Col2 varchar(20), @.Col3 varchar(7000)

DECLARE @.temp TABLE (Col1 varchar(20), Col2 varchar(20), Col3 varchar(7000))

OPEN IdontWantToUseACursor

FETCH NEXT FROM IdontWantToUseACursor
INTO @.Col1, @.Col2

WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.Col3 = ''
SELECT @.Col3 = @.Col3 + ', ' + Col3 FROM myTable99 WHERE Col1 = @.Col1 AND Col2 = @.Col2
INSERT INTO @.temp (Col1, Col2, Col3) SELECT @.Col1, @.Col2, SUBSTRING(@.Col3,2,LEN(@.Col3)-2)
FETCH NEXT FROM IdontWantToUseACursor
INTO @.Col1, @.Col2
END

CLOSE IdontWantToUseACursor
DEALLOCATE IdontWantToUseACursor

SELECT * FROM @.Temp
GO
SET NOCOUNT OFF
GO

DROP TABLE myTable99
GO|||Set based...Thanks to Nigel Rivett

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=29931&whichpage=2|||set based? smoke and mirrors, i say

buried deep within that function is a SELECT statement which is executed for every single distinct combination of col1 and col2

however, there's no getting around the fact that in order to do the denormalization of many rows to one, something like this is necessary, whether in a user-defined function or in application code

but consider the bandwidth, to return multiple rows to the application from the database, where each returned row has "redundant" col1 and col2 values, compared to returning just one with the UDF method

for example, this technique might come in awfully handy when col1 is article_id and col2 is article_text varchar(6000), and the multiple col3 values are keywords describing the article...

rudy|||Hi EveryBody
Thank you all for replying me,I had found a solution and I will mail you that tommorow (if you would like to know),I like to know about Cross tab queries,can anyone help me....?
I have a master table in which Continent, countries and states are defined and What I did is , used a Select Case Statement and a cursor.
I had found a worderful solution for this.But still i like to know about cross tab qeuries which some of you were discussing.

Thank You
Repsor|||Look up CROSSTAB in Books Online. It really does a good jog of explaining it.

And for another example, check out this thread:

http://www.dbforums.com/t956654.html

This is a classic crosstab problem, and hkamatgi's post is a classic crosstab solution.

blindman