Question regarding use of multiple prompts in a list

[Bren312]

Hello, this is my first post on this forum.  I've only been using Report Studio for a few weeks, so while I'm learning how to navigate in it I don't know a lot of the technical phrasing - basically, please be patient with me :)

My company uses 13 Fiscal Periods to make up a Fiscal Year; I'm trying to build a report which will show information for both a certain Fiscal Period as well as the YTD values.  For example, our new Fiscal Year started at the end of August and Fiscal Period 4 just ended in Mid-December, so I need a way to see Period 4 amounts as well as the YTD amounts since September.  I thought something where I'm prompting for the entire Fiscal Year (for YTD info) as well as a second prompt (for Per. 4) would be ideal; but I can't figure out how to make it work.   

A crosstab might work; but I'd rather not have all the Periods making up the YTD total listed as columns across the report.  Thanks all; any ideas are greatly appreciated!

Bren

[BigChris]

Welcome to the forum :)

The first thing I'd suggest would be to consider a calendar table. You can load it up with all of the dates in the year, and against each date you can load up columns for (e.g.) working day, elapsed working days in period, elapsed working days in fiscal year, fiscal year start date, fiscal period start date etc. etc. It sounds like a lot of work, but it's a once-a-year task and a lot of it can be automated in excel with formulae.

In terms of your specific query, I would imagine you can do it with a few of prompts. Essentially, you'd have you calculated fields in your query that look something like:

Code Select Expand

if([YourDateField] between ?prompt_Fiscal_Year_Start? and ?prompt_Fiscal_Year_End?) then ([YourValueField) else (0)

if([YourDateField] between ?prompt_Fiscal_Period_Start? and ?prompt_Fiscal_Period_End?) then ([YourValueField) else (0)

When you run the report you'll get prompted for four dates: fiscal period start and end date, fiscal year start and end dates.

[Invisi]

Firstly, does your date dimension support the fiscal periods in your company? Consider that if yes, it makes selecting a fiscal period and related data much easier.

Secondly, do you want to present the choice for either a fiscal period OR YTD? Of will a fiscal period and YTD be shown together? Your requirement it still vague for me (my bad).

[Lynn]

Is your source relational or dimensional? Reporting techniques can differ greatly depending on the type of source.

[BigChris]

Good point Lynn - I leapt straight in and assumed relational...really must learn to ask first

[Bren312]

Hi guys, thanks for the replies; I really appreciate them!  BigChris, I'll give your suggestions a try when I can (our system is down right now).

Invisi, yes; the date dimension supports the Fiscal Periods of the company.  The report I'm trying to recreate had dollar amount, count of transactions, and average dollar amount of transactions for a specific period (Period 4, say) on the left of the report and dollar amount, count of transactions, and average dollar amount YTD on the right side of the report.

Lynn, the source is relational. 

Thanks again guys!

[Lynn]

Ah, relational. In that case I think BigChris has given an answer that will work for you.

Welcome to Cognos and to Cognoise! The Cognoise Reporting FAQs and also the Forum Etiquette might be helpful to you if you haven't stumbled across them yet.

[Lynn]

Good point Lynn - I leapt straight in and assumed relational...really must learn to ask first

I wish I could remember all the things I need to remember!