Impromptu Problem

[Swathi37]

Hi All,

I have a question in Impromptu 7.1,
There is a simple list that runs every month.

TNo TAmt
----------
1 $25
2 $139
1 $100
1 $39
3 $90

Final Report should be
----------------------

TNo Curr.Month Amt Last_Mon_Amt Inc/Dec %Inc/Dec
--------------------------------------------------------
1 $164 $100 $64 64%
2 $139 $110 $29 26.4%
3 $90 $50 $40 80%


My question is how do I get the transaction amount for the last month and get the increase/decrease and Inc/Dec% in the same page.

Is this possible or should I add one more list frame with date prompts and run the report for the previous month?
One more problem I face with this report is:
If I run the report it prompts me for the date for the first simple list report. This is OK. But it prompts again to display the summary report with the total transaction amount. How can I avoid this?
Note: I have simple list and summary report on the same page.

Please help. Thanks in advance

 

[CognosProfessional]

You can try to create "if" statements for each column if the data is readily in the catalog.


CP

 

[bhas79]

Is they any column to know when the record has been entered.how will you know whether the record has been entered last month or current month.Please post the specific details of the table structure

 

[Swathi37]

Thank you so much for your response.

The following query is for current month. I run this query every month.
TNo TAmt
----------
1 $25
2 $139
1 $100
1 $39
3 $90

As far as the final report is concerned I have manually added the calculated values for last month's report in Excel and calculated the Increase/Decrease %.

My question is how can I compare last month and current month values and get the output. Should I run the query twice by creating a 2 list reports? I use date prompts for the above query.
Moreover I may want to display the query and final output on two different pages. But inserting a new page is another problem I have now. Please help me with this.
I apppreciate your help.

 

[BruceReed]

You are going to have to code 2 variables in your report :current month, and last month. Any other date ignore, then total up what you want. Then compare total last month to total this month in a summary footer.

ie if(date=this month) then (this month)else 0, etc.

This is a bit messy, but should work.

Why not use PowerPlay? This type of relative date comparisions is a snap in PP. Makes you look like a superhero to your coworkers, without working too hard!

Good luck
Bruce

 

[Swathi37]

Bruce,

Thank you so much.
Idea is great. But I dont know powerplay. Impromptu is new to me. I use date prompts to extract the data. That is I use 2 variables. Start_Date and End_Date. Is there a way I can use prompts with just month and year? If so how can I do that?
Thanks in advance

 

[bhas79]

TNo Value entered date
1 200 8/1/03 0:00
2 200 4/1/03 0:00
2 300 4/1/03 0:00
2 400 4/1/03 0:00
2 400 5/1/03 0:00
2 23 5/1/03 0:00
2 213 5/1/03 0:00
3 13 5/1/03 0:00
3 123 5/1/03 0:00
3 123 4/1/03 0:00
3 113 4/1/03 0:00

I hope you are getting data from the same table.

define current month=
total((if(month(entered date)-month(date)=0)then (Value) else (0)) for TNo

month is the function
date you can find in Value Component.which returns the current date on the system.

define last month=
total(if(month(date)-month(entered date)=1)then (Value) else (0)) for TNo

select entered date and Value from catalog columns


TNo Current month Last month
1 0 0
2 636 900
3 136 236

 

[Swathi37]

Bhas79,

Thanks a lot!!! It works!!!
I wrote a macro to export this report to MS-Excel. But the data gets exported to the first cell i.e A1. Moreover the contents of list header does not get exported. How do I code a macro so that the output of the report gets exported to the 3rd line i.e A3. Since the contents of the header does not get exported is there a way to code a macro to insert a text on A1 cell in Excel and format it as bold and increase the font size? Please help me with this.

Thank you so much for all your help.

 

[bhas79]

they are some problems with exporting data to Excel in IWR 6.0 the data will be pushed to other columns.this problem has been resolved in IWR 6.1.
you can publish the report and select the format has Excel and changes in the report like Bold and font size will refelect in the Excel report.
you cannot get list header information in Excel format.
I am not sure how can we write a Macro to get list header information.

good luck,
Bhas79

 

[Swathi37]

Bhas79,

Thank you so much for your response. Exporting the header information has always been a problem. It is cumbersome to manually type the report headings in each and every worksheet in Excel. I contacted cognos support but all they say is it is not possible to export report to particular line in Excel.

Thank you once again.